Re: sql with SQL_OPCODE=0

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Wed, 1 Dec 2010 10:33:44 +0900
Message-ID: <AANLkTimdzrAGNo0JME=AnF0JzM401qdP19ryOn0F=dz+_at_mail.gmail.com>

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_at_yahoo.com>

> 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_at_gmail.com>
> *To:* denis.sun_at_yahoo.com
> *Cc:* oracle-l_at_freelists.org
> *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_at_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 - 19:33:44 CST