RE: what are null sql_id sqls doing
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Sep 2014 06:49:54 +0000
Here's a thought experiment - which someone might want to turn into a real
experiment:
Imagine you execute an SQL statement that returns a lot of data which you fetch
100 rows at a time.
In the statement you have a pl/sql function in the select list and another in
the where clause.
Both functions call SQL statements, both function do a (relatively) complicated
piece of pl/sql processing for each row they fetch.
At any instant (and there are time-dependent variations) what will you see as
v$session.sql_id and v$session.prev_sql_id. E.g. what's in those columns at a
moment when the pl/sql function in the select list has fetched a row and the
pl/sql code is now doing its complex thing before discarding that row and
fetching another ?
It's easy to forget that the SQL_ID and PREV_SQL_ID have to cover complex
situations.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of David Fitzjarrell [dmarc-noreply@xxxxxxxxxxxxx]
Sent: 16 September 2014 23:53
To: carlospena999@xxxxxxxxx; 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:
