Re: library cache: bucket mutex X"

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 25 Jun 2021 03:14:45 +0300
Message-ID: <CAOVevU4w6ODkyc-jeQCME1uUpLHdL8xuqRu6U=Jf1wSQvOf9uQ_at_mail.gmail.com>

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 - 02:14:45 CEST