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>


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-l
Received on Tue Nov 30 2010 - 18:53:55 CST

Original text of this message