Re: library cache: bucket mutex X"

From: kyle Hailey <kylelf_at_gmail.com>
Date: Thu, 24 Jun 2021 18:36:11 -0700
Message-ID: <CADsdiQi920hBg5ivHydyeN5gSYq0mt8KLpRYGXBQ7PJd1FYoBg_at_mail.gmail.com>

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;

| 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 ;

| 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 - 03:36:11 CEST

This message: [ Message body ]
Next message: Jonathan Lewis: "Re: library cache: bucket mutex X""
In reply to Sayan Malakshinov: "Re: library cache: bucket mutex X""
Next in thread: Jonathan Lewis: "Re: library cache: bucket mutex X""
Reply: Jonathan Lewis: "Re: library cache: bucket mutex X""

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

Original text of this message