Saturday, September 26, 2020

Scripts to demo 19c autoindex features using Swingbench OE workloads

 Scripts:

 select interval, enabled from DBA_AUTOTASK_SCHEDULE_CONTROL where TASK_NAME='Auto Index Task';
 exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task', 'INTERVAL',180);
 EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SOE',null);
 EXEC DBMS_AUTO_INDEX.drop_auto_indexes('SOE',NULL,true);
 select * from dba_indexes where owner='SOE';
-- commit is required for repeating testing, otherwise AUTO INDEX Task blocked by this session
 commit;

 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','SOE');
 select * from DBA_AUTO_INDEX_CONFIG;
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

select * from dba_auto_index_executions;
select * from dba_auto_index_ind_actions;

SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF

-- report last 30 minutes auto index activities
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => SYSTIMESTAMP-1/48,
         activity_end   => SYSTIMESTAMP,
         type           => 'TEXT',
         section        => 'ALL',
         "LEVEL"        => 'ALL')
FROM   dual;

-- report auto index activities between 16:30 to 17:30
SELECT DBMS_AUTO_INDEX.report_activity(
         activity_start => TO_TIMESTAMP('202009261630','YYYYMMDDHH24MI'),
         activity_end   => TO_TIMESTAMP('202009261730','YYYYMMDDHH24MI'),
         type           => 'TEXT',
         section        => 'ALL',
         "LEVEL"        => 'ALL')
FROM   dual;
SQL> SET LONG 1000000 LONGCHUNKSIZE 1000000SQL> SET LINESIZE 1000 PAGESIZE 0
SQL> SET TRIM ON TRIMSPOOL ON
SQL> SET ECHO OFF FEEDBACK OFF
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 26-SEP-2020 16:30:00
 Activity end                 : 26-SEP-2020 17:30:00
 Executions completed         : 4
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 8
 Indexes created (visible / invisible)         : 8 (5 / 3)
 Space used (visible / invisible)              : 150.08 MB (134.22 MB / 15.86 MB)
 Indexes dropped                               : 0
 SQL statements verified                       : 14
 SQL statements improved (improvement factor)  : 6 (55.3x)
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 19.5x
-------------------------------------------------------------------------------

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 |
--------------------------------------------------------------------------------------------
| SOE   | PRODUCT_INFORMATION | * SYS_AI_b9k5zyq0mjwf5 | CATEGORY_ID | B-TREE | NONE       |
--------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : SOE
 SQL ID               : 29qp10usqkqh0
 SQL Text             : SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE,
                      CUSTOMERS.CUST_FIRST_NAME, CUSTOMERS.CUST_LAST_NAME FROM
                      (SELECT ORDERS.ORDER_TOTAL, ORDERS.SALES_REP_ID,
                      ORDERS.ORDER_DATE, ORDERS.CUSTOMER_ID, RANK() OVER (ORDER
                      BY ORDERS.ORDER_TOTAL DESC) SAL_RANK FROM ORDERS WHERE
                      ORDERS.SALES_REP_ID = :B1 ...
 Improvement Factor   : 2578.6x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  84415551                      24028
 CPU Time (s):      40248498                      8008
 Buffer Gets:       8257774                       27
 Optimizer Cost:    8947                          9
 Disk Reads:        0                             2
 Direct Writes:     0                             0
 Rows Processed:    10543                         0
 Executions:        385                           1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 3619984409

-----------------------------------------------------------------------------------------
| Id | Operation                       | Name         | Rows | Bytes  | Cost | Time     |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |              |      |        | 8443 |          |
|  1 |   HASH JOIN                     |              | 1459 | 124015 | 8443 | 00:00:01 |
|  2 |    NESTED LOOPS                 |              | 1459 | 124015 | 8443 | 00:00:01 |
|  3 |     NESTED LOOPS                |              | 1459 | 124015 | 8443 | 00:00:01 |
|  4 |      STATISTICS COLLECTOR       |              |      |        |      |          |
|  5 |       VIEW                      |              | 1459 |  94835 | 5524 | 00:00:01 |
|  6 |        WINDOW SORT PUSHED RANK  |              | 1459 |  35016 | 5524 | 00:00:01 |
|  7 |         TABLE ACCESS FULL       | ORDERS       | 1459 |  35016 | 5523 | 00:00:01 |
|  8 |      INDEX UNIQUE SCAN          | CUSTOMERS_PK |    1 |        |    1 | 00:00:01 |
|  9 |     TABLE ACCESS BY INDEX ROWID | CUSTOMERS    |    1 |     20 |    2 | 00:00:01 |
| 10 |    TABLE ACCESS FULL            | CUSTOMERS    |    1 |     20 |    2 | 00:00:01 |
-----------------------------------------------------------------------------------------

Notes
-----
- optimizer_use_stats_on_conventional_dml = yes
- This is an adaptive plan


- With Auto Indexes
-----------------------------
 Plan Hash Value  : 3900469033

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                      |    2 |   170 |    9 | 00:00:01 |
|   1 |   NESTED LOOPS                            |                      |    2 |   170 |    9 | 00:00:01 |
|   2 |    NESTED LOOPS                           |                      |    2 |   170 |    9 | 00:00:01 |
| * 3 |     VIEW                                  |                      |    2 |   130 |    5 | 00:00:01 |
| * 4 |      WINDOW SORT PUSHED RANK              |                      |    2 |    48 |    5 | 00:00:01 |
|   5 |       TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS               |    2 |    48 |    4 | 00:00:01 |
| * 6 |        INDEX RANGE SCAN                   | SYS_AI_gbwwy984mc1ft |    1 |       |    3 | 00:00:01 |
| * 7 |     INDEX UNIQUE SCAN                     | CUSTOMERS_PK         |    1 |       |    1 | 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID            | CUSTOMERS            |    1 |    20 |    2 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("TT"."SAL_RANK"<=10)
* 4 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("ORDERS"."ORDER_TOTAL") DESC )<=10)
* 6 - access("ORDERS"."SALES_REP_ID"=:B1)
* 7 - access("CUSTOMERS"."CUSTOMER_ID"="TT"."CUSTOMER_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  : SOE
 SQL ID               : 7hk2m2702ua0g
 SQL Text             : WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID
                      FROM ORDERS WHERE ORDER_STATUS <= 4 AND WAREHOUSE_ID =
                      :B1 AND ROWNUM < 10 ) SELECT O.ORDER_ID, OI.LINE_ITEM_ID,
                      OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORDER_MODE,
                      O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID,
                      O.PROMOTION_ID, C.CUSTOMER_ID...
 Improvement Factor   : 1.9x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  3638001                       267113
 CPU Time (s):      1098401                       28911
 Buffer Gets:       185829                        157
 Optimizer Cost:    197                           40
 Disk Reads:        543                           16
 Direct Writes:     0                             0
 Rows Processed:    835                           50
 Executions:        835                           1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 2307454521

--------------------------------------------------------------------------------------------------
| Id | Operation                               | Name           | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                        |                |      |       |  199 |          |
|  1 |   HASH JOIN OUTER                       |                |   45 |  5580 |  199 | 00:00:01 |
|  2 |    NESTED LOOPS OUTER                   |                |   45 |  5580 |  199 | 00:00:01 |
|  3 |     STATISTICS COLLECTOR                |                |      |       |      |          |
|  4 |      HASH JOIN                          |                |    9 |   936 |  163 | 00:00:01 |
|  5 |       NESTED LOOPS                      |                |    9 |   936 |  163 | 00:00:01 |
|  6 |        STATISTICS COLLECTOR             |                |      |       |      |          |
|  7 |         HASH JOIN                       |                |    9 |   504 |  145 | 00:00:01 |
|  8 |          NESTED LOOPS                   |                |    9 |   504 |  145 | 00:00:01 |
|  9 |           STATISTICS COLLECTOR          |                |      |       |      |          |
| 10 |            VIEW                         |                |    9 |   117 |  127 | 00:00:01 |
| 11 |             COUNT STOPKEY               |                |      |       |      |          |
| 12 |              TABLE ACCESS FULL          | ORDERS         |   10 |   130 |  127 | 00:00:01 |
| 13 |           TABLE ACCESS BY INDEX ROWID   | ORDERS         |    1 |    43 |    2 | 00:00:01 |
| 14 |            INDEX UNIQUE SCAN            | ORDER_PK       |    1 |       |    1 | 00:00:01 |
| 15 |          TABLE ACCESS FULL              | ORDERS         |    1 |    43 |    2 | 00:00:01 |
| 16 |        TABLE ACCESS BY INDEX ROWID      | CUSTOMERS      |    1 |    48 |    2 | 00:00:01 |
| 17 |         INDEX UNIQUE SCAN               | CUSTOMERS_PK   |    1 |       |    1 | 00:00:01 |
| 18 |       TABLE ACCESS FULL                 | CUSTOMERS      |    1 |    48 |    2 | 00:00:01 |
| 19 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS    |    5 |   100 |    4 | 00:00:01 |
| 20 |      INDEX RANGE SCAN                   | ORDER_ITEMS_PK |    5 |       |    2 | 00:00:01 |
| 21 |    TABLE ACCESS FULL                    | ORDER_ITEMS    |    5 |   100 |    4 | 00:00:01 |
--------------------------------------------------------------------------------------------------

Notes
-----
- optimizer_use_stats_on_conventional_dml = yes
- This is an adaptive plan


- With Auto Indexes
-----------------------------
 Plan Hash Value  : 420986395

-------------------------------------------------------------------------------------------------------------
| Id   | Operation                                  | Name                 | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                           |                      |    5 |   620 |   40 | 00:00:01 |
|    1 |   NESTED LOOPS OUTER                       |                      |    5 |   620 |   40 | 00:00:01 |
|    2 |    NESTED LOOPS                            |                      |    1 |   104 |   36 | 00:00:01 |
|    3 |     NESTED LOOPS                           |                      |    1 |    56 |   34 | 00:00:01 |
|    4 |      VIEW                                  |                      |    1 |    13 |   32 | 00:00:01 |
|  * 5 |       COUNT STOPKEY                        |                      |      |       |      |          |
|  * 6 |        TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS               |    1 |    13 |   32 | 00:00:01 |
|  * 7 |         INDEX RANGE SCAN                   | SYS_AI_3z00frhp9vd91 | 1436 |       |    3 | 00:00:01 |
|    8 |      TABLE ACCESS BY INDEX ROWID           | ORDERS               |    1 |    43 |    2 | 00:00:01 |
|  * 9 |       INDEX UNIQUE SCAN                    | ORDER_PK             |    1 |       |    1 | 00:00:01 |
|   10 |     TABLE ACCESS BY INDEX ROWID            | CUSTOMERS            |    1 |    48 |    2 | 00:00:01 |
| * 11 |      INDEX UNIQUE SCAN                     | CUSTOMERS_PK         |    1 |       |    1 | 00:00:01 |
|   12 |    TABLE ACCESS BY INDEX ROWID BATCHED     | ORDER_ITEMS          |    5 |   100 |    4 | 00:00:01 |
| * 13 |     INDEX RANGE SCAN                       | ORDER_ITEMS_PK       |    5 |       |    2 | 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter(ROWNUM<10)
* 6 - filter("ORDER_STATUS"<=4)
* 7 - access("WAREHOUSE_ID"=:B1)
* 9 - access("NTP"."ORDER_ID"="O"."ORDER_ID")
* 11 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
* 13 - access("OI"."ORDER_ID"="O"."ORDER_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  : SOE
 SQL ID               : 7t0959msvyt5g
 SQL Text             : SELECT ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID,
                      ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID,
                      WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY,
                      WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID,
                      CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID FROM ORDERS
                      WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
 Improvement Factor   : 1243.5x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  617460332                     1319853
 CPU Time (s):      285638621                     7876
 Buffer Gets:       65646755                      34
 Optimizer Cost:    5798                          12
 Disk Reads:        547                           1
 Direct Writes:     0                             0
 Rows Processed:    4776                          0
 Executions:        3084                          1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 335441244

-----------------------------------------------------------------------
| Id | Operation            | Name   | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT     |        |      |       | 5520 |          |
|  1 |   COUNT STOPKEY      |        |      |       |      |          |
|  2 |    TABLE ACCESS FULL | ORDERS |    9 |   846 | 5520 | 00:00:01 |
-----------------------------------------------------------------------

Notes
-----
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1119417062

--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    9 |   846 |   12 | 00:00:01 |
| * 1 |   COUNT STOPKEY                        |                      |      |       |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS               |    9 |   846 |   12 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_5p2zapcmkj174 |    9 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)


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


-------------------------------------------------------------------------------
 Parsing Schema Name  : SOE
 SQL ID               : 7ws837zynp1zv
 SQL Text             : SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER,
                      EXPIRY_DATE, IS_VALID, SECURITY_CODE FROM CARD_DETAILS
                      WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
 Improvement Factor   : 752.3x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  1058848442                    797551
 CPU Time (s):      483284263                     7401
 Buffer Gets:       83307704                      31
 Optimizer Cost:    2755                          4
 Disk Reads:        210                           2
 Direct Writes:     0                             0
 Rows Processed:    12452                         0
 Executions:        8267                          1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 2597291669

-----------------------------------------------------------------------------
| Id | Operation            | Name         | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |              |      |       | 2755 |          |
|  1 |   COUNT STOPKEY      |              |      |       |      |          |
|  2 |    TABLE ACCESS FULL | CARD_DETAILS |    9 |   360 | 2755 | 00:00:01 |
-----------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1494990609

--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    2 |    80 |    4 | 00:00:01 |
| * 1 |   COUNT STOPKEY                        |                      |      |       |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | CARD_DETAILS         |    2 |    80 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_dt4w4vr174j9m |    1 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)


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


-------------------------------------------------------------------------------
 Parsing Schema Name  : SOE
 SQL ID               : g81cbrq5yamf5
 SQL Text             : SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED,
                      HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY,
                      POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID =
                      :B2 AND ROWNUM < :B1
 Improvement Factor   : 1327.8x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  1944433630                    952727
 CPU Time (s):      950376552                     7150
 Buffer Gets:       195665481                     31
 Optimizer Cost:    4691                          4
 Disk Reads:        15                            2
 Direct Writes:     0                             0
 Rows Processed:    17204                         0
 Executions:        11353                         1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 1286489376

--------------------------------------------------------------------------
| Id | Operation            | Name      | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |           |      |       | 4690 |          |
|  1 |   COUNT STOPKEY      |           |      |       |      |          |
|  2 |    TABLE ACCESS FULL | ADDRESSES |    9 |   657 | 4690 | 00:00:01 |
--------------------------------------------------------------------------

- With Auto Indexes
-----------------------------
 Plan Hash Value  : 1527416462

--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Rows | Bytes | Cost | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |    2 |   146 |    4 | 00:00:01 |
| * 1 |   COUNT STOPKEY                        |                      |      |       |      |          |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | ADDRESSES            |    2 |   146 |    4 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_4bz3nuupj3kt5 |    1 |       |    3 | 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)


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


-------------------------------------------------------------------------------
 Parsing Schema Name  : SOE
 SQL ID               : gkxxkghxubh1a
 SQL Text             : SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID,
                      SUM(ORDER_TOTAL), COUNT(1) FROM ORDERS, WAREHOUSES WHERE
                      ORDERS.WAREHOUSE_ID = WAREHOUSES.WAREHOUSE_ID AND
                      WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY
                      CUBE(ORDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID)
 Improvement Factor   : 7.5x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  31754462                      247888
 CPU Time (s):      14927141                      12874
 Buffer Gets:       2926232                       1235
 Optimizer Cost:    5528                          1223
 Disk Reads:        0                             3
 Direct Writes:     0                             0
 Rows Processed:    1152                          8
 Executions:        144                           1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 2692802960

---------------------------------------------------------------------------------
| Id | Operation               | Name          | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |               |      |       | 5527 |          |
|  1 |   SORT GROUP BY         |               |    2 |    36 | 5527 | 00:00:01 |
|  2 |    GENERATE CUBE        |               |    2 |    36 | 5527 | 00:00:01 |
|  3 |     SORT GROUP BY       |               |    2 |    36 | 5527 | 00:00:01 |
|  4 |      NESTED LOOPS       |               | 1431 | 25758 | 5526 | 00:00:01 |
|  5 |       INDEX UNIQUE SCAN | WAREHOUSES_PK |    1 |     4 |    1 | 00:00:01 |
|  6 |       TABLE ACCESS FULL | ORDERS        | 1431 | 20034 | 5525 | 00:00:01 |
---------------------------------------------------------------------------------

Notes
-----
- optimizer_use_stats_on_conventional_dml = yes


- With Auto Indexes
-----------------------------
 Plan Hash Value  : 3836151239

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                      |    2 |    36 | 1223 | 00:00:01 |
|   1 |   SORT GROUP BY                           |                      |    2 |    36 | 1223 | 00:00:01 |
|   2 |    GENERATE CUBE                          |                      |    2 |    36 | 1223 | 00:00:01 |
|   3 |     SORT GROUP BY                         |                      |    2 |    36 | 1223 | 00:00:01 |
|   4 |      NESTED LOOPS                         |                      | 1436 | 25848 | 1222 | 00:00:01 |
| * 5 |       INDEX UNIQUE SCAN                   | WAREHOUSES_PK        |    1 |     4 |    1 | 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS               | 1436 | 20104 | 1221 | 00:00:01 |
| * 7 |        INDEX RANGE SCAN                   | SYS_AI_3z00frhp9vd91 | 1261 |       |    4 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("WAREHOUSES"."WAREHOUSE_ID"=:B1)
* 7 - access("ORDERS"."WAREHOUSE_ID"=:B1)


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


-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

ERRORS
---------------------------------------------------------------------------------------------
No errors found.
-------------------------------------

No comments:

Post a Comment