SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 210

In the tkprof (Transient Kernel Profiler utility) output from a 10046 event trace, which, it should be noted,
retrieves just 87 rows from the database, the SQL is processing a large number ( 6556373 ) of rows from the USER_LOGIN
table, and no index is being used to retrieve the data. Now, if an index is created on the USER_LOGIN table, the query
performance improves:
SQL> CREATE INDEX USRLI_INDX1 ON USER_LOGIN(USRLI_ACTIVE_STATUS_CD);
Index created.
Rows Row Source Operation
------- ---------------------------------------------------
487 SORT ORDER BY (cr=3176 r=66 w=66 time=346886 us)
487 TABLE ACCESS BY GLOBAL INDEX ROWID USER_SECURITY PARTITION: 1 1
(cr=3176 r=66 w=66 time=338109 us)
978 NESTED LOOPS (cr=3088 r=66 w=66 time=334551 us)
490 NESTED LOOPS (cr=2596 r=66 w=66 time=322337 us)
490 NESTED LOOPS (cr=1614 r=66 w=66 time=309393 us)
490 VIEW (cr=632 r=66 w=66 time=293827 us)
490 HASH JOIN (cr=632 r=66 w=66 time=292465 us)
56373 INDEX FAST FULL SCAN PK_USRLI PARTITION: 1 1
(cr=190 r=0 w=0 time=38776 us)(object id 24891)
490 INDEX FAST FULL SCAN USRLI_INDX1 (cr=442 r=0 w=0 time=56947 us)
(object id 24706)
490 TABLE ACCESS BY LOCAL INDEX ROWID ELOGEX_USER PARTITION: 1 1
(cr=982 r=0 w=0 time=12238 us)
490 INDEX UNIQUE SCAN PK_EUSR PARTITION: 1 1 (cr=492 r=0 w=0 time=6055 us)
(object id 24741)
490 TABLE ACCESS BY LOCAL INDEX ROWID COMPANY PARTITION: 1 1
(cr=982 r=0 w=0 time=10135 us)
490 INDEX UNIQUE SCAN PK_COMP PARTITION: 1 1 (cr=492 r=0 w=0 time=4905 us)
(object id 24813)
487 INDEX RANGE SCAN USEC_INDX1 (cr=492 r=0 w=0 time=9115 us)(object id 24694)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
487 SORT (ORDER BY)
487 TABLE ACCESS GOAL: ANALYZED (BY GLOBAL INDEX ROWID) OF
'USER_SECURITY' PARTITION: START=1 STOP=1
978 NESTED LOOPS
490 NESTED LOOPS
490 NESTED LOOPS
490 VIEW OF 'index$_join$_003'
490 HASH JOIN
56373 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_USRLI' (UNIQUE) PARTITION: START=1 STOP=1
490 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'USRLI_INDX1' (NON-UNIQUE)
490 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID)
OF 'ELOGEX_USER' PARTITION: START=1 STOP=1
490 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_EUSR'
(UNIQUE) PARTITION: START=1 STOP=1
490 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
'COMPANY' PARTITION: START=1 STOP=1