After this rather long discussion of what led the optimizer to choose one index over another — and, more importantly, of the significant consequences this choice brought — let us return to my test query.
Bad news though – we are still not done with the optimizer’s internal decision-making, so this means diving yet again into the internals of 10053-trace file.
Side note.
if you are looking patiently at the outcome of SQL Monitor below, you’ll notice that in my test below this number of lookups is 32247 (not 13378 as brought to you in the previous part – part2).
This is because, upon intercepting of the bind variables I needed ~15 minutes to prepare the test query, etc. and finally ran it at 15:30.
In the meantime though, there was apparently a wave of DELETEs and INSERTs which even further shifted the min and max (more importantly: max value), increasing the gap (admittedly, the values of the bind variables followed, but much more lazily)
Obviously, CBO still had no clue about it.
I prepared the SQL statement based on the intercepted predicates as shown above and executed it, intercepting optimizer’s decisions to the trace file (tr10053.sql).
It is important to remember that the both red-marked values were deliberately chosen to be located outside of the min-max boundaries for both HIST_IDs, which is exactly what was happening in the reality.
As also shown in the previous part of this series (part2) this drove the optimizer to calculate the cardinality of the both most important tables to 1.
As a reminder (we’ll see it soon again), it’s clearly stated in the 10053 trace file saying : Using prorated density: <density> of col #<number> as selectivity of out-of-range/non-existent value pred
# cat testq.sql
SELECT /*+ monitor */
*
FROM
VERYSECOWNR.pr_secrtb_secrtpos_info inf,
VERYSECOWNR.pr_secrtb_secrtpos_value val,
VERYSECOWNR.pr_secrtb_my_versec sta,
VERYSECOWNR.pr_secrtb_det1 uni,
VERYSECOWNR.pr_rverysecr res,
VERYSECOWNR.pr_realsec dev,
VERYSECOWNR.pr_reallysec loc
WHERE
inf.hist_id = val.hist_secret_id (+)
AND inf.myversecret_column = sta.secrid (+)
AND inf.sensor_unit_autoid = uni.secrid
AND inf.secretlo = loc.secrid (+)
AND uni.secdev = dev.secrid
AND dev.mysecret_ref = res.secrid (+)
AND inf.hist_id >= 285970450
AND val.hist_id (+) >= 588592848
order by inf.hist_id , inf.verysecret_timstmp
FETCH NEXT 50000 ROWS WITH TIES
# cat tr10053.sql
set timing on
ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier';
alter session set events '10053 trace name context forever, level 1';
SQL> @tr10053.sql
SYS> @testq.sql
50004 rows selected.
SQL_ID: gc2g2s5dvtqxu
Elapsed: 00:08:09.62
Here is the corresponding SQL Plan and the outcome from SQL Monitor :
This time we will focus on Plan Id = 7 (NESTED LOOP)
Execution Plan
----------------------------------------------------------
Plan hash value: 364305447
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1652 | 12 (9)| 00:00:01 |
|* 1 | VIEW | | 1 | 1652 | 12 (9)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK | | 1 | 251 | 12 (9)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 251 | 11 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 222 | 10 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 201 | 9 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 187 | 8 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 174 | 7 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| PR_SECRTB_SECRTPOS_INFO | 1 | 107 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_INFO_PK | 1 | | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| PR_SECRTB_SECRTPOS_VALUE | 1 | 67 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_VALUE_PK | 1 | | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PR_REALLYSEC | 1 | 13 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_RASEC_SECRID | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PR_SECRTB_DET1 | 1 | 14 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_SECRTBDET1_SECRID | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | PR_REALSEC | 1 | 21 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_IREALSEC_SECRID | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PR_SECRTB_MY_VERSEC | 1 | 29 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_SECRTBMY_SECRID | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
SQL Monitor (several lines removed from the “Global Information” section) :
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (2456:5151)
SQL ID : gc2g2s5dvtqxu
SQL Execution ID : 16777216
Execution Started : 03/11/2026 15:30:09
First Refresh Time : 03/11/2026 15:30:09
Last Refresh Time : 03/11/2026 15:38:19
Duration : 490s
Global Stats
============================================================
| Elapsed | Cpu | Cluster | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets |
============================================================
| 489 | 486 | 0.00 | 3.71 | 3335 | 101M |
============================================================
SQL Plan Monitoring Details (Plan Hash Value=364305447)
=================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +490 | 1 | 50004 | . | | |
| 1 | VIEW | | 1 | 12 | 1 | +490 | 1 | 50004 | . | | |
| 2 | WINDOW SORT PUSHED RANK | | 1 | 12 | 489 | +2 | 1 | 71689 | 26MB | | |
| 3 | NESTED LOOPS OUTER | | 1 | 11 | 489 | +2 | 1 | 71689 | . | | |
| 4 | NESTED LOOPS | | 1 | 10 | 489 | +2 | 1 | 71689 | . | | |
| 5 | NESTED LOOPS | | 1 | 9 | 489 | +2 | 1 | 71689 | . | | |
| 6 | NESTED LOOPS OUTER | | 1 | 8 | 489 | +2 | 1 | 71689 | . | | |
| 7 | NESTED LOOPS OUTER | | 1 | 7 | 489 | +2 | 1 | 71689 | . | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | PR_SECRTB_SECRTPOS_INFO | 1 | 4 | 489 | +2 | 1 | 32247 | . | | |
| 9 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_INFO_PK | 1 | 3 | 489 | +2 | 1 | 32247 | . | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | PR_SECRTB_SECRTPOS_VALUE | 1 | 3 | 489 | +2 | 32247 | 67143 | . | 90.79 | Cpu (434) |
| 11 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_VALUE_PK | 1 | 2 | 489 | +2 | 32247 | 2G | . | 8.99 | Cpu (43) |
| 12 | TABLE ACCESS BY INDEX ROWID | PR_REALLYSEC | 1 | 1 | 489 | +2 | 71689 | 71663 | . | | |
| 13 | INDEX UNIQUE SCAN | PK_RASEC_SECRID | 1 | | 489 | +2 | 71689 | 71663 | . | | |
| 14 | TABLE ACCESS BY INDEX ROWID | PR_SECRTB_DET1 | 1 | 1 | 489 | +2 | 71689 | 71689 | . | | |
| 15 | INDEX UNIQUE SCAN | PK_SECRTBDET1_SECRID | 1 | | 489 | +2 | 71689 | 71689 | . | | |
| 16 | TABLE ACCESS BY INDEX ROWID | PR_REALSEC | 1 | 1 | 489 | +2 | 71689 | 71689 | . | 0.21 | Cpu (1) |
| 17 | INDEX UNIQUE SCAN | PK_IREALSEC_SECRID | 1 | | 489 | +2 | 71689 | 71689 | . | | |
| 18 | TABLE ACCESS BY INDEX ROWID | PR_SECRTB_MY_VERSEC | 1 | 1 | 489 | +2 | 71689 | 71689 | . | | |
| 19 | INDEX UNIQUE SCAN | PK_SECRTBMY_SECRID | 1 | | 489 | +2 | 71689 | 71689 | . | | |
=================================================================================================================================================================================
It’s fairly obvious that the discrepancy between the Rows (Estim) and Rows (Actual) for the Plan ID = 11 is by far the most severe: 1 to 2G, so, 1 row (estimated) against at least 2’000’000’000 rows (real).
We already know what is behind the optimizer’s decision to chose these (especially: IX_SECRTB_SECRTPOS_VALUE_PK) indexes involved in the join.
Before showing the applied tuning strategy, we will deep dive into the internals of the mechanisms which led the optimizer to calculate the NESTED LOOP type of join (Plan Id = 7) and not e.g. HASH JOIN.
Costing of Joins
We already know which scenarios the CBO took into account when evaluating the access paths.
Particularly important is the fact that the joined table is accessed via the PK index, while the join condition is ignored, even though an index supporting that condition is available but was costed (slightly) higher.
We also know that this decision was driven by the estimated cardinality of 1 due to values of the predicates (both) being placed outside of min-max boundaries derived from the statistics.
This was manifested in the optimizer’s trace file by the following entry : Using prorated density: of col # as selectivity of out-of-range/non-existent value pred
Having the ACCESS PATHs established (the choices costed are e.g. Full Table Scan, Index Range Scan), it comes to the evaluation of the Joins.
There are three types of Joins calculated: NESTED LOOP, SORT MERGE and HASH JOIN (more details here).
It’s quite easy to establish which join permutations were examined with the simple grep (output slightly aligned to our needs) :
With a little effort (from the order of joins in the SQL plan) it can be determined that the Join Order[6] was finally chosen :
# cat -n REALDB01_ora_10053_trace_189307.trc | grep "Join order\["
7373 Join order[1]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5
8004 Join order[2]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_SECRTB_DET1[UNI]#4 PR_REALLYSEC[LOC]#3 PR_REALSEC[DEV]#5
8376 Join order[3]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5 PR_REALLYSEC[LOC]#3
8647 Join order[4]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_REALLYSEC[LOC]#3 PR_SECRTB_MY_VERSEC[STA]#2 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5
9121 Join order[5]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALSEC[DEV]#5
9508 Join order[6]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5 PR_SECRTB_MY_VERSEC[STA]#2
9793 Join order[7]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_DET1[UNI]#4 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALLYSEC[LOC]#3 PR_REALSEC[DEV]#5
10268 Join order[8]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5
10898 Join order[9]: PR_SECRTB_SECRTPOS_INFO[INF]#0 PR_REALLYSEC[LOC]#1 PR_SECRTB_DET1[UNI]#2 PR_REALSEC[DEV]#3 PR_SECRTB_MY_VERSEC[STA]#4 PR_SECRTB_SECRTPOS_VALUE[VAL]#5
11524 Join order[10]: PR_SECRTB_SECRTPOS_INFO[INF]#0 PR_REALLYSEC[LOC]#1 PR_SECRTB_DET1[UNI]#2 PR_REALSEC[DEV]#3 PR_SECRTB_SECRTPOS_VALUE[VAL]#5 PR_SECRTB_MY_VERSEC[STA]#4
11873 Join order[11]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_REALLYSEC[LOC]#2 PR_SECRTB_DET1[UNI]#3 PR_REALSEC[DEV]#4 PR_SECRTB_MY_VERSEC[STA]#5The math-work is dropped wherever possible.
That means, if the result of calculations at certain stage shows that the cost got exceeded when compared to already calculated, the remaining part is dropped with the message :Join order aborted: cost > best plan cost.
Equally, the cost of the initial join, that is, the join between PR_SECRTB_SECRTPOS_INFO[INF]#1 and PR_SECRTB_SECRTPOS_VALUE[VAL]#0 is calculated only once.
The cost figure representing that join is then used as the “Outer Table” for the subsequent join.
In general, the path “from left to right” which was already costed (its cost was calculated) is skipped by the subsequent path, until another join-member, not yet costed, was found along the joining path.
E.g. for the Join order[1] the entire cost path has been calculated.
That means, for that join permutation: Join order[1], all the subsequent joined members were taken into account ::Join order[1]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5
joined members :7394 Now joining: PR_SECRTB_SECRTPOS_VALUE[VAL]#0
7561 Now joining: PR_SECRTB_MY_VERSEC[STA]#2
7672 Now joining: PR_REALLYSEC[LOC]#3
7768 Now joining: PR_SECRTB_DET1[UNI]#4
7879 Now joining: PR_REALSEC[DEV]#5
But for the Join order[2] the first three tables were dropped (I should better put it: skipped ;-)) as this part of math was already done in the previous step.
That is, for : Join order[2]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_SECRTB_DET1[UNI]#4 PR_REALLYSEC[LOC]#3 PR_REALSEC[DEV]#5
only the following joined tables were evaluated :8052 Now joining: PR_SECRTB_DET1[UNI]#4
8163 Now joining: PR_REALLYSEC[LOC]#3
8259 Now joining: PR_REALSEC[DEV]#5
Because the calculations for that sub-path: “PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2”
were already completed in the previous step.
Then, consequently, for Join order[3] only the following tables were considered, since the remaining joins were evaluated in the previous step(s).8433 Now joining: PR_REALSEC[DEV]#5
8544 Now joining: PR_REALLYSEC[LOC]#3
Therefore, even though the Join order[6] has been ultimately chosen (its accumulated join cost across all evaluated types of joins turned out the lowest), it is necessary to examine the initial Join order[1] to find the details of the most inner join :PR_SECRTB_SECRTPOS_INFO with PR_SECRTB_SECRTPOS_VALUE (which is critical).
I kept the numbers of lines in the trace file on the left-hand side, so that it’ll be easy to spot that dependency while following the sequence of all evaluations.
Below is the evaluation of that join PR_SECRTB_SECRTPOS_INFO[INF] and PR_SECRTB_SECRTPOS_VALUE[VAL] accompanied with some comments.
Let us start from the formula used for the NESTED LOOP cost calculation:
NL cost = Cost of outer row source + (Outer Cardinality × Cost per inner probe)
Fairly intuitive:Cost of Outer Source – it’s indeed showed in the SQL Plan as the one-time operation (Exec = 1). Therefore, it’s just the summand.
This summand is added to :
Outer Cardinality (number of rows returned from the “driving rowset”) multiplied by the cost of reaching out to a single entry in the joined rowset.
This single entry (with every loop “the next one”) from the joined rowset will be visited “Outer Cardinality” times – this is why these values are multiplied.
The silent (and fair) assumption is, the cost of reaching out to each of the joined entry is the same.
Simple example can be built upon this excerpt :
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1652 | 12 (9)| 00:00:01 |
…
| 7 | NESTED LOOPS OUTER | | 1 | 174 | 7 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| PR_SECRTB_SECRTPOS_INFO | 1 | 107 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_INFO_PK | 1 | | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| PR_SECRTB_SECRTPOS_VALUE | 1 | 67 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IX_SECRTB_SECRTPOS_VALUE_PK | 1 | | 2 (0)| 00:00:01 |
…
--------------------------------------------------------------------------------------------------------------------------
NL cost = Cost of outer row source + (Outer Cardinality × Cost per inner probe)
Cost of outer row source = 4 (“Cost (%CPU)” at Plan Id = 8)
Outer Cardinality = 1 (“Rows” at the same Plan Id = 8)
Cost per inner probe = 3 (“Cost (%CPU)” at Plan Id = 10)
Finally :
Cost of NESTED LOOP (OUTER) = 7, calculated as: 4 + (1 * 3) = 7 (“Cost (%CPU)” at Plan Id = 7)
Worth noting, with such low cardinality = 1, the summand being the result of multiplication Outer Cardinality × Cost per inner probe tends to be extremally low, almost definitely lower than the cost of HASH JOIN and/or SORT MERGE JOIN (we’ll see that soon).
Another detail which deserves to be addressed.
Seemingly, there is no reason why the cost of reaching out to PR_SECRTB_SECRTPOS_VALUE shouldn’t be 4 (yet it’s 3, see Plan Id = 10).
The direct reason is the lower (-1) cost of reaching out to the index at Plan Id = 11 (the cost of Plan Id = 10 is the accumulated cost of Plan Id 11 (cost=2) and 10(cost=1), so 2+1).
Because the selected method of the join is NESTED LOOP, therefore the segment from Plan Id=11, which happens to be an index, will potentially be “hammered” multiple times.
Now (this is purely my speculation) – with all these, potentially hundreds or thousands of index lookups, there will be just one physical I/O directed to the index root block (top block of the index tree).
The remaining lookups I/O operations may visit different areas of the index tree, yet this index root block will soon become a LIO, logical read (it’ll get cached in the buffer cache right after the first physical read and from that time on it will be accessed from the cache).
It seems that the architects of the CBO decided to spare that I/O and do not count it in, while costing the joined rowset with NESTED LOOP.
That detail would probably have gone unnoticed if a higher cardinality had been involved.
In attempt to support that “theory” here is the excerpt from the same trace (see Cost: 4.000853 while calculating pure ACCESS PATH which was not yet involved in the NESTED LOOP using that index) :
2717 ****** Costing Index IX_SECRTB_SECRTPOS_VALUE_PK
2722 Using prorated density: 1.6087e-07 of col #18 as selectivity of out-of-range/non-existent value pred
2723 Access Path: index (RangeScan)
2724 Index: IX_SECRTB_SECRTPOS_VALUE_PK
2725 resc_io: 4.000000 resc_cpu: 29261
2726 ix_sel: 1.6087e-07 ix_sel_with_filters: 1.6087e-07
2727 Cost: 4.000853 Resp: 4.000853 Degree: 1Coming back to the evaluation of the Joins.
7373 Join order[1]: PR_SECRTB_SECRTPOS_INFO[INF]#1 PR_SECRTB_SECRTPOS_VALUE[VAL]#0 PR_SECRTB_MY_VERSEC[STA]#2 PR_REALLYSEC[LOC]#3 PR_SECRTB_DET1[UNI]#4 PR_REALSEC[DEV]#5
Outer table is: PR_SECRTB_SECRTPOS_INFO[INF]
Under “Now joining:” is the joined (inner) table: PR_SECRTB_SECRTPOS_VALUE[VAL]
1.Evaluating the Join with PR_SECRTB_SECRTPOS_VALUE accessed via full table scan :
7394 Now joining: PR_SECRTB_SECRTPOS_VALUE[VAL]#0
7395 ***************
7396 NL Join
7397 Outer table: Card: 0.500000 Cost: 4.000858 Resp: 4.000858 Degree: 1 Bytes: --> PR_SECRTB_SECRTPOS_INFO[INF] (already evaluated during the ACCESS PATH phase – cardinality: 1 [ ceil(0.5) ]
7398 Access path analysis for PR_SECRTB_SECRTPOS_VALUE
7399 Scan IO Cost (Disk) = 65292.000000
7400 Scan CPU Cost (Disk) = 3426246362.240000
7401 Cost of predicates:
7402 io = NOCOST, cpu = 50.000000, sel = 0.999992 flag = 2048 ("INF"."HIST_ID"="VAL"."HIST_SECRET_ID"(+))
7403 io = NOCOST, cpu = 50.000000, sel = 0.000000 flag = 2064 ("VAL"."HIST_ID"(+)>=588592848)
7404 io = NOCOST, cpu = 50.000000, sel = 0.000000 flag = 2048 ("VAL"."HIST_MASTER_ID"(+)>=285970450)
7405 Total Scan IO Cost = 65292.000000 (scan (Disk))
7406 + 0.000000 (io filter eval) (= 0.000000 (per row) * 3108134.000000 (#rows))
7407 = 65292.000000
7408 Total Scan CPU Cost = 3426246362.240000 (scan (Disk))
7409 + 155406725.000006 (cpu filter eval) (= 50.000008 (per row) * 3108134.000000 (#rows))
7410 = 3581653087.240006
7411 Inner table: PR_SECRTB_SECRTPOS_VALUE Alias: VAL
7412 Access Path: TableScan
7413 NL Join: Cost: 65400.457178 Resp: 65400.457178 Degree: 1
7414 Cost_io: 65296.000000 Cost_cpu: 3581682503
7415 Resp_io: 65296.000000 Resp_cpu: 35816825032. Evaluating the Join with PR_SECRTB_SECRTPOS_VALUE accessed over IX_SECRTB_SECRTPOS_VALUE_IX1 :
7418 ****** Costing Index IX_SECRTB_SECRTPOS_VALUE_IX1
7423 Using prorated density: 2.4172e-07 of col #21 as selectivity of out-of-range/non-existent value pred
7424 Access Path: index (AllEqJoinGuess)
7425 Index: IX_SECRTB_SECRTPOS_VALUE_IX1
7426 resc_io: 3.000000 resc_cpu: 23364
7427 ix_sel: 7.8494e-07 ix_sel_with_filters: 1.8974e-13
7428 NL Join (ordered): Cost: 7.001542 Resp: 7.001542 Degree: 1
7429 Cost_io: 7.000000 Cost_cpu: 52880
7430 Resp_io: 7.000000 Resp_cpu: 528803. Evaluating the Join with PR_SECRTB_SECRTPOS_VALUE accessed over Index IX_SECRTB_SECRTPOS_VALUE_PK :
7431 ****** Costing Index IX_SECRTB_SECRTPOS_VALUE_PK
7436 Using prorated density: 1.6087e-07 of col #18 as selectivity of out-of-range/non-existent value pred
7437 Access Path: index (RangeScan)
7438 Index: IX_SECRTB_SECRTPOS_VALUE_PK
7439 resc_io: 3.000000 resc_cpu: 22989
7440 ix_sel: 1.6087e-07 ix_sel_with_filters: 1.6087e-07
7441 NL Join : Cost: 7.001528 Resp: 7.001528 Degree: 1
7442 Cost_io: 7.000000 Cost_cpu: 52405
7443 Resp_io: 7.000000 Resp_cpu: 524054. Evaluating the Join of PR_SECRTB_SECRTPOS_INFO with PR_SECRTB_SECRTPOS_VALUE using SORT MERGE (SM) and HASH JOIN (HA) :
3147 SM Join
3148 SM cost: 9.001711
3149 resc: 9.001711 resc_io: 8.000000 resc_cpu: 34347201
3150 resp: 9.001711 resp_io: 8.000000 resp_cpu: 34347201
3151 Outer table: PR_SECRTB_SECRTPOS_INFO Alias: INF
3152 resc: 4.000858 card 0.500000 bytes: deg: 1 resp: 4.000858
3153 Inner table: PR_SECRTB_SECRTPOS_VALUE Alias: VAL
3154 resc: 4.000853 card: 0.000000 bytes: deg: 1 resp: 4.000853
3155 using dmeth: 2 #groups: 1
3156 Cost per ptn: 0.017506 #ptns: 1
3157 hash_area: 508 (max=51200) buildfrag: 1 probefrag: 1 hmbio: 127 fanout: 8 ppasses = 1.0000
3158 Hash join: Resc: 8.019217 Resp: 8.019217 [multiMatchCost=0.000000]
3159 HA Join
3160 HA cost: 8.019217
3161 resc: 8.019217 resc_io: 8.000000 resc_cpu: 658927
3162 resp: 8.019217 resp_io: 8.000000 resp_cpu: 6589275. Final result derived from simple comparison of all cost figures from all types of joins:
3163 Best:: JoinMethod: NestedLoop
3164 Cost: 7.001528 Degree: 1 Resp: 7.001528 Card: 0.500000 Bytes:Easy to spot, the NESTED LOOP join using the IX_SECRTB_SECRTPOS_VALUE_PK is the winner.
It is also interesting to realize how wabbly this decision is.
I do not mean the costs of HASH or SORT MERGE join which are away from the NESTED LOOP scenario, if difference measured in terms of the range of 1 (HASH JOIN) or 2 (SORT MERGE) can be defined as “away”.
But if you compare the Cost: 7.001528 (IX_SECRTB_SECRTPOS_VALUE_PK) and Cost: 7.001542 (IX_SECRTB_SECRTPOS_VALUE_IX1) and realize that we talk about the difference at the fifth decimal place, things get a bit scary, considering the price paid (10 seconds vs 3 days, if you reach out to the simulation in the previous part of this series).
It’s even more striking if you realize that this tiny difference comes from the CPU cost factor (if you carry out the conversion of both: resc_cpu: 22989 and resc_cpu: 23364 you’ll get the 0.001528 and 0.001542, respectively).
The difference in CPU factors ultimately comes from the difference of the estimated selectivity (note, all the remaining factors applied during the CPU cost conversion are the same and are withdrawn from the SYSTEM STATISTICS).
This in turn is certainly controlled by the fact that IX_SECRTB_SECRTPOS_VALUE_PK represents the primary key. Even the ix_sel_with_filters: 1.8974e-13 did not help (the selectivity after applying additional filtering derived from the HIST_ID on top of the join inf.hist_id = val.hist_master_id).
How can we handle that
The natural question arises, how can we (and ultimately: how did I) handle that performance problem which clearly suffered from the lack of correct information (statistics) the optimizer received.
There seem to be basically two ways, both with pros and cons.
That is, either to make sure the CBO gets the proper statistics at all times or make the CBO to stop “thinking”, so to use hints.
As much as hinting should be avoided, I hate to state that I went into this direction, weighing the following :
– The size of the two crucial and by far the biggest tables is not overly large (both ~2GB in size) and it won’t practically change. Virtually, for every INSERT there is a corresponding DELETE – actually, the sequence looked the other way around – I just wanted to make it more intuitive.
– The approach to gather the statistics at much higher frequency might turn out not frequent enough at extreme circumstances (the subsequent stats gathering may be triggered up “a second” too late and might not catch up early enough the predicate exceeding the min-max boundary – and I would phase the estimated cardinality = 1 symptom again).
– Real-time statistics during DML would way too expensive, considering truly large amount of changes.
– I have magnificent I/O capabilities with a possibility of offload as running on Exadata (this one didn’t help at all, as it will turn out soon)
With all that in mind I have decided to suggest the optimizer the worst possible scenario.
That is, to assume the both predicates involving HIST_IDs will make the query to read the entire content of both tables: PR_SECRTB_SECRTPOS_INFO (with ~1million rows) and .
Such scenario did in reality occasionally take place – especially towards the end of the business day.
The course of that tuning was then fairly simple:
1. to manually run the SQL with these three hints :cardinality(val,2e6)
cardinality(inf,1e6)opt_param('optimizer_adaptive_plans','false')
2. to intercept the SQL_ID + Child_no and gather the Outline Data section with select * from table(dbms_xplan.display_cursor('&sql_id',&child_no,'ALLSTATS +outline'))
3. to create SQL Patch for the PROD (target) sql_id embedding the intercepted hints from the Outlines
I believe Tanel Põder described a similar (same?) path in one of his articles – just long time before I did 🙂
If I find it, I will link it here.
Anyhow, the steps in details :
(1) manually run the problematic SQL (there’s no need for this query to complete – it’s important that the CBO passes the optimization phase and builds the SQL Plan with these hints)
SELECT
/*+
cardinality(val,2e6)
cardinality(inf,1e6)
opt_param('optimizer_adaptive_plans','false')
*/
*
FROM
VERYSECOWNR.pr_secrtb_secrtpos_info inf,
VERYSECOWNR.pr_secrtb_secrtpos_value val,
VERYSECOWNR.pr_secrtb_my_versec sta,
VERYSECOWNR.pr_secrtb_det1 uni,
VERYSECOWNR.pr_rverysecr res,
VERYSECOWNR.pr_realsec dev,
VERYSECOWNR.pr_reallysec loc
WHERE
inf.hist_id = val.hist_secret_id (+)
AND inf.myversecret_column = sta.secrid (+)
AND inf.sensor_unit_autoid = uni.secrid
AND inf.secretlo = loc.secrid (+)
AND uni.secdev = dev.secrid
AND dev.mysecret_ref = res.secrid (+)
AND inf.hist_id >= <any_number>
AND val.hist_id (+) >= <any_number>
order by inf.hist_id , inf.verysecret_timstmp
FETCH NEXT 50000 ROWS WITH TIES(2) intercept the SQL_ID + child_no and get the Outline Data section to use (part of it ) in the SQL Patch. The crucial hints marked.
SQL> select * from table(dbms_xplan.display_cursor('<my_sql_id>',0,'ALLSTATS +outline')) ;
Plan hash value: 1770546435
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | VIEW | | 2038K| | | |
|* 2 | WINDOW SORT PUSHED RANK | | 2038K| 80896 | 80896 | 70178/0/0|
|* 3 | HASH JOIN RIGHT OUTER | | 2038K| 2814K| 2814K| 70176/0/0|
| 4 | VIEW | index$_join$_007 | 361 | | | |
|* 5 | HASH JOIN | | | 3221K| 3221K| 70179/0/0|
| 6 | INDEX STORAGE FAST FULL SCAN | IX_RASEC_SECRID | 361 | 1028K| 1028K| |
| 7 | INDEX STORAGE FAST FULL SCAN | IX_RASEC_ANOTHERID | 361 | 1028K| 1028K| |
|* 8 | HASH JOIN RIGHT OUTER | | 2038K| 2643K| 2643K| 70175/0/0|
| 9 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_MY_VERSEC | 29 | 1028K| 1028K| |
|* 10 | HASH JOIN | | 2038K| 2991K| 2991K| 70173/0/0|
| 11 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_REALSEC | 1948 | 1028K| 1028K| |
|* 12 | HASH JOIN | | 2083K| 3443K| 3443K| 70176/0/0|
| 13 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_DET1 | 1526 | 1028K| 1028K| |
|* 14 | HASH JOIN OUTER | | 2083K| 1820K| 1820K| 70178/0/0|
|* 15 | TABLE ACCESS STORAGE FULL FIRST ROWS| PR_SECRTB_SECRTPOS_INFO | 1000K| 1028K| 1028K| |
|* 16 | TABLE ACCESS STORAGE FULL FIRST ROWS| PR_SECRTB_SECRTPOS_VALUE | 2000K| 1028K| 1028K| |
--------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F54B8D8F")
OUTLINE_LEAF(@"SEL$259F1CC8")
ELIMINATE_JOIN(@"SEL$1" "RES"@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$2" "from$_subquery$_008"@"SEL$2")
FULL(@"SEL$259F1CC8" "INF"@"SEL$1")
FULL(@"SEL$259F1CC8" "VAL"@"SEL$1")
FULL(@"SEL$259F1CC8" "UNI"@"SEL$1")
FULL(@"SEL$259F1CC8" "DEV"@"SEL$1")
FULL(@"SEL$259F1CC8" "STA"@"SEL$1")
INDEX_JOIN(@"SEL$259F1CC8" "LOC"@"SEL$1" ("PR_REALLYSEC"."SECRID") ("PR_REALLYSEC"."STATION_REF"
"PR_REALLYSEC"."ID"))
LEADING(@"SEL$259F1CC8" "INF"@"SEL$1" "VAL"@"SEL$1" "UNI"@"SEL$1" "DEV"@"SEL$1" "STA"@"SEL$1" "LOC"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "VAL"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "UNI"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "DEV"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "STA"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "LOC"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$259F1CC8" "UNI"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$259F1CC8" "DEV"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$259F1CC8" "STA"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$259F1CC8" "LOC"@"SEL$1")
END_OUTLINE_DATA
*/Note, I haven’t really forced the optimizer to apply full table scans nor have I forced any particular type ACCEES PATH or JOINs – just suggested what it may expect in terms of the “worst possible cardinality” for the two crucial tables.
So the optimizer still had a chance to do its part.
That said, with this knowledge at hand it managed to work our really efficient plan (and the query usually runs within 1 up to 2 seconds!).
(3) create SQL Patch for the PROD (target) sql_id embedding the most important hints intercepted from the Outlines. Actually, I don’t see a reason for which you wouldn’t just take all of them.
DECLARE
l_name VARCHAR2(128);
BEGIN
l_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => 'fsd60grbs90gu',
hint_text => q'[
opt_estimate(@"SEL$259F1CC8" table "INF"@"SEL$1" rows=1e6)
opt_estimate(@"SEL$259F1CC8" table "VAL"@"SEL$1" rows=2e6)
LEADING(@"SEL$259F1CC8" "INF"@"SEL$1" "VAL"@"SEL$1" "UNI"@"SEL$1" "DEV"@"SEL$1" "STA"@"SEL$1" "LOC"@"SEL$1")
FULL(@"SEL$259F1CC8" "INF"@"SEL$1")
FULL(@"SEL$259F1CC8" "VAL"@"SEL$1")
FULL(@"SEL$259F1CC8" "UNI"@"SEL$1")
FULL(@"SEL$259F1CC8" "DEV"@"SEL$1")
FULL(@"SEL$259F1CC8" "STA"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "VAL"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "UNI"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "DEV"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "STA"@"SEL$1")
USE_HASH(@"SEL$259F1CC8" "LOC"@"SEL$1")
opt_param('optimizer_adaptive_plans','false')
]',
name => 'SQLpatch_fsd60grbs90gu'
);
END;
/
Several notes.
First, I decided to keep the cardinality suggestion (just in case) :
opt_estimate(@"SEL$259F1CC8" table "INF"@"SEL$1" rows=1e6)
opt_estimate(@"SEL$259F1CC8" table "VAL"@"SEL$1" rows=2e6)
Interestingly, SQL Patch does not allow to apply the hint keyword “cardinality”, so I used opt_estimate(@<OUTLINE_LEAF> keyword=table <table_alias> <cardinality estimation>)
Secondly, you might ask – why did I use the query block qualifiers, like SEL$259F1CC8, etc. So, why not simply put e.g. opt_estimate(table INF rows=1e6) ?
I have noticed that the query must have been transformed and the final SQL has certainly changed (the presence of index$_join$_007 confirms that). Keeping the outline leaves and query block names gives me certainty that – even following the query transformation – the optimizer gets the consistent “shape of SQL” adhering to the blocks it managed to optimize.
Last note – the opt_param('optimizer_adaptive_plans','false') has been replaced with these three hints : OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
which is a clear suggestion which hidden parameters are behind optimizer_adaptive_plans itself.
Finally, a representative execution statistics upon
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : fsd60grbs90gu
SQL Execution ID : 23198392
Duration : .75723s
Module/Action : JDBC Thin Client/-
Program : JDBC Thin Client
Fetch Calls : 2
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | NUMBER | 344390903 |
| :2 | 2 | NUMBER | 708371010 |
| :3 | 3 | NUMBER | 50000 |
========================================================================================================================
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.76 | 0.75 | 0.01 | 2 | 399K |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1770546435)
================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 197 | . | | |
| 1 | VIEW | | 2M | 222K | 1 | +0 | 1 | 197 | . | | |
| 2 | WINDOW SORT PUSHED RANK | | 2M | 222K | 1 | +0 | 1 | 197 | 67584 | | |
| 3 | HASH JOIN RIGHT OUTER | | 2M | 120K | 1 | +0 | 1 | 197 | 5MB | | |
| 4 | VIEW | index$_join$_007 | 361 | 2 | 1 | +0 | 1 | 364 | . | | |
| 5 | HASH JOIN | | | | 1 | +0 | 1 | 364 | 5MB | | |
| 6 | INDEX STORAGE FAST FULL SCAN | IX_RASEC_SECRID | 361 | 1 | 1 | +0 | 1 | 364 | . | | |
| 7 | INDEX STORAGE FAST FULL SCAN | IX_RASEC_ANOTHERID | 361 | 1 | 1 | +0 | 1 | 364 | . | | |
| 8 | HASH JOIN RIGHT OUTER | | 2M | 120K | 1 | +0 | 1 | 197 | 5MB | | |
| 9 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_MY_VERSEC | 29 | 3 | 1 | +0 | 1 | 30 | . | | |
| 10 | HASH JOIN | | 2M | 120K | 1 | +0 | 1 | 197 | 5MB | | |
| 11 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_REALSEC | 1948 | 9 | 1 | +0 | 1 | 1954 | . | | |
| 12 | HASH JOIN | | 2M | 120K | 1 | +0 | 1 | 197 | 5MB | | |
| 13 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_DET1 | 1526 | 7 | 1 | +0 | 1 | 1529 | . | | |
| 14 | HASH JOIN OUTER | | 2M | 120K | 1 | +0 | 1 | 197 | 5MB | | |
| 15 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_SECRTPOS_INFO | 1M | 42804 | 1 | +0 | 1 | 107 | . | | |
| 16 | TABLE ACCESS STORAGE FULL FIRST ROWS | PR_SECRTB_SECRTPOS_VALUE | 2M | 65378 | 1 | +0 | 1 | 159 | . | | |
================================================================================================================================================================================
As you can see, the time elapsed is below 1 sec.
I still owe you one explanation after referring to :
I have magnificent I/O capabilities with a possibility of offload as running on Exadata (this one didn’t help at all, as it will turn out soon)
Offload did not trigger at all, even though at first glance the prerequisites seemed nearly ideal.
Let us examine the example from above.
Considering a full table scan of two not-so-small tables and, as shown in the execution report above, fairly good filtering (see the Rows Actual values for PR_SECRTB_SECRTPOS_INFO and PR_SECRTB_SECRTPOS_VALUE), one might say: “offload is certain.”
Yet it did not kick in.
The reason becomes clear if you take a deeper look at the state of caching of these two tables: both are almost entirely cached in the buffer cache.
Alone, if you take Buffer Gets = 399K and compare it with the physical sizes of both tables (see below) – you’ll come to the conclusion that these tables must be fully cached (I owe you one information: the size of all remaining segments is negligible)
Then another symptom: Elapsed Time(s) is virtually equal to Cpu Time(s).
Both give you the indication that all the buffers were satisfied from buffer cache, so there weren’t any physical reads, thus it is hard to expect a smart scan path.
Most of all: no mentioning of any percentage of OFFLOAD in the Global Stats section.
I am explaining this mechanism (lack of expected offload) here.
For the time being, just take a look at the physical size of both tables vs the state of their caching.
It is, by the way, quite interesting to see how many buffers are reported as XCUR (current, changed buffers) and CR (consistent read). This gives a good sense that both tables are being modified quite heavily.
PHYSICAL SIZE of both tables :
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SEG_MB LOBSIZE_MB TOTAL_MB
------------------------------------------------------------ ------------------ ------------------------------ ------------ ------------ ------------
VERYSECOWNR.PR_SECRTB_SECRTPOS_INFO TABLE TS_VERYSECOWNR 1,238 1,238
VERYSECOWNR.PR_SECRTB_SECRTPOS_VALUE TABLE TS_VERYSECOWNR 1,889 1,889
------------ ------------ ------------
sum 3,127 3,127
STATE of CACHING of PR_SECRTB_SECRTPOS_VALUE:
INST_ID STATUS OWNER OBJECT_NAME OBJECT_TYPE Buffers B_CACHE MB_CACHE
------- -------------------- ------------------------------ ------------------------------ -------------------- ---------------- ---------------- ----------
1 pi VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 14 114,688 .1
xcur VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 194 1,589,248 1.5
cr VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 39,275 321,740,800 306.8
scur VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 241,021 1,974,444,032 1883
******* ---------------- ----------
sum 280,504 2191.4
2 pi VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 2 16,384 0
cr VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 200,469 1,642,242,048 1566.2
scur VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 241,026 1,974,484,992 1883
******* ---------------- ----------
sum 441,497 3449.2
3 scur VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 40 327,680 .3
cr VERYSECOWNR PR_SECRTB_SECRTPOS_VALUE TABLE 139 1,138,688 1.1
******* ---------------- ----------
sum 179 1.4
STATE of CACHING of PR_SECRTB_SECRTPOS_INFO :
INST_ID STATUS OWNER OBJECT_NAME OBJECT_TYPE Buffers B_CACHE MB_CACHE
------- -------------------- ------------------------------ ------------------------------ -------------------- ---------------- ---------------- ----------
1 cr VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 24,736 202,637,312 193.3
xcur VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 53,963 442,064,896 421.6
scur VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 103,669 849,256,448 809.9
******* ---------------- ----------
sum 182,368 1424.8
2 scur VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 2,194 17,973,248 17.1
cr VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 31,258 256,065,536 244.2
******* ---------------- ----------
sum 33,452 261.3
3 scur VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 1 8,192 0
cr VERYSECOWNR PR_SECRTB_SECRTPOS_INFO TABLE 56 458,752 .4
******* ---------------- ----------
sum 57 .4
Leave a Reply