GENERAL INFORMATION
Activity start : 09-AUG-2019 19:00:00
Activity end : 09-AUG-2019 20:45:00
Executions completed : 5
Executions interrupted : 0
Executions with fatal error : 0


SUMMARY (AUTO INDEXES)
Index candidates : 27
Indexes created (visible / invisible) : 13 (8 / 5)
Space used (visible / invisible) : 8.32_GB (5.36_GB / 2.96_GB)
Indexes dropped : 0
SQL statements verified : 21
SQL statements improved (improvement factor) : 15 (671.3x)
SQL plan baselines created : 0
Overall improvement factor : 666.2x


SUMMARY (MANUAL INDEXES)
Unused indexes : 0
Space used : 0_B
Unusable indexes : 0


INDEX DETAILS
1. The following indexes were created:
*: invisible
Owner Table Index Key Type Properties
TPCE ACCOUNT_PERMISSION * SYS_AI_bvugz2g2mwg4z AP_CA_ID,AP_TAX_ID,AP_L_NAME B-TREE NONE
TPCE CUSTOMER_ACCOUNT SYS_AI_d3kmzk12banrf CA_C_ID B-TREE NONE
TPCE CUSTOMER_TAXRATE SYS_AI_7wkawm0x4nwbr CX_C_ID B-TREE NONE
TPCE DAILY_MARKET * SYS_AI_804nrthjdbw65 DM_S_SYMB,DM_DATE B-TREE NONE
TPCE HOLDING SYS_AI_fv8ngjrq4dnd6 H_CA_ID,H_S_SYMB B-TREE NONE
TPCE HOLDING_HISTORY * SYS_AI_c2vqkp1cnaynt HH_H_T_ID B-TREE NONE
TPCE SECURITY * SYS_AI_2gm9u3nyxkwcu S_CO_ID B-TREE NONE
TPCE TRADE SYS_AI_1hjwdmk5q4mkz T_CA_ID B-TREE NONE
TPCE TRADE SYS_AI_385rjxyamg6nd T_S_SYMB B-TREE NONE
TPCE TRADE SYS_AI_a8805thkfs7ua T_ST_ID B-TREE NONE
TPCE TRADE_REQUEST SYS_AI_1qxmpjswr2n43 TR_B_ID B-TREE NONE
TPCE TRADE_REQUEST SYS_AI_czrf19jhu7cux TR_S_SYMB B-TREE NONE
TPCE WATCH_LIST * SYS_AI_3h68gvvm1cmvr WL_C_ID B-TREE NONE


VERIFICATION DETAILS
1. The performance of the following statements improved:
Parsing Schema Name : TPCE
SQL ID : 1vfn3bhnxkd4c
SQL Text : SELECT H_QTY, H_PRICE FROM HOLDING WHERE H_CA_ID = :B2 AND H_S_SYMB = :B1 ORDER BY H_DTS DESC
Improvement Factor : 3416.4x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 646714 468286
CPU Time (s): 642095 12910
Buffer Gets: 153741 45
Optimizer Cost: 9012 5
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 76 1
Executions: 5 1


PLANS SECTION


- Original
Plan Hash Value : 2886858917

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 9012
1 . SORT ORDER BY 1 41 9012 00:00:01
2 .. TABLE ACCESS STORAGE FULL HOLDING 1 41 9011 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 3494294591

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 6 246 5 00:00:01
1 . SORT ORDER BY 6 246 5 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID BATCHED HOLDING 6 246 4 00:00:01
* 3 ... INDEX RANGE SCAN SYS_AI_fv8ngjrq4dnd6 1 3 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : 27ydgx9ayacz0
SQL Text : SELECT B_ID FROM ( SELECT B_ID ,COUNT(*) FROM TRADE ,CUSTOMER_ACCOUNT ,BROKER WHERE B_ID = CA_B_ID AND CA_ID = T_CA_ID AND T_ST_ID = 'SBMT' GROUP BY B_ID ORDER BY 2 DESC FETCH FIRST 1 ROW ONLY)
Improvement Factor : 1096210.8x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 10500168847 930
CPU Time (s): 10487810129 930
Buffer Gets: 3787408461 3
Optimizer Cost: 316158 8
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 21 0
Executions: 3455 1


PLANS SECTION


- Original
Plan Hash Value : 4173877292

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 316158
1 . SORT ORDER BY 1 39 316158 00:00:13
2 .. VIEW 1 39 316157 00:00:13
3 ... WINDOW SORT PUSHED RANK 1 28 316157 00:00:13
4 .... HASH GROUP BY 1 28 316157 00:00:13
5 ..... HASH JOIN 1 28 316155 00:00:13
6 ...... NESTED LOOPS 1 28 316155 00:00:13
7 ....... NESTED LOOPS 1 28 316155 00:00:13
8 ........ STATISTICS COLLECTOR
9 ......... TABLE ACCESS STORAGE FULL TRADE 1 13 316154 00:00:13
10 ........ INDEX UNIQUE SCAN CUSTOMER_ACCOUNT_PK_IDX 1 0
11 ....... TABLE ACCESS BY INDEX ROWID CUSTOMER_ACCOUNT 1 15 1 00:00:01
12 ...... TABLE ACCESS STORAGE FULL CUSTOMER_ACCOUNT 1 15 1 00:00:01

Notes
- This is an adaptive plan


- With Auto Indexes
Plan Hash Value : 2894511534

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 39 8 00:00:01
1 . SORT ORDER BY 1 39 8 00:00:01
* 2 .. VIEW 1 39 7 00:00:01
* 3 ... WINDOW SORT PUSHED RANK 1 32 7 00:00:01
4 .... HASH GROUP BY 1 32 7 00:00:01
5 ..... NESTED LOOPS 1 32 5 00:00:01
6 ...... NESTED LOOPS 1 32 5 00:00:01
7 ....... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 1 17 4 00:00:01
* 8 ........ INDEX RANGE SCAN SYS_AI_a8805thkfs7ua 1 3 00:00:01
* 9 ....... INDEX UNIQUE SCAN CUSTOMER_ACCOUNT_PK_IDX 1 0
10 ...... TABLE ACCESS BY INDEX ROWID CUSTOMER_ACCOUNT 1 15 1 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
- This is an adaptive plan


Parsing Schema Name : TPCE
SQL ID : 3g5smum5guw6n
SQL Text : SELECT TR_T_ID ,TR_BID_PRICE ,TR_TT_ID ,TR_QTY FROM TRADE_REQUEST WHERE TR_S_SYMB = :B2 AND ( (TR_TT_ID = 'TSL' AND TR_BID_PRICE >= :B1 ) OR (TR_TT_ID = 'TLS' AND TR_BID_PRICE <= :B1 ) OR (TR_TT_ID = 'TLB' AND TR_BID_PRICE >= :B1 ) )
Improvement Factor : 181.3x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 801959 459836
CPU Time (s): 771423 7947
Buffer Gets: 578548 40
Optimizer Cost: 2 2
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 1629 0
Executions: 3191 1


PLANS SECTION


- Original
Plan Hash Value : 1579621731

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2
1 . TABLE ACCESS STORAGE FULL TRADE_REQUEST 1 51 2 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 770327140

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 26 2 00:00:01
* 1 . TABLE ACCESS BY INDEX ROWID BATCHED TRADE_REQUEST 1 26 2 00:00:01
* 2 .. INDEX RANGE SCAN SYS_AI_czrf19jhu7cux 1 1 00:00:01

Predicate Information (identified by operation id):

Notes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : 58usmxwz5ap68
SQL Text : SELECT B_NAME ,SUM(TR_QTY * TR_BID_PRICE) AS VOLUME FROM TRADE_REQUEST ,SECTOR ,INDUSTRY ,COMPANY ,BROKER ,SECURITY WHERE TR_B_ID = B_ID AND TR_S_SYMB = S_SYMB AND S_CO_ID = CO_ID AND CO_IN_ID = IN_ID AND SC_ID = IN_SC_ID AND B_NAME IN ( :B4 ,:B3 ,:B2 ) AND SC_NAME = :B1 GROUP BY B_NAME ORDER BY 2 D...
Improvement Factor : 14.1x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 102972 484291
CPU Time (s): 99204 69949
Buffer Gets: 16549 276
Optimizer Cost: 7 18
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 90 1


PLANS SECTION


- Original
Plan Hash Value : 97872209

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 7
1 . SORT ORDER BY 1 118 7 00:00:01
2 .. HASH GROUP BY 1 118 7 00:00:01
3 ... HASH JOIN 1 118 5 00:00:01
4 .... NESTED LOOPS 1 118 5 00:00:01
5 ..... NESTED LOOPS 1 118 5 00:00:01
6 ...... STATISTICS COLLECTOR
7 ....... HASH JOIN 1 102 4 00:00:01
8 ........ NESTED LOOPS 1 102 4 00:00:01
9 ......... STATISTICS COLLECTOR
10 .......... HASH JOIN 1 96 3 00:00:01
11 ........... NESTED LOOPS 1 96 3 00:00:01
12 ............ STATISTICS COLLECTOR
13 ............. HASH JOIN 1 86 2 00:00:01
14 .............. NESTED LOOPS 1 86 2 00:00:01
15 ............... STATISTICS COLLECTOR
16 ................ HASH JOIN 1 61 2 00:00:01
17 ................. NESTED LOOPS 1 61 2 00:00:01
18 .................. STATISTICS COLLECTOR
19 ................... TABLE ACCESS STORAGE FULL TRADE_REQUEST 1 48 2 00:00:01
20 .................. TABLE ACCESS BY INDEX ROWID SECURITY 1 13 0
21 ................... INDEX UNIQUE SCAN SECURITY_PK_IDX 1 0
22 ................. TABLE ACCESS STORAGE FULL SECURITY 1 13 0
23 ............... TABLE ACCESS BY INDEX ROWID BROKER 1 25 0
24 ................ INDEX UNIQUE SCAN BROKER_PK_IDX 1 0
25 .............. TABLE ACCESS STORAGE FULL BROKER 1 25 0
26 ............ TABLE ACCESS BY INDEX ROWID COMPANY 1 10 1 00:00:01
27 ............. INDEX UNIQUE SCAN COMPANY_PK_IDX 1 0
28 ........... TABLE ACCESS STORAGE FULL COMPANY 1 10 1 00:00:01
29 ......... TABLE ACCESS BY INDEX ROWID INDUSTRY 1 6 1 00:00:01
30 .......... INDEX UNIQUE SCAN INDUSTRY_PK_IDX 1 0
31 ........ TABLE ACCESS STORAGE FULL INDUSTRY 1 6 1 00:00:01
32 ...... INDEX UNIQUE SCAN SECTOR_PK_IDX 1 0
33 ..... TABLE ACCESS BY INDEX ROWID SECTOR 1 16 1 00:00:01
34 .... TABLE ACCESS STORAGE FULL SECTOR 1 16 1 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes
- This is an adaptive plan


- With Auto Indexes
Plan Hash Value : 3497217759

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 89 18 00:00:01
1 . SORT ORDER BY 1 89 18 00:00:01
2 .. HASH GROUP BY 1 89 18 00:00:01
3 ... NESTED LOOPS 1 89 16 00:00:01
4 .... NESTED LOOPS 2 89 16 00:00:01
5 ..... NESTED LOOPS 2 146 14 00:00:01
6 ...... NESTED LOOPS 2 134 12 00:00:01
7 ....... NESTED LOOPS 2 114 10 00:00:01
8 ........ NESTED LOOPS 2 88 8 00:00:01
* 9 ......... TABLE ACCESS STORAGE FULL BROKER 2 50 7 00:00:01
10 ......... TABLE ACCESS BY INDEX ROWID BATCHED TRADE_REQUEST 1 19 1 00:00:01
* 11 .......... INDEX RANGE SCAN SYS_AI_1qxmpjswr2n43 1 0
12 ........ TABLE ACCESS BY INDEX ROWID SECURITY 1 13 1 00:00:01
* 13 ......... INDEX UNIQUE SCAN SECURITY_PK_IDX 1 0
14 ....... TABLE ACCESS BY INDEX ROWID COMPANY 1 10 1 00:00:01
* 15 ........ INDEX UNIQUE SCAN COMPANY_PK_IDX 1 0
16 ...... TABLE ACCESS BY INDEX ROWID INDUSTRY 1 6 1 00:00:01
* 17 ....... INDEX UNIQUE SCAN INDUSTRY_PK_IDX 1 0
* 18 ..... INDEX UNIQUE SCAN SECTOR_PK_IDX 1 0
* 19 .... TABLE ACCESS BY INDEX ROWID SECTOR 1 16 1 00:00:01

Predicate Information (identified by operation id):

Notes
- Dynamic sampling used for this statement ( level = 11 )
- This is an adaptive plan


Parsing Schema Name : TPCE
SQL ID : 65sun6bb6z9d7
SQL Text : SELECT H_T_ID, H_QTY, H_PRICE FROM HOLDING WHERE H_CA_ID = :B2 AND H_S_SYMB = :B1 ORDER BY H_DTS ASC
Improvement Factor : 3416x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 1513610 418861
CPU Time (s): 1506069 8685
Buffer Gets: 368925 44
Optimizer Cost: 8759 5
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 120 1
Executions: 12 1


PLANS SECTION


- Original
Plan Hash Value : 1016658961

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8759
1 . SORT ORDER BY 1 41 8759 00:00:01
2 .. TABLE ACCESS STORAGE FULL HOLDING 1 41 8758 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 3494294591

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 41 5 00:00:01
1 . SORT ORDER BY 1 41 5 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID BATCHED HOLDING 1 41 4 00:00:01
* 3 ... INDEX RANGE SCAN SYS_AI_fv8ngjrq4dnd6 1 3 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : 9gugdjb59hbh5
SQL Text : SELECT T_ID FROM (SELECT T_ID FROM TRADE WHERE T_S_SYMB = :B4 AND T_DTS >= :B3 AND T_DTS <= :B2 AND T_CA_ID <= :B1 ) WHERE ROWNUM <= :B5
Improvement Factor : 398.3x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 30999910 551457
CPU Time (s): 30841606 11577
Buffer Gets: 9694190 716
Optimizer Cost: 2307 14
Disk Reads: 0 4
Direct Writes: 0 0
Rows Processed: 293 2
Executions: 36 1


PLANS SECTION


- Original
Plan Hash Value : 2252022288

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 2307
1 . COUNT STOPKEY
2 .. FILTER
3 ... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE 12 576 2307 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 948758788

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 48 14 00:00:01
* 1 . COUNT STOPKEY
* 2 .. FILTER
* 3 ... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 1 48 14 00:00:01
* 4 .... INDEX RANGE SCAN SYS_AI_385rjxyamg6nd 25227 3 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : a4gm2jyarnxcf
SQL Text : SELECT T_ID ,T_S_SYMB ,T_QTY ,ST_NAME ,TH_DTS FROM (SELECT T_ID AS ID FROM TRADE WHERE T_CA_ID = :B1 ORDER BY T_DTS DESC FETCH FIRST 10 ROWS ONLY ) T ,TRADE ,TRADE_HISTORY ,STATUS_TYPE WHERE T_ID = ID AND TH_T_ID = T_ID AND ST_ID = TH_ST_ID ORDER BY TH_DTS DESC FETCH FIRST 30 ROWS ONLY
Improvement Factor : 1132074.2x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 682506405 555422
CPU Time (s): 681515468 11398
Buffer Gets: 165282848 41
Optimizer Cost: 322305 72
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 146 1


PLANS SECTION


- Original
Plan Hash Value : 468588762

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 322305
1 . VIEW 30 2430 322305 00:00:13
2 .. WINDOW SORT PUSHED RANK 24 2112 322305 00:00:13
3 ... HASH JOIN 24 2112 322304 00:00:13
4 .... NESTED LOOPS 24 1776 322297 00:00:13
5 ..... NESTED LOOPS 24 1776 322297 00:00:13
6 ...... NESTED LOOPS 10 480 322257 00:00:13
7 ....... VIEW 10 130 322237 00:00:13
8 ........ VIEW 10 390 322237 00:00:13
9 ......... WINDOW SORT PUSHED RANK 1 39 322237 00:00:13
10 .......... TABLE ACCESS STORAGE FULL TRADE 1 39 322236 00:00:13
11 ....... TABLE ACCESS BY INDEX ROWID TRADE 1 35 2 00:00:01
12 ........ INDEX UNIQUE SCAN TRADE_PK_IDX 1 1 00:00:01
13 ...... INDEX RANGE SCAN TRADE_HISTORY_PK_IDX 2 3 00:00:01
14 ..... TABLE ACCESS BY INDEX ROWID TRADE_HISTORY 2 52 4 00:00:01
15 .... TABLE ACCESS STORAGE FULL STATUS_TYPE 5 70 7 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 3148146906

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 30 2430 72 00:00:01
* 1 . VIEW 30 2430 72 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 24 2040 72 00:00:01
* 3 ... HASH JOIN 24 2040 71 00:00:01
4 .... NESTED LOOPS 24 1704 64 00:00:01
5 ..... NESTED LOOPS 24 1704 64 00:00:01
6 ...... NESTED LOOPS 10 450 24 00:00:01
7 ....... VIEW 10 130 4 00:00:01
* 8 ........ VIEW 10 390 4 00:00:01
* 9 ......... WINDOW SORT PUSHED RANK 1 39 4 00:00:01
10 .......... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 1 39 3 00:00:01
* 11 ........... INDEX RANGE SCAN SYS_AI_1hjwdmk5q4mkz 1 3 00:00:01
12 ....... TABLE ACCESS BY INDEX ROWID TRADE 1 32 2 00:00:01
* 13 ........ INDEX UNIQUE SCAN TRADE_PK_IDX 1 1 00:00:01
* 14 ...... INDEX RANGE SCAN TRADE_HISTORY_PK_IDX 2 3 00:00:01
15 ..... TABLE ACCESS BY INDEX ROWID TRADE_HISTORY 2 52 4 00:00:01
16 .... TABLE ACCESS STORAGE FULL STATUS_TYPE 5 70 7 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : ac4f3dj89t67z
SQL Text : SELECT SUM(TX_RATE) FROM TAX_RATE WHERE TX_ID IN (SELECT CX_TX_ID FROM CUSTOMER_TAXRATE WHERE CX_C_ID = :B1 )
Improvement Factor : 24.3x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 11664 432672
CPU Time (s): 10044 11312
Buffer Gets: 979 28
Optimizer Cost: 7 3
Disk Reads: 8 1
Direct Writes: 0 0
Rows Processed: 8 1
Executions: 8 1


PLANS SECTION


- Original
Plan Hash Value : 1411431246

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 7
1 . SORT AGGREGATE 1 20
2 .. HASH JOIN 1 20 7 00:00:01
3 ... NESTED LOOPS 1 20 7 00:00:01
4 .... STATISTICS COLLECTOR
5 ..... TABLE ACCESS STORAGE FULL TAX_RATE 320 2880 7 00:00:01
6 .... INDEX UNIQUE SCAN CUSTOMER_TAXRATE_PK_IDX 1 11 0
7 ... INDEX STORAGE FAST FULL SCAN CUSTOMER_TAXRATE_PK_IDX 1 11 0

Notes
- This is an adaptive plan


- With Auto Indexes
Plan Hash Value : 823189019

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 20 3 00:00:01
1 . SORT AGGREGATE 1 20
2 .. NESTED LOOPS 1 20 3 00:00:01
3 ... NESTED LOOPS 1 20 3 00:00:01
4 .... TABLE ACCESS BY INDEX ROWID BATCHED CUSTOMER_TAXRATE 1 11 2 00:00:01
* 5 ..... INDEX RANGE SCAN SYS_AI_7wkawm0x4nwbr 1 1 00:00:01
* 6 .... INDEX UNIQUE SCAN TAX_RATE_PK_IDX 1 0
7 ... TABLE ACCESS BY INDEX ROWID TAX_RATE 1 9 1 00:00:01

Predicate Information (identified by operation id):

Notes
- Dynamic sampling used for this statement ( level = 11 )
- This is an adaptive plan


Parsing Schema Name : TPCE
SQL ID : avv0dvvc34z8r
SQL Text : SELECT T_ID FROM TRADE ,CUSTOMER_ACCOUNT ,BROKER WHERE B_ID = :B1 AND CA_ID = T_CA_ID AND T_ST_ID = 'SBMT'
Improvement Factor : 969.8x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 66253410 468057
CPU Time (s): 66180961 9640
Buffer Gets: 22986535 52
Optimizer Cost: 316154 3
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 3386 1


PLANS SECTION


- Original
Plan Hash Value : 4068577614

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 316154
1 . NESTED LOOPS 1 24 316154 00:00:13
2 .. INDEX UNIQUE SCAN BROKER_PK_IDX 1 7 0
3 .. TABLE ACCESS STORAGE FULL TRADE 1 17 316154 00:00:13

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 2662655412

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 24 3 00:00:01
1 . NESTED LOOPS 1 24 3 00:00:01
* 2 .. INDEX UNIQUE SCAN BROKER_PK_IDX 1 7 0 00:00:01
3 .. TABLE ACCESS BY INDEX ROWID BATCHED TRADE 1 17 3 00:00:01
* 4 ... INDEX RANGE SCAN SYS_AI_a8805thkfs7ua 1 2 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : bc11fk66p8myf
SQL Text : SELECT T_ID, T_IS_CASH FROM (SELECT T_ID ,T_IS_CASH FROM TRADE WHERE T_CA_ID = :B3 AND T_DTS >= :B2 AND T_DTS <= :B1 ORDER BY T_DTS ASC) WHERE ROWNUM <= :B4
Improvement Factor : 328x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 7200544785 441660
CPU Time (s): 7190435162 18869
Buffer Gets: 1869862511 3471
Optimizer Cost: 319472 3521
Disk Reads: 0 12
Direct Writes: 0 0
Rows Processed: 23504 19
Executions: 1658 1


PLANS SECTION


- Original
Plan Hash Value : 3313683128

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 319472
1 . COUNT STOPKEY
2 .. VIEW 1116 29016 319472 00:00:13
3 ... SORT ORDER BY STOPKEY 1116 35712 319472 00:00:13
4 .... FILTER
5 ..... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE 1116 35712 319471 00:00:13

- With Auto Indexes
Plan Hash Value : 4037007282

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19 494 3521 00:00:01
* 1 . COUNT STOPKEY
2 .. VIEW 1780 46280 3521 00:00:01
* 3 ... SORT ORDER BY STOPKEY 1780 92560 3521 00:00:01
* 4 .... FILTER
* 5 ..... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 1780 92560 3520 00:00:01
* 6 ...... INDEX RANGE SCAN SYS_AI_1hjwdmk5q4mkz 3530 13 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : c789gtaqfmv4j
SQL Text : SELECT CA_ID AS ACCT_ID ,CA_BAL AS CASH_BAL ,NVL((SUM(HS_QTY * LT_PRICE)),0) AS ASSETS_TOTAL FROM CUSTOMER_ACCOUNT LEFT OUTER JOIN HOLDING_SUMMARY ON HS_CA_ID = CA_ID ,LAST_TRADE WHERE CA_C_ID = :B1 AND LT_S_SYMB = HS_S_SYMB GROUP BY CA_ID, CA_BAL ORDER BY 3 ASC FETCH FIRST 10 ROWS ONLY
Improvement Factor : 8.2x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 335445 435959
CPU Time (s): 276302 13877
Buffer Gets: 39590 72
Optimizer Cost: 104 44
Disk Reads: 0 1
Direct Writes: 0 0
Rows Processed: 761 9
Executions: 146 1


PLANS SECTION


- Original
Plan Hash Value : 1696298958

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 104
1 . SORT ORDER BY 10 650 104 00:00:01
2 .. VIEW 10 650 103 00:00:01
3 ... WINDOW SORT PUSHED RANK 18 900 103 00:00:01
4 .... HASH GROUP BY 18 900 103 00:00:01
5 ..... HASH JOIN 51 2550 101 00:00:01
6 ...... JOIN FILTER CREATE :BF0000 51 2040 89 00:00:01
7 ....... NESTED LOOPS 51 2040 89 00:00:01
8 ........ NESTED LOOPS 51 2040 89 00:00:01
9 ......... TABLE ACCESS STORAGE FULL FIRST ROWS CUSTOMER_ACCOUNT 5 115 75 00:00:01
10 ......... INDEX RANGE SCAN HOLDING_SUMMARY_PK_IDX 10 2 00:00:01
11 ........ TABLE ACCESS BY INDEX ROWID HOLDING_SUMMARY 10 170 3 00:00:01
12 ...... JOIN FILTER USE :BF0000 3425 34250 11 00:00:01
13 ....... TABLE ACCESS STORAGE FULL FIRST ROWS LAST_TRADE 3425 34250 11 00:00:01

- With Auto Indexes
Plan Hash Value : 3615014476

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 10 650 44 00:00:01
1 . SORT ORDER BY 10 650 44 00:00:01
* 2 .. VIEW 10 650 43 00:00:01
* 3 ... WINDOW SORT PUSHED RANK 58 2900 43 00:00:01
4 .... HASH GROUP BY 58 2900 43 00:00:01
* 5 ..... HASH JOIN 93 4650 40 00:00:01
6 ...... JOIN FILTER CREATE :BF0000 93 3720 29 00:00:01
7 ....... NESTED LOOPS 93 3720 29 00:00:01
8 ........ NESTED LOOPS 93 3720 29 00:00:01
9 ......... TABLE ACCESS BY INDEX ROWID BATCHED CUSTOMER_ACCOUNT 9 207 2 00:00:01
* 10 .......... INDEX RANGE SCAN SYS_AI_d3kmzk12banrf 9 1 00:00:01
* 11 ......... INDEX RANGE SCAN HOLDING_SUMMARY_PK_IDX 10 2 00:00:01
12 ........ TABLE ACCESS BY INDEX ROWID HOLDING_SUMMARY 10 170 3 00:00:01
13 ...... JOIN FILTER USE :BF0000 3425 34250 11 00:00:01
* 14 ....... TABLE ACCESS STORAGE FULL FIRST ROWS LAST_TRADE 3425 34250 11 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : caa25g7rnhutz
SQL Text : SELECT H_T_ID, H_QTY, H_PRICE FROM HOLDING WHERE H_CA_ID = :B2 AND H_S_SYMB = :B1 ORDER BY H_DTS DESC
Improvement Factor : 3417.2x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 508482 431753
CPU Time (s): 505332 8576
Buffer Gets: 123022 44
Optimizer Cost: 9012 5
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 54 1
Executions: 4 1


PLANS SECTION


- Original
Plan Hash Value : 2886858917

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 9012
1 . SORT ORDER BY 1 41 9012 00:00:01
2 .. TABLE ACCESS STORAGE FULL HOLDING 1 41 9011 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 3494294591

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 6 246 5 00:00:01
1 . SORT ORDER BY 6 246 5 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID BATCHED HOLDING 6 246 4 00:00:01
* 3 ... INDEX RANGE SCAN SYS_AI_fv8ngjrq4dnd6 1 3 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : d3vxc76p8tawv
SQL Text : SELECT H_QTY, H_PRICE FROM HOLDING WHERE H_CA_ID = :B2 AND H_S_SYMB = :B1 ORDER BY H_DTS ASC
Improvement Factor : 3416.2x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 1563178 440527
CPU Time (s): 1552641 7780
Buffer Gets: 368958 44
Optimizer Cost: 8759 5
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 120 1
Executions: 12 1


PLANS SECTION


- Original
Plan Hash Value : 1016658961

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 8759
1 . SORT ORDER BY 1 41 8759 00:00:01
2 .. TABLE ACCESS STORAGE FULL HOLDING 1 41 8758 00:00:01

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 3494294591

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 41 5 00:00:01
1 . SORT ORDER BY 1 41 5 00:00:01
2 .. TABLE ACCESS BY INDEX ROWID BATCHED HOLDING 1 41 4 00:00:01
* 3 ... INDEX RANGE SCAN SYS_AI_fv8ngjrq4dnd6 1 3 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : f09dr1dpgvfd3
SQL Text : SELECT T_ID ,T_DTS ,ST_NAME ,TT_NAME ,T_S_SYMB ,T_QTY ,T_EXEC_NAME ,T_CHRG ,S_NAME ,EX_NAME FROM TRADE ,STATUS_TYPE ,TRADE_TYPE ,SECURITY ,EXCHANGE WHERE T_CA_ID = :B1 AND ST_ID = T_ST_ID AND TT_ID = T_TT_ID AND S_SYMB = T_S_SYMB AND EX_ID = S_EX_ID ORDER BY T_DTS DESC FETCH FIRST 50 ROWS ONLY
Improvement Factor : 606.9x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 1038190026 470104
CPU Time (s): 1036655876 44873
Buffer Gets: 263013841 1901
Optimizer Cost: 319516 3564
Disk Reads: 0 7
Direct Writes: 0 0
Rows Processed: 11600 50
Executions: 233 1


PLANS SECTION


- Original
Plan Hash Value : 2114262247

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 319516
1 . VIEW 50 10850 319516 00:00:13
2 .. WINDOW SORT PUSHED RANK 3568 710032 319516 00:00:13
3 ... HASH JOIN 3568 710032 319514 00:00:13
4 .... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE_TYPE 5 75 7 00:00:01
5 .... HASH JOIN 3568 656512 319507 00:00:13
6 ..... TABLE ACCESS STORAGE FULL FIRST ROWS STATUS_TYPE 5 70 7 00:00:01
7 ..... HASH JOIN 3568 606560 319500 00:00:13
8 ...... MERGE JOIN 3425 291125 28 00:00:01
9 ....... TABLE ACCESS BY INDEX ROWID EXCHANGE 4 156 2 00:00:01
10 ........ INDEX FULL SCAN EXCHANGE_PK_IDX 4 1 00:00:01
11 ....... SORT JOIN 3425 157550 26 00:00:01
12 ........ TABLE ACCESS STORAGE FULL FIRST ROWS SECURITY 3425 157550 24 00:00:01
13 ...... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE 3568 303280 319471 00:00:13

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 2849586826

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 50 10850 3564 00:00:01
* 1 . VIEW 50 10850 3564 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 3530 702470 3564 00:00:01
* 3 ... HASH JOIN 3530 702470 3563 00:00:01
4 .... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE_TYPE 5 75 7 00:00:01
* 5 .... HASH JOIN 3530 649520 3555 00:00:01
6 ..... TABLE ACCESS STORAGE FULL FIRST ROWS STATUS_TYPE 5 70 7 00:00:01
* 7 ..... HASH JOIN 3530 600100 3548 00:00:01
8 ...... MERGE JOIN 3425 291125 28 00:00:01
9 ....... TABLE ACCESS BY INDEX ROWID EXCHANGE 4 156 2 00:00:01
10 ........ INDEX FULL SCAN EXCHANGE_PK_IDX 4 1 00:00:01
* 11 ....... SORT JOIN 3425 157550 26 00:00:01
12 ........ TABLE ACCESS STORAGE FULL FIRST ROWS SECURITY 3425 157550 24 00:00:01
13 ...... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 3530 300050 3520 00:00:01
* 14 ....... INDEX RANGE SCAN SYS_AI_1hjwdmk5q4mkz 3530 13 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )


Parsing Schema Name : TPCE
SQL ID : gjrcxjt029ubv
SQL Text : SELECT T_ID FROM TRADE WHERE T_CA_ID = :B2 AND T_DTS >= :B1 ORDER BY T_DTS ASC FETCH FIRST 1 ROW ONLY
Improvement Factor : 391.5x

Execution Statistics:
Original Plan
Auto Index Plan
Elapsed Time (s): 31215129 433742
CPU Time (s): 31155682 16325
Buffer Gets: 7924863 2925
Optimizer Cost: 319473 3521
Disk Reads: 0 10
Direct Writes: 0 0
Rows Processed: 7 1
Executions: 7 1


PLANS SECTION


- Original
Plan Hash Value : 1011005032

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 319473
1 . VIEW 1 39 319473 00:00:13
2 .. WINDOW SORT PUSHED RANK 2444 95316 319473 00:00:13
3 ... TABLE ACCESS STORAGE FULL FIRST ROWS TRADE 2444 95316 319471 00:00:13

Notes
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
Plan Hash Value : 1577166064

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 39 3521 00:00:01
* 1 . VIEW 1 39 3521 00:00:01
* 2 .. WINDOW SORT PUSHED RANK 3059 119301 3521 00:00:01
* 3 ... TABLE ACCESS BY INDEX ROWID BATCHED TRADE 3059 119301 3520 00:00:01
* 4 .... INDEX RANGE SCAN SYS_AI_1hjwdmk5q4mkz 3530 13 00:00:01

Predicate Information (identified by operation id):

Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )




ERRORS
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist
- ORA-00942: table or view does not exist