Re: sql with SQL_OPCODE=0

Instead of V$SQL, search X$KGLOB, which is the mother of V$SQL.

select kglnaobj
from x$kglob
where kglobt03 = '6s8fdgnw2u49h'
;

================================
Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://sites.google.com/site/otpack (tpack)
================================


2010/12/1 Denis <denis.sun@xxxxxxxxx>

> 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
>
>
>

Other related posts: