Re: library cache: bucket mutex X"

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Fri, 25 Jun 2021 06:46:20 +0100
Message-ID: <CAGtsp8mEdSy_KQrjoAHc98k+ytYq=rhEopKgo2-VcvkPgFnKYA_at_mail.gmail.com>

Or here's one I hacked together using outdated XML technology from 10g. I do have a more up to date example somewhere, but I can't find it. I think there are at least 3 functions in the subquery that should have been replaced by new mechanisms.

select

sql_id, executions
from

v$sqlstats
where

        sql_id in (
                select  /*+ cardinality(2) */
                        extractvalue(value(t),'.') sql_id
                from
                        table(
                                select

xmlsequence(extract(xmlval,'/sql_ids/sql_id'))
                                from    (
                                        select
                                                xmltype('<sql_ids>

<sql_id>5w8u1cvrsc038</sql_id><sql_id>4tppmg7r9s0bj</sql_id> </sql_ids>') xmlval

;

| Id  | Operation                            | Name                   |
Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                     |                        |


  |       |    16 (100)|          |


|   1 |  NESTED LOOPS                        |                        |


1 | 16408 |    16  (13)| 00:00:01 |


|   2 |   VIEW                               | VW_NSO_1               |


2 | 32770 |    15   (7)| 00:00:01 |


|   3 |    HASH UNIQUE                       |                        |


1 |     4 |            |          |


|   4 |     COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |


 8168 | 16336 |    15   (7)| 00:00:01 |


|   5 |      FAST DUAL                       |                        |


1 |       |     2   (0)| 00:00:01 |
|*  6 |   FIXED TABLE FIXED INDEX            | X$KKSSQLSTAT (ind:1)   |
1 |    23 |     0   (0)|          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

6 - filter(("SQL_ID"="SQL_ID" AND INTERNAL_FUNCTION("CON_ID") AND "INST_ID"=USERENV('INSTANCE'))) Regards
Jonathan Lewis

On Fri, 25 Jun 2021 at 02:36, kyle Hailey <kylelf_at_gmail.com> wrote:

>
> cool - thanks!
>
> Also found this one which does index access:
>
> with sqlids as (
> select
> '5w8u1cvrsc038,4tppmg7r9s0bj,3ysr2kddws09s,bjwgv151gh08k,3z7am7x9wn07k,0ftumbr3kw07c,82ms0j89fw05k,cbs3s3tgpn042'
> ids from dual
> ),
> data as (
> SELECT REGEXP_SUBSTR ( sqlids.ids , '[^,]+', 1, LEVEL) i_sqlid
> FROM sqlids
> CONNECT BY REGEXP_SUBSTR (sqlids.ids, '[^,]+', 1, LEVEL) IS NOT NULL
> )
> select sql_id, executions, elapsed_time, rows_processed, buffer_gets,
> physical_read_requests, physical_write_requests, total_sharable_mem,
> cpu_time from V$SQLSTATS s , data d where s.sql_id = d.i_sqlid;
>
>
> ------------------------------------------------------------------------------------------------------
> | Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time
>   |
>
> -------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT       |      |       |       |     2 (100)|      |
> |   1 |  NESTED LOOPS       |      |     1 |   269 |     2 (0)| 00:00:01 |
> |   2 |   VIEW       |      |     1 |   224 |     2 (0)| 00:00:01 |
> |   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |   |      |
> |   4 |     FAST DUAL       |      |     1 |       |     2 (0)| 00:00:01 |
> |*  5 |   FIXED TABLE FIXED INDEX      | X$KKSSQLSTAT (ind:1) |     1 |
>  45 |     0 (0)|      |
>
>
> Strange but the following still does a full table scan
>
>
> with
> sqlids as (
>   select * from table(sys.DBMS_DEBUG_VC2COLL(
>   '5w8u1cvrsc038' ,
>   '4tppmg7r9s0bj' ,
>   '3ysr2kddws09s' ,
>   'bjwgv151gh08k' ,
>   '3z7am7x9wn07k' ,
>   '0ftumbr3kw07c' ,
>   '82ms0j89fw05k' ,
>   'cbs3s3tgpn042' ,
>   '66vupg1ks0038 '
>  ))
> )
> select executions, elapsed_time, rows_processed, buffer_gets,
> physical_read_requests, physical_write_requests, total_sharable_mem,
> cpu_time  from v$sqlstats s, sqlids l where s.sql_id=l.column_value
> ;
>
>
>
> -------------------------------------------------------------------------------------------------------
> | Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time
>   |
>
> -------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT       |      |       |       |    30 (100)|      |
> |*  1 |  HASH JOIN       |      |  8168 |   374K|    30 (4)| 00:00:01 |
> |   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 16336 |
>  29 (0)| 00:00:01 |
> |*  3 |   FIXED TABLE FULL       | X$KKSSQLSTAT |  5614 |   246K|     1
> (100)| 00:00:01 |
>
> -------------------------------------------------------------------------------------------------------
>
>
>
>
>
> On Thu, Jun 24, 2021 at 5:14 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
>> Hi Kyle,
>>
>> JPPD doesn't work with kokbf$ functions (TABLE(), json_table, xmltable),
>> but you can use this query as a workaround:
>>
>> with sqlids(sqlid) as (
>>   select *
>>   from table(sys.ku$_vcnt(
>>     '5w8u1cvrsc038', '4tppmg7r9s0bj'
>>     --mine:
>>     ,'2z0udr4rc402m'
>>     ,'gngtvs38t0060'
>>     ,'dzdjmp6fts0cc'
>>     ))
>> )
>> select
>>   x.*
>> from sqlids,
>>     xmltable(
>>         '/ROWSET/ROW'
>>         passing xmltype.createXML(cursor(select * from v$sqlstats s where
>> s.sql_id=sqlids.sqlid))
>>         columns
>>            sql_id     varchar2(13) path 'SQL_ID',
>>            executions number path 'EXECUTIONS'
>>     )(+) x;
>>
>> Plan:
>> Plan hash value: 4118548789
>>
>>
>> ----------------------------------------------------------------------------------------------------------------
>> | Id  | Operation                              | Name                 |
>> E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
>>
>> ----------------------------------------------------------------------------------------------------------------
>> |   0 | SELECT STATEMENT                       |                      |
>>      |       |   221K(100)|          |
>> |   1 |  NESTED LOOPS OUTER                    |                      |
>>   66M|   381M|   221K  (1)| 00:00:09 |
>> |   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|                      |
>> 8168 | 16336 |    29   (0)| 00:00:01 |
>> |   3 |   XMLTABLE EVALUATION                  |                      |
>>      |       |            |          |
>> |   4 |    VIEW                                | V_$SQLSTATS          |
>>    1 | 17832 |     0   (0)|          |
>> |   5 |     VIEW                               | V$SQLSTATS           |
>>    1 | 17832 |     0   (0)|          |
>> |   6 |      VIEW                              | GV$SQLSTATS          |
>>    1 | 17845 |     0   (0)|          |
>> |*  7 |       FIXED TABLE FIXED INDEX          | X$KKSSQLSTAT (ind:1) |
>>    1 |  1690 |     0   (0)|          |
>>
>> ----------------------------------------------------------------------------------------------------------------
>>
>> Query Block Name / Object Alias (identified by operation id):
>> -------------------------------------------------------------
>>
>>    1 - SEL$2ADF6A80
>>    2 - SEL$2ADF6A80 / KOKBF$0_at_SEL$2
>>    4 - SEL$7        / V_$SQLSTATS_at_SEL$6
>>    5 - SEL$8        / V$SQLSTATS_at_SEL$7
>>    6 - SEL$9        / GV$SQLSTATS_at_SEL$8
>>    7 - SEL$9        / X$KKSSQLSTAT_at_SEL$9
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>>    7 - filter(("SQL_ID"=:B1 AND "INST_ID"=USERENV('INSTANCE') AND
>> INTERNAL_FUNCTION("CON_ID")))
>>
>>
>> On Fri, Jun 25, 2021 at 2:24 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> The workaround is to use one of the messy bits of code to turn a list
>>> into something that can be cast back into a table() to drive a nested loop;
>>> XML it, or JSON it, There may even be a pre-declared "table of varchar2()"
>>> database type that could be used.
>>>
>>> I think even v$sql has the same problem.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Thu, 24 Jun 2021 at 23:59, kyle Hailey <kylelf_at_gmail.com> wrote:
>>>
>>>>
>>>> Thanks for testing those out Jonathan.
>>>>
>>>> Bit frustrating that the IN clause with V$SQLSTATS kicks off a full
>>>> table scan.
>>>>
>>>> Someone posted the same question back in 2017 no answers :
>>>> https://www.oracle.com/webfolder/community/oracle_database/4106512.html
>>>>
>>>> Kyle
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Jun 24, 2021 at 3:16 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>>> wrote:
>>>>
>>>>>
>>>>> Trying the OR_EXPAND() hint with the simple IN list of sql_ids, the
>>>>> 10053 trace reports:
>>>>>
>>>>> ORE: Checking validity of OR Expansion for query block SEL$1 (#0)
>>>>> ORE: Predicate chain before QB validity check - SEL$1
>>>>> "V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
>>>>> "V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
>>>>> ORE: Predicate chain after QB validity check - SEL$1
>>>>> "V$SQLSTATS"."SQL_ID"='3w8u1cvrsc038' OR
>>>>> "V$SQLSTATS"."SQL_ID"='4tppmg7r9s0bj'
>>>>> ORE: bypassed - No valid predicate for OR expansion.
>>>>>
>>>>> Trying the use_concat() hint the 10053 is slightly more informative
>>>>>
>>>>> LORE: Trying or-Expansion on query block SEL$88122447 (#0)
>>>>> LORE: Or-expansion bypassed: No index driver found in OR chain:
>>>>> id=0  predicate=("X$KKSSQLSTAT"."SQL_ID"='3w8u1cvrsc038' OR
>>>>> "X$KKSSQLSTAT"."SQL_ID"='4tppmg7r9s0bj') AND ("X$KKSSQLSTAT"."CON_ID"=3 OR
>>>>> "X$KKSSQLSTAT"."CON_ID"=0) AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
>>>>> LORE: Or-expansion bypassed: No index driver found in OR chain:
>>>>> id=0  predicate=("X$KKSSQLSTAT"."CON_ID"=3 OR
>>>>> "X$KKSSQLSTAT"."CON_ID"=0) AND "X$KKSSQLSTAT"."INST_ID"=USERENV('INSTANCE')
>>>>> LORE: USE_CONCAT hint was ignored.
>>>>>
>>>>>
>>>>> The antepenultimate line of the use_concat() case suggests that the
>>>>> code or or_expansion (old or new) doesn't understand x$ pseudo-indexes.
>>>>>
>>>>> Regards
>>>>> Jonathan Lewis
>>>>>
>>>>>
>>>>>
>>>>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Oracle performance tuning engineer
>> Oracle ACE Associate
>> http://orasql.org
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 25 2021 - 07:46:20 CEST

This message: [ Message body ]
Next message: William Robertson: "Re: On the premises Oracle 21c download"
Previous message: kyle Hailey: "Re: library cache: bucket mutex X""
In reply to: kyle Hailey: "Re: library cache: bucket mutex X""
Next in thread: kyle Hailey: "Re: library cache: bucket mutex X""

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message