Charles Hooper wrote:
> dbaplusplus_at_hotmail.com wrote:
> > I am on Oracle 9.2.0.5 on HP UNIX 11i. I have several queries, which
> > takes 1-2 minutes
> > Despite indexes being used. When I look at explain plans (gotten from
> > v$sql_plan), here is INDEX FAST FULL SCAN on a large no of rows (137K
> > below).
> > 0 | SELECT STATEMENT | | |
> > | |
> > 316 |* 1 | FILTER | |
> > | | |
> > 317 | 2 | NESTED LOOPS | |
> > 94 | 24346 | 14463 (0)|
> > 318 | 3 | NESTED LOOPS | |
> > 3218 | 722K| 14141 (0)|
> > 319 | 4 | NESTED LOOPS | |
> > 1495 | 296K| 13842 (0)|
> > 320 | 5 | INDEX FAST FULL SCAN | D_1F00C73D80000D01 |
> > 137K| 2286K| 68 (0)|
> > 321 |* 6 | TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S |
> > 1 | 186 | 2 (50)|
> > 322 |* 7 | INDEX UNIQUE SCAN | D_1F00C73D80000108 |
> > 1 | | |
> > Is there any way to reduce response time of such queries? I have
> > already tuned my database many times (e.g., it has !GB of
> > db_buffer_cacahe * 8K).
> >
> > I do not have access to source code. so I cannot rewrite queries,
> > however I can alter stored outlines. If there any hidden parameters in
> > init.ora, willing to try as well.
> >
> > Any ideas will be appreciated.
>
> What is the SORT_AREA_SIZE set to, the default of 64KB? Have you
> looked at the wait events associated with the session that is executing
> these hard coded SQL statements? Setting the system up for a large
> buffer cache should not be the only tuning method used to troubleshoot
> performance problems.
>
> Here is an example of what you can do when a performance problem occurs
> when processing those slow SQL statements. First, find the SID of the
> session by checking V$SESSION. Before executing the SQL statements,
> execute this SQL statement, filling in the SID of the session:
> SELECT
> EVENT,
> TIME_WAITED,
> ROWNUM
> FROM
> (SELECT
> EVENT,
> TIME_WAITED
> FROM
> V$SESSION_EVENT
> WHERE
> EVENT NOT LIKE 'SQL*Net %'
> AND EVENT NOT IN ('pmon timer','rdbms ipc message','smon
> timer','Null event','Streams AQ: qmn slave idle wait','wait for unread
> message on broadcast channel','jobq slave wait','Streams AQ: waiting
> for messages in the queue','Streams AQ: qmn coordinator idle
> wait','Streams AQ: waiting for time management or cleanup
> tasks','Streams AQ: qmn coordinator waiting for slave to start','rdbms
> ipc reply')
> AND SID= sid_of_session_executing_sql
> ORDER BY
> TIME_WAITED DESC)
> WHERE
> ROWNUM<=10;
>
> The above will retrieve the top 10 wait events for the session - this
> is the starting point. In the session, execute one of the slow
> performing SQL statements. Then execute the above SQL statement and
> compare it with the previous run. The delta values of the wait events
> should give you a rough idea of what is causing the delay. Note that
> the above eliminates the wait events that begin with SQL*Net - such
> events indicate that Oracle is waiting for the client session to make
> another request. You may be able to use these events to see if the
> client is the cause of the slow execution.
>
> If the above does not help much, try creating a 10046 trace of the
> session, then have the session execute the slow SQL statements. By
> manually reviewing the 10046 trace file, you can determine the exact
> database objects cauing the slow performance, the exact point when the
> wait events occurred, the the SQL plan.
>
> As Sybrand stated, the plan that you provided is of limited help - it
> looks like the database isn't being asked to do anything too stressful.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
SORT_AREA_SIZE=20M
I have looked into statspack reports, do not see any noticeable delays
for wait events.
Yes, my sql_satement is not asking anything too stressaful, yet my
staements take 1-2 minutes to execute. I will try the event trace you
discuss. Thanks.
Received on Sat Sep 30 2006 - 23:22:07 CDT
