Oracle

Gather Fixed Object Statistics

Posted Anand

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 🙂

Leave a Reply