| Strange Performance Problem [message #65072] |
Wed, 14 April 2004 23:22  |
Manish Deshpande
Messages: 1
Registered: April 2004
|
Junior Member |
|
|
We are facing some strange performance problems recently. ONE SQL Query was working fine and there observed a performance problem, When we set parameter optimizer_index_cost_adj=10, plan looks better (similar to what it was earlier).
Attaching herewith Plans taken with and without setting optimizer_index_cost_adj.
Any suggestion is highly appreciated.
PLAN WITH optimizer_index_cost_adj=10
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 662 | 417K| 1140 | | |
| SORT ORDER BY | | 662 | 417K| 1140 | | |
| FILTER | | | | | | |
| NESTED LOOPS OUTER | | | | | | |
| NESTED LOOPS OUTER | | 662 | 396K| 940 | | |
| NESTED LOOPS | | 662 | 387K| 874 | | |
| NESTED LOOPS OUTER | | 662 | 356K| 807 | | |
| NESTED LOOPS OUTER | | 662 | 316K| 741 | | |
| NESTED LOOPS | | 662 | 313K| 740 | | |
| NESTED LOOPS OUTER | | 660 | 258K| 410 | | |
| HASH JOIN | | 660 | 253K| 409 | | |
| TABLE ACCESS BY INDEX ROWID |TTDSLS400110 | 60 | 3K| 363 | | |
| INDEX RANGE SCAN |TTDSLS400110$IDX5 | 60 | | 73 | | |
| MERGE JOIN CARTESIAN | | 29K| 9M| 46 | | |
| MERGE JOIN OUTER | | 11 | 3K| 35 | | |
| NESTED LOOPS OUTER | | 11 | 3K| 34 | | |
| NESTED LOOPS OUTER | | 11 | 2K| 33 | | |
| NESTED LOOPS OUTER | | 11 | 2K| 31 | | |
| NESTED LOOPS | | 11 | 2K| 30 | | |
| NESTED LOOPS OUTER | | 7 | 574 | 27 | | |
| TABLE ACCESS BY INDEX ROWID |TTDPUR400110 | 7 | 252 | 26 | | |
| INDEX RANGE SCAN |TTDPUR400110$IDX4 | 7 | | 7 | | |
| TABLE ACCESS BY INDEX ROWID |TTCCOM100110 | 2K| 120K| 1 | | |
| INDEX UNIQUE SCAN |TTCCOM100110$IDX1 | 2K| | | | |
| INLIST ITERATOR | | | | | | |
| TABLE ACCESS BY INDEX ROWID |TTDPUR401110 | 85K| 9M| 1 | | |
| INDEX RANGE SCAN |TTDPUR401110$IDX6 | 85K| | 3 | | |
| INDEX UNIQUE SCAN |TTCMCS080110$IDX1 | 720 | 2K| | | |
| INDEX UNIQUE SCAN |TTCIBD004110$IDX1 | 62K| 3M| 1 | | |
| INDEX UNIQUE SCAN |TTCIBD001110$IDX1 | 66K| 3M| 1 | | |
| SORT JOIN | | 1 | 7 | 34 | | |
| INDEX UNIQUE SCAN |TTCCOM001110$IDX1 | 1 | 7 | | | |
| SORT JOIN | | 2K| 26K| 45 | | |
| INDEX FULL SCAN |TTCCOM100110$IDX1 | 2K| 26K| 10 | | |
| INDEX UNIQUE SCAN |TTCCOM001110$IDX1 | 360 | 2K| | | |
| TABLE ACCESS BY INDEX ROWID |TTDSLS401110 | 704 | 57K| 1 | | |
| INDEX RANGE SCAN |TTDSLS401110$BSC | 704 | | 3 | | |
| INDEX UNIQUE SCAN |TTCMCS080110$IDX1 | 720 | 2K| | | |
| INDEX UNIQUE SCAN |TTCIBD004110$IDX1 | 62K| 3M| 1 | | |
| INDEX UNIQUE SCAN |TTCIBD001110$IDX1 | 66K| 3M| 1 | | |
| INDEX UNIQUE SCAN |TTUHPC405110$IDX1 | 383K| 5M| 1 | | |
| TABLE ACCESS BY INDEX ROWID |TTDPIE001110 | 393K| 12M| 1 | | |
| INDEX UNIQUE SCAN |TTDPIE001110$IDX6 | 393K| | 1 | | |
PLAN WITH optimizer_index_cost_adj=100 (DEFAULT)
------------------------------------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 7K| 4M| 5854 | | |
| SORT ORDER BY | | 7K| 4M| 5854 | | |
| FILTER | | | | | | |
| HASH JOIN OUTER | | | | | | |
| NESTED LOOPS | | 7K| 4M| 4733 | | |
| HASH JOIN OUTER | | 7K| 4M| 4733 | | |
| NESTED LOOPS OUTER | | 7K| 4M| 4550 | | |
| HASH JOIN | | 7K| 4M| 4550 | | |
| TABLE ACCESS BY INDEX ROWID |TTDSLS400110 | 60 | 3K| 651 | | |
| INDEX RANGE SCAN |TTDSLS400110$IDX5 | 60 | | 73 | | |
| HASH JOIN OUTER | | 7K| 3M| 3898 | | |
| NESTED LOOPS OUTER | | 7K| 3M| 3742 | | |
| HASH JOIN | | 7K| 3M| 3742 | | |
| TABLE ACCESS FULL |TTDSLS401110 | 704 | 57K| 2395 | | |
| MERGE JOIN CARTESIAN | | 732K| 257M| 1331 | | |
| MERGE JOIN OUTER | | 11 | 3K| 319 | | |
| NESTED LOOPS OUTER | | 11 | 3K| 319 | | |
| NESTED LOOPS OUTER | | 11 | 2K| 308 | | |
| NESTED LOOPS OUTER | | 11 | 2K| 297 | | |
| NESTED LOOPS | | 11 | 2K| 297 | | |
| NESTED LOOPS OUTER | | 7 | 574 | 262 | | |
| TABLE ACCESS BY INDEX ROWID |TTDPUR400110 | 7 | 252 | 255 | | |
| INDEX RANGE SCAN |TTDPUR400110$IDX4 | 7 | | 7 | | |
| TABLE ACCESS BY INDEX ROWID |TTCCOM100110 | 2K| 120K| 1 | | |
| INDEX UNIQUE SCAN |TTCCOM100110$IDX1 | 2K| | | | |
| INLIST ITERATOR | | | | | | |
| TABLE ACCESS BY INDEX ROWID |TTDPUR401110 | 85K| 9M| 5 | | |
| INDEX RANGE SCAN |TTDPUR401110$IDX6 | 85K| | 3 | | |
| INDEX UNIQUE SCAN |TTCMCS080110$IDX1 | 720 | 2K| | | |
| INDEX UNIQUE SCAN |TTCIBD004110$IDX1 | 62K| 3M| 1 | | |
| INDEX UNIQUE SCAN |TTCIBD001110$IDX1 | 66K| 3M| 1 | | |
| SORT JOIN | | 1 | 7 | 318 | | |
| INDEX UNIQUE SCAN |TTCCOM001110$IDX1 | 1 | 7 | | | |
| SORT JOIN | | 66K| 3M| 1331 | | |
| INDEX FAST FULL SCAN |TTCIBD001110$IDX1 | 66K| 3M| 92 | | |
| INDEX UNIQUE SCAN |TTCMCS080110$IDX1 | 720 | 2K| | | |
| TABLE ACCESS FULL |TTCIBD004110 | 62K| 3M| 116 | | |
| INDEX UNIQUE SCAN |TTCCOM001110$IDX1 | 360 | 2K| | | |
| INDEX FAST FULL SCAN |TTUHPC405110$IDX1 | 383K| 5M| 113 | | |
| INDEX UNIQUE SCAN |TTCCOM100110$IDX1 | 2K| 26K| | | |
| INDEX FAST FULL SCAN |TTDPIE001110$IDX1 | 393K| 12M| 310 | | |
|
|
|
|
| Re: Strange Performance Problem [message #65075 is a reply to message #65072] |
Thu, 15 April 2004 16:34  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Am not too sure if I understood your situation exactly, but setting optimizer_index_cost_adj to 10 would favour index scans over full table scans as you are telling the optimizer to evaluate the single block reads at 1/10th of the default cost.
The default of 100 is usually not appropriate for most oltp systems and you are right in reducing it.
-Thiru
|
|
|
|
| Re: Strange Performance Problem [message #65173 is a reply to message #65072] |
Mon, 31 May 2004 09:45 |
Pamela Wright
Messages: 1
Registered: May 2004
|
Junior Member |
|
|
|
Hi, My husband & I have a s-10 pickup that Foy Gilmore from Pennsacola, Florida built the chassie for it. It is like a pro mod. It was sitting right on the ground. We put taller tires on it to get it off the ground to stop the dragging, we could not get it on & off the car hauler nor in & out of the shop. Are problem is when we get the rear end of the truck soft enough to push down then the strange coil overs with single ad-justment want pull the rear end of the truck back up, it sticks or stays right where we push it down, we have to lift up on the body to release the shocks. Why? 05/31/04 Email me back at Pdwright79 @aol.com or call a toll free # 1-888-307-9027 to my husband Jr. Wright. Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
