Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Function based index
kartik
post Apr 3 2012, 03:07 AM
Post #1


Advanced Member
***

Group: Members
Posts: 44
Joined: 23-November 09
Member No.: 42,208



Hello,

One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan?

Kindly suggest and please let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.

Regards,
Kartik
Go to the top of the page
 
+Quote Post
burleson
post Apr 3 2012, 07:05 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,397
Joined: 26-January 04
Member No.: 13



Hi Katrik,

>> what could be the reason for the query to not to pick the FBIndx?

Good question!

Please read:

http://www.dba-oracle.com/t_index_not_using_index.htm

optimizer_mode: If you are using the default value of all_rows, the query my think that a full-table can is "better" than your function-based index. Remember, the all_rows optimizer mode optimizes to minimize computing resources, while the first_rows_n optimizer_mode optimizes to minimize response time.
<li>dbms_stats: If your function-based index has not been fully analyzed by dbms_stats, the optimizer may be ignoring the index. See here using statistics for indexes
<li>Bugs: It is rare, but you may need to check Oracle technical support for bugs relating to index usage.
<li>Other indexes: You may have other indexes that Oracle perceives as being "better" for the query. To test this. use the SQL*Plus "set autotrace on" and "set timing on: commands and time the queries, once with the default and again using an index hint. The "best" query for response time is the one that fetches the rows with the fewest consistent gets.

***************************************************
>> Kindly suggest and please let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.

You probably did not analyze the function-based index, that's why it is not being invoked:

Try this first:

http://www.dba-oracle.com/t_statistics_fun...based_index.htm

If you can't use a hint, adjusting stats amnd sewapping plans might be your only option.

But the big question is "why" the CBO did not choose your FBI? Testing to force Oracle to use an index: is easy:

  • optimizer_index_cost_adj: If you are already using cost-based optimization and you have already run system stats, ty setting optimizer_index_cost_adj=10. This will temporarily force the use of the index:
alter session set optimizer_index_cost_adj=10;.

  • Flush the shared pool: In a test environment you can also issue the "alter system flush shared pool" command to re-initialize all SQL.
alter system flush_shared_pool;.



Can you post your index create and plan?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Apr 3 2012, 08:31 AM
Post #3


Advanced Member
***

Group: Members
Posts: 11,397
Joined: 26-January 04
Member No.: 13



Hi Kartic,

For testing, were you able to force the DBI with an index hint?

I know you cannot use it in production, but you need to see if your index provided less consistent gets (using set autotrace on) than the existing index.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
kartik
post Apr 4 2012, 01:10 AM
Post #4


Advanced Member
***

Group: Members
Posts: 44
Joined: 23-November 09
Member No.: 42,208



QUOTE (burleson @ Apr 3 2012, 08:31 AM) *
Hi Kartic,

For testing, were you able to force the DBI with an index hint?

I know you cannot use it in production, but you need to see if your index provided less consistent gets (using set autotrace on) than the existing index.



Thanks Don for your help, I did below and query is now picking the index

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

Then analysed the table and FB indx using dbs_stats as you suggested.

Query is now picking the indexes.

Thanks Again,
Kartik
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th August 2014 - 01:29 PM