Home » RDBMS Server » Performance Tuning » need help to optimize the query (oracle 10.2.0.3 solaris 10)
need help to optimize the query [message #335829] Wed, 23 July 2008 19:16 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

i am getting problem with one query. execution plan for this query is looking good. cost is not more than 20%. still it is running slow. i am not able to pin point the problem. can any one help me.

one more thing i check in awr report it is taking 29% the db time and cpu time is 455 second. please find the details.

#### CODE REDACTED AT OP's REQUEST #### [RL:moderator]

BMVZWPRD:SYSTEM> select * from table(dbms_xplan.display('PLAN_TABLE','plan7','ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1449993865

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
#### CODE REDACTED AT OP's REQUEST #### [RL:moderator]
----------------------------------------------------------------------------------------------------------------------------


---- some portion of awr report
  Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
     5,824        455       26,893        0.2    29.9 1cguc1wwha8fb

#### CODE REDACTED AT OP's REQUEST #### [RL:moderator]

[Updated on: Thu, 06 March 2014 21:07] by Moderator

Report message to a moderator

Re: need help to optimize the query [message #336121 is a reply to message #335829] Thu, 24 July 2008 22:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
26,893 Executions!

I think I see your problem. Why execute it so many times? If it is inside a cursor loop, try to merge its detail in the cursor.

Ross Leishman
Previous Topic: need assistance to tune the query
Next Topic: type tables in plsql/ variable IN list
Goto Forum:
  


Current Time: Wed Dec 07 02:45:12 CST 2016

Total time taken to generate the page: 0.14492 seconds