Most discriminating Entries First

rajesh@10GR2> create table t as select * from all_objects;

Table created.

Elapsed: 00:00:02.31
rajesh@10GR2>
rajesh@10GR2> create index t_ind1 on t(owner,object_type,object_name) nologging;

Index created.

Elapsed: 00:00:00.67
rajesh@10GR2> create index t_ind2 on t(object_name,object_type,owner) nologging;

Index created.

Elapsed: 00:00:00.76
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.75
rajesh@10GR2>
rajesh@10GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.01
rajesh@10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> DECLARE
  2     l_cnt number;
  3  BEGIN
  4     FOR r IN (SELECT owner,object_type,object_name FROM all_objects)
  5     LOOP
  6             SELECT /*+ INDEX(T,T_IND1) */ COUNT(*)
  7             INTO l_cnt
  8             FROM T
  9             WHERE owner =r.owner
 10             AND object_type =r.object_type
 11             AND object_name =r.object_name;
 12
 13             SELECT /*+ INDEX(T,T_IND2) */ COUNT(*)
 14             INTO l_cnt
 15             FROM T
 16             WHERE owner =r.owner
 17             AND object_type =r.object_type
 18             AND object_name =r.object_name;
 19     END LOOP;
 20  END;
 21  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.17
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select rtrim(c.value,'/') || '/' || d.instance_name ||
  2  '_ora_' || ltrim(to_char(a.spid)) || '.trc'
  3  from v$process a, v$session b, v$parameter c, v$instance d
  4  where a.addr = b.paddr
  5  and b.audsid = sys_context( 'userenv', 'sessionid')
  6  and c.name = 'user_dump_dest';

RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'
-----------------------------------------------------------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.1.0\ADMIN\IRADSDB\UDUMP/iradsdb_ora_1260.trc

Elapsed: 00:00:00.14
********************************************************************************

SELECT /*+ INDEX(T,T_IND1) */ COUNT(*)
FROM
 T WHERE OWNER =:B3 AND OBJECT_TYPE =:B2 AND OBJECT_NAME =:B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  56283     13.57      13.40          0          0          0           0
Fetch    56283      1.43       1.22          0     175267          0       56283
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   112567     15.01      14.63          0     175267          0       56283

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  56283  SORT AGGREGATE (cr=175267 pr=0 pw=0 time=1541025 us)
 876837   INDEX RANGE SCAN T_IND1 (cr=175267 pr=0 pw=0 time=1613316 us)(object id 139785)

********************************************************************************

SELECT /*+ INDEX(T,T_IND2) */ COUNT(*)
FROM
 T WHERE OWNER =:B3 AND OBJECT_TYPE =:B2 AND OBJECT_NAME =:B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  56283     13.06      13.37          0          0          0           0
Fetch    56283      1.50       1.17          1     174946          0       56283
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   112567     14.56      14.54          1     174946          0       56283

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  56283  SORT AGGREGATE (cr=174946 pr=1 pw=0 time=1477390 us)
 876837   INDEX RANGE SCAN T_IND2 (cr=174946 pr=1 pw=0 time=1574236 us)(object id 139786)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
********************************************************************************