James
A reader, September 25, 2001 - 8:46 am UTC
Good Stuff... another addition to my bag of tricks
Kamal Kishore, May 26, 2003 - 9:49 am UTC
Hi Tom,
Good Info. Above you said:
"Not on 8.0.6 -- not easily anyway. In 8i, we can use stored outlines as a trick
to get this information and in 9i, there will be a built in piece of functionality."
Can you elaborate how this would be easier to figure out with Oracle9i?
Thanks,
May 26, 2003 - 10:03 am UTC
you can enable index monitoring.
start with our sample schema. We want to see if the T_PK index is used
for any queries
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int,
2 constraint t_pk primary key(x) );
Table created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t
2 select rownum
3 from all_objects
4 where rownum <= 100
5 /
100 rows created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
We'll enable monitoring on the index now
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
no rows selected
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter index t_pk monitoring usage
2 /
Index altered.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
T_PK YES NO 05/26/2003 10:06:32
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
Using RBO as we are by default, a count(*) won't use the index
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from t;
COUNT(*)
----------
100
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
T_PK YES NO 05/26/2003 10:06:32
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
An insert won't either
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into t values (-1);
1 row created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
T_PK YES NO 05/26/2003 10:06:32
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
But if we analyze the table, a count(*) will use the index
and we would have a record of it
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze table t compute statistics
2 /
Table analyzed.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on explain
ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from t;
COUNT(*)
----------
101
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=101)
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ---- ------------------- -------------------
T_PK YES YES 05/26/2003 10:06:32
Excellent!
Kamal Kishore, May 26, 2003 - 11:45 am UTC
Just the kind of stuff I needed.
Thanks,
Great!
Reader, May 26, 2003 - 12:56 pm UTC
Tom, does it mean that if I already have created a concatenated index on columns say a,b,c then i don't need to have an index created on col a, or b or c separately. In other words, could you please give me a guidance when should I create a concatenated index? My understanding is that if i use a column frequently in where clause, i should create an index on that column. Should i create a concatenated index if i use all of the columns in my where clause with and, or or conditions? could you clarify? Thanks.
May 26, 2003 - 1:54 pm UTC
if you have an index on a,b,c, you probably do not need ones on a,b or a.
You might need one on B, or B,C, or C, or C,B -- but only you know the answer to that.
How to get it in Oracle 8i.
Saibabu Devabhaktuni, August 21, 2003 - 1:45 pm UTC
Tom,
Could you please explain how to know which Indexes are being used by application in Oracle 8.1.7.4.
We are currently doing explain plan on all sql statements in v$sql and get Index usage info from plan_table, though not accurate, this approach provide very good estimate. Do you agree?
Thanks
August 21, 2003 - 7:11 pm UTC
i concur, "not 100% accurate, but good estimate"
Re: How to get it in Oracle 8i.
Saibabu devabhaktuni, August 21, 2003 - 8:44 pm UTC
Tom,
Sorry to bug you. You said, we can get which Indexes are being used in Oracle 8i using stored outlines. Could you please explain it.
Thanks a lot.
August 22, 2003 - 8:22 am UTC
if you have my book "Expert one on one Oracle" -- i have all of the gory details in there but in short.
stored query outlines basically saves a really detailed set of hints into a database table for queries that are run on your system.
you can enable the saving of these stored query outlines.
you would run your applications. the first time a query is found, we save the hints.
these hints include "use this index" type hints.
you have a table you can now query to see what queries use which indexes.
For 9i
A reader, August 21, 2003 - 9:00 pm UTC
Can you please explain your Statement :
in 9i, there will be a built in piece of
functionality.
For 9.2
August 22, 2003 - 8:24 am UTC
in 9i, you can monitor indexes.
alter index t_pk monitoring usage
/
and then query v$object_usage after a while to see if it was used or not.
Foreign Key-Refernces/Dripping Indexes
Orca777, August 22, 2003 - 8:32 am UTC
Under 9i you must be carefull dropping indexes which lay on columns of a table needed for Foreign-Key references.
Is this right tom? ;-)
Orca
August 22, 2003 - 9:02 am UTC
unindexed foreign keys may cause locking/contention issues in all releases of Oracle, yes.
Overhead
A.Titievsky, August 22, 2003 - 11:14 am UTC
Is there a significant overhead when monitoring multiple indexes?
August 22, 2003 - 7:36 pm UTC
not that I can measure, no
Index monitoring
A reader, October 15, 2003 - 1:39 pm UTC
Tom
I need to determine whether particular indexes on a table are being used for querying at all.
How can I set up this monitoring i.e. what should be done to set up this monitoring.
After it is monitored how can we decide whether it is being used frequently or not ?
Please reply.
October 15, 2003 - 6:39 pm UTC
there is a complete demo of index monitoring and how to enable it above.
as for "frequently" it is a binary thing -- we use it or we do not. we do not count "how much"
For the above post..
A reader, October 15, 2003 - 1:40 pm UTC
our database is 8i and not 9i , as such the above solution might not work.
Can you answer being specific to 8i..
October 15, 2003 - 6:49 pm UTC
nope, you cannot do this reliably in 8i, feature did not exist.
If you have access to my book "expert one on one oracle", you can look at a possible way to abuse query plan stability to capture some of this information.
Slight modification
Jeff Fulton, October 16, 2003 - 5:06 am UTC
Hi Tom,
The query above is great, but unfortunately it will report similar indexes that are on different tables. I've modified it slightly to include the table name and only report similar indexes if the table name is the same:
SELECT *
FROM
( SELECT table_name,
index_name,
MAX(DECODE(p, 1, c,NULL)) ||
MAX(DECODE(p, 2,', '||c,NULL)) ||
MAX(DECODE(p, 3,', '||c,NULL)) ||
MAX(DECODE(p, 4,', '||c,NULL)) ||
MAX(DECODE(p, 5,', '||c,NULL)) ||
MAX(DECODE(p, 6,', '||c,NULL)) ||
MAX(DECODE(p, 7,', '||c,NULL)) ||
MAX(DECODE(p, 8,', '||c,NULL)) index_cols
FROM (SELECT table_name, index_name, SUBSTR(column_name,1,30) c, column_position p
FROM USER_IND_COLUMNS )
GROUP BY table_name, index_name ) A,
( SELECT table_name,
index_name,
MAX(DECODE(p, 1, c,NULL)) ||
MAX(DECODE(p, 2,', '||c,NULL)) ||
MAX(DECODE(p, 3,', '||c,NULL)) ||
MAX(DECODE(p, 4,', '||c,NULL)) ||
MAX(DECODE(p, 5,', '||c,NULL)) ||
MAX(DECODE(p, 6,', '||c,NULL)) ||
MAX(DECODE(p, 7,', '||c,NULL)) ||
MAX(DECODE(p, 8,', '||c,NULL)) index_cols
FROM (SELECT table_name, index_name, SUBSTR(column_name,1,30) c, column_position p
FROM USER_IND_COLUMNS )
GROUP BY table_name, index_name ) B
WHERE a.table_name = b.table_name
AND a.index_name <> b.index_name
AND a.index_cols LIKE b.index_cols || '%'
A good estimate for 8i
Jeff, March 31, 2004 - 10:48 am UTC
Might be to explain all the queries in v$sqlarea? Would this be a bad idea?
March 31, 2004 - 11:26 am UTC
explain plan might not return the same plan as was really used.
bind variables, session settings all affect it.
You can do that (it would kill the system, pretty expensive and would flood the shared pool) and it would be almost accurate.
capturing the query plans with query plan stability gets you the real info.
Index Usage for Constraints
Kevin, February 22, 2005 - 5:32 pm UTC
Tom,
I ran the following on my system to check out the index usage logging:
09:12:04 USERNAME:sid@machine> drop table test;
Table dropped.
real: 93
09:14:09 USERNAME:sid@machine> create table test (col1 number, col2 number);
Table created.
real: 78
09:14:09 USERNAME:sid@machine> create unique index test_index1 on test (col1);
Index created.
real: 78
09:14:09 USERNAME:sid@machine> create index test_index2 on test(col2);
Index created.
real: 31
09:14:09 USERNAME:sid@machine> insert into test values(1,2);
1 row created.
real: 79
09:14:09 USERNAME:sid@machine> alter index test_index1 monitoring usage;
Index altered.
real: 63
09:14:09 USERNAME:sid@machine> alter index test_index2 monitoring usage;
Index altered.
real: 47
09:14:09 USERNAME:sid@machine> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
TEST_INDEX1 TEST YES NO 02/23/2005 09:16:02
TEST_INDEX2 TEST YES NO 02/23/2005 09:16:02
real: 125
09:14:09 USERNAME:sid@machine> insert into test values(1,2);
insert into test values(1,2)
*
ERROR at line 1:
ORA-00001: unique constraint (USERNAME.TEST_INDEX1) violated
real: 109
09:14:09 USERNAME:sid@machine> select * from test where col2 = 5;
no rows selected
real: 31
09:14:09 USERNAME:sid@machine> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
TEST_INDEX1 TEST YES NO 02/23/2005 09:16:02
TEST_INDEX2 TEST YES YES 02/23/2005 09:16:02
real: 94
09:14:10 USERNAME:sid@machine>
and so concluded that v$object_usage will be good to identify those unused non-unique indexes that can be dropped but since constraint testing isn't logged this mechanism it isn't an effective test for unique indexes.
I can't see an easy solution to identifying unused Unique indexes to be dropped short of wading through the code and seeing if the application relies on the uniqueness to prevent duplicates.
Any thoughts?
February 23, 2005 - 1:46 am UTC
you would not be able to drop an index used for constraint enforcement.
a UNIQUE index is by definition *not unused*, it is used for each and every insert and update.
Your documentation should tell you if "uniqueness" is needed. The very presence of a unique index should indicate that it is needed.
Uniqueness and the optimiser
Kevin, February 24, 2005 - 6:54 pm UTC
Oh to have documentation like that!
Following on from your point that 'the very presence of a unique index tells us it is needed'. In a situation where you are creating an index on a number of columns that you know are unique does the optimiser benefit from actually specifying it in the create or is there a performance benefit if it knows that a given column combination will return only one row? I suppose ultimately I'm asking if there is any benefit to specifying UNIQUE on an index that is unique but which has that uniqueness guaranteed though other processes?
February 25, 2005 - 1:48 am UTC
the optimizer has more information -- it knows "unique" if you say unique.
So Unique indexes do affect performance as well
Kevin, February 27, 2005 - 5:28 pm UTC
Thanks for that. After the beginning of this discussion I was starting to think that perhaps the only unique indexes I would define would be those required to enforce uniqueness but after your feedback tested the performance of a unique vs non-unique index with the following:
set autotrace off
drop table temp;
create table temp (col1 varchar2(128), col2 number, col3 number);
create index temp_idx1 on temp (col2);
create unique index temp_idx2 on temp(col3);
insert into temp (select object_name, rownum, rownum from dba_objects);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'XXX',tabname=>'TEMP',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>true);
set autotrace trace;
select * from temp where col2 = 45;
select * from temp where col3 = 45;
drop table temp;
and came up with (summarised):
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=28)
TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=1 Bytes=28)
INDEX (RANGE SCAN) OF 'TEMP_IDX1' (NON-UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
4 consistent gets
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=28)
TABLE ACCESS (BY INDEX ROWID) OF 'TEMP' (Cost=2 Card=1 Bytes=28)
INDEX (UNIQUE SCAN) OF 'TEMP_IDX2' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
3 consistent gets
Which was repeatable and indicates to me that the UNIQUE definition was used by the optimiser and that knowing that detail was able to get the data in fewer consistent gets.
..... and then that just leaves me searching for documentation on indexes to determine which are the constraint required unique indexes :-(.
I say that because of all the systems I've supported over the last 10 years or so I've only ever come across one that made any sort of attempt at categorising the reasons why an index had been built (and that system basically used a naming standard that named constraint indexes differently from performance indexes).
Thanks for you help.
Which Indexes are being used by our Application
UDAY MORALWAR, March 07, 2005 - 2:12 pm UTC
Hi Tom,
The only question I have is if database is already running for few days and have few cached sql statements , what happens If I start the index monitoring ? Does it invalidate existing plan ? OR should I need to flush the shared pool before I start monitoring index usage. Please let me know because this will help me deciding unused indexes.
March 08, 2005 - 7:26 am UTC
yes, it'll invalidate the sql dependent on the table that has that index.
You do not need to flush the shared pool, but you should be aware that it'll invalidate the sql (so don't turn this on during your peak processing time for example)
this can be seen by enabling trace, running a query against that table that hasn't been run as yet, altering an index on that table to be monitoring and rerunning the query. You'll see 2 misses in the library cache for that query.
Index Question
PM, July 28, 2005 - 7:48 pm UTC
Hi Tom,
I have a little confusion about Indexes. Here is a scenario we have -
Table tab1 (
column a
column b
column c
column d
column e )
We have indexes as -
Index1 on column a
Index2 on column b
Index3 on column c
Index4 on column d
Index5 on column e
The reason we have indexes on individual column because we don't know what the user will be quering on.
Here is how we create sql string in the web application -
select * from tab1
<append following if the user enters value for a>
where a = <value>
<append following if the user enters value for b>
and b = <value>
<append following if the user enters value for c>
and c = <value>
<append following if the user enters value for d>
and d = <value>
<append following if the user enters value for e>
and e = <value>;
Now, the issue with this is, if the users enters the value for "a" and "e", the sql will look like -
select * from tab1
where a=<value>
and e=<value>
With this, I noticed that oracle uses index on column "a" which has very very low cardinality and hence, the report took very long. Then I dropped the index on column "a" and ran the same report again. This time it used index on column "e" and the report was extremely fast. But, this didn't work when I ran the report on column "a".
Any idea how to tackle this?
July 29, 2005 - 8:16 am UTC
is this data predominantly read only
tell us about how the data is managed.
tell us about the data.
what is the data?
(and if A has low distinct cardinality, then why index it AT ALL, it isn't like where a = <value> by itself should even USE an index is it?)
Index Question
PM, July 28, 2005 - 8:13 pm UTC
Tom,
One more question related to the previous post.
Say, for example, i have 10 tables with AREA_CD column in each of these table. Also, all of these tables have only one value in this column, say "BOS01".
When I write a query on more than one table, the first statement in where clause will always be a join on AREA_CD.
Now, after knowing that all the valus of AREA_CD are same, does it makes any differance by creating indexes on each of these AREA_CD column?
{ We have only one value in AREA_CD right now but will keep it ready to accomodate any other area in future }
July 29, 2005 - 8:17 am UTC
insufficient data to answer, I presume there are OTHER columns you join on as well. It is not that you would index area_cd in as much as area_cd would be but one of many columns in a concatenated index.
Help me understand this.
Amal Rakshit, November 11, 2005 - 6:38 am UTC
Hello Tom,
I have a strange issue at hand and would be very grateful to you if you could throw some light on this perplexing behaviour.
Allow me to give all the details that I feel you might find useful.
I have two tables as follows (object names have been changes for obvious reasons, pls bear with me)
create table TAB1 (
col1 varchar2(10) not null,
col2 date not null,
col3 date,
col4 varchar2(10),
col5 varchar2(1),
col6 varchar2(10));
create table TAB2 (
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
col4 date);
An index IX1 on col2 and col3 exists on the TAB1...
create index IX1 on tab1 (col2, col3);
Now i run the following query on the database...
SELECT a.col1,
a.col2,
NVL (a.col3, to_date('2005-02-23 19:30:00','yyyy-mm-dd hh24:mi:ss')),
SUBSTR (b.col1, LENGTH (b.col1) - 2, 3)
FROM tab1 a,
tab2 b
WHERE a.col2 <= to_date('2005-02-23 19:30:00','yyyy-mm-dd hh24:mi:ss')
AND (a.col3 >= to_date('2005-02-23 07:30:00','yyyy-mm-dd hh24:mi:ss') OR a.col3 IS NULL)
AND a.col4 NOT IN ('ABC','DEF','GHI')
AND a.col5 IS NULL
AND a.col6 = b.col2(+)
AND b.col3(+) = var_val1
AND b.col4(+) IS NULL
order by a.col2;
But the query doesnot use the index IX1 as seen in the explain plan below (auto trace report)...
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=66548 Card=2352866 Bytes=152936290)
1 0 SORT (ORDER BY) (Cost=66548 Card=2352866 Bytes=152936290)
2 1 HASH JOIN (OUTER) (Cost=28488 Card=2352866 Bytes=152936290)
3 2 TABLE ACCESS (FULL) OF 'TAB1' (Cost=24733 Card=2352866 Bytes=98820372)
4 2 TABLE ACCESS (FULL) OF 'TAB2' (Cost=30 Card=684 Bytes=15732)
Statistics
----------------------------------------------------------
246 recursive calls
14 db block gets
257565 consistent gets
258824 physical reads
0 redo size
8896912 bytes sent via SQL*Net to client
1231976 bytes received via SQL*Net from client
10008 SQL*Net roundtrips to/from client
12 sorts (memory)
1 sorts (disk)
150085 rows processed
Then I created another index IX2 on col3 and col2, i.e. I reversed the order of the columns in IX1
create index IX2 on tab2 (col2, col1);
The query now uses the index... auto trace report:
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38448 Card=193515 Bytes=12578475)
1 0 SORT (ORDER BY) (Cost=38448 Card=193515 Bytes=12578475)
2 1 CONCATENATION
3 2 HASH JOIN (OUTER) (Cost=224 Card=113651 Bytes=7387315)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=12 Card=113651 Bytes=4773342)
5 4 INDEX (RANGE SCAN) OF 'IX2' (NON-UNIQUE) (Cost=6 Card=113651)
6 3 TABLE ACCESS (FULL) OF 'TAB2' (Cost=30 Card=684 Bytes=15732)
7 2 HASH JOIN (OUTER) (Cost=224 Card=113651 Bytes=7387315)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=12 Card=113651 Bytes=4773342)
9 8 INDEX (RANGE SCAN) OF 'IX2' (NON-UNIQUE) (Cost=6 Card=113651)
10 7 TABLE ACCESS (FULL) OF 'TAB2' (Cost=30 Card=684 Bytes=15732)
statistics
---------------------------------------------------------
89 recursive calls
13 db block gets
125067 consistent gets
97514 physical reads
0 redo size
8901911 bytes sent via SQL*Net to client
1231976 bytes received via SQL*Net from client
10008 SQL*Net roundtrips to/from client
16 sorts (memory)
1 sorts (disk)
150085 rows processed
When I forced the use of IX1 using hints the performance of the query went down.
SELECT /*+ INDEX (a IX1) */ a.col1,
a.col2,
NVL (a.col3, to_date('2005-02-23 19:30:00','yyyy-mm-dd hh24:mi:ss')),
SUBSTR (b.col1, LENGTH (b.col1) - 2, 3)
FROM TAB1 a,
TAB2 b
WHERE a.col2 <= to_date('2005-02-23 19:30:00','yyyy-mm-dd hh24:mi:ss')
AND (a.col3 >= to_date('2005-02-23 07:30:00','yyyy-mm-dd hh24:mi:ss') OR a.col3 IS NULL)
AND a.col4 NOT IN ('ABC','DEF','GHI')
AND a.col5 IS NULL
AND a.col6 = b.col2(+)
AND b.col3(+) = var_val1
AND b.col4(+) IS NULL
order by a.col2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=336214 Card=193515 Bytes=12578475)
1 0 SORT (ORDER BY) (Cost=336214 Card=193515 Bytes=12578475)
2 1 CONCATENATION
3 2 HASH JOIN (OUTER) (Cost=149613 Card=113651 Bytes=7387315)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=149401 Card=113651 Bytes=4773342)
5 4 INDEX (RANGE SCAN) OF 'IX1' (NON-UNIQUE) (Cost=146023 Card=113651)
6 3 TABLE ACCESS (FULL) OF 'TAB2' (Cost=30 Card=684 Bytes=15732)
7 2 HASH JOIN (OUTER) (Cost=149613 Card=113651 Bytes=7387315)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=149401 Card=113651 Bytes=4773342)
9 8 INDEX (RANGE SCAN) OF 'IX1' (NON-UNIQUE) (Cost=146023 Card=113651)
10 7 TABLE ACCESS (FULL) OF 'TAB2' (Cost=30 Card=684 Bytes=15732)
Statistics
----------------------------------------------------------
68 recursive calls
13 db block gets
340064 consistent gets
331901 physical reads
0 redo size
8902733 bytes sent via SQL*Net to client
1232015 bytes received via SQL*Net from client
10008 SQL*Net roundtrips to/from client
14 sorts (memory)
1 sorts (disk)
150085 rows processed
Can you please help me understand this strange behaviour.
Both col2 and col3 have high selectivity, i.e. the number of distinct values are very large.
The table TAB1 has about a million rows.
Thanks in advance!
Amal
November 12, 2005 - 8:26 am UTC
You have:
WHERE a.col2 <= K1
AND (a.col3 >= K2 OR a.col3 IS NULL)
we could use the index on (col2,col3) to find all of the:
a.col2 <= K1
rows - col3, not being on the leading edge - is not appropriate for the second part of the predicate.
I cannot comment further because at this point - your example becomes confusing.
Then I created another index IX2 on col3 and col2, i.e. I reversed the order of
the columns in IX1
create index IX2 on tab2 (col2, col1);
Help me understand this.
Amal Rakshit, November 14, 2005 - 12:18 am UTC
Tom,
Sorry for being obscure in my previous query.
I shall try to be more clear this time.
What I wanted to convey was that when I had an index IX1 on (col2, col3) the index was not used. But when I droped the index IX1 and created another index IX2 on (col3, col2), i.e. just reversed the order of coulmns, the new index was used. Please refer to the explain plan in my previous posting.
The last query in my example was just another attempt by me to try and force the use of the first index IX1 by passign it as a hint. As can be seen from the query statistics it was costlier than when Oracle CBO decided not to use the index IX1. This is only to be expected, pls ignore this.
I want to understand why for the same query the index on (col2, col3) was not used whereas the index on (col3, col2) was used.
I would greatly appreciate if you could explain this behaviour to me.
Thanks,
Amal
November 14, 2005 - 8:33 am UTC
you have this predicate in question:
WHERE a.col2 <= K1
AND (a.col3 >= K2 OR a.col3 IS NULL)
it likely felt that col2 is not selective enough when you use the first part of the predicate - that is, it said "wow, there are TONS of a.col2 <= K1 values - using the index on (col2,col3) would be an utter waste".
Later you made the index on col3,col2. Now it said "well, col3 >= K2 or col3 is null, that can make efficient use of the index and it is just a bonus that col2 is there too - thats nice (but not needed, does make it more efficient).
Think about it - if most of the data is such that "a.col2 <= K1", Oracle would have to inspect all of them in the index - sure it can evaluate the col3 part of the predicate - but the range scanning would be on col2 <= K1
Think of the data sorted in a list on paper - and you are given the task to evaluate that predicate (starting from the top of the list on down in order).
And assume that most of the data is such that col2 <= K1.
And assume that little of the data is (a.col3 >= K2 OR a.col3 IS NULL)
which way would you want to have the list sorted by - col2, col3 OR col3, col2?
Index Question
Irina, November 14, 2005 - 10:32 pm UTC
Hi Tom,
I've got an index and I'd like to monitor how many times this index was used. In other words, for each time the index was used, I'd like to see a record showing the usage time or some other statistical indicators (eg. count).
Select statement from the v$object_usage table shows that my index was used.
November 15, 2005 - 8:22 am UTC
you may be able to use v$sql_plan and v$sql to see how many queries use the index and how many times those queries were executed.
and v$segment_statistics to get a feeling for how many IO's are performed.
Help me understand this.
Amal Rakshit, November 15, 2005 - 12:01 am UTC
Thanks a million Tom!!!
This surely helps...
Tom, then do I infer from your explanation that only the leading column of the index is used by the CBO to determine the usability of an index even if the index has multiple columns and all or atleast the first few columns of the index are part of the "WHERE" clause?
If this is the case then I cannot think of any other case where we need a multiple column index except maybe where all the "SELECT" clause columns are obtained from the index itself. Please let me know if I am correct.
Thanks!
Amal
November 15, 2005 - 8:36 am UTC
no, not in general.
It can use indexes as a "skinny version of the table" if the needed columns are present (regardless of order)
It can use an access method called a "INDEX SKIP SCAN" .
select *
from t
where a = 5 and b > 50;
I would like to have an index on (a,b) perhaps. I don't know why you came to your conclusion???
Help me understand this.
Amal Rakshit, November 16, 2005 - 2:21 am UTC
Thanks Tom for bearing with me for this long.
I arrived at my conclusion based on this statement in your earlier reply
"...if most of the data is such that "a.col2 <= K1", Oracle would
have to inspect all of them in the index - sure it can evaluate the col3 part of
the predicate - but the range scanning would be on col2 <= K1"
I did try to see if having an index only on (col3) would do but this new index was not used by Oracle. I think that the optimizer does check against both the columns in the index because it used the index on (col3, col2) for the same predicates. So I am not clear about the following and would greatly appreciate your response to these:
- if an index on (col3,col2) is used then why not an index on (col3)? In both the case it is range scan and the query predicate is same.
- what is the difference between index on (col2,col3) and index on (col3,col2)? If the optimizer checks both the columns to determine whether or not to use an index then I feel both the indexes should be similar in their selectivity.
Finally as this is an OLTP system so lots of DML happen on this database. We already have an index on (col2,col3) for some queries used by other functionalities. Would you recommend having another index on (col3,col2) to satisfy our current requirement? Performance of this query is quite important from the business point of view.
Or any other suggestion that you may have for improving the query execution time by rewriting the query?
Thanks for all the help you have already extended to me and looking forward for a final resolution of this issue.
Regards,
Amal
November 16, 2005 - 5:02 pm UTC
I was giving a very specific case - in general concatenated indexes *are very good, very useful*. In the specific case you had - no, it wouldn't have been, not if the data fit the profile I described.
Is it another way to find useless indexes?
Andrius Malinauskas, February 15, 2006 - 12:05 pm UTC
I looked at your 2 suggestions on index usage monitoring in your book “Expert One-on-One Oracle”. I thought might be there is an easier way to do it in 8i?
What if we set up a job to collect this info into some table:
select owner, name, type, sysdate from v$db_object_cache
where owner not in ('SYS','SYSTEM')
I assume it should collect all DB objects, which have been used, including indexes. From there it is easy to find indexes, which are not being used.
Could you comment this, please?
Thank you
February 15, 2006 - 1:05 pm UTC
my indexes don't show up in there - how about you?
ops$tkyte@ORA817DEV> create table t ( x int );
Table created.
ops$tkyte@ORA817DEV> create index t_idx on t(x);
Index created.
ops$tkyte@ORA817DEV> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA817DEV> commit;
Commit complete.
ops$tkyte@ORA817DEV> connect / as sysdba;
Connected.
ops$tkyte@ORA817DEV> startup force
ORA-25138: LGWR_IO_SLAVES initialization parameter has been made obsolete
ORACLE instance started.
Total System Global Area 144793760 bytes
Fixed Size 73888 bytes
Variable Size 139214848 bytes
Database Buffers 5324800 bytes
Redo Buffers 180224 bytes
Database mounted.
Database opened.
ops$tkyte@ORA817DEV> connect /
Connected.
ops$tkyte@ORA817DEV> set linesize 1000
ops$tkyte@ORA817DEV> set autotrace on explain
ops$tkyte@ORA817DEV> select * from t where x = 1;
X
----------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
ops$tkyte@ORA817DEV> set autotrace off
ops$tkyte@ORA817DEV> select * from v$db_object_cache where name = 'T_IDX';
no rows selected
<b>not there after a query, but yes after DDL, which isn't relevant </b>
ops$tkyte@ORA817DEV> alter index t_idx rebuild;
Index altered.
ops$tkyte@ORA817DEV> select * from v$db_object_cache where name = 'T_IDX';
OWNER NAME DB_LINK NAMESPACE TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
---------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------- ---------------------------- ---------------------------- ------------ ---------- ---------- ---------- ---------- ---
OPS$TKYTE T_IDX INDEX INDEX 1103 1 0 0 0 NO
Thank you for quick response, Teacher
Andrius Malinauskas, February 16, 2006 - 10:01 am UTC
10G - v$object_usage is empty
Nancy, February 23, 2006 - 9:13 am UTC
Table monitoring is enabled out of the box but dba_indexes view does not have a "monitored" column. And the v$object_usage is empty. We upgraded to 10.2.0.1 about a week ago. So it looks like we need to enable this. We want to see what indices are being used - if I enable this for all schemas not sys,system what kind of performance impact are we going to see? Our application is a data warehouse (about 4TB). I am thinking I want to leave monitoring enabled for a couple of months (our load and reporting cycles).
My questions are
1)Is this the correct approach to determine what indices are used
2)Will I be taking a performance hit?
Below is the excerpt from Oracle documentation.
*******************************8
From the 10.2 documentation:
Monitoring Index Usage
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.
To start monitoring the usage of an index, issue this statement:
ALTER INDEX index MONITORING USAGE;
Later, issue the following statement to stop the monitoring:
ALTER INDEX index NOMONITORING USAGE;
The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.
Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.
***************************
February 23, 2006 - 10:01 am UTC
table monitoring is very very very different from index monitoring - the concepts are not related at all.
table monitoring - flag a segment as being in need of new statistics (mark it stale)
index monitoring - flip a bit from NO to YES when an index is used for the first time as an ACCESS PATH
table monitoring information - survives database restarts.
index monitoring information - reset when you restart, everything will be "NO" until you use it again.
performance "hit" is extremely nominal on index monitoring.
Thanks so much-appreciate it the kindness
A reader, February 23, 2006 - 10:50 am UTC
Duh - have to look at v$OBJECT_USAGE as that user too
Nancy, February 23, 2006 - 11:43 am UTC
Funny, I enabled indices monitoring -
SQL> select * from v$object_usage;
no rows selected
SQL>
connected as a user and viola - just noting in case anyone else did not know that it is by user. I turned it on for all schemas in the warehouse.
Thanks again for the clarification.
and this is a wonderful feature.
look up sys.object_usage
zztop, February 23, 2006 - 7:38 pm UTC
SELECT a.object_name , b.*
FROM sys.OBJECT_USAGE b, all_objects a
WHERE a.object_id =b.obj#
Identifying unused indexes
A reader, May 08, 2007 - 12:19 pm UTC
I tried to monitor unused indexes, so I excuted the following steps and after I altered the index and there was no row selected, what was the problem?
If I want to monitor the whole database indexes (there's about 300), does that consume a large resource? Will that impact daily operations?
Oracle product version is 10.2.0.1
SQL> select * from v$object_usage;
no rows selected
SQL> alter index SAD.PK_SAD_SYMBOLS monitoring usage;
Index altered.
SQL> select * from v$object_usage;
no rows selected
Thanks a million!
May 11, 2007 - 8:13 am UTC
ops$tkyte%ORA10GR2> create table t ( x int,
2 constraint t_pk primary key(x) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
2 select rownum
3 from all_objects
4 where rownum <= 100
5 /
100 rows created.
ops$tkyte%ORA10GR2> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
no rows selected
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter index t_pk monitoring usage
2 /
Index altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name,monitoring,used,start_monitoring,end_monitoring
2 from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
T_PK YES NO 05/11/2007 08:13:08
should work straight off - very low impact, if any really. Well, will work straight off if you are in the right schema that is:
ops$tkyte%ORA10GR2> alter index scott.pk_dept monitoring usage;
Index altered.
ops$tkyte%ORA10GR2> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
T_PK YES NO 05/11/2007 08:13:08
ops$tkyte%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
PK_DEPT YES NO 05/11/2007 08:14:18
there is no "owner" in this view, it is assumed to be the current user.
Index Monitoring
mark, August 15, 2008 - 5:47 pm UTC
Hi Tom,
I have read the blog above and used it to substantiate the use of index monitoring to help analyze our indexing strategy. The application would not allow the web users to log in. The DBA has stated index monitoring was the root cause. I have a hard time coming to the same conclusion.
Am I missing something. Just want a sanity check.
Also when I do select * from V$object_usage, I want to see all usage not just the current schemas usage. There are no rows returned after index monitoring was turned off but now I'm not sure it is telling me there was no usage or no usage for my current schema.
Thanks
August 20, 2008 - 9:01 am UTC
The DBA has stated index monitoring was the root
cause.
what evidence did they provide, it would not prevent a logon - that happens BEFORE we touch your indexes, that you enabled monitoring on some indexes would not affect your ability to connect.
you have to be in the schema you want to review for that particular v$ table to function.
Index Monitoring
mark, August 15, 2008 - 5:55 pm UTC
Hi Tom,
I apologize. I forgot to include my email from my question earlier.
I have read the blog above and used it to substantiate the use of index monitoring to help analyze our indexing strategy. The application would not allow the web users to log in. The DBA has stated index monitoring was the root cause. I have a hard time coming to the same conclusion.
Am I missing something. Just want a sanity check.
Also when I do select * from V$object_usage, I want to see all usage not just the current schemas usage. There are no rows returned after index monitoring was turned off but now I'm not sure it is telling me there was no usage or no usage for my current schema.
Thanks
which index to use and why
A reader, December 16, 2009 - 3:29 am UTC
Hi Tom,
In few of my queries, I observed followings
- say I have two where clauses t1.a = 12345 and t2.b = 'xyz' , what is the criteria..or deciding factor to which index first (there are indexes on t1.a as well as t2.b).
the reason I asked that if i force the index use of index t2.b...query performs better than the oracle choice of using index on t1.a.
i know there are several reasons - what i want to know that what statistics (which oracle captures when we gather stats) is used by optimizer to decide on such things.
any initial thought , simple to understand would be a help.
Thank you
December 16, 2009 - 7:33 am UTC
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671539468597 you would want an index on t(a,b) OR t(b,a) - depending on other factors, which are discussed in that link.
The CBO, if you just had an index on A and another separate index on B, would try to figure out which returns less rows "where a=?" or "where b=?" and combine that information with the clustering factor of each index to determine which would likely be less costly to use.
No. of time index was accessed ?
Neo, February 24, 2010 - 7:52 am UTC
Tom,
How can we find how frequently a Index is used ?
or which Query used a particular index, we may find out the no. of executions of the Query and find out the number of times a index is used.
Please let me know how to do this
Thanks
March 01, 2010 - 8:54 am UTC
we don't really track it at that level.
You can query v$segment_statistics to see IO patterns
if you have ASH/AWR, you can see how many plans reference it and how many times that plan has been executed over time.
If you do not, you could enable statspack on your own to accumulate that sort of data
(note: neither of the above two will ever be perfect - they both SAMPLE data at discrete points in time, they will be "pretty close", but not 100% - and do not need to be)
Tell us, of what use would this metric be? Maybe you tell us why you think you need it and we can tell you the metric you should be looking at?
How to calculate Index Usage?
Hari, April 15, 2010 - 5:09 am UTC
Hi Tom,
Good Day.
Alter Index...Monitoring, will enable us to see if the index is actually used by a query or not.
In case if we wanted to see how much of the index is used, you asked us to check v$SQL and v$SQL_PLAN along with V$SEGMENT_STATISTICS.
Now, ex-employee of this company has created around 150 tables for an application and I will have to study and revamp the indexes in these 150 tables. To do this, I would like to generate the output as follows:
Index Name, Used (Yes/No), How many times used, Queries which used this index, Reads/writes on this index
Can you please tell me how can I get this output so that I can eliminate the unnecessary indexes?
Thanks
Hari
April 15, 2010 - 8:57 am UTC
I think you already answered this yourself, based on what I already said?!?!?!
query v$sql_plan for rows that include an index step.
join to v$sql to get execute counts
join to v$segment_statistics to get IO'sbut that won't answer your question - only analysis can do that.
consider:
create table t as select * from all_objects;
create index t1 on t(owner);
create index t2 on t(object_type,owner);
create index t3 on t(object_name,object_type,owner);
given a set of queries:
... where owner = :x
... where owner = :x and object_type = :y;
... where owner = :x and object_type = :y and object_name = :z;
you might say "we need all three", but you would be wrong, you just need one:
create index the_only_index on t(owner,object_type,object_name);
indexes
sam, April 15, 2010 - 2:41 pm UTC
Tom:
What about if the application has SQL queries but in random order like
... where owner = :x
... where object_type = :y and owner = :x;
... where object_name = :z and owner = :x and object_type = :y;
Would your ONE index (owner, object_type, object_name) still be used by all three?
INDEX management is one challenging thing i found. That is finding the right number of indexes based on ton of SQL in the application.
April 15, 2010 - 3:16 pm UTC
what do you mean in random order? do you mean in the where clause? we don't care what order you put stuff in there.
I don't care what order you execute SQL in, it would be able to use the same set of indexes.
indexes
sam, April 15, 2010 - 5:10 pm UTC
TOm:
yes, i mean the where clause. Good to know that.
Does not this mean thay I would only need to create
ONE INDEX per table only
If i have a table T with 100 columns, I just check all the SQL that references table T and see the columns specified in the WHERE clause and create one index based on that. All the queries will then use the index.
T
-----
col1
col2
col3
col4..
..
col100
select * from T where col1=:x
select * from T where col4=:Z and col100 =:y
select * from T where col3:=F and col99:=:P
All i need is an index on
(col1,col3, col4, col99, col100)
and oracle will always use the index.
Did you write any articles/books on how to come up with correct number of indexes and columns for each index. I am not very clear on this subject
April 16, 2010 - 9:21 am UTC
... Does not this mean thay I would only need to create
ONE INDEX per table only
...
Sam, please think about it, use critical thinking - close your eyes - envision how things work. Short of that, I know you have my books - read the chapters on indexing.
Of course not, is the only answer to this. Think about how data is organized, envision what would happen.
Tell me how the index on (col1,col3,COL4,col99,COL100) would be used effectively for "select * from T where col4=:Z and col100 =:y"
*think about it*.
If I gave you a list sorted by col1,col3,.... and asked you "find all of the entries in this list sorted by col1,col3,.... where col4=5 and col100=10"
would that list be USEFUL at all to you?
Indexes have other uses as well
Mike Tefft, April 16, 2010 - 2:37 pm UTC
Do not assume that indexes are 'unnecessary' if they do not show up in plans.
Unique indexes enforce unique constraints, even if no queries use the index.
Foreign key enforcement will use indexes if possible, but that will not show up in the plans either.
April 16, 2010 - 5:07 pm UTC
correct, and the converse is true
just because they show up as being used doesn't mean they should exist either.
This is always a futile exercise - lets try to run a query to find out what we did wrong.
indexes
sam, April 17, 2010 - 1:09 pm UTC
Tom:
You know you are right.
I did close my eyes, brought a piece of paper and created an index on (c1,c3,c4,c9,c100) with somw fake values and tried to find a string where c4=5 and c100=10. i had to do a full scan.
I would need an index on (c4,c100) or (c4,c100, other cols) for that query or even (c100, c4).
Thanks for the hint.
index
sam, April 17, 2010 - 7:55 pm UTC
Tom:
Can I see the data stored in an index like a table?
or i have to create a table like
create table t3_idx as select object_name,object_type,owner from all_objects
order by object_name,object_owner
April 19, 2010 - 8:40 am UTC
we use indexes like tables all of the time sam.
You have my book - at least you say you do. Have you read the chapter on indexes??? I do cover this.
It is unlikely that that query would use an index by the way. If you are curious why, same chapter.
select data from index??
Gaurav Sachan, April 19, 2010 - 4:32 am UTC
just the index can't be used to see the data as we can see in a table just as we can't see the contents of a specific chapter by locating it in the INDEX of the book !!!!
IOT may be used, which basically combines the table and index features (in simple words)
Monitoring Table Usage
Reader, August 31, 2010 - 1:30 pm UTC
hi Tom
this thread explains in great detail how to monitor if the indexes are being used and that is clear to me.
however i am trying to find out if there is a way to find if there are any tables in the DB that are not being used.
we are using a ROLAP tool (microstrategy) that requires us to build aggregate tables which it can use for improving query performance. currently we have 100's of these agg tables built with different dimensionality (attribute columns) each month ...
i have a suspision that quite a few of thses agg tables are not being used by the tool. is there a way to monitor if a table is SELECTED from ? something like a SELECT trigger would have been ideal - but i think there is no such thing ...
do we have some options to check on this ?
Thanks in advance for your help
September 09, 2010 - 11:47 am UTC
v$segment_statistics might be useful.
auditing definitely would be useful.
index
sam, February 14, 2011 - 10:41 pm UTC
Tom:
If you have a query like this, would you create an index on
SELECT SUM(quantity_picked) FROM storage_shipment a,shipment b
WHERE a.shipment_id = b.shipment_id AND
a.stock_number = 'AB-23' AND
a.warehouse_id = 'WES' AND
a.storage_code = 'Bin 1' AND
b.shipment_date >= sysdate;
Tables
---------
shipment table (shipment_id PK)
shipment_storage(ship_storage_seq (PK), shipment_id(FK), etc..)
Indexes
---------
storage_shipment(a.shipment_id,stock_number,warehosue_id,storage_code)
shipment(shipment_Date)
If a query runs fast (0.03 sec) using a Full scan and table has about 2000 records would you still create an index to minimize logical gets and physical reads.
or allow for future scalability when table becomes in hundreds of thousands of records.
February 15, 2011 - 8:28 am UTC
impossible to answer since I don't know your data. I don't know if the predicates are selective enough.
it depends on how many rows are returned by:
a.stock_number = 'AB-23' AND
a.warehouse_id = 'WES' AND
a.storage_code = 'Bin 1' AND
versus
b.shipment_date >= sysdate;
if the second one returns very little data, I might index
shipment(shipment_date,shipment_id)
shipment_storage(warehouse_id,storage_code,stock_number,shipment_id,quantity_picked) COMPRESS 3
(assuming quantity picked comes from that table - not clear).
Now Sam, I'd like you to explain why I might do that.... You should be able to - use some critical thinking and lay it out.
Mohamed Houri, February 15, 2011 - 9:14 am UTC
(a) you compressed the second index to reduce it size and hence reduce the associated physical reads
(b) you've added the quantity_picked colums to this index in order to do not visit the table and use only the index to get the max(quantity_picked)
(c) why would you create the index shipment (shipment_date, shipment_id). Will it not be more efficient to create index shipment (shipment_id,shipment_date)
the leading colums should be on the equality predicate. Isn't it?
Best Regards
Mohamed Houri
February 15, 2011 - 9:36 am UTC
(c) what index are we leading with here. Remember "if the predicate on table B returns very few rows then...."
So, we start with B where the predicate is against the date_column, then we need the shipment id to pick up the rows in the other table.
but otherwise - yes :)
index
sam, February 15, 2011 - 9:24 am UTC
I think Tom indexed on (Shipment_date,shipment_id) because query where clause uses shipment date.
I think the optimizer will do a skip scan or range scan if it has the shipment_id first in the index instead of row id scan.
February 15, 2011 - 9:48 am UTC
no.
we are assuming we are driving from table B, we use the shipment_date part of the index to find the rows in B of interest, get the shipment_id from the index instead of the table - and then go to table A.
No skip scan, just a normal index range scan.
index analysis
sam, February 15, 2011 - 9:32 am UTC
Tom:
IT is pretty difficult to do index analysis and figure out what is the correct number of indexes or what columns to index, etcc...
I read the chapter on your book but still i am not very comfortable. Is there anything you recommend doing to become an index expert so you can look at a query and tables and determin what indexes and columns to create.
February 15, 2011 - 9:49 am UTC
... IT is pretty difficult to do index analysis and figure out what is the correct
number of indexes or what columns to index, etcc... ...
that is why you get paid to do you job and why you don't just do it out the goodness of your heart.
Lots of things are "hard"
... Is there
anything you recommend doing to become an index expert so you can look at a
query and tables and determin what indexes and columns to create. ...
Time, Experience, and the ability to visualize "what will happen if I do X". You have to be able to close your eyes and "see", to visualize what is actually happening..
index
Sam, February 20, 2011 - 10:21 pm UTC
Tom:
1) When you analyze a query for correct indexe do you exclude the columns in the JOIN from the index.
Are those columns joined indexed separately as part of the PK on T1 and foreign key index on T2?
2) MAny books say that indexes speed up DELETE and UPDATE in addition to SELECT. Is this correct?
I thought every time you delete or update you will have to update the index too. But also the main update could be faster since it locates the row by ROWID instead of full scan.
WOuld the answer be "it depends" on data?
February 22, 2011 - 10:47 am UTC
1) sam, that doesn't even make sense after I've read it 10 times now. All I can say s "huh?"
I think the answer is "of course not", but I'm not really 100% sure what you are trying to ask.
2) Many books say many things. Indexes can
a) speed up
b) have no effect on
c) slow down massively
delete and update.
Indexes help you find data. If the index in question helps you find a small bit of data from a bit set of data to delete/update - then the index may will speed it up.
On the other hand, if it does not - every index you add will likely slow down a delete (as we have to maintain the index) and perhaps every update - if the update updates indexed columns.
indexes
Sam, February 22, 2011 - 12:15 pm UTC
Tom:
On #1, I was trying to ask whether when you create an index to speed this query (parent to child) for example you would consider the columns in the JOIN (REQUEST_ID).
I assume since T1 had REQUEST_ID as PK and T2 has the FK REQUEST_ID then you do not.
You usually consider the other columns in the where clause like stock_request(REQUEST_DATE,SHIP_TO_ORG) and stock_request_item(Stock_no,CODE)
Correct?
SELECT col1,col2,col3 from stock_request a, stock_request_items b
where a.request_id = b. request_id
and a.request_date = to_date('01-JAN-2011')
and a.ship_To_org = 'ORCL'
and b.stock_no = 'AB1234'
and b.code = 'O'
On #2, i think i see what you say. IF the extra index created to speed up SELECT is also *USED* by UPDATE/DELETE then it will speed up those DML statement otherwise it will slow it down if it does not since you have to update the index when you update and delete.
This means that sometimes you may create indexes to speed up the DELETES and UPDATES if they are not using the PK. Most of the time though they should be using the PK for the ROW.
It sounds to me there is no way to know all the INDEXES requried in the DEIGN pahse of a system. Many indexes will need to be created after DEVELOPMENT since it depends on the SQL wirtten by all the developers.
Am I right?
February 23, 2011 - 12:44 pm UTC
on #1, It doesn't matter what other indexes you have, if you have a where clause like:
where a.request_id = b. request_id
and a.request_date = to_date('01-JAN-2011')
and a.ship_To_org = 'ORCL'
and b.stock_no = 'AB1234'
and b.code = 'O'
you probably want an index on either
A(request_date,ship_to_org) and B(request_id,stock_no,code)
or
A(request_id,request_date,ship_to_org) and B(stock_no,code)
depending on which table makes more sense to 'drive' the query - if the predicate on A returns fewer rows than B, the first set of indexes make sense, else the second set.
#2 ... Most of the time though they should be
using the PK for the ROW.. ...
that is not true. Most of the time they should be using the predicate that makes them do the right thing.
It sounds to me there is no way to know all the INDEXES requried in the DEIGN
pahse of a system. Many indexes will need to be created after DEVELOPMENT since
it depends on the SQL wirtten by all the developers.
not if you have done your design to a sufficient level, if you just cobble code together you'd be correct.
RE: It depends
Duke Ganote, February 22, 2011 - 5:00 pm UTC
<quote>it depends on the SQL wirtten by all
the developers.</quote>
Right. The database design can't be complete until how the database will be used is known: access, volumetrics, etc.
"Form follows function".
indexes
Sam, February 24, 2011 - 10:12 am UTC
TOm:
Your recommendation is interesting. I have a mix of your solution with an index like this
A(request_date,ship_to_org) and B(stock_no,code)
I do not know how you determine the driving table. I thought the optimizer will always start with the parent table. It seems the best thing to do is use 3 combinations separately in TEST environment and analyze the stats to see which one has runs best. correct?
I think what you mean is that the system specification document should be at sufficient level of detail so DBA can determine all the tables (data) and queries (indexes) against them so he can design them before coding. But decision on indexes also depends on existing data + future data growth so this has to be in the specs too.
In theory you are correct, but in practical life i think most indexes are created after development or even after deploying the system in production and users start complaining about response time.
February 24, 2011 - 12:55 pm UTC
... I thought the optimizer will
always start with the parent table. ...
no, why would it.
what if you coded:
select *
from dept, emp
where dept.deptno = emp.deptno and emp.name= 'KING';
wouldn't you want to start with emp, find the single row for king and then lookup the dept? I would. The parent has nothing to do with it.
In theory you are correct, but in practical life i think most indexes are
created after development or even after deploying the system in production and
users start complaining about response time.
and how sick is that - wait until people complain. Don't do any testing, don't do any thinking, don't worry about performance until it is too late.
index
Sam, February 24, 2011 - 2:18 pm UTC
<<select *
from dept, emp
where dept.deptno = emp.deptno and emp.name= 'KING'; >>
How did you determine in the query above that driving table is EMP in this case. Is it because you have a where caluse for EMP and not DEPT.
I am trying to see:
1) how you look at a query and determine what the driving table is?
2)how you determine what the driving table for a query should be based on number of records, etc.
February 24, 2011 - 6:50 pm UTC
How did you determine in the query above that driving table is EMP in this
case. Is it because you have a where caluse for EMP and not DEPT.close your eyes, envision the work. Do it both ways in psuedo code in your head.
a) dept first, driving into emp:
for x in (select * from dept) -- all records in dept will be processed
loop
for y in (select * from emp where emp.deptno = dept.deptno) -- ditto
-- unless you have an index on deptno, ename - but that is not
-- highly likely
loop
see if it is KING, if so output
end loop
end loop
b) emp first, driving into dept
for x in ( select * from emp where ename = 'KING' ) -- 1 record!!
loop
for y in (select * from dept where dept.deptno = emp.deptno ) -- 1 record!!!
loop
output it
end loop
end loop
Now, using simple math (add up the steps, assume dept has 100 records - small), which one does less work?
index
sam, February 27, 2011 - 9:51 pm UTC
Tom:
OK, i see what you are saying. But it did not answer my question.
How is the query where DEPT is the driving table look like versus the one where EMP is the driving table? i do not think the order of the table in your SELECT statement has anything to do with that.
Can you look at any SQL query and determine the driving tables or you need to look at execution plans for that?
February 28, 2011 - 8:50 am UTC
But it did not answer my question.
Sam - yes it did, it most certainly did, it ABSOLUTELY did.
How is the query where DEPT is the driving table look like versus the one where
EMP is the driving table? i do not think the order of the table in your SELECT
statement has anything to do with that.
the order of the tables had nothing to do with it. where did you get the idea from anything I wrote that table ordering impacted anything?????
Can you look at any SQL query and determine the driving tables or you need to
look at execution plans for that?
given enough time.
given all of the information about the schema (the data model)
given all of the type of information the optimizer would/could have access to (including dynamic sampling type of data! meaning "I *know* my data)
Probably. That is exactly what the optimizer is trying to do - there is an algorithm for it.
The optimizer is much faster at it than I am though.
Difference Between Unique and Non-Unique Index
Hariharan, May 08, 2011 - 1:05 pm UTC
Hi Tom,
I am actually writing a small article on Oracle Indexes where I came across the following situation. I am writing on b*tree indexes and providing information on unique and non-unique indexes. The following is the details I provided:
create table testunique (object_id number, object_name varchar2(100), object_value number(100));
create unique index objuniqidx on testunique(object_id);
create table testnonunique (object_id number, object_name varchar2(100), object_value number(100));
create index testnonuniqidx on testnonunique(object_id);
Loaded the same data of 50,000 records into the tables and analyzed the table/index using DBMS_STATS package.
I issued a simple select statement (as provided below) and observed the results:
select * from testunique where object_id < data_value;
select * from testnonunique where object_id < data_value;
where values for data_value are 1000,2000,3000,4000,5000, 6000,7000,9000,10000,15000,and 20000.
I observed that select on testunique table used indexes till 10,000 records and after that it started using full table scan. However, the select on testnonunique table used indexes till 6,000 records and after that it started using full table scan.
When I checked USER_INDEXES table I got the following:
Index Name Clustering Factor Blevel Leaf_Blocks Num_Rows
TESTNONIDX 179 1 110 50,000
OBJUNIQIDX 179 1 93 50,000
Now my query is provided below:
a) Leaf_Blocks for Unique index is less compared to Non-Unique index. Is this the reason for the unique index to get into table scan after 10,000 rows and non-unique index to get into table scan after 6,000 rows? If not, can you please provide me the reason?
b) In your book "Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions", you have mentioned that number of I/Os is based on the HEIGHT (which is Blevel -1). Now, for the following query:
SQL> select * from testunique where object_id=12782;
| 0 | SELECT STATEMENT | | 1 | 117 | 2 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTUNIQUE | 1 | 117 | 2 (0)|00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJUNIQIDX | 1 | | 1 (0)|00:00:01 |
47 consistent gets
6 physical reads
If you see here, I am getting 40 consistent gets. Can you please tell me the reason?
Thanks for all your help
Hari
May 09, 2011 - 8:52 am UTC
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table t1;
ops$tkyte%ORA10GR2> drop table t2;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1 as select * from all_objects;
ops$tkyte%ORA10GR2> create UNIQUE index t1_unq_idx on t1(object_id);
ops$tkyte%ORA10GR2> create table t2 as select * from t1;
ops$tkyte%ORA10GR2> create index t2_nonunq_idx on t2(object_id);
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T1' );
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T2' );
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t1 where object_id < 13528 union all select * from t2 where object_id < 13528;
Execution Plan
----------------------------------------------------------
Plan hash value: 759588023
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20903 | 1898K| 531 (51)| 00:00:03 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10451 | 949K| 264 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | T1_UNQ_IDX | 10451 | | 23 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 10452 | 949K| 266 (1)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | T2_NONUNQ_IDX | 10452 | | 25 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<13528)
5 - access("OBJECT_ID"<13528)
ops$tkyte%ORA10GR2> select * from t1 where object_id < 13529 union all select * from t2 where object_id < 13529;
Execution Plan
----------------------------------------------------------
Plan hash value: 435707816
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20905 | 1898K| 531 (51)| 00:00:03 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10452 | 949K| 264 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | T1_UNQ_IDX | 10452 | | 23 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 10453 | 949K| 266 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<13529)
4 - filter("OBJECT_ID"<13529)
ops$tkyte%ORA10GR2> select * from t1 where object_id < 13642 union all select * from t2 where object_id < 13529;
Execution Plan
----------------------------------------------------------
Plan hash value: 435707816
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20992 | 1906K| 533 (51)| 00:00:03 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 10539 | 957K| 266 (1)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | T1_UNQ_IDX | 10539 | | 23 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 10453 | 949K| 266 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<13642)
4 - filter("OBJECT_ID"<13529)
ops$tkyte%ORA10GR2> select * from t1 where object_id < 13643 union all select * from t2 where object_id < 13529;
Execution Plan
----------------------------------------------------------
Plan hash value: 3002291744
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20993 | 1906K| 533 (51)| 00:00:03 |
| 1 | UNION-ALL | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 10540 | 957K| 266 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T2 | 10453 | 949K| 266 (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<13643)
3 - filter("OBJECT_ID"<13529)
ops$tkyte%ORA10GR2> set autotrace off
You really need to provide FULL examples - so we can see how your data was created. Using my example, we can see there is a marginal difference - and it is due to the costing and cardinality estimates. The estimated card= values are slightly different (the optimizer has more information with the unique index after all).
In my case, the two indexes behave the same up to 13,528. Then the non-unique index stops being used at 13,529 when the cost of using the index goes slightly above the cost of the full scan. This pattern remains until we get to 13,643 when the cost of using EITHER index is found to be more than the cost of the full scan.
As for b), there is probably recursive sql taking place - to hard parse that query, and that recursive sql is doing the IO's you see here.
Index monitoring
A reader, May 09, 2011 - 11:10 am UTC
Oracle Indexes
Hariharan, May 11, 2011 - 11:09 am UTC
Hi Tom,
Thanks a lot for your excellent information. The data on both tables remain the same. Sample is provided below:
insert into testunique values (1,'aaa1',1);
insert into testunique values (2,'aaa1',2);
insert into testunique values (2,'aaa1',3);
...
...
insert into testunique values (50000,'aaa50000',50000);
The above data remains the same for both testunique and testnonunique tables. Also, both the indexes are properly analyzed using dbms_stats.
Please let me know if you need any other information.
Thanks
Hari
May 11, 2011 - 11:33 am UTC
well, database versions, how - precisely and exactly - how you gathered stats, etc.
But - I've already answered your question. Did you read the above? It shows that DUE TO THE CARDINALITY estimates primarily, there will be a slight difference.
and please verify your clustering factors, in both 10 and 11 - I do not see results like yours at all.
So, in short, provide version information and a FULL SCRIPT (that actually runs, number(100) for example - bad, inserting duplicates into testunique - bad - script that you've run and WORKS - good)
Orace Indexes
Hariharan, May 11, 2011 - 11:57 am UTC
Hi Tom,
Thanks again for your reply and sorry I do not know that you need these information. Please find below the information you needed:
Oracle: 10.2.0.1.0
Windows XP is the OS
I gathered statistics as follows:
dbms_stats.gather_index_stats('HARI', 'OBJUNIQIDX');
dbms_stats.gather_index_stats('HARI', 'TESTNONIDX');
Also for table statistics as follows:
dbms_stats.gather_table_stats('HARI', 'TESTNONUNIQUE');
dbms_stats.gather_table_stats('HARI', 'TESTUNIQUE');
Table structure and clustering_factor information is provided in my previous question.
The insert script is very huge Tom, I manually created 50,000 rows in EXCEL and then inserted into the database, as I do not have that much data in my machine at home.
Thanks for your explanation and I also saw the difference in Cardinality now. I will do the same experiment in my office and see if I am able to match your results.
Thanks
Hari
May 11, 2011 - 1:41 pm UTC
I want the test case from start to finish, from a working CREATE TABLE to selects with autotrace on - just exactly like I posted.
Sorry, I thought that was clear when I wrote:
and a FULL SCRIPT (that actually runs, number(100) for example - bad, inserting duplicates into testunique - bad - script that you've run and WORKS - good)
I want you to create the tables, the indexes, populate the data, gather stats, etc. The order in which you did things is important too.
The insert script is probably 1 line. If I understand your data:
insert into testunique values (1,'aaa1',1);
insert into testunique values (2,'aaa1',2);
insert into testunique values (2,'aaa1',3);
...
...
insert into testunique values (50000,'aaa50000',50000);
I have to assume that line 3 there is a typo, it is just:
insert into t select rownum, 'aaa'|| rownum, rownum
from dual connect by level <= 50000;
Or, maybe
begin
for i in 1 .. 50000 loop insert into t values ( i, 'aaa'||i, i ); end loop;
end;
I don't know why you would use a spreadsheet of all things????
Oracle Indexes
Hariharan, May 12, 2011 - 9:33 am UTC
Hi Tom,
Thanks for your comments. Apologize for this.
The reason for using the excel is I did not make the entire data in one shot(if it had been in one shot, I would have done in your way). I was entering batch by batch to identify and show in the article on how the DMLs are affected by the presence of Indexes.
Please find below the working scripts and details:
/*Oracle: 10.2.0.1.0
Windows XP is the OS*/
create table testunique (object_id number, object_name varchar2(100), object_value varchar2(100));
create unique index objuniqidx on testunique(object_id);
create table testnonunique (object_id number, object_name varchar2(100), object_value varchar2(100));
create index testnonuniqidx on testnonunique(object_id);
/* Insert data scripts */
begin
for test in 1 .. 50000
loop
insert into testunique values (test,('aaa'||test),test);
insert into testnonunique values (test,('aaa'||test),test);
end loop;
commit;
end;
/* index stats */
exec dbms_stats.gather_index_stats('HARI', 'OBJUNIQIDX');
exec dbms_stats.gather_index_stats('HARI', 'TESTNONUNIQIDX');
/* table stats */
exec dbms_stats.gather_table_stats('HARI', 'TESTNONUNIQUE');
exec dbms_stats.gather_table_stats('HARI', 'TESTUNIQUE');
set autotrace traceonly exp stat;
select * from testunique where object_id < 1000;
select * from testunique where object_id < 2000;
select * from testunique where object_id < 3000;
select * from testunique where object_id < 4000;
select * from testunique where object_id < 5000;
select * from testunique where object_id < 6000;
select * from testunique where object_id < 7000;
select * from testunique where object_id < 9000;
select * from testunique where object_id < 10000;
select * from testunique where object_id < 15000;
select * from testunique where object_id < 20000;
/* Similarly for the other table testnonunique:*/
select * from testnonunique where object_id < 1000;
select * from testnonunique where object_id < 2000;
select * from testnonunique where object_id < 3000;
select * from testnonunique where object_id < 4000;
select * from testnonunique where object_id < 5000;
select * from testnonunique where object_id < 6000;
select * from testnonunique where object_id < 7000;
select * from testnonunique where object_id < 9000;
select * from testnonunique where object_id < 10000;
select * from testnonunique where object_id < 15000;
select * from testnonunique where object_id < 20000;
I was executing the scripts again to check and to avoid any further trips between us on the need of information and found that when table is not analyzed, it gives a set of different results and when the table is analyzed it gives another set of results.
When I analyzed both index and table, I am able to match your results with slight difference, which like you have said, it can be attributed to the cardinality.
Please let me know if you need any further information.
I again apologize for this.
Thanks
Hari
May 12, 2011 - 9:40 am UTC
I was entering batch
by batch
still doesn't explain why you didn't just use a one line plsql script to generate the data.
Please let me know if you need any further information.
Ok, now I'll ask you the same - you have just said "when I analyze both table and index, I get what you get - which is expected", "When I don't analyze I get a different result" (sort of to be expected no? No stats = take a small sample and guess, if we have a unique index on the table - we KNOW the values are unique - we have more information than if the index is not unique. Given all we have is a tiny sample.... The unique index helps the optimizer figure things out..).
What more information (now that we know how you did things...) do you need? You have just shown that "good stats is better than no stats"...
Oracle Indexes
Hariharan, May 13, 2011 - 4:55 am UTC
Hi Tom,
Thanks again for your explanation.
Actually I analyzed indexes but not tables. I was always under the impression that gathering index stats is sufficient and never expected that gathering table stats also plays a vital role.
Thanks for helping me in identifying this.
Hari
May 13, 2011 - 12:53 pm UTC
that is so wrong in so many ways...
I'd rather have table stats over index stats any day.
You'll want to revise that view you have.
index usage
vipul patel, September 05, 2011 - 1:36 pm UTC
If I create an index on column (a,b,c) then
a, ab, abc combination of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the index.
The example is given below.
SQL> create table comp_tab(a number, b varchar2(10), c varchar2(10));
Table created.
SQL> begin
for i in 1 .. 10000
loop
insert into comp_tab values(i,'pc-'||round(dbms_random.value(1,20000),0),'pc-'||round(dbms_random.value(1,20000),0));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index comp_tab_I on comp_tab(a,b,c);
Index created.
SQL> analyze table comp_tab estimate statistics;
Table analyzed.
SQL> set autot trace
a in where cluase will use the index as a is leading portion of index.
SQL> select a,c,b from comp_tab where a=565;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
ac combination will use the index.
SQL> select a,c,b from comp_tab where a=565 and c='pc-3722';
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
bc combination is used in where clause so will not use index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------
Only b combination is used so will not use the index.
SQL> select a,c,b from comp_tab where b='pc-6735';
Execution Plan
----------------------------------------------------------
Plan hash value: 3767570742
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| COMP_TAB | 1 | 19 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------
abc combination is used in where clause and so will use the index.
SQL> select b,c,a from comp_tab where b='pc-5895' and c='pc-2893' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
ab combination in where clause will use the index.
SQL> select c from comp_tab where b='pc-5895' and a=564;
Execution Plan
----------------------------------------------------------
Plan hash value: 753992557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| COMP_TAB_I | 1 | 19 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
As the question here is that how and why this kind of behavior of optimizer is observed, please throw some light on this.
thanks in advance
vipul patel
Mumbai(India)
September 06, 2011 - 8:57 am UTC
a, ab, abc combination of columns in where clause will use the index.
maybe, if you have "where c= :c and a > :a and b > :b", we'd probably not use the index - we'd probably want an index on (c,a,b) or (c,b,a)
I'm not sure what you are asking above however, - in general - you need to be using the columns on the leading edge of the index in order to use it in a range scan
Indexes which are not being used in a week
Sikki, December 19, 2011 - 11:09 am UTC
Hi Tom,
Really appreciate your skills and knowledge sharing. Can we conclude that output of the below query will show the indexes that are not used in a week (based on views history retention) for APPN schema and hence can be considered to drop.
select index_name from dba_indexes where index_name not in ( select distinct OBJECT_NAME from dba_hist_sql_plan where OBJECT_OWNER='APPN' and object_type='INDEX') and OWNER='APPN'
Total indexes :
SQL> select count(index_name) from dba_indexes where OWNER='APPN'
2 /
COUNT(INDEX_NAME)
-----------------
338
SQL> select count(index_name) from dba_indexes where index_name not in ( select distinct OBJECT_NAME from dba_hist_sql_plan where OBJECT_OWNER='APPN' and object_type='INDEX') and OWNER='APPN'
2 /
COUNT(INDEX_NAME)
-----------------
212 (count of indexes which are not used)
SQL> select count(distinct OBJECT_NAME) from dba_hist_sql_plan where OBJECT_OWNER='APPN' and object_type='INDEX'
2 /
COUNT(DISTINCTOBJECT_NAME)
--------------------------
126 (Count of indexes which are used)
Please comment, Many thanks in Advance.
December 19, 2011 - 4:54 pm UTC
No, because the sql history is never going to be 100% complete.
You can consider using the monitoring views
alter index t_pk monitoring usage
/
and a week later:
select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
and what about the FK indexes
A reader, December 20, 2011 - 9:27 am UTC
Hi Tom,
You said
"You can consider using the monitoring views
alter index t_pk monitoring usage"
That is ok for the pk indexes but the poster should be aware that indexes on the foreign key could also be reported not used while they might be actively used by Oracle behind the scene in order to avoid locks and deadlock when DML are issued against their parent table. Bottom line is to be carreful about the immediate decision one can take when queering those monitoring views about the usage of indexes particularly about FK indexes.
Mohamed Houri
December 20, 2011 - 10:01 am UTC
I've written so many times that I find this whole "how can we find out what is not being used so we can drop it" situation a sad commentary on software development.
People want to do it for tables, for indexes, for whatever.
A little design
A little documentation
No such problem
too little design
too little documentation
you have this problem
I think anyone that is looking for unused things to drop is looking for trouble. Period.
Indexes which are not being used in a week
sikkandar, January 02, 2012 - 5:10 am UTC
Hi Tom,
As suggested i have enabled monitoring for all the indexes with respect to one table, i queried the v$object_usage after enabling it and confirmed it returns correct number of rows. The next day i logged into the same schema and queried the same whereby i dont see any entry. It returns no row selected.
I've got metalink note 261756.1 for this, this too advised to make sure "To view the information in the "V$OBJECT_USAGE" view you have to be connected as the user owner of the objects you want to monitor, otherwise you might not find the monitoring information you are looking for." - I am sure i've enabled and checked next day on the same "owner of the table".
Please advise on how to get correct info on v$object_usage.
January 02, 2012 - 8:39 am UTC
did you shutdown the instance?
Indexes which are not being used in a week
sikki, January 02, 2012 - 1:06 pm UTC
Tom,
I didn't shutdown the database,its 24/7 database, i confirmed with alert log, no message for shutdown found.
January 02, 2012 - 7:38 pm UTC
it should be populated with the indexes that were "used", if not, please contact support - something is amiss.
even though no mention of a shutdown was there - you don't see any signs of a startup do you?
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx1 on t(object_id);
Index created.
ops$tkyte%ORA11GR2> create index t_idx2 on t(object_name);
Index created.
ops$tkyte%ORA11GR2> create index t_idx3 on t(owner);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter index t_idx1 monitoring usage;
Index altered.
ops$tkyte%ORA11GR2> alter index t_idx2 monitoring usage;
Index altered.
ops$tkyte%ORA11GR2> alter index t_idx3 monitoring usage;
Index altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES NO 01/02/2012 20:37:45
T_IDX2 T YES NO 01/02/2012 20:37:45
T_IDX3 T YES NO 01/02/2012 20:37:45
ops$tkyte%ORA11GR2> select * from t where object_name = 'ABC';
no rows selected
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES NO 01/02/2012 20:37:45
T_IDX2 T YES YES 01/02/2012 20:37:45
T_IDX3 T YES NO 01/02/2012 20:37:45
ops$tkyte%ORA11GR2> select * from t where object_id = -1;
no rows selected
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES YES 01/02/2012 20:37:45
T_IDX2 T YES YES 01/02/2012 20:37:45
T_IDX3 T YES NO 01/02/2012 20:37:45
ops$tkyte%ORA11GR2> select * from t where owner = 'X';
no rows selected
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES YES 01/02/2012 20:37:45
T_IDX2 T YES YES 01/02/2012 20:37:45
T_IDX3 T YES YES 01/02/2012 20:37:45
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES YES 01/02/2012 20:37:45
T_IDX2 T YES YES 01/02/2012 20:37:45
T_IDX3 T YES YES 01/02/2012 20:37:45
ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> set linesize 1000
sys%ORA11GR2> select * from v$object_usage;
no rows selected
sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
T_IDX1 T YES YES 01/02/2012 20:37:45
T_IDX2 T YES YES 01/02/2012 20:37:45
T_IDX3 T YES YES 01/02/2012 20:37:45
index which is not being used
sikki, January 02, 2012 - 9:49 pm UTC
Tom,
Thanks for your update. Apologize, fault was from me. Working well.
Nice script
Gabriel Schor, August 11, 2015 - 3:47 pm UTC
Hello Tom,
Nice script but if the columns in the index have similar values it will flag the indexes as similar. For example if we have index I on column col and index I1 on column col1 then
"a.index_cols like b.index_cols || '%'" will evaluate to true. If we add a "," before the "%" it will work though:
"and a.index_cols like b.index_cols || ',%';"
Thank you,
Index usage in 12.2
Rajeshwaran, Jeyabal, July 11, 2018 - 1:44 pm UTC
Team,
Was reading this content
https://blog.dbi-services.com/12cr2-new-index-usage-tracking/ - new index usage tracking in 12.2
without setting "alter session set "_iut_stat_collection_type"=ALL;" this parameter in the session, i could see that index monitoring work for me.
as mentioned in that blog, is that need to be set?
demo@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> drop table t purge;
Table dropped.
demo@ORA12C> variable x number
demo@ORA12C> declare
2 l_intval int ;
3 l_strval varchar2(100);
4
5 begin
6 :x := dbms_utility.get_parameter_value(
7 parnam=>'_iut_stat_collection_type',
8 intval=>l_intval,
9 strval=>l_strval );
10 dbms_output.put_line( 'l_intval = '|| l_intval);
11 dbms_output.put_line( 'l_strval = '|| l_strval);
12 end;
13 /
l_intval = 7
l_strval = SAMPLED
PL/SQL procedure successfully completed.
demo@ORA12C> print x
X
----------
1
demo@ORA12C> create table t
2 as
3 select *
4 from all_objects;
Table created.
demo@ORA12C> create index t_idx1 on t(object_id);
Index created.
demo@ORA12C> create index t_idx2 on t(data_object_id);
Index created.
demo@ORA12C> @printtbl "select * from v$index_usage_info"
INDEX_STATS_ENABLED : "1"
INDEX_STATS_COLLECTION_TYPE : "0"
ACTIVE_ELEM_COUNT : "2"
ALLOC_ELEM_COUNT : "6"
MAX_ELEM_COUNT : "30000"
FLUSH_COUNT : "4"
TOTAL_FLUSH_DURATION : "231875"
LAST_FLUSH_TIME : "11-JUL-18 06.34.55.475 PM"
STATUS_MSG : ""
CON_ID : "0"
-----------------
PL/SQL procedure successfully completed.
demo@ORA12C>
demo@ORA12C> select count(*) from t where object_id between 55 and 150;
COUNT(*)
----------
3
demo@ORA12C> select count(*) from t where data_object_id between 55 and 1500;
COUNT(*)
----------
19
demo@ORA12C> @printtbl "select * from v$index_usage_info"
INDEX_STATS_ENABLED : "1"
INDEX_STATS_COLLECTION_TYPE : "0"
ACTIVE_ELEM_COUNT : "4"
ALLOC_ELEM_COUNT : "8"
MAX_ELEM_COUNT : "30000"
FLUSH_COUNT : "4"
TOTAL_FLUSH_DURATION : "231875"
LAST_FLUSH_TIME : "11-JUL-18 06.34.55.475 PM"
STATUS_MSG : ""
CON_ID : "0"
-----------------
PL/SQL procedure successfully completed.
demo@ORA12C> exec dbms_lock.sleep(900);
PL/SQL procedure successfully completed.
demo@ORA12C> @printtbl "select * from v$index_usage_info"
INDEX_STATS_ENABLED : "1"
INDEX_STATS_COLLECTION_TYPE : "0"
ACTIVE_ELEM_COUNT : "0"
ALLOC_ELEM_COUNT : "8"
MAX_ELEM_COUNT : "30000"
FLUSH_COUNT : "6"
TOTAL_FLUSH_DURATION : "243733"
LAST_FLUSH_TIME : "11-JUL-18 07.04.59.264 PM"
STATUS_MSG : ""
CON_ID : "0"
-----------------
PL/SQL procedure successfully completed.
demo@ORA12C> @printtbl 'select * from dba_index_usage where owner ="DEMO" and name in ("T_IDX1","T_IDX2")'
OBJECT_ID : "107259"
NAME : "T_IDX1"
OWNER : "DEMO"
TOTAL_ACCESS_COUNT : "1"
TOTAL_EXEC_COUNT : "1"
TOTAL_ROWS_RETURNED : "3"
BUCKET_0_ACCESS_COUNT : "0"
BUCKET_1_ACCESS_COUNT : "0"
BUCKET_2_10_ACCESS_COUNT : "1"
BUCKET_2_10_ROWS_RETURNED : "3"
BUCKET_11_100_ACCESS_COUNT : "0"
BUCKET_11_100_ROWS_RETURNED : "0"
BUCKET_101_1000_ACCESS_COUNT : "0"
BUCKET_101_1000_ROWS_RETURNED : "0"
BUCKET_1000_PLUS_ACCESS_COUNT : "0"
BUCKET_1000_PLUS_ROWS_RETURNED: "0"
LAST_USED : "11-jul-2018 18:49:57"
-----------------
OBJECT_ID : "107260"
NAME : "T_IDX2"
OWNER : "DEMO"
TOTAL_ACCESS_COUNT : "1"
TOTAL_EXEC_COUNT : "1"
TOTAL_ROWS_RETURNED : "19"
BUCKET_0_ACCESS_COUNT : "0"
BUCKET_1_ACCESS_COUNT : "0"
BUCKET_2_10_ACCESS_COUNT : "0"
BUCKET_2_10_ROWS_RETURNED : "0"
BUCKET_11_100_ACCESS_COUNT : "1"
BUCKET_11_100_ROWS_RETURNED : "19"
BUCKET_101_1000_ACCESS_COUNT : "0"
BUCKET_101_1000_ROWS_RETURNED : "0"
BUCKET_1000_PLUS_ACCESS_COUNT : "0"
BUCKET_1000_PLUS_ROWS_RETURNED: "0"
LAST_USED : "11-jul-2018 18:49:57"
-----------------
PL/SQL procedure successfully completed.
demo@ORA12C>
July 12, 2018 - 9:45 am UTC
That parameter controls how aggressively we track the index usage.
By default it is using sampling - that keeps the overhead very small. You *can* set it to "all" to track *every* execution...but that comes with a price.
Frank did it on his blog post to make sure that his results were repeatable.
Font Size
Rohnny, August 31, 2020 - 4:30 pm UTC
Website Critisism:
While you have so much screen space available, why are you using such a small font size for the code portion? The font size needs at-least 2 pixels more.
Seems you really do not have consideration for people having sight-related challenges. Not a good UI practice, i must say.
September 02, 2020 - 12:10 am UTC
Thanks for your feedback....We run a balancing act here ....
Every time we make any element slightly larger, we get swamped with "Please make it smaller to fit more on the screen".
Every time we make any element slightly smaller, we get swamped with "Please make it larger to make it easier to read".
We do our best