A time ago I stumbled upon a multi-dimensional performance problem where the OEM sessions got completely stuck spawning a growing pile of SQL executions that felt practically never-ending.
The culprit was a standard OEM query that returns genuinely useful information (you’ll recognize it immediately from the SQL text): tablespace space utilization, temp tablespace details, and related metrics — all across containers.
That’s why I called it “multi-dimensional”: OEM, multitenancy, and parallel execution were all in play. In the end – as so often – it came down to tuning a single (and fairly simple) SQL statement. But at the start I was completely in the dark, with no real clue where to begin.
In the nutshell, the main session was started by OEM and connected to the CDB (root container). It then launched the query below and… politely suggested I take two or three coffee breaks while it thought things over.
The query :
oemquery.sql :
SELECT
pdb.name,
ts.tablespace_name,
NVL(t.bytes/1024/1024,0) allocated_space,
NVL(DECODE(un.bytes,null,DECODE(ts.contents,'TEMPORARY',
DECODE(ts.extent_management,'LOCAL',u.bytes,t.bytes - NVL(u.bytes, 0)), t.bytes - NVL(u.bytes, 0)),
un.bytes)/1024/1024,0) used_space
FROM
cdb_tablespaces ts, v$containers pdb,
(
select con_id, tablespace_name, sum(bytes) bytes
from cdb_free_space
group by con_id,tablespace_name
UNION ALL
SELECT ts.con_id, ts.tablespace_name, NVL(SUM(s.used_blocks * ts.block_size),0)
FROM gv$sort_segment s, cdb_tablespaces ts
WHERE ts.tablespace_name = s.tablespace_name AND ts.con_id = s.con_id
GROUP BY ts.con_id, ts.tablespace_name
) u,
(
SELECT con_id, tablespace_name,
SUM(NVL(bytes, 0)) bytes
FROM cdb_data_files
GROUP BY con_id, tablespace_name
UNION ALL
SELECT con_id, tablespace_name,
SUM(NVL(bytes, 0)) bytes
FROM cdb_temp_files
GROUP BY con_id, tablespace_name
) t,
(
SELECT ts.con_id, ts.tablespace_name, NVL(um.used_space*ts.block_size, 0) bytes
FROM cdb_tablespaces ts, cdb_tablespace_usage_metrics um
WHERE ts.tablespace_name = um.tablespace_name(+)
AND ts.con_id = um.con_id(+)
AND ts.contents='UNDO'
) un
WHERE ts.tablespace_name = t.tablespace_name(+)
AND ts.tablespace_name = u.tablespace_name(+)
AND ts.tablespace_name = un.tablespace_name(+)
AND ts.con_id = pdb.con_id
AND ts.con_id = u.con_id(+)
AND ts.con_id = t.con_id(+)
AND ts.con_id = un.con_id(+)
and ts.con_id not in (40,41)
ORDER BY 1,2
/If there is a chance to examine a SQL query in isolation (and there is no license limitation), I typically add the hint /*+ monitor */ and have a glance at the output.
Very useful, as it gives you the notion in which SQL Plan ID the time gets wasted, provided there are outliers.
So it does not only report the potential discrepancy between the estimated and the actual cardinality which is the outcome of /*+ gather_plan_statistics */, but in most of the cases it may give you a hint where to revert your tuning efforts.
So, I grabbed the SQL text from above, added the hint, and ran it manually.
As you’re witnessing nearly live scenario (it’s still being executed, see “Status: EXECUTING”) and for long enough (132s) to make it a solid starting point for pinpointing the real bottleneck(s).
SQL Monitor snapshot :
Global Information
------------------------------
Status : EXECUTING
Instance ID : 5
Session : SYS (912:30203)
SQL ID : 9y04wqhbfdxm8
SQL Execution ID : 83886080
Execution Started : 06/26/2024 14:05:41
First Refresh Time : 06/26/2024 14:05:41
Last Refresh Time : 06/26/2024 14:07:11
Duration : 132s
Module/Action : sqlplus@host.com (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@host.com (TNS V1-V3)/-
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
============================================================================================
| 1083 | 796 | 184 | 0.02 | 0.00 | 102 | 56M | 12M | 89GB |
============================================================================================
Parallel Execution Details (DOP=12 , Servers Allocated=32)
Instances : 8
==========================================================================================================================================================================================
| Instance | Name | Type | Group# | Server# | Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Read | Read | Wait Events |
| | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
==========================================================================================================================================================================================
| 5 | PX Coordinator | QC | | | 0.07 | 0.06 | | | | 0.01 | | | . | |
| 1 | ppa4 | Set 1 | 1 | 1 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 2 | ppa4 | Set 1 | 1 | 2 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 3 | ppa4 | Set 1 | 1 | 3 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 4 | ppa4 | Set 1 | 1 | 4 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 5 | ppa7 | Set 1 | 1 | 5 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 6 | ppa7 | Set 1 | 1 | 6 | 0.01 | 0.01 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 7 | ppa7 | Set 1 | 1 | 7 | 0.01 | 0.00 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 8 | ppa7 | Set 1 | 1 | 8 | 0.01 | 0.00 | 0.00 | | | 0.00 | | 5 | 2MB | |
| 1 | p000 | Set 1 | 2 | 1 | | | | | | | | | . | |
| 2 | p000 | Set 1 | 2 | 2 | | | | | | | | | . | |
| 3 | p000 | Set 1 | 2 | 3 | | | | | | | | | . | cell single block physical read (1) |
| 4 | p000 | Set 1 | 2 | 4 | | | | | | | | | . | |
| 5 | p000 | Set 1 | 2 | 5 | | | | | | | | | . | |
| 5 | p001 | Set 1 | 2 | 6 | | | | | | | | | . | |
| 6 | p01y | Set 1 | 2 | 7 | | | | | | | | | . | |
| 6 | p01z | Set 1 | 2 | 8 | | | | | | | | | . | |
| 7 | p000 | Set 1 | 2 | 9 | | | | | | | | | . | |
| 7 | p001 | Set 1 | 2 | 10 | | | | | | | | | . | |
| 8 | p000 | Set 1 | 2 | 11 | | | | | | | | | . | |
| 8 | p001 | Set 1 | 2 | 12 | | | | | | | | | . | |
| 1 | p001 | Set 2 | 2 | 1 | 133 | 106 | 13 | | | 14 | 6M | 2M | 12GB | |
| 2 | p001 | Set 2 | 2 | 2 | 13 | 5.77 | 7.16 | | | 0.36 | 261K | 84505 | 660MB | |
| 3 | p001 | Set 2 | 2 | 3 | 133 | 108 | 9.48 | 0.00 | | 15 | 7M | 1M | 11GB | |
| 4 | p001 | Set 2 | 2 | 4 | 133 | 122 | 1.94 | 0.01 | | 9.03 | 10M | 2M | 13GB | |
| 5 | p002 | Set 2 | 2 | 5 | 133 | 97 | 18 | 0.00 | 0.00 | 18 | 7M | 1M | 11GB | |
| 5 | p003 | Set 2 | 2 | 6 | 133 | 114 | 1.08 | 0.00 | | 18 | 9M | 2M | 14GB | |
| 6 | p020 | Set 2 | 2 | 7 | 131 | 114 | 1.36 | 0.00 | | 16 | 8M | 2M | 14GB | |
| 6 | p021 | Set 2 | 2 | 8 | 140 | 29 | 110 | 0.01 | | | 944K | 213K | 2GB | |
| 7 | p002 | Set 2 | 2 | 9 | 0.93 | 0.19 | 0.74 | 0.00 | | | 5230 | 2010 | 16MB | |
| 7 | p003 | Set 2 | 2 | 10 | | | | | | | | | . | |
| 8 | p002 | Set 2 | 2 | 11 | 133 | 99 | 22 | 0.00 | | 12 | 7M | 1M | 10GB | |
| 8 | p003 | Set 2 | 2 | 12 | | | | | | | | | . | |
==========================================================================================================================================================================================
Instance Drill-Down
============================================================================================================================================================================
| Instance | Process Names | Elapsed | Cpu | IO | Concurrency | Cluster | Other | Buffer | Read | Read | Wait Events |
| | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | |
============================================================================================================================================================================
| 1 | ppa4 p000 p001 | 133 | 106 | 13 | | | 14 | 6M | 2M | 12GB | |
| 2 | ppa4 p000 p001 | 13 | 5.78 | 7.16 | | | 0.36 | 261K | 84510 | 662MB | |
| 3 | ppa4 p000 p001 | 133 | 108 | 9.48 | 0.00 | | 15 | 7M | 1M | 11GB | cell single block physical read (1) |
| 4 | ppa4 p000 p001 | 133 | 122 | 1.94 | 0.01 | | 9.03 | 10M | 2M | 14GB | |
| 5 | QC ppa7 p000 p001 p002 p003 | 266 | 212 | 19 | 0.00 | 0.00 | 35 | 16M | 3M | 25GB | |
| 6 | ppa7 p01y p01z p020 p021 | 271 | 143 | 112 | 0.01 | | 16 | 9M | 2M | 16GB | |
| 7 | ppa7 p000 p001 p002 p003 | 0.94 | 0.20 | 0.74 | 0.00 | | 0.00 | 5230 | 2015 | 18MB | |
| 8 | ppa7 p000 p001 p002 p003 | 133 | 99 | 22 | 0.00 | | 12 | 7M | 1M | 10GB | |
============================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4004127808)
============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 11 | | | | . | | |
| 1 | PX COORDINATOR | | | | | | 11 | | | | . | | |
| 2 | PX SEND QC (ORDER) | :TQ20014 | 323 | 54 | | | | | | | . | | |
| 3 | SORT ORDER BY | | 323 | 54 | | | | | | | . | | |
| 4 | PX RECEIVE | | 323 | 53 | | | | | | | . | | |
| 5 | PX SEND RANGE | :TQ20013 | 323 | 53 | | | | | | | . | | |
| 6 | HASH JOIN OUTER BUFFERED | | 323 | 53 | | | | | | | . | | |
| 7 | JOIN FILTER CREATE | :BF0000 | 323 | 31 | | | | | | | . | | |
| 8 | HASH JOIN OUTER | | 323 | 31 | | | | | | | . | | |
| 9 | JOIN FILTER CREATE | :BF0001 | 323 | 20 | | | | | | | . | | |
| 10 | PX RECEIVE | | 323 | 20 | | | | | | | . | | |
| 11 | PX SEND HASH | :TQ20010 | 323 | 20 | | | | | | | . | | |
| 12 | HASH JOIN BUFFERED | | 323 | 20 | | | | | | | . | | |
| 13 | BUFFER SORT | | | | | | | | | | . | | |
| 14 | PX RECEIVE | | 87 | | | | | | | | . | | |
| 15 | PX SEND HYBRID HASH | :TQ20001 | 87 | | | | | | | | . | | |
| 16 | STATISTICS COLLECTOR | | | | | | | | | | . | | |
| 17 | FIXED TABLE FULL | X$CON | 87 | | | | | | | | . | | |
| 18 | PX RECEIVE | | 325 | 20 | | | | | | | . | | |
| 19 | PX SEND HYBRID HASH | :TQ20007 | 325 | 20 | | | | | | | . | | |
| 20 | HASH JOIN OUTER BUFFERED | | 325 | 20 | | | | | | | . | | |
| 21 | PX RECEIVE | | 325 | 5 | | | | | | | . | | |
| 22 | PX SEND HASH (NULL RANDOM) | :TQ20005 | 325 | 5 | | | | | | | . | | |
| 23 | PX PARTITION LIST INLIST | | 325 | 5 | | | | | | | . | | |
| 24 | CONTAINERS FULL | DBA_TABLESPACES | 325 | 5 | | | | | | | . | | |
| 25 | PX RECEIVE | | 386 | 14 | | | | | | | . | | |
| 26 | PX SEND HASH | :TQ20006 | 386 | 14 | | | | | | | . | | |
| 27 | BUFFER SORT | | 323 | | | | | | | | . | | |
| 28 | VIEW | | 386 | 14 | | | | | | | . | | |
| 29 | UNION-ALL | | | | | | | | | | . | | |
| 30 | HASH GROUP BY | | 325 | 6 | | | | | | | . | | |
| 31 | PX RECEIVE | | 325 | 6 | | | | | | | . | | |
| 32 | PX SEND HASH | :TQ20003 | 325 | 6 | | | 10 | | | | . | | |
| 33 | HASH GROUP BY | | 325 | 6 | 131 | +2 | 10 | 0 | | | 45MB | | |
| 34 | PX PARTITION LIST INLIST | | 325 | 5 | 131 | +2 | 10 | 357K | | | . | | |
| -> 35 | CONTAINERS FULL | DBA_FREE_SPACE | 325 | 5 | 133 | +2 | 37 | 357K | | | . | | |
| 36 | HASH GROUP BY | | 61 | 8 | | | | | | | . | | |
| 37 | PX RECEIVE | | 61 | 8 | | | | | | | . | | |
| 38 | PX SEND HASH | :TQ20004 | 61 | 8 | 1 | +2 | 10 | 0 | | | . | | |
| 39 | HASH GROUP BY | | 61 | 8 | 1 | +2 | 10 | 10 | | | 31MB | | |
| 40 | HASH JOIN | | 61 | 7 | 1 | +2 | 10 | 67 | | | . | | |
| 41 | BUFFER SORT | | | | 1 | +2 | 10 | 68 | | | . | | |
| 42 | PX RECEIVE | | 18 | 1 | 1 | +2 | 10 | 68 | | | . | | |
| 43 | PX SEND HYBRID HASH | :TQ20000 | 18 | 1 | 1 | +2 | 1 | 98 | | | . | | |
| 44 | STATISTICS COLLECTOR | | | | 1 | +2 | 1 | 98 | | | . | | |
| 45 | PX COORDINATOR | | | | 1 | +2 | 9 | 98 | | | . | | |
| 46 | PX SEND QC (RANDOM) | :TQ10000 | 18 | 1 | 2 | +0 | 8 | 98 | | | . | | |
| 47 | VIEW | GV$SORT_SEGMENT | | | 2 | +0 | 8 | 98 | | | . | | |
| 48 | HASH JOIN | | 18 | 1 | 2 | +0 | 8 | 98 | | | . | | |
| 49 | FIXED TABLE FULL | X$KTSTSSD | 18 | | 2 | +0 | 8 | 98 | | | . | | |
| 50 | FIXED TABLE FULL | X$KCCTS | 4874 | 1 | 2 | +0 | 8 | 38992 | 40 | 16MB | . | | |
| 51 | PX RECEIVE | | 130K | 6 | 1 | +2 | 10 | 3677 | | | . | | |
| 52 | PX SEND HYBRID HASH | :TQ20002 | 130K | 6 | | | | | | | . | | |
| 53 | PX PARTITION LIST INLIST | | 130K | 6 | | | | | | | . | | |
| 54 | CONTAINERS FULL | DBA_TABLESPACES | 130K | 6 | | | | | | | . | | |
| 55 | PX RECEIVE | | 1300 | 12 | | | | | | | . | | |
| 56 | PX SEND HASH | :TQ20011 | 1300 | 12 | | | | | | | . | | |
| 57 | JOIN FILTER USE | :BF0001 | 1300 | 12 | | | | | | | . | | |
| 58 | PX PARTITION LIST INLIST | | 1300 | 12 | | | | | | | . | | |
| 59 | VIEW | | 1300 | 12 | | | | | | | . | | |
| 60 | HASH JOIN OUTER | | 1300 | 12 | | | | | | | . | | |
| 61 | CONTAINERS FULL | DBA_TABLESPACES | 1300 | 5 | | | | | | | . | | |
| 62 | CONTAINERS FULL | DBA_TABLESPACE_USAGE_METRICS | 130K | 6 | | | | | | | . | | |
| 63 | PX RECEIVE | | 260K | 21 | | | | | | | . | | |
| 64 | PX SEND HASH | :TQ20012 | 260K | 21 | | | | | | | . | | |
| 65 | JOIN FILTER USE | :BF0000 | 323 | | | | | | | | . | | |
| 66 | BUFFER SORT | | 323 | | | | | | | | . | | |
| 67 | VIEW | | 260K | 21 | | | | | | | . | | |
| 68 | UNION-ALL | | | | | | | | | | . | | |
| 69 | HASH GROUP BY | | 130K | 11 | | | | | | | . | | |
| 70 | PX RECEIVE | | 130K | 11 | | | | | | | . | | |
| 71 | PX SEND HASH | :TQ20008 | 130K | 11 | | | | | | | . | | |
| 72 | HASH GROUP BY | | 130K | 11 | | | | | | | . | | |
| 73 | PX PARTITION LIST INLIST | | 130K | 6 | | | | | | | . | | |
| 74 | CONTAINERS FULL | DBA_DATA_FILES | 130K | 6 | | | | | | | . | | |
| 75 | HASH GROUP BY | | 130K | 11 | | | | | | | . | | |
| 76 | PX RECEIVE | | 130K | 11 | | | | | | | . | | |
| 77 | PX SEND HASH | :TQ20009 | 130K | 11 | | | | | | | . | | |
| 78 | HASH GROUP BY | | 130K | 11 | | | | | | | . | | |
| 79 | PX PARTITION LIST INLIST | | 130K | 6 | | | | | | | . | | |
| 80 | CONTAINERS FULL | DBA_TEMP_FILES | 130K | 6 | | | | | | | . | | |
============================================================================================================================================================================================================
Ah, easy to spot that it works as a parallel query and most importantly, that it got stuck at the Plan ID 35 (see the -> 35) and “Time active (s)”.
Admittedly, the Operation column with CONTAINERS FULL should give me some hint to revert my efforts elsewhere, but at first, I was naively focused on that very SQL not even suspecting it got much deeper.
After spending far too long dissecting that statement, it became clear it had to reach beyond what was visible at first glance (and yes — it did dive down into the PDBs).
Perhaps the time invested was not entirely wasted as I also noticed that the parallel slaves (see the Instance Drill-Down section) were reading far more than you’d expect, given that the query appears to touch familiar dictionary views like DBA_FREE_SPACE, DBA_TABLESPACES, and the like.
If you take closer look you’ll notice that in some cases it’s been ~16GB read back – all just after 132 seconds.
I have decided to re-run the query, this time tracing the CBO decisions and set up the 10053 trace.
I have changed the SQL text to force hard parsing and re-executed it.
Short note –
you might use the dbms_sqldiag.dump_trace which would retrieve the 10053 trace as far as the associated cursor is still present in the shared pool :
begin
dbms_sqldiag.dump_trace(p_sql_id=>’&SQL_ID_to_dump’, p_child_number=>&child, p_component=>’Compiler’, p_file_id=>’&trace_file_mark’) ;
end;
/Anyway, I forced the hard parse by adding a comment /* ps01 */ and re-executed it :
SYS @ CDB> @tr10053
Enter value for file_identifier: OEMSQL
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='OEMSQL'
ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier';
alter session set events '10053 trace name context forever, level 1';
-- then I executed my query which originally came from OEM :
SYS @ CDB> @oemquery.sql I got CDB_ora_77029_OEMSQL.trc file being the true gold mine of the CBO’s decisions.
This time though I wasn’t interested in what you typically search for in that trace file.
What I was after is a single (typically very long) line which begins with: “Final query after transformations”.
This line needs to be copied as a whole and put into some SQL formatter.
You may try SQL*Developer – copy-paste that line into the SQL*Developer, mark it and press <Crtl+F7>.
SQLcl will typically also produce nice formatting, if raw_input.sql (even a long single line) conforms to the SQL syntax rules :# sql /nolog
format file </path/raw_input.sql> </path/your_target_formatted.sql>
Query Transformations and the “Why Isn’t My Hint Obeyed?” Problem
It may btw. be quite interesting to see what the optimizer does with the original shape of your SQL text.
In the vast majority of cases, WYSI-Not-WYG.
You may experiment with a relatively complex query which runs pretty fast and apply the hint : /*+ NO_QUERY_TRANSFORMATION */ at the top of your query (main query block), then run it.
Sometimes you’ll end up with a pleasantly long coffee break appreciating the work of the CBO architects (or not, if you really enjoy your coffee 😉).
Yet another pitfall.
Sometimes you’re applying hints (this especially pertains to hints put in subqueries) and then got frustrated by the fact that these hints got ignored.
The reason may be, you applied hints to query blocks which you think will still be present unchanged (say, “untransformed”), so will “survive” the query transformation.
Well, often they don’t and the formatted output following “Final query after transformations” in the trace file of event #10053 may be real surprise.
The result of query transformation and the phrase “FROM CONTAINERS”
Anyway, let’s come back to the main topic.
After formatting that long line you’ll see that the original text of the query got changed:
SELECT
"X$CON"."NAME" "NAME",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
nvl("T"."BYTES" / 1024 / 1024, 0) "ALLOCATED_SPACE",
nvl(DECODE(TO_CHAR("UN"."BYTES"), NULL, DECODE("K"."CONTENTS", 'TEMPORARY', DECODE("K"."EXTENT_MANAGEMENT", 'LOCAL', "U"."BYTES"
, "T"."BYTES" - nvl("U"."BYTES", 0)), "T"."BYTES" - nvl("U"."BYTES", 0)), "UN"."BYTES") / 1024 / 1024,0) "USED_SPACE"
FROM
CONTAINERS ( "SYS"."DBA_TABLESPACES" ) "K",
"SYS"."X$CON" "X$CON",
(
( SELECT
"K"."CON_ID" "CON_ID",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
SUM("K"."BYTES") "BYTES"
FROM CONTAINERS ( "SYS"."DBA_FREE_SPACE" ) "K"
WHERE
"K"."CON_ID" <>40 AND "K"."CON_ID" <>41
GROUP BY "K"."CON_ID", "K"."TABLESPACE_NAME"
)
UNION ALL
( SELECT
"K"."CON_ID" "CON_ID",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
nvl(SUM("S"."USED_BLOCKS" * "K"."BLOCK_SIZE"), 0) "NVL(SUM(S.USED_BLOCKS*TS.BLOCK_SIZE),0)"
FROM
"SYS"."GV$SORT_SEGMENT" "S",
CONTAINERS ( "SYS"."DBA_TABLESPACES" ) "K"
WHERE
"K"."TABLESPACE_NAME" = "S"."TABLESPACE_NAME"
AND "K"."CON_ID" = "S"."CON_ID"
AND "K"."CON_ID" <> 40
AND "K"."CON_ID" <> 41
AND "S"."CON_ID" <> 40
AND "S"."CON_ID" <> 41
GROUP BY
"K"."CON_ID",
"K"."TABLESPACE_NAME"
)
) "U",
(
( SELECT
"K"."CON_ID" "CON_ID",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
SUM(nvl("K"."BYTES", 0)) "BYTES"
FROM
CONTAINERS ( "SYS"."DBA_DATA_FILES" ) "K"
WHERE
"K"."CON_ID" <> 40
AND "K"."CON_ID" <> 41
GROUP BY
"K"."CON_ID",
"K"."TABLESPACE_NAME"
)
UNION ALL
( SELECT
"K"."CON_ID" "CON_ID",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
SUM(nvl("K"."BYTES", 0)) "BYTES"
FROM
CONTAINERS ( "SYS"."DBA_TEMP_FILES" ) "K"
WHERE
"K"."CON_ID" <> 40
AND "K"."CON_ID" <> 41
GROUP BY
"K"."CON_ID",
"K"."TABLESPACE_NAME"
)
) "T",
(
SELECT
"K"."CON_ID" "CON_ID",
"K"."TABLESPACE_NAME" "TABLESPACE_NAME",
nvl("K"."USED_SPACE" * "K"."BLOCK_SIZE", 0) "BYTES"
FROM
CONTAINERS ( "SYS"."DBA_TABLESPACES" ) "K",
CONTAINERS ( "SYS"."DBA_TABLESPACE_USAGE_METRICS" ) "K"
WHERE
"K"."TABLESPACE_NAME" = "K"."TABLESPACE_NAME" (+)
AND "K"."CON_ID" = "K"."CON_ID" (+)
AND "K"."CONTENTS" = 'UNDO'
AND "K"."CON_ID" <> 40
AND "K"."CON_ID" <> 41
AND "K"."CON_ID" (+) <> 40
AND "K"."CON_ID" (+) <> 41
) "UN"
WHERE
"K"."TABLESPACE_NAME" = "T"."TABLESPACE_NAME" (+)
AND "K"."TABLESPACE_NAME" = "U"."TABLESPACE_NAME" (+)
AND "K"."TABLESPACE_NAME" = "UN"."TABLESPACE_NAME" (+)
AND "K"."CON_ID" = "X$CON"."CON_ID"
AND "K"."CON_ID" = "U"."CON_ID" (+)
AND "K"."CON_ID" = "T"."CON_ID" (+)
AND "K"."CON_ID" = "UN"."CON_ID" (+)
AND "K"."CON_ID" <> 40
AND "K"."CON_ID" <> 41
AND "X$CON"."INST_ID" = userenv('INSTANCE')
AND "X$CON"."DTIME" = 0
AND "X$CON"."CON_ID" <> 40
AND "X$CON"."CON_ID" <> 41
ORDER BY
"X$CON"."NAME",
"K"."TABLESPACE_NAME"Especially eye-catching are the union’ed parts with the reference to the keyword CONTAINERS which look like : FROM CONTAINERS ()
For example : FROM CONTAINERS ( "SYS"."DBA_FREE_SPACE" ) or : FROM CONTAINERS ( "SYS"."DBA_TABLESPACES" )
or : FROM CONTAINERS ( "SYS"."DBA_TABLESPACE_USAGE_METRICS" )
So, sticking to one of them, the phrase “from cdb_free_space” has been replaced by “FROM CONTAINERS ( "SYS"."DBA_FREE_SPACE" )"
Short searching confirmed my thoughts that I am seeing just the peak of an iceberg. That saying, this clause “FROM CONTAINERS” spawns as many single threaded queries as there are PDBs (this explains the parallel query seen at the CDB level).
Each such query gets executed directly at the PDB level. Once completed, it sends back the result set to the CDB, which further aggregates delivered chunks (what parallel query, and precisely: query coordinator, usually does).
For further reading continue to: When OEM meets the CBO: cardinality chaos across containers – Part 2
Leave a Reply