sql with SQL_OPCODE=0
From: Denis <denis.sun_at_yahoo.com>
Date: Tue, 30 Nov 2010 16:53:55 -0800 (PST)
Message-ID: <785792.3852.qm_at_web57207.mail.re3.yahoo.com>
Date: Tue, 30 Nov 2010 16:53:55 -0800 (PST)
Message-ID: <785792.3852.qm_at_web57207.mail.re3.yahoo.com>
Hi,
Yesterday, after adding a datafile to a busy system, performance degraded. Run
the ASH report during problem period, noticed a SQL with sql_id=6s8fdgnw2u49h
as the top SQL:
-------------------------------------------------------------
Top SQL Statements DB/Inst: XXXXXX/XXXXX (Nov 29 16:20 to 16:30)
SQL ID Planhash % Activity Event % Event
------------- ----------- ---------- ------------------------------ ----------
6s8fdgnw2u49h N/A 69.67 CPU + Wait for CPU 69.11
** SQL Text Not Available **
However, I can not find the sql_text anywhere.
Today, I noticed that many sessions acutually run this sql as routines. for
example:
select sample_time, sql_id, sql_opcode, SESSION_TYPE from
v$active_session_history where session_id=882;
SAMPLE_TIME SQL_ID SQL_OPCODE SESSION_TY
--------------------------------- ------------- ---------- ----------
30-NOV-10 06.22.02.843 PM abd2dmdpvjvy5 3 FOREGROUND
30-NOV-10 06.21.31.493 PM 1azxzyptvjvvs 3 FOREGROUND
30-NOV-10 06.21.30.483 PM 54pdffnh5xgb7 3 FOREGROUND
....
30-NOV-10 06.17.30.468 PM gkuyk6gjc1tn6 3 FOREGROUND
30-NOV-10 06.16.58.038 PM 0 FOREGROUND
30-NOV-10 06.16.57.028 PM 6s8fdgnw2u49h 0 FOREGROUND ======>
sql_id
30-NOV-10 06.16.49.951 PM 3kbr7bthvwqkx 3 FOREGROUND
30-NOV-10 06.15.26.961 PM cfm8mwq01sg68 3 FOREGROUND
30-NOV-10 06.15.18.861 PM 54pdffnh5xgb7 3 FOREGROUND
30-NOV-10 06.13.59.864 PM 0 FOREGROUND
30-NOV-10 06.13.42.674 PM 0 FOREGROUND
30-NOV-10 06.12.24.736 PM 54pdffnh5xgb7 3 FOREGROUND
.....
My question is in which situation we have sql_id but don't have sql_text in
v$sql or dba_hist_sqltext? what's the meaning of SQL_OPCODE=0 ? Appreciate
anyone can shed some lights on this.
Denis
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 30 2010 - 18:53:55 CST