I recently gave a webcast about using indexes to boost your application performance, of course at the end of the webcast we took questions, unfortunately I wasn’t able to anser all of the questions so I pulled the ones I felt were most important and decided to answer them here in my blog. For the full webcast see :
index webcastHere are the questions from the webcast:
Jayesh (AFL) Asked: if you have high db file sequential read and db file scattered read does that indicate lack of index?
They aren’t related, you can have both on the same database/table
- DB_file_sequential_read indicates index reads
- Db_file_scattered_read indicates full table scans
If you look at the individual SQL and you have high db_file_scattered_reads, or a full table scan in indicated in v$sql_plan then full table/index scans are occurring when the SQL runs. If the reads are due to a full table scan, then the table may be an indexing candidate.
haj Asked: how do you know you've too many indexes on your table? and how do you know whether those indexes are used?
Too many indexes will show up as excessive insert, update or delete times, high db_file_sequential_reads during (Insert, Update and Delete) IUD, large numbers of physical reads/writes during IUD. You can review the v$object_usage VPD to determine if an object has been used. The v$sql_plan table will show if the index is being used in your current execution plans.
Michael Sew Asked: index monitoring only shows a boolean, whether it has been 'ever' used, doesn't it? not number of uses or frequency of use.
Sorry, you are correct, the earlier versions had accumulators as I recall, the latest versions don’t.
SQL> desc v$object_usage
Name Null? Type
----------------------------------------- -------- -------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
JayR Asked: Any performance proactive measures/procedures can be taken for Oracle domain indexes?
The biggest thing with domain indexes is memory, according to Oracle’s tuning guide:
“You can set your index memory with the system parameters DEFAULT_INDEX_MEMORYand MAX_INDEX_MEMORY. You can also set your index memory at run time with the CREATE INDEXmemoryparameter in the parameter string.
You should aim to set the DEFAULT_INDEX_MEMORYvalue as high as possible, without causing paging.
You can also improve Indexing performance by increasing the SORT_AREA_SIZEsystem parameter.
Experience has shown that using a large index memory setting, even into hundreds of megabytes, will improve the speed of indexing and reduce the fragmentation of the final indexes. However, if set too high, then the memory paging that occurs will cripple indexing speed.
With parallel indexing, each stream requires its own index memory. When dealing with very large tables, you can tune your database system global area (SGA) differently for indexing and retrieval. For querying, you are hoping to get as much information cached in the system global area's (SGA) block buffer cache as possible. So you should be allocating a large amount of memory to the block buffer cache. But this will not make any difference to indexing, so you would be better off reducing the size of the SGA to make more room for a large index memory settings during indexing.”
Steve Asked: When mentioning proper storage parameter can you give examples of what proper means?
The three parameters that affect index storage performance the most are pctfree, initrans and block size. The pctfree parameter determines how much space the index reserves fro updates to index values. If pctfree is insufficient then block splitting can occur. The initrans parameter determines ITL list structure, if there are insufficient ITL spots in a block due to the block being full, then any transaction needing an ITL slot will serialize and cause performance issues. Block size determines index density, for non-RAC installation the larger the block for a B-tree the better storage efficiency you get. For bitmap indexes that lock at the node level, a larger block can result in more locking issues.
By ensuring you have enough pctfree to handle updates and initrans to handle transactions you can eliminate many of the wait situations. In later Oracle releases these parameters have been automated to some extent. The problem with monotonically increasing index values combined with large deletes causing index “white space” usually cannot be fixed with storage parameters.
RobC Asked: Can multiple bitmap join indexes be created between two tables?
No, since a bitmap join index goes between the foreign key columns and primary key of the related table it cannot have more than one. That being said, if you have surrogate keys with unique constraints against them, in addition to the PK columns, you might be able to create a bitmap join between that column and another table, however, that would be a non-3rd normal form condition, and I have not tested that possibility.
Cindy Ullery Asked: Does a large # of full table scan create busy buffer waits.
Only if the system has memory issues as well, in that case the full table scans tie up buffers that should be used by other processes causing buffer busy waits.
Lily Asked: How to query V$SQL_PLAN to review the plan? What are the most important columns you look at?
I use:
select
a.hash_value,a.object_owner,a.object_name, rtrim(a.operation) operation,
a.options, sum(b.executions) executions, c.bytes,
(sum(b.executions)*c.bytes)/(1024*1024) fts_meg
from
v$sql_Plan a, v$sqlarea b, dba_segments c
where
(a.object_owner=c.owner
and a.object_name=c.segment_name)
and a.address=b.address
and a.operation IN ('TABLE ACCESS')
and nvl(a.options,'NULL') in ('FULL','NULL')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
--and b.executions>1
group by a.hash_value,a.object_owner, a.object_name, operation, options, c.bytes
order by a.object_owner,a.object_name,operation, options, executions desc
/
Bob Asked: Would you recommend dropping an index prior to loading it, then re-creating it after the load or setting the index to unusable prior to the load, then just rebuild it?
I would test both scenarios as it would depend on the percentage of the index entries being added, deleted or updated.
Sanjay Makwana Asked: What is impact of optimizer_index_cost_adj on explain plan?
It only affects full index scan type access paths. However it can be used to increase the likelihood of index scans or, decrease them depending on its setting.
Todd Adams Changes Question To: in your research, have you found a soft limit on number of indexes before performance is effected? also in addition, how about the number of columns in an index before it is effected.
Here is a quick test:
It isn’t the number of columns it is the number of IOs needed to support the indexes.
antony Changes Question To: using bind variable of a SQL chooses different index when the bind variable value changed. why?
This could be caused by skewed data with the use of histograms. With bind variable peaking the path can change based on the histograms telling the optimizer it is better to use a full table scan due to the amount of data being returned.
Li Mei Lin Changes Question To: when upgrading from 9i to 10g, do we need to change the indexes?
No.
antony Changes Question To: how column order of an index affects the plan?
Let’s look at an example:
SQL> create index test2 on tables (table_name,tablespace_name);
Index created.
SQL> select table_name,status from tables where table_name='TABLES'
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3115918026
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost(%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TABLES | 1 | 23 | 3 (0)| 00:00:01 |
| * 2 | INDEX RANGE SCAN | TEST2 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TABLES')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
335 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> drop index test2;
Index dropped.
SQL> create index test2 on tables (tablespace_name,table_name);
Index created.
SQL> select table_name,status from tables where table_name='TABLES'
2 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 624263108
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 7 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TABLES | 1 | 23 | 7 (0) | 00:00:01 |
| * 2 | INDEX SKIP SCAN | TEST2 | 1 | | 6 (0) | 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TABLES')
filter("TABLE_NAME"='TABLES')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
6 physical reads
0 redo size
335 bytes sent via SQL*Net to client
370 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
So the skip scan was a more expensive operation than the direct scan, in this case by a factor of over 100 percent, simply by reversing the order of the index we get better performance.
Bryan Marsh Asked: Are artificial keys a bad index strategy for OLTP systems?
There is a constant debate about the use of artificial (i.e. sequences) keys verses use of “natural” keys. If a “reasonable” natural key exists, use it, however, if the “natural” key results in an unwieldy monstrosity of a character based composite key, then use the artificial key. You don’t want to have to carry over a three column, 250 character three column key into multiple related tables if a single integer value artificial key will work just as well.
Milan Nikolic Asked: What is typical performance overhead of index monitoring, in your experience?
Not measurable.
Gia Huynh Asked: A design question: Should I create an index on a customer address column defined as varchar 255 for online look up?
It may be advisable to look at deriving a signature on the varchar then use the signature for lookup if it is a full lookup scenario such as address matching. If individual components of the 255 character field need to be searched, look at using domain indexes instead.
Fuad Arshad pointed out that the requirements for using Cost based optimizer and the query rewrite parameters may be incorrect for use of function based indexes, so test in your environment if you wish to use them. In fact they are probably usable under rule based optimization.
Deepak Asked: Quest optimizer has a way to monitor, identify and analyze current performance of indexes and does it advice how to build new indexes (scripts for new sets of indexes) to achieve optimized performance?
Answered: Quest SQL Optimizer has a module to recommend new indexes along with analysis of the execution plan on additional SQL's of creating that index
The tools used during the webcast included:
- Quest Performance Analysis for Oracle
- Quest SQL Optimizer for Oracle
- Quest Benchmark Factory