OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » sql tuning (unix, 8.1.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql tuning [message #300482]

Fri, 15 February 2008 11:29

daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india

Member

Hi Gurus,
i have generated a execution plan for the query. my query runnning very slow. please could anybody help me in tuning this query.

Attachment: NewExcPlanTime.txt
(Size: 10.11KB, Downloaded 1155 times)

Report message to a moderator

Re: sql tuning [message #300485 is a reply to message #300482]

Fri, 15 February 2008 11:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What is different between this SQL in this post & in
http://www.orafaq.com/forum/t/97270/74940/

Report message to a moderator

Re: sql tuning [message #300486 is a reply to message #300485]

Fri, 15 February 2008 11:44

daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india

Member

may the Tkprof is not of this query but the exection plan i have generated so i am very much sure that execution belongs to this query.

Thanks,
Daniesh

Report message to a moderator

Re: sql tuning [message #300495 is a reply to message #300486]

Fri, 15 February 2008 12:20

daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india

Member

Hi Anacedent,
Michael told me that Tkprof report doesnt belong to this query
even i was not sure it was generated by my colleague but the execution plan is generated by me. so i am sure that execution plan belong to this query.
could you please tell me are you looking into it so that i can update you.

Report message to a moderator

Re: sql tuning [message #300496 is a reply to message #300495]

Fri, 15 February 2008 12:27

daniesh.shaikh@gmail.com
Messages: 62
Registered: February 2008
Location: india

Member

could you please look into it. i have certain assumptioms about it.which i like to share with you.i checked the clutering factor and blocks . as oracle suggets that
If the value of this CF(clustering Factor) is near to the number of blocks,
then the table is very well ordered with respect to the index.
from the below result, i can say the query is well orderd.

SQL> select blocks from dba_tables
2 where table_name ='PBM_CLAIM';

BLOCKS
----------
874156

SQL> select clustering_factor from dba_indexes
2 where index_name='CLAIM_GROUP_FK_I';

CLUSTERING_FACTOR
-----------------
9912591
u can see the difference between the two. i came to the conclusion that my query is not well ordered

my 2nd assumtiom is that

Report message to a moderator