OraFAQ Forum: Performance Tuning » need help to optimize the query

Home » RDBMS Server » Performance Tuning » need help to optimize the query (oracle 10.2.0.3 solaris 10)

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

need help to optimize the query [message #335829]

Wed, 23 July 2008 19:16

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