RE: what are null sql_id sqls doing
- From: Xiang Rao <xiang_rao@xxxxxxxxxxx>
- To: <dmarc-noreply@xxxxxxxxxxxxx>, "carlospena999@xxxxxxxxx" <carlospena999@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Sep 2014 21:44:21 -0400
Sometime you can find sql_id from v$sql_monitor.
Sent from my Windows Phone
________________________________
From: David Fitzjarrell<mailto:dmarc-noreply@xxxxxxxxxxxxx>
Sent: 9/16/2014 6:54 PM
To: carlospena999@xxxxxxxxx<mailto:carlospena999@xxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: what are null sql_id sqls doing
I would expect that the SQL associated with the prev_sql_id is what is running.
Possibly V$SESSION_LONGOPS can provide some insight, but remember that this
view doesn't capture all long-running statements. You could create a login.sql
that starts a 10046 trace to capture what each session is doing. Remember to
remove or rename it after the tracing period is over. That may be the only way
to know with any certainty what is consuming CPU.
David Fitzjarrell
Principal author, "Oracle Exadata Survival Guide"
On Tuesday, September 16, 2014 3:22 PM, C P <carlospena999@xxxxxxxxx> wrote:
Hi,
We see some sqls that have been running for a long time in our prod DB, 11.2.
when we check the v$session, i see null sql ids for them. The sql_address shows
up as 00. I would like to know what these sqls are doing because they
correspond to processes that are burning up the CPU and driving the usage to >
85%.
I looked up the prev_sql_id column for these null sqlid queries and found the
SQLs corresponding to the prev_sql_ids. Checked the vsql view, found out that
they have first load time 8 to 10 months ago. Their executions counts are still
increasing over a period of minutes, though not dramatically.
Cp.
Other related posts:
