Re: Re: Index usage
sorry, the second query uses equality operator.. WHERE UPPER(col1) = 'xyz'; index hint is not helping. regards, B S Pradhan -- On Thu, 08 Jan 2004 zions swordfish wrote : hi, pradhan, I don't see any kind of differences with your two queries, but I suggest you to use hint in your query to activate index. Regards, Sony - Original Message - DATE: Thu, 08 Jan 2004 07:09:26 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Cc: Hi All, I have the query like: SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; Obviously, the function based index i have is not hetting used because of the ineqality. When using SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; index is getting used. How can I possibly use index in the 1st case. Thanks and Regards, B S Pradhan Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005
Index usage
Hi All, I have the query like: SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; Obviously, the function based index i have is not hetting used because of the ineqality. When using SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; index is getting used. How can I possibly use index in the 1st case. Thanks and Regards, B S Pradhan
Re: Index usage
hi, pradhan,I don't see any kind of differences with your two queries, butI suggest you to use hint in your query to activate index.Regards,Sony - Original Message - DATE: Thu, 08 Jan 2004 07:09:26 From: "bhabani s pradhan" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Cc: Hi All,I have the query like:SELECT *FROM tab1WHERE UPPER(col1) 'abc';Obviously, the function based index i have is not hetting used because of the ineqality.When using SELECT *FROM tab1WHERE UPPER(col1) 'abc';index is getting used.How can I possibly use index in the 1st case.Thanks and Regards,B S Pradhan Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!Login To Lycos Mail
RE: Index usage
Am I missing something? It looks to me like these queries are the same... -Original Message- Sent: Thursday, January 08, 2004 9:09 AM To: Multiple recipients of list ORACLE-L Hi All, I have the query like: SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; Obviously, the function based index i have is not hetting used because of the ineqality. When using SELECT * FROM tab1 WHERE UPPER(col1) 'abc'; index is getting used. How can I possibly use index in the 1st case. Thanks and Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Re: Index usage
Thanks. Regards, B S Pradhan - On Fri, 26 Dec 2003 zhu chao wrote : Hi, To see why oracle choose FTS, alter session set events '10053 trace name context forever,level 2'; You can do alter session to change index_adj and optimizer_index_caching to change only your session, or using hint. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 8:09 PM Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: Hi All, Merry Christmas to all I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan --Please see the official ORACLE-L FAQ: http://www.orafaq.net --Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego,
Re: Re: Index usage
Hi, Nice example and it should behave similarly in my case also. I have to research more.. Regards, B S Pradhan --- On Wed, 24 Dec 2003 zhu chao wrote : Hi, It cound be possible that without Hint, oracle will choose FTS for second SQL, because with col3 clause, if using index, oracle will have to do a range scan on index ind1 and than table access by rowid. If CBO thinks that col1='val1' will get a lot of rows then doing FTS may be cheaper.But with hint, oracle should be able to pick that index. Sample: 00:48:18 [EMAIL PROTECTED] create table test as select * from dba_tables; Table created. 00:48:45 [EMAIL PROTECTED] create index ind1 on test(owner,table_name) compute statistics; Index created. 00:49:39 [EMAIL PROTECTED] select 'x' from test where owner='PUBLIC'; no rows selected Elapsed: 00:00:00.03 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=5 Bytes=15) 10 INDEX (FAST FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=2 Card =5 Bytes=15) 00:50:00 [EMAIL PROTECTED] select 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM'; no rows selected Elapsed: 00:00:00.00 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=20) 10 TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=20) 00:51:14 [EMAIL PROTECTED] select /*+index(test ind1)*/ 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM'; no rows selected Elapsed: 00:00:00.01 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=20) 10 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=5 Card=1 Byt es=20) 21 INDEX (FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=3 Card=5) - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 24, 2003 9:59 PM Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Index usage
Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Index usage
I think you should start by posting the exactSQL with exact execution plan to us, there's no point in wild guessing... Tanel. - Original Message - From: bhabani s pradhan To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 2:09 PM Subject: Re: Re: Index usage Hi All,Agreed.. and it should behave that way i.eif (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization.So, w/o a hint that is expected.But why it is not picking the index in my case i donot know.Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ?Thanks for all the inputs.Regards,B S PradhanOn Wed, 24 Dec 2003 Mike Spalinger wrote :The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3.Mikeanu wrote:No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index.2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck."Daniel W. Fink" [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote:Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used.SELECT 'x'FROM tab ta WHERE ta.c1='val1';(gives index ind1 range scan) But for SELECT 'x' FROM tab taWHERE ta.c1='val1'AND ta.c3 = 'val2';(gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).!Do you Yahoo!?Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mike Spalinger INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California-- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Re: Index usage
Hi, To see why oracle choose FTS, alter session set events '10053 trace name context forever,level 2'; You can do alter session to change index_adj and optimizer_index_caching to change only your session, or using hint. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 25, 2003 8:09 PM Hi All, Agreed.. and it should behave that way i.e if (cost of ind1 scan + then based on c1's selection table access for c3) (direct table access for c1 and c1) then oracle will use FTS with cost based optimization. So, w/o a hint that is expected. But why it is not picking the index in my case i donot know. Also, can optimizer_index_cost_adj help? Its 100 now. Also that affects the whole DB, so is there any way to set it for this particular query ? Thanks for all the inputs. Regards, B S Pradhan On Wed, 24 Dec 2003 Mike Spalinger wrote : The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: Hi All, Merry Christmas to all I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan --Please see the official ORACLE-L FAQ: http://www.orafaq.net --Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to:
Index usage
Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan
Re: Index usage
You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index usage
No. The index should get used.Thequery result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan.The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index.2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full tablescan. What is the cardinality like?It is strange thatRULE or index hint is not taking it.Can you try a simple index(ta) hint or send your hint syntax.Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. "Daniel W. Fink" [EMAIL PROTECTED] wrote: You answered your own question.ta.c3 is a nonindexed column, this means that the only way to satisfy thepredicate is to perform a full table scan. Since this predicate condition forcesa full table scan on ta, which will retrieve the ta.c1 column values at the sametime, there is no need to use an index. In fact, an additional index accesswould decrease the query performance.Daniel Finkbhabani s pradhan wrote: Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Daniel W. FinkINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).! Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: Index usage
The difference is that the first query never has to go to the table (because you're selecting a constant 'x'). The second query has to go to the table to filter on c3. Mike anu wrote: No. The index should get used. The query result for query 2 is a subset of rows with ta.c1='val1' will get selected. Subset of query 1. So there is no need for a full table scan. The index can be used in the following way : 1) Use index ind1 to get rows with ta.c1='val1' (which is query 1). This can definitely use an index. 2) Further filter using ta.c3 = 'val2' Now may be the index is not very selective and the optimizer is going in for a full table scan. What is the cardinality like? It is strange that RULE or index hint is not taking it. Can you try a simple index(ta) hint or send your hint syntax. Can you try the hint on another table to make sure hint is working. I do not know why hint should not work. Good luck. Daniel W. Fink [EMAIL PROTECTED] wrote: You answered your own question. ta.c3 is a nonindexed column, this means that the only way to satisfy the predicate is to perform a full table scan. Since this predicate condition forces a full table scan on ta, which will retrieve the ta.c1 column values at the same time, there is no need to use an index. In fact, an additional index access would decrease the query performance. Daniel Fink bhabani s pradhan wrote: Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ! Do you Yahoo!? Free Pop-Up Blocker - Get it now http://us.rd.yahoo.com/slv/mailtag/*http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mike Spalinger INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index usage
Hi, It cound be possible that without Hint, oracle will choose FTS for second SQL, because with col3 clause, if using index, oracle will have to do a range scan on index ind1 and than table access by rowid. If CBO thinks that col1='val1' will get a lot of rows then doing FTS may be cheaper.But with hint, oracle should be able to pick that index. Sample: 00:48:18 [EMAIL PROTECTED] create table test as select * from dba_tables; Table created. 00:48:45 [EMAIL PROTECTED] create index ind1 on test(owner,table_name) compute statistics; Index created. 00:49:39 [EMAIL PROTECTED] select 'x' from test where owner='PUBLIC'; no rows selected Elapsed: 00:00:00.03 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=5 Bytes=15) 10 INDEX (FAST FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=2 Card =5 Bytes=15) 00:50:00 [EMAIL PROTECTED] select 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM'; no rows selected Elapsed: 00:00:00.00 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=20) 10 TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=20) 00:51:14 [EMAIL PROTECTED] select /*+index(test ind1)*/ 'x' from test where owner='PUBLIC' and tablespace_name='SYSTEM'; no rows selected Elapsed: 00:00:00.01 Execution Plan -- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=20) 10 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=5 Card=1 Byt es=20) 21 INDEX (FULL SCAN) OF 'IND1' (NON-UNIQUE) (Cost=3 Card=5) - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 24, 2003 9:59 PM Hi All, Merry Christmas to all. I have this interesting problem.. For this query index ind1 on (c1,c2) columns is getting used. SELECT 'x' FROM tab ta WHERE ta.c1='val1'; (gives index ind1 range scan) But for SELECT 'x' FROM tab ta WHERE ta.c1='val1' AND ta.c3 = 'val2'; (gives FTS) index ind1 is not being used. c3 is a nonindexed column. I have already tried index(ta ind1) , RULE hints. The table and the index are analyzed. What cud be the reason for that? Regards, B S Pradhan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Monitor Index Usage
Note that 9.2 has the nice family of v$segment_statistic views that can give you this level of information very easily. It has advantages over v$object_usage in that is gives you an indication on how often indexes are used, rather than that they've been used. Although sampling and other factors may impact their accuracy, those indexes that have a very high ratio of logical reads to db block changes you know are highly used for legitimate index accesses, those closer to a 2-1 ratio are only being accessed mainly due to dml changes. A bit of investigation and experimentation and these views can be very useful. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 10:19 AM Check this http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:531147287 002 HTH GovindanK Oracle Certified Professional(8,8i) Brainbench Certified Master DBA(8) On Sun, 09 Nov 2003 20:59:25 -0800, Arvind Kumar [EMAIL PROTECTED] said: Hi all, is there a way to monitor index usages in oracle 8i ,like 9i v$object_usage? Thanks Arvind Kumar -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GKatteri INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Monitor Index Usage
Richard ... thanks for this advise ... now I can add that to my list of observations ... Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, November 11, 2003 8:20 AM To: Multiple recipients of list ORACLE-L Note that 9.2 has the nice family of v$segment_statistic views that can give you this level of information very easily. It has advantages over v$object_usage in that is gives you an indication on how often indexes are used, rather than that they've been used. Although sampling and other factors may impact their accuracy, those indexes that have a very high ratio of logical reads to db block changes you know are highly used for legitimate index accesses, those closer to a 2-1 ratio are only being accessed mainly due to dml changes. A bit of investigation and experimentation and these views can be very useful. Cheers Richard ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Monitor Index Usage
Check this http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:531147287002 HTH GovindanK Oracle Certified Professional(8,8i) Brainbench Certified Master DBA(8) On Sun, 09 Nov 2003 20:59:25 -0800, Arvind Kumar [EMAIL PROTECTED] said: Hi all, is there a way to monitor index usages in oracle 8i ,like 9i v$object_usage? Thanks Arvind Kumar -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GKatteri INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Monitor Index Usage
Hi all, is there a way to monitor index usages in oracle 8i ,like 9i v$object_usage? Thanks Arvind Kumar
Re: Index Usage ?!
Once again thanx a lot Tanel for spending your precious time to make me understand. Regards, Jp. 28-07-2003 22:59:25, Tanel Poder [EMAIL PROTECTED] wrote: The point is, that index access is cheap in reality, but CBO thinks it's very expensive and chooses next best executin plan in it's opinion, but this opinion is actually wrong, because unset optimizer_ parameters for example. One more thing, your test query returned no rows, it could be that with index access Oracle didn't even have to visit any table blocks because no relevant keys found in index. But when you'll have keys matching your query conditions in future, index access might get slower as well (here's where clustering_factor comes into play). Without analyzing, the index cost in reality is still low, and with super-optimistic default statistics CBO luckily picks that path. No, according plan in your orignal post, index range scan was used. Btw, I submitted a simple research about CBO table/index access costs to comp.databases.oracle.server yesterday under subject 2 Oracel doubts, you might want to read that one as well to get more understanding on your issue. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
My only doubt is: 1.After analyzing the index,CBO doesn't read the index because it is costlier in terms of I/O and other resources. The point is, that index access is cheap in reality, but CBO thinks it's very expensive and chooses next best executin plan in it's opinion, but this opinion is actually wrong, because unset optimizer_ parameters for example. One more thing, your test query returned no rows, it could be that with index access Oracle didn't even have to visit any table blocks because no relevant keys found in index. But when you'll have keys matching your query conditions in future, index access might get slower as well (here's where clustering_factor comes into play). 2.But without analyzing,CBO uses the index now,which is supposed to be coslty,and hence incurs the same I/O and other resources . Without analyzing, the index cost in reality is still low, and with super-optimistic default statistics CBO luckily picks that path. 3.when the role of CBO is over,the h/w resources comes into play to read the index.even now it reads the full index and the response is faster.how is it so ? No, according plan in your orignal post, index range scan was used. Btw, I submitted a simple research about CBO table/index access costs to comp.databases.oracle.server yesterday under subject 2 Oracel doubts, you might want to read that one as well to get more understanding on your issue. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi Tanel, thanks a lot for your wonderful explanation. sorry to pester u you again with my doubts on the first day of the week. My only doubt is: 1.After analyzing the index,CBO doesn't read the index because it is costlier in terms of I/O and other resources. 2.But without analyzing,CBO uses the index now,which is supposed to be coslty,and hence incurs the same I/O and other resources . 3.when the role of CBO is over,the h/w resources comes into play to read the index.even now it reads the full index and the response is faster.how is it so ? hope i am clear in telling u what i had understood ?! plz. excuse me if i'm sound silly and stupid. of the above 3 points,where am i wrong ? Regards, Jp. 25-7-2003 21:24:45, Tanel Poder [EMAIL PROTECTED] wrote: Hi! Your original post shows that when index was not analyzed, optimizer used default statistics and found out that index scan is quite cheap. Also, no sorting had to be done, since descending index range scan could be used to satisfy your order by clause. Whe index was analyzed, then CBO actually saw, that index isn't that good as defaul values showed, the number of leaf blocks was higher and also the clustering factor was probably much-mugh higher, meaning that for any non-unique index key value it has to visit several different data blocks to get all matching rows. That means lot's of IOs. Eventually CBO decided that it's cheaper to do let say 1600 multiblock (plus one segment header + possibly bitmap) reads directly and scan through the whole table than to traverse through index branches, scan leaf blocks and visit every data block individually. So, with not-analyzed index, CBO had nothing else to do, than to be super-optimistic about the index (#LB, CLUF) thus using index in your case. But when analyzed, CBO had accurate data, but did make bad decision because too pessimistic values for optimizer_index_cost_adj and optimizer_index_caching. You should set them at session level for testing to let say 50 and 90, but read the document below for understanding those parameters. http://www.evdbt.com/SearchIntelligenceCBO.doc Cheers, Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. Kindly throw some light on this. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Tanel, quoteAre your other indexes analyzed?/quote Yes. Case 1: If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier. Case 2: Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now), CBO takes default values and decides that index scan is cheaper. As per case 1,the I/O to read the index will be costlier. So CBO decides not to use the index. But as per case 2 (existing situation): After the decision is made by CBO, it still needs to read the index and then fetch table data. So the same I/O to read the index is going to happen as in case 1. * correct me if i'm wrong * Aren't they contradictory ?! So how does case 2 benefit in terms of CPU or memory consumption ? and there is a big diff. in response time between both cases...how ? ...totally confused :( Regards, Jp. 25-07-2003 18:34:23, Tanel Poder [EMAIL PROTECTED] wrote: Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Thanx a lot Irfan. let me also go thro' metalink and try out something. Regards, Jp. 25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote: From what i understand when u analyze , use the keyword of size to set the histogram buckets Generally it should be no of distinct values in a column + som ball park no. say ten. U can play around with this analyzing with histograms. I tried and it worked for me and so it should for you as well. I will do more research and will get back to you. Irfan , Khan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi! Your original post shows that when index was not analyzed, optimizer used default statistics and found out that index scan is quite cheap. Also, no sorting had to be done, since descending index range scan could be used to satisfy your order by clause. Whe index was analyzed, then CBO actually saw, that index isn't that good as defaul values showed, the number of leaf blocks was higher and also the clustering factor was probably much-mugh higher, meaning that for any non-unique index key value it has to visit several different data blocks to get all matching rows. That means lot's of IOs. Eventually CBO decided that it's cheaper to do let say 1600 multiblock (plus one segment header + possibly bitmap) reads directly and scan through the whole table than to traverse through index branches, scan leaf blocks and visit every data block individually. So, with not-analyzed index, CBO had nothing else to do, than to be super-optimistic about the index (#LB, CLUF) thus using index in your case. But when analyzed, CBO had accurate data, but did make bad decision because too pessimistic values for optimizer_index_cost_adj and optimizer_index_caching. You should set them at session level for testing to let say 50 and 90, but read the document below for understanding those parameters. http://www.evdbt.com/SearchIntelligenceCBO.doc Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 25, 2003 2:54 PM Tanel, quoteAre your other indexes analyzed?/quote Yes. Case 1: If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier. Case 2: Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now), CBO takes default values and decides that index scan is cheaper. As per case 1,the I/O to read the index will be costlier. So CBO decides not to use the index. But as per case 2 (existing situation): After the decision is made by CBO, it still needs to read the index and then fetch table data. So the same I/O to read the index is going to happen as in case 1. * correct me if i'm wrong * Aren't they contradictory ?! So how does case 2 benefit in terms of CPU or memory consumption ? and there is a big diff. in response time between both cases...how ? ...totally confused :( Regards, Jp. 25-07-2003 18:34:23, Tanel Poder [EMAIL PROTECTED] wrote: Hi! but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed? 2.does it mean that a composite index is better than individual ones ? Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups short range scans significantly. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi! 25-07-2003 18:46:51, Irfan Khan [EMAIL PROTECTED] wrote: From what i understand when u analyze , use the keyword of size to set the histogram buckets Generally it should be no of distinct values in a column + som ball park no. say ten. Max nr. of buckets is 254 per column, thus in some conditions you can't have a bucket for each value. But more importantly, histograms aren't used with queries using bind values since during next query the bind variable could be set to completely different value and in order to compute a more appropriate execution plan we would have to go through the most expensive parse phase again. Also, when not using bind variables (which is a bad idea in OLTP because additional parses), using histograms makes each individual parse (which involves analyzing predicates on histogrammed columns) even slower. OTOH, histograms are good for processing the data in large quantities, but bad or even unusable in high concurrency OLTP environments. Of course maybe 10G already has some kind of pre-parsing enchancements where multiple execution plans are calculated for each possible bind variable value range (bucket). We'll see ;) Tanel. U can play around with this analyzing with histograms. I tried and it worked for me and so it should for you as well. I will do more research and will get back to you. Irfan , Khan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index Usage ?!
Guys, SQL SELECT NAME,AGE,MEMNO,BLOODTYPE,HIGHT,LOOKSLIKE,CONTENT,PICID FROM PROFILE WHERE PREF = :PREF AND SEX = :OP_SEX AND FLAG = :FLAG AND ENTPC = :ENTPC AND NAME IS NOT NULL AND FACCESS SYSDATE - 14 ORDER BY ENTDAY DESC; NO ROWS SELECTED ELAPSED: 00:00:00.07 EXECUTION PLAN -- 0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=7 CARD=187 BYTES=13838) 10 TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE' (COST=7 CARD=187 BYTES=13838) 21 INDEX (RANGE SCAN DESCENDING) OF 'IDX_PROFILE_SHINKI' (NON-UNIQUE) (COST=2 CARD=3759) STATISTICS -- 0 RECURSIVE CALLS 0 DB BLOCK GETS 0 CONSISTENT GETS 0 PHYSICAL READS 0 REDO SIZE 599 BYTES SENT VIA SQL*NET TO CLIENT 372 BYTES RECEIVED VIA SQL*NET FROM CLIENT 1 SQL*NET ROUNDTRIPS TO/FROM CLIENT 0 SORTS (MEMORY) 0 SORTS (DISK) 0 ROWS PROCESSED after analyzing the index idx_profile_shinki , the exection plan is as below and the elapsed time is 31 secs.but before analyzing the elapsed time was 0.07 secs ( as above ). ELAPSED: 00:00:31.04 EXECUTION PLAN -- 0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=1680 CARD=187 BYTES=13838) 10 SORT (ORDER BY) (COST=1680 CARD=187 BYTES=13838) 21 TABLE ACCESS (FULL) OF 'PROFILE' (COST=1676 CARD=187 BYTES=13838) STATISTICS -- 0 RECURSIVE CALLS 0 DB BLOCK GETS 17448 CONSISTENT GETS 5876 PHYSICAL READS 0 REDO SIZE 599 BYTES SENT VIA SQL*NET TO CLIENT 372 BYTES RECEIVED VIA SQL*NET FROM CLIENT 1 SQL*NET ROUNDTRIPS TO/FROM CLIENT 1 SORTS (MEMORY) 0 SORTS (DISK) 0 ROWS PROCESSED the env. is 9.2.0.3/win2k-sp3. it is built on profile table . the index has the columns entpc,sex,flag,pref,entday in it. the order of the columns in the index is also the same as i have mentioned. 1.so ,does it mean that idx_profile_shinki is a bad index. 2.if it is bad , why does cbo select this index ? 3.if it is good , why does elapsed time increase after analyzing this index ? 4.the leading column (entpc) of the index is not there in the beginning of where clause. then how is the index used ? it was not said so in a perf tuning book by richard j. niemiec It's eating my brains.Kindly explain me Gurus. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi! (B (B the env. is 9.2.0.3/win2k-sp3. (B (B it is built on "profile" table . (B the index has the columns "entpc,sex,flag,pref,entday" in it. (B the order of the columns in the index is also the same as i have (Bmentioned. (B (B 1.so ,does it mean that "idx_profile_shinki" is a bad index. (B 2.if it is bad , why does cbo select this index ? (B (Bdid you analyze your table in addition to index as well? (Bfirst time you were probably using RBO, which always counts index access (Bbetter than table access. (Bbut after analyzing your segments CBO thinks that full table scan is better (Bbecause of optimizer_index_cost_adj and optimizer_index_caching parameters. (Bwhat are your values of those parameters? (also (Bdb_file_multiblock_read_count). (B (B 3.if it is good , why does elapsed time increase after analyzing this (Bindex ? (B 4.the leading column (entpc) of the index is not there in the beginning of (B"where" clause. (B then how is the index used ? it was not said so in a perf tuning book by (B"richard j. niemiec" (B (Bdoesn't matter, as long as your column is somewhere in the where clause. (B(note than in 9i indexes can sometimes be used even when first column(s) of (Bindex aren't in where clauses - it's called index skip scanning). (B (BIf you already have analyzed your indexes and tables, have set the (Bparameters, then run alter session set events '10053 trace name context (Bforever, level 1'; (Bthen run your statements and send me the trace file from udump. (B (BTanel. (B (B (B (B It's eating my brains.Kindly explain me Gurus. (B (B Regards, (B Jp. (B (B (B (B -- (B Please see the official ORACLE-L FAQ: http://www.orafaq.net (B -- (B Author: Prem Khanna J (B INET: [EMAIL PROTECTED] (B (B Fat City Network Services-- 858-538-5051 http://www.fatcity.com (B San Diego, California-- Mailing list and web hosting services (B - (B To REMOVE yourself from this mailing list, send an E-Mail message (B to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (B the message BODY, include a line containing: UNSUB ORACLE-L (B (or the name of mailing list you want to be removed from). You may (B also send the HELP command for other information (like subscribing). (B (B (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Tanel Poder (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Hi Tanel, (B (Bquote (Bdid you analyze your table in addition to index as well? (Bfirst time you were probably using RBO, which always counts index access (Bbetter than table access. (B/quote (B (Bi have analyzed PROFILE table also and hope it's CBO by default in 9i. (Banyway,it is CBO right from the beginning in my case here. (B (BSQLselect num_rows,avg_row_len,chain_cnt from user_Tables where table_name='PROFILE'; (B NUM_ROWS AVG_ROW_LEN CHAIN_CNT BLOCKS (B --- - -- (B736820 168 42 17407 (B (Bquotebecause of optimizer_index_cost_adj and optimizer_index_caching (Bparameters./quote (B (Boptimizer_index_cost_adj = 100 (Boptimizer_index_caching = 0 (Bdb_file_multiblock_read_count = 16 (B (Bquoteit's called index skip scanning/quote (BThanx for the info Tanel. I was not knowing this. (B (BAs u said ,I have attached the Trace file also. (B (BKindly throw some light on this Tanel. (B (BRegards, (BJp. memb_ora_2400.trc Description: Binary data
Re: Index Usage ?!
The trace seems to be from when the index is not analyzed. The CBO then uses defaults for the index statistics - leaf_blocks=25 and clustering factor=800. These defaults are much lower than when the index is analyzed and the resulting cost for using the index is very low (7 compared to 1676 for a full scan). When you analyze the index, the statistics will be orders of magnitude larger - I estimate that the clustering factor will be 300,000, and therefore the cost of using the index exceeds that of the full scan ( still 1676 ). There are two things you can do Leave the index un-analyzed if it works for you ( I have a few tables where I use that trick) Set optimizer_index_cost_adj to a value lower than 100 - again if it works for you. Test that it does not adversely affect other queries. Many advocate that it should be set lower but I have not had any luck with it. At 04:24 AM 7/24/2003 -0800, you wrote: Hi Tanel, quote did you analyze your table in addition to index as well? first time you were probably using RBO, which always counts index access better than table access. /quote i have analyzed PROFILE table also and hope it's CBO by default in 9i. anyway,it is CBO right from the beginning in my case here. SQLselect num_rows,avg_row_len,chain_cnt from user_Tables where table_name='PROFILE'; NUM_ROWS AVG_ROW_LEN CHAIN_CNT BLOCKS --- - -- 736820 168 42 17407 quotebecause of optimizer_index_cost_adj and optimizer_index_caching parameters./quote optimizer_index_cost_adj = 100 optimizer_index_caching = 0 db_file_multiblock_read_count = 16 quoteit's called index skip scanning/quote Thanx for the info Tanel. I was not knowing this. As u said ,I have attached the Trace file also. Kindly throw some light on this Tanel. Regards, Jp. Content-Disposition: attachment; filename==?iso-2022-jp?Q?memb=5Fora=5F2400.trc?= Content-Type: application/octet-stream Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index Usage ?!
Thanx Breitling. quoteLeave the index un-analyzed if it works for you/quote as u said,index IDX_PROFILE_SHINKI is not analyzed now. that works fine. but there are seperate indexes on column PREF, FLAG and SEX. when those indexes are used ,cost=999. whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC, FLAG,SEX,PREF,ENTDAY) is used , cost=7. 1.whys there is a huge difference in COST ? 2.does it mean that a composite index is better than individual ones ? Kindly throw some light on this. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index usage in 8.1.6
How do you monitor if the indexes are being used in 8i? Individually the tkprof and explain plans do the trick. However from a running system, how can the index usage be determined? If the index is being used? How many times did the index get used during a specific time frame etc. Regards MenonDo you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more
RE: Index usage in 8.1.6
Murali If I'm not mistaken, the first Oracle version that offers real support for this is Oracle9i. I think what you are doing is about all that can be done -- capture SQL, run explain plan on it and make a list of the indexes that are used. If you look around, there are scripts to help and packages that automate this. But in the end you still have to answer the question of how long do you wait to see if an index isn't used before you drop it -- a day, a week, a month? There is always that danger that there will be a really critical report that is only run on irregular occasions that will need that index. For example, a manufacturing plant tends to go through a production season. The applications running at the first of the season are different from those run toward the end. If you pick a month at the first of the season, you may drop indexes needed toward the end. If you have a definite indication that your application is over-indexed, this effort may be worth your trouble. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 01, 2003 10:59 AM To: Multiple recipients of list ORACLE-L How do you monitor if the indexes are being used in 8i? Individually the tkprof and explain plans do the trick. However from a running system, how can the index usage be determined? If the index is being used? How many times did the index get used during a specific time frame etc. Regards Menon _ Do you Yahoo!? Yahoo! Tax http://us.rd.yahoo.com/finance/mailsig/*http://tax.yahoo.com Center - File online, calculators, forms, and more -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index usage in 8.1.6
Murali , I have attached a script. i use the same to find index usage. hope this helps. Regards, Jp. FindIndexUsage.sql Description: Binary data
NVL and index usage
Title: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: NVL and index usage
Title: NVL and index usage In a quick test on 9013 it changed the index which it was using and went from Range scan to fast full scan. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, January 02, 2003 9:48 AM Subject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
Re: NVL and index usage
It won't be able to use the index as far as I know. Unless it's a functional index ;). Mogens Jamadagni, Rajendra wrote: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: NVL and index usage
Title: RE: NVL and index usage Thanks ... the development is rolling out a new change by adding a new nullable column to a table and adding following to all appropriate queries ... and nvl(new_column,'A') = nvl(some_value,'A') I learned of this few minutes ago and luckily they are releasing it to development tomorrow. Thanks once again Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 1:22 PM To: Multiple recipients of list ORACLE-L Subject: Re: NVL and index usage It won't be able to use the index as far as I know. Unless it's a functional index ;). Mogens *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: NVL and index usage
If you mean where nvl(col,:x) = :y then yes it will be negated. If memory serves, I've even had problems in the past using nvl in a function based index, the workaround doing the equivalent with decode, but I can't remember the specifics hth connor --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: NVL and index usage
Title: NVL and index usage Raj, the index will not be used. think about it. for those records that do not have a value (are null) for the indexed column- there is not an entry in the index - so they can not be evaluated to return a row. secondly, even if all rows were represented in the index - why perform two queries - one for the index, and then one for the table - to evaluate whether to use the row in the query? either way, it would not work properly. that's why Oracle gave us function based indexes - so that we could use a function and get fast results from a large table. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message-From: Shaleen [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 1:22 PMTo: Multiple recipients of list ORACLE-LSubject: Re: NVL and index usage In a quick test on 9013 it changed the index which it was using and went from Range scan to fast full scan. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, January 02, 2003 9:48 AM Subject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: NVL and index usage
Title: RE: NVL and index usage Thanks Tom and everyone ... as I mentioned they plan to use it as follows ... and nvl(new_column,'A') = nvl(some_value,'A') So I asked them to see the possibility of creating the column with a DEFAULT VALUE of 'A' so the where clause can be written as and new_column = nvl(some_value, 'A') which I think is better and based on all your input, I am sure it will be. Finally it will be their call. When apps stop using indexes, they will call us though (with the standard question 'Did we run stats collection today?') Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 1:51 PM To: Multiple recipients of list ORACLE-L Subject: RE: NVL and index usage Raj, the index will not be used. think about it. for those records that do not have a value (are null) for the indexed column - there is not an entry in the index - so they can not be evaluated to return a row. secondly, even if all rows were represented in the index - why perform two queries - one for the index, and then one for the table - to evaluate whether to use the row in the query? either way, it would not work properly. that's why Oracle gave us function based indexes - so that we could use a function and get fast results from a large table. hope this helps. Tom Mercadante Oracle Certified Professional This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: NVL and index usage
Title: RE: NVL and index usage No idea ... my guess is few years down the line, they will make it varchar2(2) ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 4:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: NVL and index usage Just curious, why varchar2(1) and not char(1)? Regards, Waleed -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 12:48 PM To: Multiple recipients of list ORACLE-L Subject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: NVL and index usage
Raj, If they think that stats need to be collected every day for indexes to work then they obviously don't know much about Oracle... Either that or you have some incredibly volatile tables like a fully refreshed DSS or something. Ideally you should ask the developers which tables should be analyzed and how often. They should understand the movement of data although it sounds like they might not understand Oracle enough anyway. At least they have you looking out for them. Regards, Mark. PS: Since the column is being added to an existing table remember that you will have to update existing rows as well as adding the default clause. Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagni cc: @espn.comSubject: RE: NVL and index usage Sent by: [EMAIL PROTECTED] 03/01/2003 06:11 Please respond to ORACLE-L Thanks Tom and everyone ... as I mentioned they plan to use it as follows ... and nvl(new_column,'A') = nvl(some_value,'A') So I asked them to see the possibility of creating the column with a DEFAULT VALUE of 'A' so the where clause can be written as and new_column = nvl(some_value, 'A') which I think is better and based on all your input, I am sure it will be. Finally it will be their call. When apps stop using indexes, they will call us though (with the standard question 'Did we run stats collection today?') Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, January 02, 2003 1:51 PM To: Multiple recipients of list ORACLE-L Raj, the index will not be used. think about it. for those records that do not have a value (are null) for the indexed column - there is not an entry in the index - so they can not be evaluated to return a row. secondly, even if all rows were represented in the index - why perform two queries - one for the index, and then one for the table - to evaluate whether to use the row in the query? either way, it would not work properly. that's why Oracle gave us function based indexes - so that we could use a function and get fast results from a large table. hope this helps. Tom Mercadante Oracle Certified Professional (See attached file: ESPN_Disclaimer.txt) Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. ESPN_Disclaimer.txt Description: Binary data
RE: NVL and index usage
Title: NVL and index usage Just curios, why varchar2(1) and not char(1)? Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 12:48 PMTo: Multiple recipients of list ORACLE-LSubject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: NVL and index usage
Title: NVL and index usage Just curious, why varchar2(1) and not char(1)? Regards, Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Thursday, January 02, 2003 12:48 PMTo: Multiple recipients of list ORACLE-LSubject: NVL and index usage Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: NVL and index usage
What is the difference? Just curios, why varchar2(1) and not char(1)? Waleed -Original Message- Sent: Thursday, January 02, 2003 12:48 PM To: Multiple recipients of list ORACLE-L Does anyone know off hand if using NVL on an indexed column negate use of an index in CBO? This is 9202 ... and the column will be a varchar2(1). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partition and Index Usage
I think that there is another dimension to the problem that you may not be aware of and that is even when you think that you succeeded to eliminate the use of the index on the batch_date using a hint (no_index), Oracle still has to go the table using the rowid (after conversion from bitmap) to filter on the batch_date predicate. Probably you did not notice that since you have to go there to pick up some other columns that are not included in the index. But if you try: select count(cust_id) using your hints you will find it's still going to the table although nothing is needed from there. The reason is it needs to filter on the batch_date. Something else I need to correct (from my previous reply) and that is the optimizer sometimes does not have to check on the predicates on the partitioning key if it's concluded from partitioning and partitions eliminations. But I see this happening only when it does table/table partition segment full scan. And here comes the question: why? The only answer I can come with: When it's a full table/table partition scan The optimizer is able to do the right thing since there is a direct relation between the table, its partitions and the partition key. But in case of local indexes, it's indirect relationship. The index is just a local index which makes harder for the optimizer to optimize! I'm not saying it is hard to do, I'm saying that the functionality may not be there yet (8.1.7). I do not have access to a 9i database now to compare. Waleed -Original Message- Sent: Saturday, October 12, 2002 10:23 PM To: Multiple recipients of list ORACLE-L Hi Larry, First I would suggest doing daily partitioning and dropping the index on the batch_date. That's been kicked around. It's not a bad idea -- it would make sure the index, since it wouldn't exist, doesn't get in the way. FWIW, it's partitioned on a monthly basis to fall more in line with bulk maintenance operations that are sometimes performed. External feed discovers a problem and resends a month? A simple exchange partition novalidate after the data is loaded in staging and verified. The pruning is another benefit, but not the only reason it's partitioned by month -- 99% of the queries are for month, 2 months, quarter, year, etc. Going to a more granular level will just result in more partitions being examined. Would we get better performance? Maybe, maybe not. Would have to test, and will not be able to build a full blown test for another month or so until an additional 4.2 TB disk space comes on-line. And yeah, where *are* the aggregates ;-) Regarding your sql: partitions eliminations never substitutes the necessity to validate any predicates on the partitioning key in the where clause. And that was really the whole reason for posing this question -- why does the CBO even consider that index, in this particular case where a month range is specified, the same as the partition. It *should* know that the batch date index can be no more or no less than what the partition comprises. But the CBO obviously isn't thinking that way and is evaluating the index selectivity just like it does all the other BMI's. And in some cases decides to go ahead and include it, doing the bitmap merge with multiple other BMI's on which criteria exists. Just thought it was odd that it doesn't take it into consideration, seeing if the boundaries of the criteria on the batch date are the same as the boundaries for the partition. Probably a good reason for it -- it just escapes me what it might be. Maybe to consider an index join between multiple BMI's avoiding hitting the table at all, but it's not doing that. If it's not feasible to partition by day, I would drop the BMI on the batch_date and include the batch_date in the cust_id BMI (local index). This is actually done in a couple of cases on some other tables, but more for the reason of being able to resolve some specific queries against those tables entirely in the index. Anyway, good thoughts and suggestions, I appreciate it. Each of them could address this specific issue. I'm still going to go ahead and dig into the 10053 and stats. Try to get it to include the batch date index when helpful, and avoid it when it's not. Or I could always set the distinct keys to 1 or drop the index ;-). FWIW, this isn't a huge problem -- the performance when it includes the batch date even when a month is specified is still quite good, we just know it could be even better. We use Usage Tracker from Ambeo, plus my ongoing monitoring, and the query execution time numbers overall for the system are *extremely* good. We just have that occasional ad-hoc query that could be a bit better (sometimes a lot better, but it's not the batch date index that is causing problems on those that really need some help ;-)). But we have identified the solutions for those. But yes, your suggestions are certainly things to keep in mind when encountering the non-selective usage of the index on
Re: Partition and Index Usage
Larry,
Perhaps this is out of the question, but...
What about the possibility of creating a batch_month column and using that
to partition the table. Leave the bitmap index on batch_date for those who
need it and let the other users go into the table using batch_month
= 'blah'. Unfortunately I realise that this isn't a pure back-end fix, but
perhaps it is worth considering. Depending on what you are querying
perhaps an aggregated table (materialized view even?) is the next step to
reduce the volume to something a little easier for Oracle - but that's
really getting into the end-user / application part of town.
Regards,
Mark.
PS: Sorry I couldn't provide a simple solution but I'm not sure that there
is one.
Larry Elkins
elkinsl@flash To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
.netcc:
Sent by: Subject: Partition and Index Usage
[EMAIL PROTECTED]
om
13/10/02 02:03
Please respond
to ORACLE-L
Listers,
I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's the
scenario.
8.1.7.4
Partitioned table, by month, on a date column called batch_date. 30 some
odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:
WHERE CUST_ID = 12345 and
Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')
I end up in some cases with a BITMAP MERGE operation, using the BMI indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and, the
batch date value is inclusive of *all* rows in that partition (batch_date
has no time component, ok, technically it's midnight). So, using the BMI on
batch date to merge with the BMI on cust id is wasted effort -- there will
be no rows in that partition outside of the date range specified, and all
rows in the partition are *in* that range -- the index on batch date does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of the BMI on batch date, and use just the BMI on
cust id and see substantial improvement. Obviously I would prefer to get
the
stats squared away as opposed to using a hint, especially since hinting
isn't feasible with the dynamic queries issued by the various ad-hoc tools
used.
Anyway, it just seems strange to me that the CBO, on occasion, not always,
will choose to include the usage of the index on batch date when it matches
the partition boundaries and will do nothing as far as filtering rows. Oh
yeah, since the upper boundary of the partition is defined as less than
TO_DATE('02012002','MMDD'), and the criteria would leave wiggle room in
there for dates on 01312002 that have a time component, I can change the
criteria to be BATCH_DATE = TO_DATE('01012002','MMDD') and BATCH_DATE
TO_DATE('02012002','MMDD'). This would account for a time component
(though time component is midnight). But I still get the same BMI merge
with
batch date on the handful of sample queries exhibiting this behavior.
Oh well, off to dig into the stats and play with 10053 traces. Just curious
if someone has run into something similar. And yes, I could simply drop the
index altogether, but that wouldn't help the folks querying on just a
single
day.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network
Partition and Index Usage
Listers,
I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's the
scenario.
8.1.7.4
Partitioned table, by month, on a date column called batch_date. 30 some odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:
WHERE CUST_ID = 12345 and
Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')
I end up in some cases with a BITMAP MERGE operation, using the BMI indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and, the
batch date value is inclusive of *all* rows in that partition (batch_date
has no time component, ok, technically it's midnight). So, using the BMI on
batch date to merge with the BMI on cust id is wasted effort -- there will
be no rows in that partition outside of the date range specified, and all
rows in the partition are *in* that range -- the index on batch date does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of the BMI on batch date, and use just the BMI on
cust id and see substantial improvement. Obviously I would prefer to get the
stats squared away as opposed to using a hint, especially since hinting
isn't feasible with the dynamic queries issued by the various ad-hoc tools
used.
Anyway, it just seems strange to me that the CBO, on occasion, not always,
will choose to include the usage of the index on batch date when it matches
the partition boundaries and will do nothing as far as filtering rows. Oh
yeah, since the upper boundary of the partition is defined as less than
TO_DATE('02012002','MMDD'), and the criteria would leave wiggle room in
there for dates on 01312002 that have a time component, I can change the
criteria to be BATCH_DATE = TO_DATE('01012002','MMDD') and BATCH_DATE
TO_DATE('02012002','MMDD'). This would account for a time component
(though time component is midnight). But I still get the same BMI merge with
batch date on the handful of sample queries exhibiting this behavior.
Oh well, off to dig into the stats and play with 10053 traces. Just curious
if someone has run into something similar. And yes, I could simply drop the
index altogether, but that wouldn't help the folks querying on just a single
day.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Partition and Index Usage
Hi Larry,
First I would suggest doing daily partitioning and dropping the index on the
batch_date.
Regarding your sql: partitions eliminations never substitutes the necessity
to validate any predicates on the partitioning key in the where clause.
If it's not feasible to partition by day, I would drop the BMI on the
batch_date and include the batch_date in the cust_id BMI (local index).
Regards,
Waleed
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/12/02 12:03 PM
Listers,
I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's
the
scenario.
8.1.7.4
Partitioned table, by month, on a date column called batch_date. 30 some
odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:
WHERE CUST_ID = 12345 and
Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')
I end up in some cases with a BITMAP MERGE operation, using the BMI
indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and,
the
batch date value is inclusive of *all* rows in that partition
(batch_date
has no time component, ok, technically it's midnight). So, using the BMI
on
batch date to merge with the BMI on cust id is wasted effort -- there
will
be no rows in that partition outside of the date range specified, and
all
rows in the partition are *in* that range -- the index on batch date
does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of the BMI on batch date, and use just the BMI
on
cust id and see substantial improvement. Obviously I would prefer to get
the
stats squared away as opposed to using a hint, especially since hinting
isn't feasible with the dynamic queries issued by the various ad-hoc
tools
used.
Anyway, it just seems strange to me that the CBO, on occasion, not
always,
will choose to include the usage of the index on batch date when it
matches
the partition boundaries and will do nothing as far as filtering rows.
Oh
yeah, since the upper boundary of the partition is defined as less than
TO_DATE('02012002','MMDD'), and the criteria would leave wiggle room
in
there for dates on 01312002 that have a time component, I can change
the
criteria to be BATCH_DATE = TO_DATE('01012002','MMDD') and
BATCH_DATE
TO_DATE('02012002','MMDD'). This would account for a time
component
(though time component is midnight). But I still get the same BMI merge
with
batch date on the handful of sample queries exhibiting this behavior.
Oh well, off to dig into the stats and play with 10053 traces. Just
curious
if someone has run into something similar. And yes, I could simply drop
the
index altogether, but that wouldn't help the folks querying on just a
single
day.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Partition and Index Usage
Hi Larry,
First I would suggest doing daily partitioning and dropping the
index on the
batch_date.
That's been kicked around. It's not a bad idea -- it would make sure the
index, since it wouldn't exist, doesn't get in the way. FWIW, it's
partitioned on a monthly basis to fall more in line with bulk maintenance
operations that are sometimes performed. External feed discovers a problem
and resends a month? A simple exchange partition novalidate after the data
is loaded in staging and verified. The pruning is another benefit, but not
the only reason it's partitioned by month -- 99% of the queries are for
month, 2 months, quarter, year, etc. Going to a more granular level will
just result in more partitions being examined. Would we get better
performance? Maybe, maybe not. Would have to test, and will not be able to
build a full blown test for another month or so until an additional 4.2 TB
disk space comes on-line. And yeah, where *are* the aggregates ;-)
Regarding your sql: partitions eliminations never substitutes
the necessity
to validate any predicates on the partitioning key in the where clause.
And that was really the whole reason for posing this question -- why does
the CBO even consider that index, in this particular case where a month
range is specified, the same as the partition. It *should* know that the
batch date index can be no more or no less than what the partition
comprises. But the CBO obviously isn't thinking that way and is evaluating
the index selectivity just like it does all the other BMI's. And in some
cases decides to go ahead and include it, doing the bitmap merge with
multiple other BMI's on which criteria exists. Just thought it was odd that
it doesn't take it into consideration, seeing if the boundaries of the
criteria on the batch date are the same as the boundaries for the partition.
Probably a good reason for it -- it just escapes me what it might be. Maybe
to consider an index join between multiple BMI's avoiding hitting the table
at all, but it's not doing that.
If it's not feasible to partition by day, I would drop the BMI on the
batch_date and include the batch_date in the cust_id BMI (local index).
This is actually done in a couple of cases on some other tables, but more
for the reason of being able to resolve some specific queries against those
tables entirely in the index.
Anyway, good thoughts and suggestions, I appreciate it. Each of them could
address this specific issue.
I'm still going to go ahead and dig into the 10053 and stats. Try to get it
to include the batch date index when helpful, and avoid it when its not. Or
I could always set the distinct keys to 1 or drop the index ;-). FWIW, this
isn't a huge problem -- the performance when it includes the batch date even
when a month is specified is still quite good, we just know it could be even
better. We use Usage Tracker from Ambeo, plus my ongoing monitoring, and the
query execution time numbers overall for the system are *extremely* good. We
just have that occasional ad-hoc query that could be a bit better (sometimes
a lot better, but its not the batch date index that is causing problems on
those that really need some help ;-)). But we have identified the solutions
for those.
But yes, your suggestions are certainly things to keep in mind when
encountering the non-selective usage of the index on batch date. Just not
sure if its something we have the time to pursue right now. And if you
follow the classic example of when to composite partition, this table is
screaming for it -- range on batch date, hash on another column (no meaning
generated numeric value that is always specified).
Regards,
Waleed
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/12/02 12:03 PM
Listers,
I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's
the
scenario.
8.1.7.4
Partitioned table, by month, on a date column called batch_date. 30 some
odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:
WHERE CUST_ID = 12345 and
Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')
I end up in some cases with a BITMAP MERGE operation, using the BMI
indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and,
the
batch date value is inclusive of *all* rows in that partition
(batch_date
has no time component, ok, technically it's midnight). So, using the BMI
on
batch date to merge with the BMI on cust id is wasted effort -- there
will
be no rows in that partition outside of the date range specified, and
all
rows in the partition are *in* that range -- the index on batch date
does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of
RE: Index Usage Monitoring
Hi, Why not to use otrace? Of cource, you may need some space to save trace results, but you'll definitely get complete statistics. Vadim Gorbounov Oracle DBA -Original Message- Sent: Tuesday, January 30, 2001 3:57 PM To: Multiple recipients of list ORACLE-L We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index Usage Monitoring
Hi, Eveleen, you a right, x$bh is the veiw, which contain important information about database buffer. This means, you will find here references to only those database blocks, which are currently in buffer, i.e., most recently used. You even can identify "hot" (often updated) blocks, because Oracle reconstructs block image in memory for different queries, and this will be different records in x$bh. I use the following query select FILE#, DBABLK, count(*) from x$bh group by FILE#, DBABLK having count(*) 10 order by 3 desc; Top line blocks are subjects to revise segment parameters. But this doesn't not completely the same task, as in question. Vadim Gorbounov Oracle DBA -Original Message- Sent: Monday, February 05, 2001 1:57 PM To: Multiple recipients of list ORACLE-L I do know there is a way to tell which indexes are accessed most recently by query x$bh view. Is this what you want or something different? Sorry I forgot the initial posting of this issue. Eveleen Please respond to [EMAIL PROTECTED] Vadim Gorbounov [EMAIL PROTECTED] on 02/05/2001 09:30:47 AM Message - From: Vadim Gorbounov [EMAIL PROTECTED] on 02/05/2001 03:30 PM GMT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: (bcc: Eveleen Xu/NNIB/NNNG) Subject: RE: Index Usage Monitoring Hi, Why not to use otrace? Of cource, you may need some space to save trace results, but you'll definitely get complete statistics. Vadim Gorbounov Oracle DBA -Original Message- Sent: Tuesday, January 30, 2001 3:57 PM To: Multiple recipients of list ORACLE-L We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mai
Re: Index Usage Monitoring
Pine Cone Systems has software to do this. There is also another company that has software to do this, but I can't recall their name at the moment. If you talk to Pine Cone, ask them who their competitors are. Be forewarned, this stuff is very expensive. Jared On Tue, 30 Jan 2001, PRINCE, PATRICK W. wrote: We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index Usage Monitoring
V$access appears to be pretty unreliable. V$db_object_cache does not appear to carry indexes but does have some good information on tables. -Original Message- Sent: Wednesday, January 31, 2001 2:51 PM To: Multiple recipients of list ORACLE-L And starting with the time immemorial, there is the V$ACCESS table which shows the parse locks that the application has. It also shows the indexes (indices) used by the application. -Original Message- Sent: Wednesday, January 31, 2001 2:42 PM To: Multiple recipients of list ORACLE-L Hi ! Starting from Oracle9i there will be a new view called V$OBJECT_USAGE (Name may change ??) can be used to monitor the index usage (!!) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index Usage Monitoring
We have a purchased application with over 1,300 indexes. Can someone suggest a method to monitor the system to determine which indexes are actively being used over time? I'm assuming that some are old/not necessary and would like to save the overhead of maintaining them. Oracle 8.0.6 Patrick Prince email: [EMAIL PROTECTED] Omaha Public Power District voice: (402) 636-3762 444 S 16th St. Mall, Omaha, NE 68102fax: (402) 636-3931 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: PRINCE, PATRICK W. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index usage for a table
Is there anyway to find out which indexes are being used and which ones are not being used for any given table? Thanks in Advance Alan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browning, Alan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index usage for a table
On Tuesday 30 January 2001 23:47, [EMAIL PROTECTED] wrote: run a query using explain plan ... and use sql trace oli -Original Message- Alan Sent: Tuesday, January 30, 2001 4:40 PM To: Multiple recipients of list ORACLE-L Is there anyway to find out which indexes are being used and which ones are not being used for any given table? Thanks in Advance Alan -- --- Oliver Artelt, System- und Datenbankadministration --- cubeoffice GmbH Co.KG # jordanstrasse 7 # 39112 magdeburg telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19 email: [EMAIL PROTECTED] # web: http://www.cubeoffice.de --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oliver Artelt INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
