OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL TUNING (Oracle 10G)

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

SQL TUNING [message #521110]

Fri, 26 August 2011 07:37

Bhaskarquadrant
Messages: 4
Registered: August 2011
Location: CHENNAI

Junior Member

Hi ,
Can any one having experience in tuning tell me ,what is to be reduced in this trace file output?
My trace file looks like as follows.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 1378 0.13 0.20 0 0 0 0
Fetch 1378 582.12 3713.95 2433759 16034211 0 1378
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2761 582.25 3714.15 2433759 16034211 0 1378

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 248 (recursive depth: 1)

Thanks-Bhaskar

Report message to a moderator

Re: SQL TUNING [message #521111 is a reply to message #521110]

Fri, 26 August 2011 07:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

what is to be reduced in this trace file output?

Execute less often the statement.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Fri, 26 August 2011 07:40]

Report message to a moderator

Re: SQL TUNING [message #521112 is a reply to message #521111]

Fri, 26 August 2011 07:41

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Hey,
Post the sql statements,we can tune it.

Report message to a moderator

Re: SQL TUNING [message #521113 is a reply to message #521112]

Fri, 26 August 2011 07:43

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And before, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: SQL TUNING [message #521114 is a reply to message #521113]

Fri, 26 August 2011 07:49

Bhaskarquadrant
Messages: 4
Registered: August 2011
Location: CHENNAI

Junior Member

Hi,
First of all i am interested to know that what should i look in atrace file to tune the sql?
Please tell me out the following what should one understand and consider ?
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 1378 0.13 0.20 0 0 0 0
Fetch 1378 582.12 3713.95 2433759 16034211 0 1378
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2761 582.25 3714.15 2433759 16034211 0 1378

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 248 (recursive depth: 1)
Thanks-Bhaskar

Report message to a moderator

Re: SQL TUNING [message #521115 is a reply to message #521114]

Fri, 26 August 2011 07:52

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

What additional information is in this post that wasn't in the first one?

Report message to a moderator

Re: SQL TUNING [message #521116 is a reply to message #521114]

Fri, 26 August 2011 07:53

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Please tell me out the following what should one understand and consider ?

With only this no one can understand something above all when it is not formatted.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

[Updated on: Fri, 26 August 2011 07:53]

Report message to a moderator

Re: SQL TUNING [message #521332 is a reply to message #521114]

Sun, 28 August 2011 22:23

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

You just have not provided enough information.

All we can see is that you have had 1,378 executions, each execution fetching 1 row but doing 11,636 block gets.

How about starting by looking at the SQL statement and the execution plan ?

Hemant K Chitale

Report message to a moderator

Previous Topic:	Managing Large Table
Next Topic:	RANGE SCAN VS FAST FULL SCAN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 01:38:57 CST 2024