RE: what are null sql_id sqls doing
Date: Tue, 16 Sep 2014 21:44:21 -0400
Message-ID: <BLU405-EAS25268B15083FD8767C4108495B60_at_phx.gbl>
Sometime you can find sql_id from v$sql_monitor.
Sent from my Windows Phone
From: David Fitzjarrell<mailto:dmarc-noreply_at_freelists.org> Sent: 9/16/2014 6:54 PM
To: carlospena999_at_gmail.com<mailto:carlospena999_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> 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_at_gmail.com> 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Sep 17 2014 - 03:44:21 CEST