| Reading Output of Plan Table [message #143746] |
Fri, 21 October 2005 16:06  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi All
This is the output of a plan table i have run on select statement of cursors, like this there are couple of select statements in a cursor inside a procedure.
What is to be conclude by reading a output of a plan table,
what are columns to be focused, Please help me so that i can tune the procedure,Since i am new to tuning some help is requested from you all there...
1 out put ---
SELECT STATEMENT, GOAL = CHOOSE
Cost=20 Cardinality=1 Bytes=106
SORT ORDER BY Cost=20 Cardinality=1 Bytes=106
FILTER
NESTED LOOPS Cost=9 Cardinality=1 Bytes=106
NESTED LOOPS Cost=8 Cardinality=1 Bytes=94
MERGE JOIN CARTESIAN Cost=5 Cardinality=1 Bytes=41
MERGE JOIN CARTESIAN Cost=3 Cardinality=1 Bytes=21
TABLE ACCESS FULL Object owner=PM_DBA Object name=FACT_TYPE Cost=2 Cardinality =1 Bytes=10
BUFFER SORT Cost=1 Cardinality=1 Bytes=11
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=1 Cardinality=1 Bytes=11
INDEX RANGE SCAN Object owner=PM_DBA Object name=BUSINESS_UNIT_IE01 Cardinality=1
BUFFER SORT Cost=4 Cardinality=1 Bytes=20
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=COMPOSITE Cost=2 Cardinality=1 Bytes=20
INDEX RANGE SCAN Object owner=PM_DBA Object name=COMPOSITE_AK01 Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=DAILY_COMPOSITE_RETURNS Cost=3 Cardinality=1 Bytes=53
INDEX RANGE SCAN Object owner=PM_DBA Object name=DAILY_COMPOSITE_RETURNS_PK Cost=2 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CODE_TYPE Cost=1 Cardinality =2 Bytes=24
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CODE_TYPE_PK
Cardinality =105
--2 plan table out put
SELECT STATEMENT, GOAL = CHOOSE Cost=816 Cardinality=1 Bytes=144
SORT ORDER BY Cost=816 Cardinality=1 Bytes=144
FILTER
NESTED LOOPS Cost=805 Cardinality=1 Bytes=144
NESTED LOOPS Cost=804 Cardinality=1 Bytes=131
NESTED LOOPS Cost=803 Cardinality=1 Bytes=119
NESTED LOOPS Cost=802 Cardinality=1 Bytes=92
NESTED LOOPS Cost=801 Cardinality=1 Bytes=81
NESTED LOOPS Cost=801 Cardinality=1 Bytes=77
NESTED LOOPS Cost=800 Cardinality=1 Bytes=57
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CURRENCY_PK Cardinality=1 Bytes=4
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=DAILY_COMPOSITE_RETURNS Cost=799 Cardinality=1 Bytes=53
INDEX RANGE SCAN Object owner=PM_DBA Object name=DAILY_COMPOSITE_RETURNS_IE01 Cost=131 Cardinality=1418
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=COMPOSITE Cost=1 Cardinality=1 Bytes=20
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=COMPOSITE_PK Cardinality=1660
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=COUNTRY_PK Cardinality=1 Bytes=4
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=BUSINESS_UNIT Cost=1 Cardinality=1 Bytes=11
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=BUSINESS_UNIT_PK Cardinality=176
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=INSTRUMENT_TYPE_HIERARCHY Cost=1 Cardinality=1 Bytes=27
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=INSTRUMENT_TYPE_HIERARCHY_PK Cardinality=33
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CODE_TYPE Cost=1 Cardinality=1 Bytes=12
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CODE_TYPE_PK Cardinality=105
TABLE ACCESS BY INDEX ROWID Object owner=PM_DBA Object name=CALENDAR_DATE Cost=1 Cardinality=1 Bytes=13
INDEX UNIQUE SCAN Object owner=PM_DBA Object name=CALENDAR_DATE_PK Cardinality=1
Thanks
|
|
|
|
| Re: Reading Output of Plan Table [message #143748 is a reply to message #143746] |
Fri, 21 October 2005 18:06  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Tune what?
Is the query running slow
or
too fast?
YOu need to provide more information.
oracle version, OS etc.
The plan depends on collected statistics.
Did you gather the stats?
>>MERGE JOIN CARTESIAN Cost=5 Cardinality=1 Bytes=41
>>MERGE JOIN CARTESIAN Cost=3 Cardinality=1 Bytes=21
>> TABLE ACCESS FULL Object owner=PM_DBA Object name=FACT_TYPE Cost=2 Cardinality =1 Bytes=10
The cartesian join would be painful.
Are you sure the concerned column on FACT_TYPE is indexed ( and table/index statistics gathered?)
[Updated on: Fri, 21 October 2005 18:11] Report message to a moderator |
|
|
|
| Re: Reading Output of Plan Table [message #143780 is a reply to message #143748] |
Sat, 22 October 2005 07:51 |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi Mahesh
Thanks for responding,
Well Oracle is 9i,OS is Unix,
This procedure is running slow,
Regarding gathering stats we don't have privilege ,we are developers only told to tune this procedure.
I gave those 2 plans out of may be 10 cursors and select statements in a procedure,
As you mention
>>MERGE JOIN CARTESIAN Cost=5 Cardinality=1 Bytes=41
>>MERGE JOIN CARTESIAN Cost=3 Cardinality=1 Bytes=21
>> TABLE ACCESS FULL Object owner=PM_DBA Object name=FACT_TYPE Cost=2 Cardinality =1 Bytes=10
Do you want me to check joins for catesian product and index on
Object name=FACT_TYPE
What does CARTESIAN Cost=5 and CARTESIAN Cost=3 means..
Thanks
|
|
|
|
| Re: Reading Output of Plan Table [message #143783 is a reply to message #143780] |
Sat, 22 October 2005 08:53 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>we don't have privilege ,we are developers only told to tune this procedure.
Makes no sense.
To get the data in an efficient manner, CBO comes with path/plan, based on statistics it has.
It may be good or bad. It depends on statistics.
Same thing like in google map/yahoo maps , that gets the best route withing two places. If the current Road/route conditions are not updated, you are relying on OLD data.
So first ask your 'privileged' people whether the statistics are updated and if not to do it.
You have not posted the query.
Based on plan, we can say that at somepoint, there is an cartesian join, following by an FTS which usually is not required.
Either fix your query.
or
As In most cases, your are missing an index or the existing index is not used because of changes in data distribution or the statistics are just old.
(user PM_DBA is doing a full table scan on table FACT_TYPE to fetch only ONE record. If there are many rows in a table, an index may be useful.).
Here COST means nothing. It is just a number.
|
|
|
|
| Re: Reading Output of Plan Table [message #143806 is a reply to message #143783] |
Sat, 22 October 2005 11:59 |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi Mahesh
I will post the updated stats on this procedure's code.
You can have a look at this code guide me whether some thing is wrong in the logic or coding standards.
I will also work on that cartesian product and FTS and index.
I have attached the Code.
Thanks
[Updated on: Sat, 22 October 2005 12:00] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
