| JOIN_INDEX Fied in DBA_INDEXES? [message #61001] |
Fri, 19 March 2004 01:02  |
Grigoriadis
Messages: 7
Registered: March 2004
|
Junior Member |
|
|
Hi there!
We have an Oracle 9 DB, which is the result of an export from a Oracle 8 DB and an import in the new Oracle 9 DB.
Now I discovered an SQL-Statement of our application, which performs much worse in Ora9 than in Ora8. Looking at the explain-plan, the difference seems to be full table scan (fts) in Ora9, which in Ora8 had been an index range scan (irs).
Asking myself what could be different in the handling of the index, I queried DBA_INDEXES with the name of this index. The statistics are comparable and can not be the reason for the fts.
Anybody a clue, what could be the difference?
Thanks in advance.
|
|
|
|
| Let me explain. [message #61003 is a reply to message #61001] |
Fri, 19 March 2004 02:23  |
Grigoriadis
Messages: 7
Registered: March 2004
|
Junior Member |
|
|
Let me explain myself further...
The original subject comes from my initial question, because the index in ora9 has the field JOIN_INDEX set to "NO". I don't know the meaning of the field.
But now I can give you the following fact:
Using
select *
from tab1
where col1=val1
gives for the same value val1 an index range scan on ora8, but a full table scan in ora9. The values in the table have not changed much since migration.
Is perhaps ora9 compared to ora8 more or less sensitive to low selectivity?
However using the INDEX hint, to enforce use of the index, rather even made the costs even higher.
What could be the reason????
|
|
|
|
| Re: Let me explain. [message #61005 is a reply to message #61003] |
Fri, 19 March 2004 04:41 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Join_Index=NO means this index is not a bitmap join index ,which is introduced in 9i.
What are your optimizer_* settings and db_file_multiblock_read_count on both the databases ? A bunch of parameter default values(mostly undocumented) changed between these versions ,that could constitute the change in the execution plan.
-Thiru
|
|
|
|
| Parameters [message #61006 is a reply to message #61005] |
Fri, 19 March 2004 04:53 |
Grigoriadis
Messages: 7
Registered: March 2004
|
Junior Member |
|
|
Thanks for the response.
db_file_multiblock_read_count is the same in both ora8 and ora9.
Take a look at the optimizer settings:
Ora9
----
optimizer_features_enable 9.2.0
optimizer_mode CHOOSE
optimizer_max_permutations 2000
optimizer_index_cost_adj 100
optimizer_index_caching 0
optimizer_dynamic_sampling 1
Ora8
-----
optimizer_features_enable 8.1.7
optimizer_mode CHOOSE
optimizer_max_permutations 80000
optimizer_index_cost_adj 100
optimizer_index_caching 0
optimizer_percent_parallel 0
|
|
|
|
| Re: Parameters [message #61012 is a reply to message #61006] |
Fri, 19 March 2004 06:53 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Just to make sure that this optimizer related issue,you can try setting optimizer_features_enable =8.1.7 on the 9.2 database and see if there is any change in the response.
If it does make a difference,then you may need to start adjusting the changed parameter defaults of the undocumented parameters,after consulting with Oracle support.
BTW,I hope you have updated the statistics on the 9i schema.
-Thiru
|
|
|
|
| Re: Parameters [message #61014 is a reply to message #61006] |
Fri, 19 March 2004 10:02 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
|
also ,you may want to increase optimizer_index_caching to around 80-90 and reduce optimizer_index_cost_adj to say 40(this depends on your relative speed of single block vs multiblock reads ). Again the values I mentioned here are to just find out if you can influence the optimizer to favour Index scans over full table scans at the instance level.
|
|
|
|
| Re: Parameters [message #61055 is a reply to message #61014] |
Wed, 24 March 2004 03:41 |
Grigoriadis
Messages: 7
Registered: March 2004
|
Junior Member |
|
|
Thanks for the hint, but I fear I would get performance problems elsewhere, where I now don't have, because a change of those global parameters would change the behaviour of the optimizer everywhere.
But as the settings for ora 9 and 8 show, the parameters have equal values.
So, can it be that the optimizer of ora9 is much more sensible to missing indexes than 0ra8, reacting with full table scans?
Thanks a lot in advance
|
|
|
|
| Re: Parameters [message #61060 is a reply to message #61055] |
Wed, 24 March 2004 11:55 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
as I mentioned earlier,there are many undocumented(ie starting with underscore) parameters whose defaults changed between 8i and 9i. Some of these are
_PUSH_JOIN_PREDICATE (FALSE in 8i,TRUE in 9i)
_UNNEST_SUBQUERY((FALSE in 8i,TRUE in 9i)
_INDEX_JOIN_ENABLED (FALSE in 8i,TRUE in 9i)
_COMPLEX_VIEW_MERGING (FALSE in 8i,TRUE in 9i)
etc and these are capable of changing the execution plan.
You will need to selectively disable(ie make it FALSE) on the 9i instance to simulate 8i behaviour. Again,any undocumented parameter change needs to be done at your own risk :-)
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
