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