Re: sql with SQL_OPCODE=0
- From: Denis <denis.sun@xxxxxxxxx>
- To: Dion Cho <ukja.dion@xxxxxxxxx>
- Date: Tue, 30 Nov 2010 17:26:04 -0800 (PST)
Dion,
Thanks! see following, any more insights on how to interpret it and the
x$kglob? That table_e_a is certainly not an application object.
select kglnaobj
from x$kglob
where kglobt03 = '6s8fdgnw2u49h'
;
2 3 4
KGLNAOBJ
------------------------------------------------------------------------------------------------------------------------
table_e_a_27db_7_0_0
table_e_a_27db_7_0_0
table_e_a_27db_7_0_0
table_e_a_27db_7_0_0
________________________________
From: Dion Cho <ukja.dion@xxxxxxxxx>
To: denis.sun@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Tue, November 30, 2010 8:11:46 PM
Subject: 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:
