Re: Waiting sql
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, 'Oracle-L Freelists' <Oracle-L@xxxxxxxxxxxxx>
- Date: Fri, 26 Apr 2013 07:01:26 -0700 (PDT)
You don't say which version you are using, but this is exactly what SQL monitor
- 11g (built in) is for.
Anything that runs longer than 5 seconds is tracked automatically. No need to
bother with tracing.
http://www.oracle.com/technetwork/database/focus-areas/manageability/sqlmonitor-084401.html
Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a very
effective way to identify run-time performance problems with resource intensive
long-running and parallel SQL statements.
Interactive Enterprise Manager screens display details of SQL execution
using new, fine-grained SQL statistic that are tracked out-of-the-box
with no performance penalty to production systems. Statistics at each
step of the execution plan are tracked by key performance metrics,
including elapsed time, CPU time, number of reads and writes, I/O wait
time and various other wait times. This allows DBAs to analyze SQL
execution more deeply than previously possible and decide on the most
appropriate tuning strategies for monitored SQL statements.
----
SQL monitor active reports can be generated directly from EM live UI
while viewing a detailed SQL monitor report. There (see save/send e-mail
buttons on the top right of that page), the SQL monitor detail page can be
either saved or sent by e-mail as an active report. Alternatively,
the active report can be directly produced using command line by
invoking the PL/SQL procedure dbms_sqltune.report_sql_monitor() using
"active" as the report type. For example, the following SQL*Plus script
shows how to generate an active report for the statement that was
monitored last by Oracle:
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off
The resulting file sqlmon_active.html must be
edited to remove the header (first line in the file) and the last line
(the spool off). The resulting html file can then be viewed in any
browser. The browser must have connectivity to OTN to load the active
report code.
________________________________
From: Mark W. Farnham <mwf@xxxxxxxx>
To: 'Oracle-L Freelists' <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, April 25, 2013 10:33 AM
Subject: RE: Waiting sql
Mr. Bort is correct if you're waiting for some resource incrementally
supplying your query's needs.
First I'd probably determine your sid and then
select * from dba_waiters where waiting_session = <session of interest's
sid>;
from another session to see if there is a logical wait (aka a blocker) in
your way.
If that returns anything, then you can investigate the sessions listed in
the column holding_session.
If that returns nothing, then you're waiting for something to grind out
incrementally.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Guillermo Alan Bort
Sent: Thursday, April 25, 2013 9:55 AM
To: rajugaru.vij@xxxxxxxxx
Cc: oracle-l-freelists
Subject: Re: Waiting sql
You can look at the waits for the session issuing the query: v$sesstat,
v$waitstat, v$statname Alternatively you can use toad's session browser.
or you can run a trace and tkprof the output to get a report on what the
session is waiting for.
cheers
Alan.-
On Thu, Apr 25, 2013 at 10:28 AM, <rajugaru.vij@xxxxxxxxx> wrote:
> Hi,
> Iam trying to run a query, which is taking 13 to 14 minutes. Its cost
> is 70. Its waiting for something. How to know for what my query is
> waiting and taking so long.
>
> Thanks
> Sent on my BlackBerryR from Vodafone--
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
