Re: question on table access by index rowid batched
- From: Pap <oracle.developer35@xxxxxxxxx>
- To: Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- Date: Fri, 8 Oct 2021 23:04:27 +0530
It's fluctuating. I am now not able to reproduce the scenario for that
same small query for which I had posted here just before. And you are
correct , I was luckily having sql monitors saved. I am attaching those
here. If you see that, the main query was showing those rowid batched
operations when we have the optimizer_adaptive_reporting_only set as
default/false and was taking a long time to finish and also the first few
rows were also taking longer to get produced out of the query. But the
sample small query which i had posted a cursor plan for was just showing
the opposite behaviour. That is going for a rowid batched path when
optimizer_adaptive_reporting_only sets as true.
However, now I am seeing that same small query in both the cases
(irrespective of value of optimizer_adaptive_reporting_only) going for
'rowid batched' execution path. Not sure if it's just stats or
anything else influencing and I am seeing different things behaviour. Just
to note we have 'optimizer_adaptive_plans' set to true ,
'optimizer_adaptive_statistics' set to false. The only change we made was
moving ' optimizer_adaptive_reporting_only' from false to true.
And Jonathan when you said the optimizer_adaptive_reporting_only = true
will introduce 'statistics collector' operation, but if you see the
attached sql monitor for the main query, i am seeing 'statistics collector'
even when optimizer_adaptive_reporting_only is = false. Is that expected
behaviour?
On Fri, Oct 8, 2021 at 7:08 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
THere is a bit of confusion or I didn`t understood properly.
You mentioned initially that optimizer_adaptive_reporting_only was set to
TRUE and you could see no batching was used.
Then in your example you are making optimizer_adaptive_reporting_only to
FALSE and we can see no batching is used
În vin., 8 oct. 2021 la 13:43, Jonathan Lewis <jlewisoracle@xxxxxxxxx> a
scris:
What do the two plans look like if you use the 'adaptive' option for the
format. ('Advanced' doesn't show you everything).
I can't reproduce the effect - but that's partly because there's not
enough information available, and maybe because I'm on 19.11, or maybe
because I've got adaptive_plans enabled anyway.
Can you create a complete reproducible example - viz: create table,
create indexes, then the two calls to explain that show the difference.
Format=>'outline projection adaptive' should be sufficient.
There is a POSSIBLE argument why the plans should operate the table
access differently as the adaptive reporting is switched on and off.
When adaptive reporting is in place then adaptive plans get "statistics
collector" operations which accumulate and count data for a while
(typically to see if the plan should swtich between NLJ and HJ). This means
that there's a line in the plan which is "blocking" and there are some
operations that Oracle will not choose to perform unless there's a blocking
operation further up the plan - so the choice between batching and not
batching may depend on the presence or absence of the statistics
collector. (And it's POSSIBLE that in this particular example it's
happening because of a simple code reuse principle rather than because of
an explicit decision by a programmer that it's appropriate here.)
Regards
Jonathan Lewis
On Fri, 8 Oct 2021 at 07:05, Pap <oracle.developer35@xxxxxxxxx> wrote:
Able to replicate the behavior with a small query as below . Its(rowid
batching) is really changing with the setting of
optimizer_adaptive_reporting_only parameter. But still unable to figure out
how?
As per Oracle doc , "*With this setting, the information required for
an adaptive optimization is gathered, but no action is taken to change the
plan*", so why is it influencing the plan in our case? The default
value of the parameter is false, so is it advisable to turn it to TRUE and
its not having any negative impact?
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_ADAPTIVE_REPORTING_ONLY.html#GUID-8DD128F9-4891-4061-9B2D-9D45315D44FB
************ Test case*************
alter session set optimizer_adaptive_reporting_only=true;
explain plan for
SELECT TRIM ( SUBSTR (descr, 1, INSTR (descr, ',') - 1)) AS msi,
TRIM ( SUBSTR (descr, INSTR (descr, ',') + 1)) AS msv
FROM USER1.tab1
WHERE name = 'XXXX';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
Plan hash value: 3274520851
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 |
615 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| tab1 | 15 |
615 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | tab1_pk | 15 |
| 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / tab1@SEL$1
2 - SEL$1 / tab1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "tab1"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "tab1"@"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='XXXX')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "descr"[VARCHAR2,60]
2 - "tab1".ROWID[ROWID,10]
Query Block Registry:
---------------------
<q o="2"
f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[tab1]]></t><s><![CDATA[SEL$1]]
></s></h></f></q>
*************************** Setting to False******************
alter session set optimizer_adaptive_reporting_only=false;
explain plan for
SELECT TRIM ( SUBSTR (descr, 1, INSTR (descr, ',') - 1)) AS msi,
TRIM ( SUBSTR (descr, INSTR (descr, ',') + 1)) AS msv
FROM USER1.tab1
WHERE name = 'XXXX';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
-------------------------------------------------------------------------------------------------------------
Plan hash value: 2347410815
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 615 |
3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| tab1 | 15 | 615 |
3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | tab1_pk | 15 | |
1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / tab1@SEL$1
2 - SEL$1 / tab1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "tab1"@"SEL$1" ("tab1"."NAME" "tab1"."CODE"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='XXXX')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "descr"[VARCHAR2,60]
2 - "tab1".ROWID[ROWID,10]
Query Block Registry:
---------------------
<q o="2"
f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[tab1]]></t><s><![CDATA
[SEL$1]]></s></h></f></q>
On Fri, Oct 8, 2021 at 1:04 AM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, we have one customer database on version 19.9. One of
the search queries which was running with first_rows optimizer mode was
running for ~10+minutes but giving the first few rows after ~2.5minutes.
But suddenly we see the same query is finishing in ~4minutes and
it's giving the first few rows almost instantly. We want to know the
reason.
Looking into the execution path , we found that the execution path for
the slow one, was having 'table access by index rowid batched' in many of
its access paths throughout the plan whereas the fast execution path does
not have any, it was simple 'table access by index rowid'. The outlines
section for the fast execution also shows hints as
opt_param('optimizer_batch_table_access_by_rowid','false').
I have three questions:
1) If there is a known performance issue associated with the new 'rowid
batching' optimization feature(in 19.9 specifically) in conjunction with
first _rows mode and any workaround for that exists?
2) We were trying to understand what caused this feature change. and
as per the team the only change done was
'optimizer_adaptive_reporting_only' has been changed to TRUE, so can this
be anyway related to the 'rowid batching' feature being turned off in this
query? How can we get the cause?
3)Is there any downside(bad impact on performance) of setting
'optimizer_adaptive_reporting_only' to true?
Regards
Pap
******************** Plan when optimizer_adaptive_reporting_only was
FALSE******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
Session : XXXXX (509:27860)
SQL ID : 8t19y7v5j9ztg
SQL Execution ID : 33554432
Execution Started : 10/07/2021 07:56:09
First Refresh Time : 10/07/2021 07:56:09
Last Refresh Time : 10/07/2021 08:07:17
Duration : 668s
Module/Action : SQL*Plus/-
Service : XXXXX.XXXXX.com
Program : sqlplus.exe
Fetch Calls : 370
Global Stats
==========================================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read
| Read | Write | Write | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs
| Bytes | Reqs | Bytes | Returned Bytes |
==========================================================================================================================
| 705 | 280 | 270 | 0.00 | 155 | 370 | 40M | 984K
| 11GB | 6422 | 3GB | 6GB |
==========================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3015036808)
====================================================================================================================================================================================================================================
| Id | Operation | Name
| Rows | Cost | Time | Start | Execs | Rows | Read |
Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | |
| (Estim) | | Active(s) | Active | | (Actual) | Reqs |
Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
====================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT |
| | | 512 | +157 | 1 | 2M | |
| | | . | . | | |
| 1 | FILTER |
| | | 512 | +157 | 1 | 2M | |
| | | . | . | | |
| 2 | NESTED LOOPS OUTER |
| 1 | 3M | 512 | +157 | 1 | 2M | |
| | | . | . | | |
| 3 | NESTED LOOPS OUTER |
| 1 | 3M | 512 | +157 | 1 | 2M | |
| | | . | . | | |
| 4 | HASH JOIN OUTER |
| 1 | 3M | 538 | +131 | 1 | 2M | 3387 |
2GB | 3387 | 2GB | 450MB | 2GB | | |
| 5 | NESTED LOOPS OUTER |
| 1 | 3M | 27 | +131 | 1 | 2M | |
| | | . | . | | |
| 6 | STATISTICS COLLECTOR |
| | | 27 | +131 | 1 | 2M | |
| | | . | . | | |
| 7 | NESTED LOOPS OUTER |
| 1 | 3M | 27 | +131 | 1 | 2M | |
| | | . | . | | |
| 8 | HASH JOIN OUTER |
| 1 | 3M | 155 | +3 | 1 | 2M | 3035 |
1GB | 3035 | 1GB | 309MB | 1GB | | |
| 9 | NESTED LOOPS OUTER |
| 1 | 3M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 10 | STATISTICS COLLECTOR |
| | | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 11 | NESTED LOOPS OUTER |
| 1 | 3M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 12 | NESTED LOOPS OUTER |
| 1 | 3M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 13 | NESTED LOOPS |
| 272K | 2M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 14 | NESTED LOOPS OUTER |
| 272K | 2M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 15 | NESTED LOOPS |
| 272K | 2M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 16 | NESTED LOOPS OUTER |
| 272K | 1M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 17 | NESTED LOOPS |
| 272K | 1M | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 18 | FILTER |
| | | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 19 | NESTED LOOPS OUTER |
| 272K | 598K | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 20 | VIEW | index$_join$_006
| 276K | 48299 | 129 | +3 | 1 | 2M | |
| | | . | . | | |
| 21 | HASH JOIN |
| | | 129 | +3 | 1 | 2M | |
| | | 132MB | . | | |
| 22 | HASH JOIN |
| | | 3 | +1 | 1 | 2M | |
| | | 124MB | . | | |
| 23 | INDEX STORAGE FAST FULL SCAN | TET_IX2
| 276K | 8505 | 1 | +1 | 1 | 2M | 129 |
54MB | | | . | . | | |
| 24 | INDEX STORAGE FAST FULL SCAN | TET_IX4
| 276K | 13077 | 3 | +1 | 1 | 2M | 167 |
81MB | | | . | . | | |
| 25 | INDEX STORAGE FAST FULL SCAN | TET_PK
| 276K | 11889 | 129 | +3 | 1 | 2M | 198 |
61MB | | | . | . | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TT
| 1 | 2 | 129 | +3 | 2M | 2M | 1488 |
12MB | | | . | . | | |
| 27 | INDEX UNIQUE SCAN | TT_PK
| 1 | 1 | 129 | +3 | 2M | 2M | 7 |
57344 | | | . | . | | |
| 28 | TABLE ACCESS BY INDEX ROWID | TM
| 1 | 2 | 129 | +3 | 2M | 2M | 9875 |
77MB | | | . | . | | |
| 29 | INDEX UNIQUE SCAN | TM_PK
| 1 | 1 | 129 | +3 | 2M | 2M | 1235 |
10MB | | | . | . | | |
| 30 | TABLE ACCESS BY INDEX ROWID | TU
| 1 | 1 | 119 | +11 | 2M | 17764 | |
| | | . | . | | |
| 31 | INDEX UNIQUE SCAN | TU_PK
| 1 | | 119 | +11 | 2M | 17764 | |
| | | . | . | | |
| 32 | TABLE ACCESS BY INDEX ROWID | TEP
| 1 | 2 | 129 | +3 | 2M | 2M | 140K |
1GB | | | . | . | | |
| 33 | INDEX UNIQUE SCAN | TEP_PK
| 1 | 1 | 129 | +3 | 2M | 2M | 1478 |
12MB | | | . | . | | |
| 34 | TABLE ACCESS BY INDEX ROWID | TLIM
| 1 | 1 | 129 | +3 | 2M | 2M | |
| | | . | . | | |
| 35 | INDEX UNIQUE SCAN | TLIM_PK
| 1 | | 129 | +3 | 2M | 2M | |
| | | . | . | | |
| 36 | TABLE ACCESS BY INDEX ROWID | TLPSE
| 1 | 1 | 129 | +3 | 2M | 2M | |
| | | . | . | | |
| 37 | INDEX UNIQUE SCAN | TLPSE_PK
| 1 | | 129 | +3 | 2M | 2M | |
| | | . | . | | |
| 38 | INDEX RANGE SCAN | TCX_IX2
| 1 | 2 | 129 | +3 | 2M | 2M | 4642 |
36MB | | | . | . | | |
| 39 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 129 | +3 | 2M | 2M | 22307 |
174MB | | | . | . | | |
| 40 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 129 | +3 | 2M | 2M | 546 |
4MB | | | . | . | | |
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | | | |
| | | . | . | | |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 1 | +131 | 1 | 976K | |
| | | . | . | | |
| 43 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 27 | +131 | 2M | 2M | 21549 |
168MB | | | . | . | | |
| 44 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 27 | +131 | 2M | 2M | 959 |
7MB | | | . | . | | |
| 45 | TABLE ACCESS BY INDEX ROWID BATCHED | TP
| 1 | 3 | | | | | |
| | | . | . | | |
| 46 | INDEX RANGE SCAN | TP_PK
| 15 | 1 | | | | | |
| | | . | . | | |
| 47 | TABLE ACCESS BY INDEX ROWID BATCHED | TP
| 1 | 3 | 36 | +157 | 1 | 15 | |
| | | . | . | | |
| 48 | INDEX RANGE SCAN | TP_PK
| 15 | 1 | 36 | +157 | 1 | 15 | |
| | | . | . | | |
| 49 | TABLE ACCESS STORAGE FULL FIRST ROWS | TLIET
| 1 | 3 | 512 | +157 | 2M | 2M | |
| | | . | . | | |
| 50 | VIEW PUSHED PREDICATE | TEB_VW
| 1 | 57 | 506 | +163 | 2M | 1459 | |
| | | . | . | | |
| 51 | NESTED LOOPS OUTER |
| 1 | 57 | 506 | +163 | 2M | 1459 | |
| | | . | . | | |
| 52 | NESTED LOOPS |
| 1 | 55 | 506 | +163 | 2M | 1459 | |
| | | . | . | | |
| 53 | NESTED LOOPS |
| 1 | 53 | 506 | +163 | 2M | 1459 | |
| | | . | . | | |
| 54 | NESTED LOOPS |
| 1 | 51 | 506 | +163 | 2M | 1459 | |
| | | . | . | | |
| 55 | NESTED LOOPS |
| 5 | 41 | 510 | +159 | 2M | 6965 | |
| | | . | . | | |
| 56 | NESTED LOOPS |
| 1 | 7 | 510 | +159 | 2M | 770K | |
| | | . | . | | |
| 57 | NESTED LOOPS |
| 1 | 4 | 510 | +159 | 2M | 770K | |
| | | . | . | | |
| 58 | NESTED LOOPS |
| 1 | 3 | 510 | +159 | 2M | 770K | |
| | | . | . | | |
| 59 | TABLE ACCESS BY INDEX ROWID | TEP
| 1 | 3 | 512 | +157 | 2M | 770K | 661K |
5GB | | | . | . | | |
| 60 | INDEX UNIQUE SCAN | TEP_PK
| 1 | 2 | 512 | +157 | 2M | 2M | 2934 |
23MB | | | . | . | | |
| 61 | INDEX RANGE SCAN | TLP_IX1
| 1 | | 510 | +159 | 770K | 770K | |
| | | . | . | | |
| 62 | VIEW |
| 1 | 1 | 510 | +159 | 770K | 770K | |
| | | . | . | | |
| 63 | SORT AGGREGATE |
| 1 | | 510 | +159 | 770K | 770K | |
| | | . | . | | |
| 64 | TABLE ACCESS BY INDEX ROWID | TPR
| 1 | 1 | 510 | +159 | 770K | 770K | |
| | | . | . | | |
| 65 | INDEX UNIQUE SCAN | TPR_PK
| 1 | | 510 | +159 | 770K | 770K | |
| | | . | . | | |
| 66 | TABLE ACCESS BY INDEX ROWID BATCHED | TET
| 1 | 3 | 511 | +158 | 770K | 770K | 79759 |
623MB | | | . | . | | |
| 67 | INDEX RANGE SCAN | TET_Ix1
| 1 | 2 | 510 | +159 | 770K | 899K | 15834 |
124MB | | | . | . | | |
| 68 | TABLE ACCESS BY INDEX ROWID BATCHED | TWE
| 5 | 34 | 506 | +163 | 770K | 6965 | 2080 |
16MB | | | . | . | | |
| 69 | INDEX RANGE SCAN | TWE_IDX1
| 35 | 2 | 506 | +163 | 770K | 6965 | 118 |
944KB | | | . | . | | |
| 70 | TABLE ACCESS BY INDEX ROWID | TT
| 1 | 2 | 506 | +163 | 6965 | 1459 | 208 |
2MB | | | . | . | | |
| 71 | INDEX UNIQUE SCAN | TT_PK
| 1 | 1 | 506 | +163 | 6965 | 6965 | |
| | | . | . | | |
| 72 | INDEX RANGE SCAN | TCX_IX2
| 1 | 2 | 506 | +163 | 1459 | 1459 | 1388 |
11MB | | | . | . | | |
| 73 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 506 | +163 | 1459 | 1459 | 936 |
7MB | | | . | . | | |
| 74 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 506 | +163 | 1459 | 1459 | 75 |
600KB | | | . | . | | |
| 75 | TABLE ACCESS BY INDEX ROWID BATCHED | TLS
| 1 | 2 | 506 | +163 | 1459 | 1451 | 1 |
8192 | | | . | . | | |
| 76 | INDEX SKIP SCAN | TLS_PK
| 1 | 1 | 506 | +163 | 1459 | 1451 | 1 |
8192 | | | . | . | | |
| 77 | SORT AGGREGATE |
| 1 | | 512 | +157 | 2M | 2M | |
| | | . | . | | |
| 78 | FIRST ROW |
| 1 | 3 | 512 | +157 | 2M | 2M | |
| | | . | . | | |
| 79 | INDEX RANGE SCAN (MIN/MAX) | TCX_IX2
| 1 | 3 | 512 | +157 | 2M | 2M | 9356 |
73MB | | | . | . | | |
====================================================================================================================================================================================================================================
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - first_rows / hint overridden by another in parent query block
- first_rows
56 - SEL$5
- no_merge
Note
-----
- this is an adaptive plan
******************** Plan after setting optimizer_adaptive_reporting_only to
TRUE******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
Session : XXXXX (510:5394)
SQL ID : 791qwn38bq6gv
SQL Execution ID : 33554432
Execution Started : 10/07/2021 11:46:56
First Refresh Time : 10/07/2021 11:46:56
Last Refresh Time : 10/07/2021 11:51:36
Duration : 280s
Module/Action : SQL*Plus/-
Service : XXXXX.XXXXX.com
Program : sqlplus.exe
Fetch Calls : 370
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 252 | 170 | 71 | 11 | 370 | 39M | 251K | 2GB |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=250668601)
============================================================================================================================================================================================================
| Id | Operation | Name
| Rows | Cost | Time | Start | Execs | Rows | Read |
Read | Mem | Activity | Activity Detail |
| | |
| (Estim) | | Active(s) | Active | | (Actual) | Reqs |
Bytes | (Max) | (%) | (# samples) |
============================================================================================================================================================================================================
| 0 | SELECT STATEMENT |
| | | 279 | +2 | 1 | 2M | |
| . | | |
| 1 | FILTER |
| | | 279 | +2 | 1 | 2M | |
| . | | |
| 2 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 3 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 4 | HASH JOIN OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 5 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 6 | STATISTICS COLLECTOR |
| | | 279 | +2 | 1 | 2M | |
| . | | |
| 7 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 8 | HASH JOIN OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 9 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 10 | STATISTICS COLLECTOR |
| | | 279 | +2 | 1 | 2M | |
| . | | |
| 11 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 12 | NESTED LOOPS OUTER |
| 1 | 3M | 279 | +2 | 1 | 2M | |
| . | | |
| 13 | NESTED LOOPS |
| 272K | 2M | 279 | +2 | 1 | 2M | |
| . | | |
| 14 | NESTED LOOPS OUTER |
| 272K | 2M | 279 | +2 | 1 | 2M | |
| . | | |
| 15 | NESTED LOOPS |
| 272K | 2M | 279 | +2 | 1 | 2M | |
| . | | |
| 16 | NESTED LOOPS OUTER |
| 272K | 1M | 279 | +2 | 1 | 2M | |
| . | | |
| 17 | NESTED LOOPS |
| 272K | 1M | 279 | +2 | 1 | 2M | |
| . | | |
| 18 | FILTER |
| | | 279 | +2 | 1 | 2M | |
| . | | |
| 19 | NESTED LOOPS OUTER |
| 272K | 598K | 279 | +2 | 1 | 2M | |
| . | | |
| 20 | VIEW | index$_join$_006
| 276K | 48299 | 279 | +2 | 1 | 2M | |
| . | | |
| 21 | HASH JOIN |
| | | 279 | +2 | 1 | 2M | |
| 132MB | | |
| 22 | HASH JOIN |
| | | 2 | +1 | 1 | 2M | |
| 124MB | | |
| 23 | INDEX STORAGE FAST FULL SCAN | TET_IX2
| 276K | 8505 | 1 | +2 | 1 | 2M | |
| . | | |
| 24 | INDEX STORAGE FAST FULL SCAN | TET_IX4
| 276K | 13077 | 1 | +2 | 1 | 2M | |
| . | | |
| 25 | INDEX STORAGE FAST FULL SCAN | TET_PK
| 276K | 11889 | 279 | +2 | 1 | 2M | 149 |
62MB | . | | |
| 26 | TABLE ACCESS BY INDEX ROWID | TT
| 1 | 2 | 279 | +2 | 2M | 2M | 2347 |
18MB | . | | |
| 27 | INDEX UNIQUE SCAN | TT_PK
| 1 | 1 | 279 | +2 | 2M | 2M | 11 |
90112 | . | | |
| 28 | TABLE ACCESS BY INDEX ROWID | TM
| 1 | 2 | 279 | +2 | 2M | 2M | 12476 |
97MB | . | | |
| 29 | INDEX UNIQUE SCAN | TM_PK
| 1 | 1 | 279 | +2 | 2M | 2M | 1683 |
13MB | . | | |
| 30 | TABLE ACCESS BY INDEX ROWID | TU
| 1 | 1 | 257 | +21 | 2M | 17764 | 137 |
1MB | . | | |
| 31 | INDEX UNIQUE SCAN | TU_PK
| 1 | | 257 | +21 | 2M | 17764 | 1 |
8192 | . | | |
| 32 | TABLE ACCESS BY INDEX ROWID | TEP
| 1 | 2 | 279 | +2 | 2M | 2M | 155K |
1GB | . | | |
| 33 | INDEX UNIQUE SCAN | TEP_PK
| 1 | 1 | 279 | +2 | 2M | 2M | 1729 |
14MB | . | | |
| 34 | TABLE ACCESS BY INDEX ROWID | TLIM
| 1 | 1 | 279 | +2 | 2M | 2M | |
| . | | |
| 35 | INDEX UNIQUE SCAN | TLIM_PK
| 1 | | 279 | +2 | 2M | 2M | |
| . | | |
| 36 | TABLE ACCESS BY INDEX ROWID | TLPSE
| 1 | 1 | 279 | +2 | 2M | 2M | |
| . | | |
| 37 | INDEX UNIQUE SCAN | TLPSE_PK
| 1 | | 279 | +2 | 2M | 2M | |
| . | | |
| 38 | INDEX RANGE SCAN | TCX_IX2
| 1 | 2 | 279 | +2 | 2M | 2M | 8870 |
69MB | . | | |
| 39 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 279 | +2 | 2M | 2M | 14648 |
114MB | . | | |
| 40 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 279 | +2 | 2M | 2M | 157 |
1MB | . | | |
| 41 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | 279 | +2 | 2M | 2M | |
| . | | |
| 42 | INDEX RANGE SCAN | TCX_PK
| 1 | 2 | | | | | |
| . | | |
| 43 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 279 | +2 | 2M | 2M | 16037 |
125MB | . | | |
| 44 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 279 | +2 | 2M | 2M | 224 |
2MB | . | | |
| 45 | TABLE ACCESS BY INDEX ROWID | TP
| 1 | 3 | 279 | +2 | 2M | 2M | |
| . | | |
| 46 | INDEX RANGE SCAN | TP_PK
| 15 | 1 | 279 | +2 | 2M | 28M | |
| . | | |
| 47 | TABLE ACCESS BY INDEX ROWID | TP
| 1 | 3 | | | | | |
| . | | |
| 48 | INDEX RANGE SCAN | TP_PK
| 15 | 1 | | | | | |
| . | | |
| 49 | TABLE ACCESS STORAGE FULL FIRST ROWS | TLIET
| 1 | 3 | 279 | +2 | 2M | 2M | |
| . | | |
| 50 | VIEW PUSHED PREDICATE | TEB_VW
| 1 | 57 | 256 | +24 | 2M | 1459 | |
| . | | |
| 51 | NESTED LOOPS OUTER |
| 1 | 57 | 272 | +8 | 2M | 1459 | |
| . | | |
| 52 | NESTED LOOPS |
| 1 | 55 | 256 | +24 | 2M | 1459 | |
| . | | |
| 53 | NESTED LOOPS |
| 1 | 53 | 256 | +24 | 2M | 1459 | |
| . | | |
| 54 | NESTED LOOPS |
| 1 | 51 | 272 | +9 | 2M | 1459 | |
| . | | |
| 55 | NESTED LOOPS |
| 5 | 41 | 279 | +2 | 2M | 6965 | |
| . | | |
| 56 | NESTED LOOPS |
| 1 | 7 | 279 | +2 | 2M | 770K | |
| . | | |
| 57 | NESTED LOOPS |
| 1 | 4 | 279 | +2 | 2M | 770K | |
| . | | |
| 58 | NESTED LOOPS |
| 1 | 3 | 279 | +2 | 2M | 770K | |
| . | | |
| 59 | TABLE ACCESS BY INDEX ROWID | TEP
| 1 | 3 | 279 | +2 | 2M | 770K | |
| . | | |
| 60 | INDEX UNIQUE SCAN | TEP_PK
| 1 | 2 | 279 | +2 | 2M | 2M | |
| . | | |
| 61 | INDEX RANGE SCAN | TLP_IX1
| 1 | | 279 | +2 | 770K | 770K | |
| . | | |
| 62 | VIEW |
| 1 | 1 | 279 | +2 | 770K | 770K | |
| . | | |
| 63 | SORT AGGREGATE |
| 1 | | 279 | +2 | 770K | 770K | |
| . | | |
| 64 | TABLE ACCESS BY INDEX ROWID | TPR
| 1 | 1 | 279 | +2 | 770K | 770K | |
| . | | |
| 65 | INDEX UNIQUE SCAN | TPR_PK
| 1 | | 279 | +2 | 770K | 770K | |
| . | | |
| 66 | TABLE ACCESS BY INDEX ROWID | TET
| 1 | 3 | 279 | +2 | 770K | 770K | 28892 |
226MB | . | | |
| 67 | INDEX RANGE SCAN | TET_Ix1
| 1 | 2 | 279 | +2 | 770K | 899K | 6957 |
54MB | . | | |
| 68 | TABLE ACCESS BY INDEX ROWID | TWE
| 5 | 34 | 272 | +9 | 770K | 6965 | 890 |
7MB | . | | |
| 69 | INDEX RANGE SCAN | TWE_IDX1
| 35 | 2 | 272 | +9 | 770K | 6965 | 22 |
176KB | . | | |
| 70 | TABLE ACCESS BY INDEX ROWID | TT
| 1 | 2 | 272 | +9 | 6965 | 1459 | |
| . | | |
| 71 | INDEX UNIQUE SCAN | TT_PK
| 1 | 1 | 272 | +9 | 6965 | 6965 | |
| . | | |
| 72 | INDEX RANGE SCAN | TCX_IX2
| 1 | 2 | 256 | +24 | 1459 | 1459 | 932 |
7MB | . | | |
| 73 | TABLE ACCESS BY INDEX ROWID | TC
| 1 | 2 | 256 | +24 | 1459 | 1459 | |
| . | | |
| 74 | INDEX UNIQUE SCAN | TC_PK
| 1 | 1 | 256 | +24 | 1459 | 1459 | |
| . | | |
| 75 | TABLE ACCESS BY INDEX ROWID | TLS
| 1 | 2 | 256 | +24 | 1459 | 1451 | |
| . | | |
| 76 | INDEX SKIP SCAN | TLS_PK
| 1 | 1 | 256 | +24 | 1459 | 1451 | |
| . | | |
| 77 | SORT AGGREGATE |
| 1 | | 279 | +2 | 2M | 2M | |
| . | | |
| 78 | FIRST ROW |
| 1 | 3 | 279 | +2 | 2M | 2M | |
| . | | |
| 79 | INDEX RANGE SCAN (MIN/MAX) | TCX_IX2
| 1 | 3 | 279 | +2 | 2M | 2M | |
| . | | |
============================================================================================================================================================================================================
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - first_rows / hint overridden by another in parent query block
- first_rows
56 - SEL$5
- no_merge
Note
-----
- this is an adaptive plan
Other related posts:
