




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

Improper Index Usage

Oracle Tips by Mike Ault

In the good old days Oracle followed the rule based optimizer (RBO) and the rule based optimizer followed essentially one simple premise from which it’s rules were devised:

INDEXES GOOD! FULL TABLE SCANS BAD!

Unfortunately this simple rule basis led to many less than optimal execution plans so SQL tuners spent a lot of time doing things such as null value concatenation or 0/1 math (add zero, or multiple by 1) to eliminate index usage. Of course now we have the cost based optimizer (CBO) which always gives us the correct path…not!

In essence we now have to look for full table scans and examine the table size, available indexes and other factors to determine if the CBO has made the proper choice. In most cases where improper full table scans are occurring I have generally found that missing or improper indexes were the cause, not the optimizer.

Pre-9i determining full table scans was either done live by looking for full table scan related waits and backtracking to the objects showing the waits (as shown in Figure 6) or by periodically stripping the SQL from the V$SQLTEXT or V$SQLAREA views and performing explain plan commands on them into a table. The table was then searched for the plans that showed full table accesses. Neither of these were particularly user friendly.

rem fts_rep.sql
rem FUNCTION: Full table scan report
rem MRA
rem
col sid format 99999
col owner format a15
col segment_name format a30
col td new_value rep_date noprint
select to_char(sysdate,'ddmonyyhh24mi') td from dual;
@title80 'Full Table Scans &rep_date'
spool rep_out\&db\fts_rep_&rep_date
SELECT DISTINCT A.SID,
C.OWNER,
C.SEGMENT_NAME
FROM SYS.V_$SESSION_WAIT A,
SYS.V_$DATAFILE B,
SYS.Remote DBA_EXTENTS C
WHERE A.P1 = B.FILE# AND
B.FILE# = C.FILE_ID AND
A.P2 BETWEEN C.BLOCK_ID AND
(C.BLOCK_ID + C.BLOCKS) AND
A.EVENT = 'db file scattered read';
spool off
ttitle off

Figure 6: Example Realtime report to obtain object undergoing full table scans

You will be happy to know that starting with Oracle9i there is a new view that keeps the explain plans for all current SQL in the shared pool, this view, appropriately named V$SQL_PLAN allows Remote DBAs to determine exactly what statements are using full table scans and more importantly how often the particular SQL statements are being executed. An example report against the V$SQL_PLAN table is shown in Figure 7.

rem fts report
rem based on V$SQL_PLAN table
col operation format a15
col object_name format a32
col object_owner format a15
col options format a20
col executions format 999,999,999
set pages 55 lines 132 trims on
@title132 'Full Table/Index Scans'
spool rep_out\&&db\fts
select a.object_owner,a.object_name, rtrim(a.operation) operation, a.options, b.executions from v$sql_plan a, v$sqlarea b
where
a.address=b.address
and a.operation IN ('TABLE ACCESS','INDEX')
and a.options in ('FULL','FULL SCAN','FAST FULL SCAN','SKIP SCAN','SAMPLE FAST FULL SCAN')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT')
group by object_owner,object_name, operation, options
order by object_owner, operation, options, object_name
/
spool off
set pages 20
ttitle off

Figure 7: Example SQL to get full table scan data from database

Notice that I didn’t limit myself to just full table scans, I also looked for expensive index scans as well. An example excerpt from this report is shown in Figure 8.

Date:11/09/04                                                                   Page: 1
Time: 08:31 PM                      Full Table/Index Scans                     PERFSTAT
                                      whoemail database

HASH_VALUE OWNER OBJECT_NAME OPERATION OPTIONS EXECUTIONS BYTES FTS_MEG
---------- ------ ------------------------- ------------- -------------- ---------- -------- -------4278137387 SDM DB_STATUS TABLE ACCESS FULL 30,303 1048576 30303

1977943106 SDM DB_STATUS TABLE ACCESS FULL 1,863 1048576 1863

3391889070 SDM FORWARD_RECIPIENTS TABLE ACCESS FULL 29,785 4194304 119140

1309516963 SDM FORWARD_RECIPIENTS TABLE ACCESS FULL 3,454 4194304 13816

4017881007 SDM GLOBAL_SUPPRESSION_LIST TABLE ACCESS FULL 168,020 1048576 168020

3707567343 SDM ORGANIZATION2 TABLE ACCESS FULL 6,008 1048576 6008

1705069780 SDM SP_CAMPAIGN_MAILING TABLE ACCESS FULL 1,306 10485760 13060

1047433976 SDM SS_LAST_SENT_JOB TABLE ACCESS FULL 572,896 1048576 572896

3556187438 SDM SS_LAST_SENT_JOB TABLE ACCESS FULL 572,896 1048576 572896

3207589632 SDM SS_SEND TABLE ACCESS FULL 32,275 20971520 645500

788044291 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 25,655 20971520 513100

1417625610 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 11,802 20971520 236040

2719565392 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 11,379 20971520 227580

1533235337 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 10,981 20971520 219620

3273441234 SDM SS_SENDJOBSTATUSTYPE_NNDX INDEX FAST FULL SCAN 10,594 20971520 211880

1823729862 SDM SS_TASK_OWNER_NNDX INDEX FULL SCAN 3,992 10485760 39920

3673286081 SDM SYSTEM_SEED_LIST TABLE ACCESS FULL 81,249 1048576 81249

4712038 SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN 11,159 62914560 669540

3797121012 SDM TRACK_OPEN_MRRJ_LOCK_NNDX INDEX FULL SCAN 11,159 62914560 669540

1624438202 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,786 31457280 893580

4232130615 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,786 31457280 893580

3109457251 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,785 31457280 893550

3391889070 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 29,785 31457280 893550

1309516963 SDM TRACK_RAW_OPEN TABLE ACCESS FULL 3,454 31457280 103620

3685251647 SDM TR_M_TOP_DOMAINS TABLE ACCESS FULL 5,925 10485760 59250

2318926907 SDM TR_R_CLICKSTREAM TABLE ACCESS FULL 1,155 10485760 11550

804017134 SDM TR_R_OPTOUT TABLE ACCESS FULL 2,375 10485760 23750

3707567343 SDM USER_INFO TABLE ACCESS FULL 6,008 10485760 60080

3328028760 SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN 43,265 1048576 43265

1150816681 SDM VIRTUAL_MTA_LOOKUP_PK INDEX FAST FULL SCAN 20,193 1048576 20193

Figure 8: Example output from FTS Report.

Notice instead of trying to capture the full SQL statement I just grab the HASH value. I can then use the hash value to pull the interesting SQL statements using SQL similar to:

select sql_text from v$sqltext where hash_value=&hash
order by piece;

Once I see the SQL statement I use SQL similar to this to pull the table indexes:

set lines 132
col index_name form a30
col table_name form a30
col column_name format a30
select a.table_name,a.index_name,a.column_name,b.index_type
from Remote DBA_ind_columns a, Remote DBA_indexes b
where a.table_name =upper('&tab')
and a.table_name=b.table_name
and a.index_owner=b.owner
and a.index_name=b.index_name
order by a.table_name,a.index_name,a.column_position
/
set lines 80

Once I have both the SQL and the indexes for the full scanned table I can usually quickly come to a tuning decision if any additional indexes are needed or, if an existing index should be used. In some cases there is an existing index that could be used of the SQL where rewritten. In that case I will usually suggest the SQL be rewritten. An example extract from a SQL analysis of this type is shown in Figure 9.

SQL> @get_it
Enter value for hash: 605795936

SQL_TEXT
----------------------------------------------------------------DELETE FROM BOUNCE WHERE UPDATED_TS < SYSDATE - 21

1 row selected.

SQL> @get_tab_ind
Enter value for tab: bounce
TABLE_NAME INDEX_NAME COLUMN_NAME INDEX_TYPE
------------ -------------------------- -------------- ----------
BOUNCE BOUNCE_MAILREPRECJOB_UNDX MAILING_ID NORMAL

BOUNCE BOUNCE_MAILREPRECJOB_UNDX RECIPIENT_ID NORMAL

BOUNCE BOUNCE_MAILREPRECJOB_UNDX JOB_ID NORMAL

BOUNCE BOUNCE_MAILREPRECJOB_UNDX REPORT_ID NORMAL

BOUNCE BOUNCE_PK MAILING_ID NORMAL

BOUNCE BOUNCE_PK RECIPIENT_ID NORMAL

BOUNCE BOUNCE_PK JOB_ID NORMAL

7 rows selected.

As you can see here there is no index on UPDATED_TS

SQL> @get_it
Enter value for hash: 3347592868

SQL_TEXT
----------------------------------------------------------------SELECT VERSION_TS, CURRENT_MAJOR, CURRENT_MINOR, CURRENT_BUILD,
CURRENT_URL, MINIMUM_MAJOR, MINIMUM_MINOR, MINIMUM_BUILD, MINIMU
M_URL, INSTALL_RA_PATH, HELP_RA_PATH FROM CURRENT_CLIENT_VERSION

4 rows selected.

Here there is no WHERE clause, hence a FTS is required.

SQL> @get_it
Enter value for hash: 4278137387

SQL_TEXT
----------------------------------------------------------------SELECT STATUS FROM DB_STATUS WHERE DB_NAME = 'ARCHIVE'

1 row selected.

SQL> @get_tab_ind
Enter value for tab: db_status

no rows selected

Yep, no indexes will cause a FTS everytime…

Figure 9: Example SQL Analysis

Of course even after you come up with a proposed index list you must thoroughly test them in a test environment as they may have other “side-effects” on other SQL statements, it would be a shame to improve the performance of one statement and shoot six others in the head.

SEE CODE DEPOT FOR FULL SCRIPTS