Re: library cache: bucket mutex X"

Hi Mark,

Yes, it is such a treat to get input for folks like Jonathan and Sayan on this list!

Yes, UNION ALL uses the index in my tests. That would be an easy solution with a fixed # of SQL_IDs that could be substituted into a fixed query.
Could work out some situation where the UNION all was either built on the fly (seems messy) or have a set of UNION ALL queries with different # of SQL_ID values.
For me, my list changes, and I can send in an arbitrary size list to a single fixed query. Seemed like the cleanest solution.

Kyle

On Fri, Jun 25, 2021 at 3:02 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> I’m curious whether with only eight ?permanent? data values whether an
> eight part union all with s.sql_id = literal uses the index efficiently.
>
>
>
> mwf
>
>
>
> PS: The three of you certainly showed a tour de force of example uses that
> should be bundled up somewhere by someone. Very cool and a tip of my hat to
> all y’all!
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *kyle Hailey
> *Sent:* Friday, June 25, 2021 1:27 PM
> *To:* Jonathan Lewis
> *Cc:* ORACLE-L
> *Subject:* Re: library cache: bucket mutex X"
>
>
>
>
>
>
>
> 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-l