sql monitoring 长SQL ASH AWR 都没有 未Commit or export to csv

Duration 4小时， Database Time 22.5， Session Inactive，

1.未Commit原因,

2.慢慢导出成csv文件？

How is v$session status = 'INACTIVE' and v$sql_monitor status = 'EXECUTING' concurrently

2641811 Posts: 8

Jan 11, 2016 6:47PM edited Jan 19, 2016 6:07PM 6 commentsAnswered

Can someone please explain a scenario where v$session status = 'INACTIVE' and v$sql_monitor status = 'EXECUTING'. 

I am seeing this currently in one of my databases and do not understand this scenario?

Thanks for your help.

FlagQuoteOff TopicLike

Answers

• Mark D Powell DBA Posts: 17,366 Rubellite

  Jan 11, 2016 7:19PM edited Jan 11, 2016 7:19PM

  user2641811, I haven't really used this view but I note from the manual that the view can retain data until the space is necessary for reuse so what you see may be related to this.  The view V$SESSION only shows ACTIVE when a SQL statement is actually executing.  Once a statement completes the session is officially INCACTIVE till the next SQL statement is executed.

  - -

  "When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored"

  Ref: V$SQL_MONITOR

  - -

  Your exact version of Oracle may make a difference to the expected behavior but I did not search support for any bug or usage notes that may exist.

  - -

  HTH -- Mark D Powell --

  HTH -- Mark D Powell --

  FlagQuoteOff TopicLike

• 2641811 Posts: 8

  Jan 11, 2016 8:05PM edited Jan 11, 2016 8:05PM

  Thanks for your reply Mark.

  I am running Oracle version 12.1.0.2.3 on Linux.

  I have been monitoring v$sesstat as well and do not see any statistics changes for that session.

  I started tracing the session to look at activity from that angle and see the following constantly being written:

  WAIT #140323598165824: nam='SQL*Net message from client' ela= 24995 driver id=1413697536 #bytes=1 p3=0 obj#=1971587 tim=317089128562

  WAIT #140323598165824: nam='SQL*Net message to client' ela= 13 driver id=1413697536 #bytes=1 p3=0 obj#=1971587 tim=317089128737

  FETCH #140323598165824:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=10,dep=0,og=1,plh=2205593993,tim=317089128786

  So, does this mean that when a session is done executing and is only fetching data that the session is marked as "INACTIVE" in v$session whereas fetching data is still considered "EXECUTING" in v$sql_monitor?

  FlagQuoteOff TopicLike

• top.gun Posts: 4,778 Gold Crown

  Jan 11, 2016 8:11PM edited Jan 11, 2016 8:11PM

  You would need to determine what the session is currently waiting for.

  I could take a guess....

  What if your session was waiting for a lock, so it's executing, but at the same time waiting for the lock.....

  FlagQuoteOff TopicLike

• 2641811 Posts: 8

  Jan 11, 2016 8:23PM edited Jan 11, 2016 8:23PM

  There are no blocking locks in the database.  I monitored v$session for a bit and the only wait events for that session are "SQL*Net message from client" and "SQL*Net message to client".

  FlagQuoteOff TopicLike

• Mark D Powell DBA Posts: 17,366 Rubellite

  Jan 11, 2016 9:09PM edited Jan 11, 2016 9:09PM

  The FETCH line indicates that a cursor row was obtained so if you see new FETCHes in the trace then either the SQL is very fast and the application is likely executing the same SQL statement with different bind variables or it previously executed a cursor and is now passing back a row at a time from what would appear to be a large result set.

  - -

  You can query the SQL associated with the session via the V$SESSION sql_address, sql_hash_value or sql_id columns used in a join to one of the V$SQL views such as V$SQLAREA or V$SQLTEXT.  If there is no current SQL statement check the PREV_* statement columns.