Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: what should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 5:56 AM
in response to: user00726
|
|
|
TRUNC(LAS COUNT(*)
--------- ----------
08-JAN-09 7
09-JAN-09 10
12-MAR-09 1831
01-MAY-09 4
16
TRUNC(LAS COUNT(*)
--------- ----------
12-MAR-09 1583
01-MAY-09 21
27
It looks like you have have a strategic problem here (you have to figure out the way things should be) rather than a tactical one (where hitting the thing with a hammer might fix it).
As Mr. Burleson said - if things went faster when the previous DBA rebuilt all the indexes, then why don't you just rebuild all the indexes and see what happens. The drawback to that advice is that maybe he didn't just rebuild the indexes - still, if it's only a small system and the performance is a total disaster right now, you don't have much to lose if it doesn't help.
The bigger issue, though, is the variation in statistics. Is it possible that your DBA rebuilt the indexes on 1st May 09 - and collected stats for a few critical objects - and those stats are now sufficiently wrong that the optimizer is failing.
I note that some of your queries make this look like a system relating to a university campus system - and universities often have timetables run to three terms (semesters) per year. Is the long running job asking about data for NEXT term, when the statistics are telling the optimizer that the data stopped at the end of LAST term ? This would encourage the optimizer to do some incredibly stupid things for large reports.
Tactically, you could rebuild all the indexes - I would advise against it at this point, but it probably won't make things much worse, and you might get lucky.
Strategically you need to find out why some objects have missing statistics, why most objects last had their stats gathered in March, and why some of them had stats gathered in May (and the first schema you listed is worst of all - the stats are years out of date and you have lots of tables with stats where the indexes don't have stats). Then you need to know why the optimizer_mode is set to choose - because if some of the code is supposed to run rule-based you don't want stats on any objects accessed by that part of the code. Then you need to work out the correct strategy for statistics collection. (If you can copy the database I'd be inclined to do it and gather schema stats on the copy on all relevant schemas and see what that does for your problem ).
Until you can sort out what your statistics collection and optimisation strategy is supposed to be, you cannot stabilise your system.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan |
|
|
Posts:
192
Registered:
06/11/09
|
|
|
Posts:
96
Registered:
01/29/04
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 10:22 AM
in response to: Jonathan Lewis
|
|
|
|
This is a great thread (...ok, most of it :-)),
Jonathan, can you help me understand something?
You posted a statement "...and get a lot of I/O protection from a cache somewhere outside Oracle". Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.
Thanks,
Michael Cunningham
|
|
|
Posts:
137
Registered:
05/17/09
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 2:02 PM
in response to: Bizarro Don
|
|
|
|
Mr. Lewis,
You need to no that not everybody who disagrees with you is DKB!!!
When you smacked Rich Niemiec great book with a poor review you made enemies as likened when you attacked DKB.
Noone likes people who attack there fellows and you come here not to help the OP but to put down those who do.
|
|
|
Posts:
802
Registered:
07/04/99
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 2:47 PM
in response to: Michael C
|
|
|
This is a great thread (...ok, most of it :-)),
Jonathan, can you help me understand something?
You posted a statement "...and get a lot of I/O protection from a cache somewhere outside Oracle". Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.
Thanks,
Michael Cunningham
Hi Michael
On average his disk reads from normal work (db_file_scattered_read and db_file_sequential_read) both take under 5ms to complete. Disks don't serve data that fast. Cache does. Look at what oracle thinks is a physical i/o operation and compare the elapsed time to what you know hardware is capable of.
Niall Litchfield
http://www.orawin.info/ |
|
|
Posts:
802
Registered:
07/04/99
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 3:01 PM
in response to: Absorbine Jr.
|
|
|
I added the term "-Burleson" to your suggested search string (many savvy Oracle professionals do this to improve the efficiency of their searches on Google)
Har thats what you hope they will do!!!!!
The internet is not broken the popular authors are where they should be.
never a good argument to confuse temporary popularity with quality. Right now the best seller on amazon.com (22:57 UTC + 1 11-JUN-2009) is a book by a US based shock jock. Liberty and Tyranny: A Conservative Manifesto. It may have many qualities but it's not likely to be the "best" book for most people. Personally I recommend http://tinyurl.com/nx4dvr for oracle professionals especially when read against the work of certain authors from the american south who value publicity over accuracy.
Niall |
|
|
Posts:
897
Registered:
05/05/08
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 10:27 PM
in response to: Niall Litchfield
|
|
|
Hi all,
I have found that some of the indexes have a height greater than 8 and also high number of deleted entries....
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
2 ;
OWNER INDEX_NAME
------------------------------ ------------------------------
TABLE_NAME BLEVEL
------------------------------ ----------
DATA3 IDX_DRCR_FLG
FT_CUR_TRANS_DETAIL 7
DATA3 IDX_SC_COURSE_CODE
OS_STU_COURSE 8
DATA3 IND_IEC_COURSE_CODE
OT_ISS_ELG_CERT 10
OWNER INDEX_NAME
------------------------------ ------------------------------
TABLE_NAME BLEVEL
------------------------------ ----------
DATA3 IND_SPY_COURSE_CODE
OS_STU_PASS_YN 4
DATA3 IDX_DRCR_FLAG
FT_UNPOSTED_TRANS_DETAIL 8
SQL> analyze index IDX_DRCR_FLG validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
ROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
IDX_DRCR_FLG 8 11120 7598 68.3273381
SQL> analyze index IDX_SC_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
ROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
IDX_SC_COURSE_CODE 9 148591 11006 7.4069089
SQL> analyze index
2 IND_IEC_COURSE_CODE validate structure;
Index analyzed.
SQL>
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
ROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
IND_IEC_COURSE_CODE 11 50738 28027 55.2386771
SQL> analyze index IND_SPY_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
IND_SPY_COURSE_CODE 5 36587 379 1.03588706
SQL> analyze index IDX_DRCR_FLAG validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
IDX_DRCR_FLAG 9 10354 5146 49.7005988
SQL>
Should I rebuild all those indexes.......
And should I also set the parameter optimizer_mode=first_rows. as out system is OLTP....
Edited by: user00726 on Jun 11, 2009 10:27 PM |
|
|
Posts:
897
Registered:
05/05/08
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 10:52 PM
in response to: user00726
|
|
|
But here also I am getting error while rebuilding the same....
because some of the indexes are BITMAP
SQL> select INDEX_TYPE,index_name,table_name,blevel from dba_indexes where BLEVE
L>3;
INDEX_TYPE INDEX_NAME TABLE_NAME BLEVEL
--------------- --------------- ------------------------------ ----------
BITMAP IDX_DRCR_FLG FT_CUR_TRANS_DETAIL 7
BITMAP IDX_SC_COURSE_C OS_STU_COURSE 8
ODE
BITMAP IND_IEC_COURSE_ OT_ISS_ELG_CERT 10
CODE
BITMAP IND_SPY_COURSE_ OS_STU_PASS_YN 4
CODE
BITMAP IDX_DRCR_FLAG FT_UNPOSTED_TRANS_DETAIL 8
SQL>
Pls suggest me... |
|
|
Posts:
802
Registered:
07/04/99
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 11:02 PM
in response to: user00726
|
|
|
well you do seem rather to want the one magic bullet, instead of taking a measured and planned approach to managing the database. You should read Jonathan's note which advised you to work out a sensible strategy for gathering statistics on both tables and indexes (note that by default in your version gathering schema stats doesn't gather stats on indexes as well as tables - that is the default for the parameter cascade is false instead of true). I'd be looking however for a strategy that most likely regularly gathered stats on all tables and indexes whilst preserving the old values.
Now that all said you do seem to have some indexes with extraordinarily large values for blevel given the number of rows apparently in the underlying table - especially given the column names which suggest that these are rather short columns. A rebuild might well be in order, but working out why an index on a flag column with only 11000 rows in it has a blevel of 8 would be worthwhile as well. Are these by any chance bitmap indexes?
Niall |
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 11, 2009 11:57 PM
in response to: user00726
|
|
|
But here also I am getting error while rebuilding the same....
because some of the indexes are BITMAP
SQL> select INDEX_TYPE,index_name,table_name,blevel from dba_indexes where BLEVE
L>3;
INDEX_TYPE INDEX_NAME TABLE_NAME BLEVEL
--------------- --------------- ------------------------------ ----------
BITMAP IDX_DRCR_FLG FT_CUR_TRANS_DETAIL 7
BITMAP IDX_SC_COURSE_C OS_STU_COURSE 8
ODE
BITMAP IND_IEC_COURSE_ OT_ISS_ELG_CERT 10
CODE
BITMAP IND_SPY_COURSE_ OS_STU_PASS_YN 4
CODE
BITMAP IDX_DRCR_FLAG FT_UNPOSTED_TRANS_DETAIL 8
SQL>
Pls suggest me...
Now we're getting somewhere - and the problem is a strategic one. We've already seen that there is probably no proper strategy for collecting stats on this database, now we see that there is probably no proper indexing strategy.
You've said that this is an OLTP system - but bitmap indexes and OLTP systems should not go together because (a) the mix results in locking (and deadlocking) problem and (b) bitmap indexes degenerate and waste an enormous amount of space and grow to a ridiculous blevel when subject to the typical type of DML that appears in OLTP systems. (See for example this blog entry of mine which lists three articles about bitmap indexes - you need to read the first one, the other two are less relevant to you).
Strategically, you need to get rid of those bitmap indexes and work out a good set of b-tree indexes to replace them (a temporary measure would be to replace each with the corresponding b-tree equivalent) - but you also need to think about colllecting histogram information (but ONLY on a small number relevant columns) to give the optimizer some idea about when to use them.
In the short term, and just to get your reports running faster, you probably do need to rebuild these indexes. I would suggest that you set pctfree to 67 when you do so as this may limit the "growth" problem.
But here also I am getting error while rebuilding the same....
I think there are about 20,000 possible error messages that Oracle can give for server problems - so we can't help if you don't tell us which one: e.g.
ORA-03113: end-of-file on communication channel
ORA-01652: unable to extend temp segment by in tablespace
ORA-00054: resource busy and acquire with NOWAIT specified
etc ... etc ... etc ...
If I had to guess I'd gamble on ORA-00054 - which means people are updating the table when you need an exclusive lock on the table to rebuild the index, and unfortunately you can't do an online rebuild of a bitmap index until version 10g, and I think you were on 9i. If this is the case, you need to block access to the system for a while so that you can rebuild those indexes.
Bottom line: You need to learn about indexing then apply that learning to your system; and you need to learn about statistics - with special attention to histograms - and apply that information to your system.
Addendum: I did suggest on Wednesday afternoon that you should pick out some execution plans for expensive queries - if you had done so we might have spotted that you were using bitmap indexes much sooner. It's possible that parts of your report are running slowly BECAUSE the optimizer is using those bitmap indexes which have exploded enormously, and it's using them because the statistics are from a time when the indexes were new and small. (And if that's the case, fiddling with optimizer_index_cost_adj could make things worse because reducing it will encourage the optimizer to make even more use of those degenerate indexes).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan
Edited by: Jonathan Lewis on Jun 12, 2009 8:17 AM |
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 12, 2009 12:14 AM
in response to: Michael C
|
|
|
This is a great thread (...ok, most of it :-)),
Jonathan, can you help me understand something?
You posted a statement "...and get a lot of I/O protection from a cache somewhere outside Oracle". Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.
Michael
Niall's covered the key point. Unless you have the latest fastest discs you probably have something which quote 6 m/s as the average read time for random I/O - the statspack reports show this user as getting an average of 4 m/s, (which is about as fast as the latest discs could be under a low-concurrency load). So it's very likely that a fair number of his reads are coming from a cache. (Since he's running RAC, it's most likely to be a SAN cache rather than local memory).
On a busy (I/O intensive) system, even if you see 6 m/s as an average read time you can be a little "suspicious" of caching, because queueing theory warns us that if a queue takes 6 m/s without load, then by the time you hit 50% load the average response time is likely to double. (This is why v$event_histogram in 10g in 10g is so useful - it shows you a spread of values).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan |
|
|
Posts:
897
Registered:
05/05/08
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 12, 2009 2:24 AM
in response to: Jonathan Lewis
|
|
|
After rebuilding the indexing lf_rows an del_lf_rows comes down but obviously space of all the indexing have been increased...
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
,pct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IDX_SC_COURSE_CODE 9 148563 10961 7.37801471
51
SQL> alter index IDX_SC_COURSE_CODE rebuild;
Index altered.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
no rows selected
SQL> analyze index IDX_SC_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,
pct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IDX_SC_COURSE_CODE 2 114 0 0
85
SQL> analyze index IND_IEC_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IND_IEC_COURSE_CODE 11 50738 28027 55.2386771
57
SQL> alter index IND_IEC_COURSE_CODE rebuild;
Index altered.
SQL> analyze index IND_IEC_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IND_IEC_COURSE_CODE 2 55 0 0
84
SQL> analyze index IND_IEC_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IND_IEC_COURSE_CODE 2 55 0 0
84
SQL> analyze index IND_SPY_COURSE_CODE validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IND_SPY_COURSE_CODE 5 36587 379 1.03588706
62
SQL> alter index
2 IND_SPY_COURSE_CODE rebuild;
Index altered.
SQL> analyze index IND_SPY_COURSE_CODE validate structure;
Index analyzed.
SQL>
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IND_SPY_COURSE_CODE 2 59 0 0
81
SQL> analyze index IDX_DRCR_FLAG validate structure;
Index analyzed.
SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
ct_used from index_stats;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
PCT_USED
----------
IDX_DRCR_FLAG 2 6 0 0
61
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
; 2
OWNER INDEX_NAME
------------------------------ ------------------------------
TABLE_NAME BLEVEL
------------------------------ ----------
data3 IDX_DRCR_FLG
FT_CUR_TRANS_DETAIL 7
data3 IDX_SC_COURSE_CODE
OS_STU_COURSE 8
data3 IND_IEC_COURSE_CODE
OT_ISS_ELG_CERT 10
OWNER INDEX_NAME
------------------------------ ------------------------------
TABLE_NAME BLEVEL
------------------------------ ----------
data3 IND_SPY_COURSE_CODE
OS_STU_PASS_YN 4
data3 IDX_DRCR_FLAG
FT_UNPOSTED_TRANS_DETAIL 8
SQL>
|
|
|
Posts:
802
Registered:
07/04/99
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 12, 2009 5:17 AM
in response to: user00726
|
|
|
After rebuilding the indexing lf_rows an del_lf_rows comes down but obviously space of all the indexing have been increased...
I don't see anything in your listing that justifies that statement, unless you mean something very odd by it indeed. As you have rebuilt your indexes, as a quick fix you might do well to gather stats on them again as well, quite clearly the stats are both out of date and inaccurate now.
Niall Litchfield
http://www.orawin.info/ |
|
|
Posts:
1,781
Registered:
01/23/07
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 14, 2009 2:26 AM
in response to: user00726
|
|
|
After rebuilding the indexing lf_rows an del_lf_rows comes down but obviously space of all the indexing have been increased...
I don't quite follow what you are demonstrating - but I think you are misinterpreting the effect of the 'analyze index ... validate structure' command. This does NOT collect optimizer statistics, so the query against dba_indexes will still be showing the old figures.
You need to execute some calls to dbms_stats (e.g. gather_table_stats with the cascade option set to true, or gather_index_stats, or gather_schema_stats) to collect new statistics.
It's possible that your previous DBA used other forms of the analyze command to collect optimizer statistics, though, such as 'analyze table XXX compute statistics', and that may be why you thought that your analyze commands would change the optimizer stats.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk
New Scientist: "Would you prefer a cautious expert or a confident ignoramus"
http://www.newscientist.com/article/mg20227115.500-humans-prefer-cockiness-to-expertise.html |
|
|
Posts:
897
Registered:
05/05/08
|
|
|
|
Re: waht should be the value of Optimizer_index_cost_adj in oracle 9i
Posted:
Jun 17, 2009 12:19 AM
in response to: Jonathan Lewis
|
|
|
Jonathan Lewis :
HI all,
Thanks all for your valuable suggestions.....
I have gathered stats on last monday
SQL> select trunc(last_analyzed), count(*) from
dba_indexes where owner='CADATA3' group by trunc(last_analyzed)
order by trunc(last_analyzed)
2 3 4 ;
TRUNC(LAS COUNT(*)
--------- ----------
14-JUN-09 1604
27
SQL> select trunc(last_analyzed), count(*) from
dba_tables where owner='CADATA3' group by trunc(last_analyzed)
order by trunc(last_analyzed)
2 3 4 ;
TRUNC(LAS COUNT(*)
--------- ----------
08-JAN-09 7
09-JAN-09 10
14-JUN-09 1851
2
Although we had generate reports but in future ,
can you please specify me some steps what should be done to oveercome the same...... |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
