In a recent thread on OTN database general the following question was asked
What is the query to find the tables being accessed by the users?
I provided the suggestion to query v$access :-
select * from v$access where type=’TABLE’;
After providing the solution i just though of trying something on my test database.I opened a session ran the below query:-
session 1:-
02:25:33 SYS @ oracle >select distinct(type) from v$access;
The session seemed to have hanged so i opened a new session to look for the session wait :-
session 2:-
02:30:59 SYS @ oracle >@active_sess_Wait USERNAME SID SERIAL# SPID EVENT WAIT_TIME SECONDS_IN_WAIT STATE ------------------------------ ---------- ---------- ------------ -------------------------------------- ---------- --------------- ------------------- SYS 139 1507 1624 latch: library cache 15 0 WAITED KNOWN TIME After 5 mins 02:40:39 SYS @ oracle >@active_sess_Wait USERNAME SID SERIAL# SPID EVENT WAIT_TIME SECONDS_IN_WAIT STATE ------------ ----- --------- ------------ ---------------------------------------- --------- ----------------- ----------------- SYS 139 1507 1624 latch: library cache -1 136 WAITED SHORT TIME
What does the explain plan of the query say??
session 2:-
02:45:43 SYS @ oracle >explain plan into plan_table for select distinct(type) from v$access; Explained. Elapsed: 00:00:01.00 02:47:22 SYS @ oracle >select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------- Plan hash value: 4228135978 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 105 | 10710 | 1 (100)| 00:00:01 | | 1 | HASH UNIQUE | | 105 | 10710 | 1 (100)| 00:00:01 | | 2 | NESTED LOOPS | | 105 | 10710 | 0 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 10 | 720 | 0 (0)| 00:00:01 | | 4 | MERGE JOIN CARTESIAN | | 100 | 5100 | 0 (0)| 00:00:01 | |* 5 | FIXED TABLE FULL | X$KSUSE | 1 | 17 | 0 (0)| 00:00:01 | | 6 | BUFFER SORT | | 100 | 3400 | 0 (0)| 00:00:01 | | 7 | FIXED TABLE FULL | X$KGLDP | 100 | 3400 | 0 (0)| 00:00:01 | |* 8 | FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) | 1 | 21 | 0 (0)| 00:00:01 | |* 9 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 10 | 300 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("S"."INST_ID"=USERENV('INSTANCE')) 8 - filter("L"."KGLLKUSE"="S"."ADDR" AND "L"."KGLLKHDL"="D"."KGLHDADR" AND "L"."KGLNAHSH"="D"."KGLNAHSH") 9 - filter("O"."KGLNAHSH"="D"."KGLRFHSH" AND "O"."KGLHDADR"="D"."KGLRFHDL") 24 rows selected.
MERGE JOIN CARTESIAN wasn’t looking good to me in the explain plan.I killed the session 1 and checked for the statistics on the fixed objects :-
session 2:- 02:48:14 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KSUSE'); no rows selected Elapsed: 00:00:00.31 02:48:40 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KGLDP'); no rows selected Elapsed: 00:00:00.04 02:49:06 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KGLLK'); no rows selected Elapsed: 00:00:00.04
After looking at the above output i immediately gathered the statistics on the fixed objects :-
session 2:-
02:49:28 SYS @ oracle >exec dbms_stats.gather_fixed_objects_stats; PL/SQL procedure successfully completed. Elapsed: 00:01:48.40 02:51:35 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KGLDP'); ROWCNT BLKCNT ANALYZETI SAMPLESIZE ---------- ---------- --------- ---------- 6639 0 25-DEC-09 6639 Elapsed: 00:00:00.21 02:52:27 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KSUSE'); ROWCNT BLKCNT ANALYZETI SAMPLESIZE ---------- ---------- --------- ---------- 170 0 25-DEC-09 170 Elapsed: 00:00:00.06 02:52:42 SYS @ oracle >select rowcnt, blkcnt, analyzetime, samplesize from tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KGLLK'); ROWCNT BLKCNT ANALYZETI SAMPLESIZE ---------- ---------- --------- ---------- 1110 0 25-DEC-09 1110 Elapsed: 00:00:00.07
Lets check the explain plan back again:-
02:52:52 SYS @ oracle >explain plan into plan_table for select distinct(type) from v$access; Explained. Elapsed: 00:00:00.09 02:53:01 SYS @ oracle >select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 4293798117 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 28 | 1792 | 4 (100)| 00:00:01 | | 1 | HASH UNIQUE | | 28 | 1792 | 4 (100)| 00:00:01 | | 2 | NESTED LOOPS | | 1110 | 71040 | 3 (100)| 00:00:01 | | 3 | NESTED LOOPS | | 1110 | 54390 | 2 (100)| 00:00:01 | | 4 | HASH JOIN | | 1110 | 27750 | 1 (100)| 00:00:01 | | 5 | FIXED TABLE FULL | X$KSUSE | 170 | 1360 | 0 (0)| 00:00:01 | | 6 | FIXED TABLE FULL | X$KGLLK | 1110 | 18870 | 0 (0)| 00:00:01 | | 7 | FIXED TABLE FIXED INDEX| X$KGLDP (ind:1) | 1 | 24 | 0 (0)| 00:00:01 | | 8 | FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) | 1 | 15 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 15 rows selected. Elapsed: 00:00:01.09
” MERGE JOIN CARTESIAN ” has disappeared from the explain plan.Now after gathering fixed object statistics will i be able to get the query output or will it hang again.
session 3:-
02:53:33 SYS @ oracle >select distinct(type) from v$access; TYPE ------------------------ PACKAGE LIBRARY NON-EXISTENT TRIGGER TABLE VIEW SYNONYM CURSOR TYPE 9 rows selected. Elapsed: 00:00:00.65
Look at the elapsed time.Amazing, gathering statistics on the fixed objects worked 🙂