FAQ
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

Search Discussions

  • Dion Cho at Dec 1, 2010 at 1:33 am
    Convert the hex value(27db) to decimal value.

    Lookup dba_objects view with the decimal value.

    select object_name from dba_objects where data_object_id =;

    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
    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
    *To:* denis.sun_at_yahoo.com
    *Cc:* oracle-l@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
    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
  • Kerry Osborne at Dec 1, 2010 at 1:36 am
    Denis,

    You might want to look at this post I did a while back. http://kerryosborne.oracle-guy.com/2009/04/hidden-sql-why-cant-i-find-my-sql-text/
    Dion and I actually had a bit of discussion on that post. Also 27db bit of the table name may be the hex representation of the object_id being accessed (also discussed in that post). Dion, I'd be interested in any other things you've learned on this subject recently as I haven't really thought about it since that post.

    Kerry Osborne
    Enkitec
    blog: kerryosborne.oracle-guy.com
    On Nov 30, 2010, at 7:26 PM, Denis wrote:

    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
    To: denis.sun_at_yahoo.com
    Cc: oracle-l@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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 1, '10 at 12:53a
activeDec 1, '10 at 1:36a
posts3
users3
websiteoracle.com

3 users in discussion

Denis: 1 post Kerry Osborne: 1 post Dion Cho: 1 post

People

Translate

site design / logo © 2022 Grokbase