|
-
It's best if you can find out why CBO is choosing the plans it is, but
 Originally Posted by srt
If I use hints /*+ FIRST_ROWS, USE_NL */ it works fine. Let me know if there any way that I can do without changing in SQL as these were working fine in 8i.
if you can't change the original sql (or db design either), try a stored outline to force the hint.
Not a replacement for understanding all the other CBO options, but when properly used can be very effective...
(That sound you now hear is me stepping off my "outlines are wonderful" soapbox...)
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
-
I did not change any parameters of what you mentioned. I changed optimizer and feature.
The below is explain plan in 8i for
SELECT
C.R_NAMED_INSRD1_NAM,
P.STATUS, P.PYE_NAM, P.ADD_BY,
P.SITE_ID,P.ISS_DTE, CL.R_COV_HANDL_OFC_DSK_COD,
N.TYP
FROM
P,CL,CM, C,N
WHERE
P.STATUS = 'H' AND
C.UNQ_ID = CM.UNQ_ID AND
CM.CLM_SFX = CL.CLM_SFX AND
CM.UNQ_ID = CL.UNQ_ID AND
CL.COV_MIN = P.COV_MIN AND
CL.CLM_SFX = P.CLM_SFX AND
CL.UNQ_ID = P.UNQ_ID AND
CM.SOC_NUM = N.ID
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 M 4943948
NESTED LOOPS 1 M 277 M 4943948
NESTED LOOPS 1 M 212 M 3648362
NESTED LOOPS 1 M 150 M 2304300
NESTED LOOPS 1 M 115 M 1630256
TABLE ACCESS BY INDEX ROWID P 1 M 75 M 943114
INDEX RANGE SCAN PA_STA 1 M 21369
TABLE ACCESS BY INDEX ROWID CL 595 K 16 M 1
INDEX UNIQUE SCAN CMC_CLM 595 K
TABLE ACCESS BY INDEX ROWID PYRAMID.CM 292 K 6 M 1
INDEX UNIQUE SCAN CM_CLM 292 K
TABLE ACCESS BY INDEX ROWID C 282 K 13 M 1
INDEX RANGE SCAN CL_COV_MAJ 282 K 1
TABLE ACCESS BY INDEX ROWID N 455 K 20 M 1
INDEX RANGE SCAN IX_NAMEIND_ID_U_CMP_NAM 455 K 1
In 8i It was using the indexes.
-
you are not matching P to the CM table.
Make sure you match all columns to all tables.
Here is a rewrite, but the WHERE clause is the important part
where I match P to CM for their like columns.
PHP Code:
SELECT
(select C.R_NAMED_INSRD1_NAM
from C where C.UNQ_ID = CM.UNQ_ID) r_named_insrd1_nam
P.STATUS,
P.PYE_NAM,
P.ADD_BY,
P.SITE_ID,
P.ISS_DTE,
CL.R_COV_HANDL_OFC_DSK_COD,
(select N.TYP from N where CM.SOC_NUM = N.ID) typ
FROM
(select * from P where P.STATUS = 'H') p,
CL, CM
WHERE
P.UNQ_ID = CL.UNQ_ID AND
P.UNQ_ID = CM.UNQ_ID AND
CM.UNQ_ID = CL.UNQ_ID AND
P.CLM_SFX = CL.CLM_SFX AND
P.CLM_SFX = CM.CLM_SFX AND
CM.CLM_SFX = CL.CLM_SFX AND
P.COV_MIN = CL.COV_MIN;
- Cookies
-
Hi, I am in a big confusion now.
The below I made changes before
1) opt was first rows.
Few queries was running slow and few faster.
2) changed to Choose.
Most of them were working and after testing few were running slow.
I ran the statistics also.
3) I changed back to first rows .
Now I tested all the queries which had problem all the time , is working fine.
I did not run statistics after chaning the optimizer plan.
Kindly let me know what is happening here ???
I am totally confused.
-
Hard to say, with so many changes being made.
Start from where you are right now - what are your init settings?
compatible
optimizer_features_enable
optimizer_index_caching
optimizer_index_cost_adj
optimizer_max_permutations
optimizer_mode
cursor_sharing
db_file_multiblock_read_count
pga_aggregate_target
How are you testing, by running/explaining the sql yourself or from the app itself?
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
-
[code ] compatible = 9.2.0
optimizer_features_enable = 9.2.0
optimizer_index_caching =80
optimizer_index_cost_adj = 45
optimizer_max_permutations not set
optimizer_mode = FIRST_ROWS
cursor_sharing not set
db_file_multiblock_read_count = 112
pga_aggregate_target 314572800.
[/Code].
I always tested by changing just the optimizer mode for the queries.
One more thing is work_area_size = auto. is different.
It was manual and later auto with choose and changed to auto with first_rows .
I am running sql statements which are taken from application trace ( Applicaiton was timing out when ever the query was running too slow from application).
Testing first through sql and the same thing from application also.
-
db_file_multiblock_read_count = 112 - usually 8 to 64 is the rule of thumb. (default is 8).
optimizer_index_cost_adj = 45 - favors indexes over fts
seems to me these two would conflict - the 112 says FTS is easy, so grab lots of data at a time; while cost_adj=45 says indexes are cheaper.
and
optimizer_index_caching =80
says nested loop joins are much better than the default cost.
The problem with the optimizer settings is they're all or nothing; I've seen them help, and hurt - depending.
I would be tempted to set everything to default - optimizer_mode, multiblock read, all optimizer settings - and see how it runs. Then if you have a few problem sqls, see why CBO is behaving that way on those specific sqls. You want to avoid making global changes just to get one or two problem sqls working better.
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
-
Thank you tom.
I will set to default and test if I see any more problem queries.
Thanks for all your help
-
Hi, When I thought every thing is going fine. I had one problem query found. I cannot change the query as it is from application. It is using index full scan . The selection is happening from a view.
This time query is
Code:
select a,b,c,d,e from VIEW_table where STATUS in ('K','H') and UNQ_ID='1232' and CLM_SFX='001' order by UNQ_ID,CLM_SFX,GRP_ID
Explain plan
Code:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 2752936
TABLE ACCESS BY INDEX ROWID CL 1 29 2
NESTED LOOPS 1 436 2752936
TABLE ACCESS BY INDEX ROWID PA 7 2 K 2752922
INDEX FULL SCAN PA_GRP 8 M 29222
INDEX RANGE SCAN CL_COV_MAJ 1 1
My parameter file.
Code:
Buffer_pool_keep = (buffers:8192, lru_latches:48)
buffer_pool_recycle = (buffers:8192, LRu_latches:24)
compatible = 9.2.0
db_block_buffers = 49152
db_domain = world
db_file_multiblock_read_count = 112
#db_file_direct_io_count = 112
db_writer_processes = 6
dml_locks = 500
#fast_start_io_target = 0
global_names = TRUE
hash_area_size = 0
hash_join_enabled = FALSE
ifile = /u01/app/oracle/admin/TEST/pfile/configTEST.ora
java_pool_size = 0
job_queue_processes = 1
log_archive_start = FALSE
log_buffer = 262144
log_checkpoint_interval = 0 # none
log_checkpoint_timeout = 0 # none
log_checkpoints_to_alert = TRUE
max_dump_file_size = 96M
# mts_dispatchers = "tcp,4"
# mts_max_dispatchers = 16
# mts_max_servers = 48
# mts_servers = 8
optimizer_features_enable = 9.2.0
optimizer_index_caching = 80
optimizer_index_cost_adj = 45
optimizer_mode = FIRST_ROWS
open_cursors = 256
processes = 384
pga_aggregate_target=314572800
workarea_size_policy=AUTO
partition_view_enabled = TRUE
query_rewrite_enabled = TRUE
query_rewrite_integrity = TRUSTED
resource_limit = TRUE
remote_login_passwordfile = EXCLUSIVE
service_names = TEST.WORLD
sessions = 512
shared_pool_size = 256m
shared_pool_reserved_size = 16777216 # 16Mb.
sort_area_size = 67108864 # 64Mb.
sort_area_retained_size = 4194304
sql_trace = FALSE
star_transformation_enabled = TRUE
timed_statistics = TRUE
_B_TREE_BITMAP_PLANS = FALSE
May be few parameters might help in init file.
Kindly let me know what to be done.
Thank you
-
Do you collect system statistics?
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote