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@xxxxxxxxx> 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@xxxxxxxxx>
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@SEL$2
4 - SEL$7 / V_$SQLSTATS@SEL$6
5 - SEL$8 / V$SQLSTATS@SEL$7
6 - SEL$9 / GV$SQLSTATS@SEL$8
7 - SEL$9 / X$KKSSQLSTAT@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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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
