1. Convert the hex value(27db) to decimal value. 2. Lookup dba_objects view with the decimal value. select object_name from dba_objects where data_object_id = <value_from_step_1>; 3. check whether the table has LOB column and you have any query run on that LOB column. (I believe that 99% of causes of non-existent SQL text problem are LOB-related) I don't see any direct relationship between the datafile addition and the slowdown of query on LOB , but there would be some reason that should be identified with more informations like AWR report and/or something. ================================ 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> > 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 >> >> >> > >