Index monitoring usage

Index monitoring usage

A. Hr 스키마내 employees 테이블에 지정된 모든 인덱스에 대한 정보를 확인하는데 인덱스 이름, 컬럼이름, 순서를 조회하시오

SQL> conn hr/hr Connected. SQL> col column_name for a20 SQL> select index_name, column_name, column_position from user_ind_columns where table_name = 'EMPLOYEES' order by 1; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------------- ---------------------- EMP_DEPARTMENT_IX DEPARTMENT_ID 1 EMP_EMAIL_UK EMAIL 1 EMP_EMP_ID_PK EMPLOYEE_ID 1 EMP_EMP_ID_SAL_IX SALARY 2 EMP_EMP_ID_SAL_IX EMPLOYEE_ID 1 EMP_JOB_IX JOB_ID 1 EMP_MANAGER_IX MANAGER_ID 1 EMP_NAME_IX LAST_NAME 1 EMP_NAME_IX FIRST_NAME 2 9 rows selected.

B. Hr.employees 테이블의 인덱스 중 last_name, first_name에 지정된 인덱스에 대해서 monitoring usage 기능을 활성화하시오

인덱스 찾기 : ALL_IND_COLUMNS SQL> select * from ALL_ind_columns where table_name = 'EMPLOYEES' ORDER BY INDEX_NAME; SQL> alter index hr.EMP_NAME_IX monitoring usage; Index altered. SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage; INDEX_NAME START_MONITORING END_MONITORING USE ------------------------------ ------------------------------- ---------------------------- ---------- EMP_NAME_IX 01/02/2013 14:48:47 NO

C. 다음의 쿼리문을 수행하시오

Select * from employees where last_name = 'Whalen'; Select * from employees where first_name = 'Susan';

D. B에서 지정한 monitoring usage를 비활성화하시오

SQL> alter index EMP_NAME_IX nomonitoring usage; Index altered.

E. 인덱스 사용에 대한 정보를 조회하시오. 이 때 hr 세션에서 조회하시오. 만약 sys에서 조회하면 결과는? 이유는? 해결방법은?

① 인덱스 사용에 대한 정보를 조회 SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage; INDEX_NAME START_MONITORING END_MONITORING USE ------------------------------ ------------------- ------------------- --- EMP_NAME_IX 01/02/2013 14:48:47 01/02/2013 20:31:52 YES ② Sys에서 조회 SQL> conn /as sysdba Connected. SQL> select index_name, start_monitoring, end_monitoring, used from v$object_usage; no rows selected è 데이터가 조회되지 않는다. SQL> set long 30000 SQL> select text from dba_views where view_name = upper('v$object_usage'); TEXT -------------------------------------------------------------------------------- select io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring, ou.end_monitoring from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou where io.owner# = userenv('SCHEMAID') and i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo# è 조회되지 않음. 이유는 v$object_usage는 userenv('SCHEMAID')로 조회하는데 index의 owner와 userenv('SCHEMAID')는 다르기 때문 è 해결하기 위해 새로운 view 생성 SQL> create view V$ALL_OBJECT_USAGE (owner, INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING) as select u.name, io.name, t.name, decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), ou.start_monitoring, ou.end_monitoring from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo# and io.owner# = u.user#; View created. SQL> select * from v$all_object_usage; OWNER INDEX_NAME TABLE_NAME MONUSE START_MONITORING END_MONITORING --------- ---------------- -------------- -------------- -------------------- ------------------- HR EMP_NAME_IX EMPLOYEES NO YES 01/02/2013 14:48:47 01/02/2013 20:31:52 ③ hr에서 조회 SQL> conn hr/hr Connected. SQL> select * from v$all_object_usage; select * from v$all_object_usage * ERROR at line 1: ORA-00942: table or view does not exist SQL> conn /as sysdba Connected. SQL> grant select on sys.v$all_object_usage to public; Grant succeeded. SQL> create public synonym v$all_object_usage for sys.v$all_object_usage; Synonym created. SQL> conn hr/hr Connected. SQL> select * from v$all_object_usage; OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------ ------------------- ------------------------------ --- --- ------------------- ------------------ HR EMP_NAME_IX EMPLOYEES NO YES 01/02/2013 14:48:47 01/02/2013 20:31:52 è hr에서도 v$all_object_usage로 조회 가능