RE: library cache: bucket mutex X"
Date: Fri, 25 Jun 2021 17:56:22 -0400
Message-ID: <6d1c01d76a0c$f03584b0$d0a08e10$_at_rsiz.com>
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
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
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)| |
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: library cache: bucket mutex X"
var sqlids varchar2(240)
:sqlids := '5w8u1cvrsc038,4tppmg7r9s0bj,3ysr2kddws09s,bjwgv151gh08k,3z7am7x9wn07k,0ftumbr3kw07c,82ms0j89fw05k,cbs3s3tgpn042';
| 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 - 23:56:22 CEST