SQL> alter session set sql_trace = true
2 /
Session altered.
SQL>
Then I created a table, added lots of data to it and counted the rows:
SQL> create table andrews_table
2 storage (maxextents unlimited)
3 as select * from dba_tables
4 /
Table created.
SQL> begin
2 for a in 1..9 loop
3 insert into andrews_table
4 select * from andrews_table;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from andrews_table
2 /
COUNT(*)
----------
1074688
SQL>
Then I counted the number of rows with SYSTEM as the owner. The percentage was quite small so using an index to access them should be better than doing a full table scan:
SQL> select owner, count(*)
2 from andrews_table
3 where owner = 'SYSTEM'
4 group by owner
5 /
OWNER COUNT(*)
------------------------------ ----------
SYSTEM 75264
SQL>
Next I created an index, analyzed the table and checked that both the table and the index had been analyzed:
SQL> create index andrews_index
2 on andrews_table(owner)
3 /
Index created.
SQL> analyze table andrews_table
2 compute statistics
3 /
Table analyzed.
SQL> select last_analyzed from user_tables
2 where table_name = 'ANDREWS_TABLE'
3 /
LAST_ANAL
---------
09-MAY-12
SQL> select last_analyzed from user_indexes
2 where index_name = 'ANDREWS_INDEX'
3 /
LAST_ANAL
---------
09-MAY-12
SQL>
Then I queried the rows with SYSTEM as the owner. Sometimes when I run tests like this, I give Oracle a perfectly good index to use but it does a full table scan instead and I never know why so I added a hint to make sure that Oracle used the index:
SQL> select /*+ index(andrews_table andrews_index) */
2 count(*) after_analyze from andrews_table
3 where owner = 'SYSTEM'
4 and pct_used = 40
5 /
AFTER_ANALYZE
-------------
50176
SQL> alter session set sql_trace = false
2 /
Session altered.
SQL>
The next step was to repeat the test using dbms_stats instead:
SQL> alter session set sql_trace = true
2 /
Session altered.
SQL> create table andrews_table
2 storage (maxextents unlimited)
3 as select * from dba_tables
4 /
Table created.
SQL> begin
2 for a in 1..9 loop
3 insert into andrews_table
4 select * from andrews_table;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from andrews_table
2 /
COUNT(*)
----------
1074688
SQL> select owner, count(*)
2 from andrews_table
3 where owner = 'SYSTEM'
4 group by owner
5 /
OWNER COUNT(*)
------------------------------ ----------
SYSTEM 75264
SQL> create index andrews_index
2 on andrews_table(owner)
3 /
Index created.
SQL> exec dbms_stats.gather_table_stats -
> (ownname=>'oracle', -
> tabname=>'andrews_table', -
> cascade=>true);
PL/SQL procedure successfully completed.
SQL> select last_analyzed from user_tables
2 where table_name = 'ANDREWS_TABLE'
3 /
LAST_ANAL
---------
09-MAY-12
SQL> select last_analyzed from user_indexes
2 where index_name = 'ANDREWS_INDEX'
3 /
LAST_ANAL
---------
09-MAY-12
SQL> select /*+ index(andrews_table andrews_index) */
2 count(*) after_dbms_stats from andrews_table
3 where owner = 'SYSTEM'
4 and pct_used = 40
5 /
AFTER_DBMS_STATS
----------------
50176
SQL> alter session set sql_trace = false
2 /
Session altered.
SQL>
Finally I ran the trace file through tkprof and compared the results. CPU time, elapsed time, disk reads and buffer gets were all quite a bit less when dbms_stats was used to collect statistics:
********************************************************************************
select /*+ index(andrews_table andrews_index) */
count(*) after_analyze from andrews_table
where owner = 'SYSTEM'
and pct_used = 40
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.54 31.74 12696 13209 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.55 31.75 12696 13209 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 32 (ORACLE)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=13209 pr=12696 pw=0 time=31745246 us)
50176 TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=13209 pr=12696 pw=0 time=43170772 us)
75264 INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=191 pw=0 time=633026 us)(object id 120507)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
50176 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ANDREWS_TABLE' (TABLE)
75264 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'
(INDEX)
********************************************************************************
select /*+ index(andrews_table andrews_index) */
count(*) after_dbms_stats from andrews_table
where owner = 'SYSTEM'
and pct_used = 40
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.12 24.39 11777 12711 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.12 24.39 11777 12711 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 32 (ORACLE)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=12711 pr=11777 pw=0 time=24395897 us)
50176 TABLE ACCESS BY INDEX ROWID ANDREWS_TABLE (cr=12711 pr=11777 pw=0 time=26819905 us)
75264 INDEX RANGE SCAN ANDREWS_INDEX (cr=192 pr=134 pw=0 time=378268 us)(object id 120510)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
50176 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ANDREWS_TABLE' (TABLE)
75264 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ANDREWS_INDEX'
(INDEX)
********************************************************************************
No comments:
Post a Comment