Re: library cache: bucket mutex X"
From: kyle Hailey <kylelf_at_gmail.com>
Date: Fri, 25 Jun 2021 10:27:02 -0700
Message-ID: <CADsdiQiWP9zXWH_AbUWhQPFajXsu1RiKNv0m1ZPR6osCQw4ACQ_at_mail.gmail.com>
'5w8u1cvrsc038,4tppmg7r9s0bj,3ysr2kddws09s,bjwgv151gh08k,3z7am7x9wn07k,0ftumbr3kw07c,82ms0j89fw05k,cbs3s3tgpn042';
Date: Fri, 25 Jun 2021 10:27:02 -0700
Message-ID: <CADsdiQiWP9zXWH_AbUWhQPFajXsu1RiKNv0m1ZPR6osCQw4ACQ_at_mail.gmail.com>
Thanks Jonathan and Sayan for the cool examples. Wish there was a way to force the optimiser to do the index access without such contorsions, but at least there is a workaround. I think I'm going to go with something like
var sqlids varchar2(240)
:sqlids :=
'5w8u1cvrsc038,4tppmg7r9s0bj,3ysr2kddws09s,bjwgv151gh08k,3z7am7x9wn07k,0ftumbr3kw07c,82ms0j89fw05k,cbs3s3tgpn042';
with sqlids as (
select :sqlids ids from dual
),
data as (
SELECT REGEXP_SUBSTR ( sqlids.ids , '[^,]+', 1, LEVEL) 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.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)| |
-------------------------------------------------------------------------------------------------------
On Thu, Jun 24, 2021 at 10:46 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> 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
> from dual
> )
> ) t
> )
> ;
>
>
> ---------------------------------------------------------------------------------------------------------------
> | 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-lReceived on Fri Jun 25 2021 - 19:27:02 CEST