1 SQL_ID 41v45z3dtqa2s, child number 0
2 -------------------------------------
3 select * from CUSTOMERS C, ORDERS O WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
4 -- join predicate AND C.customer_id = :v1 -- filter predicate and
5 O.order_status = 'PENDING' --filter predicate order by order_date --
6 sorting
7
8 Plan hash value: 3311696933
9
10 ----------------------------------------------------------------------------------------------
11 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
12 ----------------------------------------------------------------------------------------------
13 | 0 | SELECT STATEMENT | | | | 8 (100)| |
14 | 1 | SORT ORDER BY | | 8 | 1560 | 8 (13)| 00:00:01 |
15 | 2 | NESTED LOOPS | | 8 | 1560 | 7 (0)| 00:00:01 |
16 | 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 150 | 2 (0)| 00:00:01 |
17 |* 4 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 |
18 |* 5 | TABLE ACCESS BY INDEX ROWID| ORDERS | 8 | 360 | 5 (0)| 00:00:01 |
19 |* 6 | INDEX RANGE SCAN | CUSTOMER_IDX | 16 | | 2 (0)| 00:00:01 |
20 ----------------------------------------------------------------------------------------------
21
22 Peeked Binds (identified by position):
23 --------------------------------------
24
25 1 - :V1 (NUMBER): 1001
26
27 Predicate Information (identified by operation id):
28 ---------------------------------------------------
29
30 4 - access("C"."CUSTOMER_ID"=:V1)
31 5 - filter("O"."ORDER_STATUS"='PENDING')
32 6 - access("O"."CUSTOMER_ID"=:V1)
--
-- Detaillierteren Ausf�hrungsplan ermitteln:
--
SQL_ID d64pskw2p2qmd, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from CUSTOMERS C, ORDERS O WHERE
O.CUSTOMER_ID = C.CUSTOMER_ID -- join predicate AND C.customer_id = :v1
-- filter predicate and O.order_status = 'PENDING' --filter predicate
order by order_date -- sorting
Plan hash value: 3311696933
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.65 | 22300 | | | |
| 1 | SORT ORDER BY | | 1 | 8 | 1 |00:00:00.65 | 22300 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 8 | 1 |00:00:00.65 | 22300 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 8 | 1 |00:00:00.65 | 22297 | | | |
|* 6 | INDEX RANGE SCAN | CUSTOMER_IDX | 1 | 16 | 700K|00:00:00.92 | 1467 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CUSTOMER_ID"=:V1)
5 - filter("O"."ORDER_STATUS"='PENDING')
6 - access("O"."CUSTOMER_ID"=:V1)
--
-- Idee 1: Histogramme erzeugen
--
SQL_ID d64pskw2p2qmd, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from CUSTOMERS C, ORDERS O WHERE
O.CUSTOMER_ID = C.CUSTOMER_ID -- join predicate AND C.customer_id = :v1
-- filter predicate and O.order_status = 'PENDING' --filter predicate
order by order_date -- sorting
Plan hash value: 1247841217
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.50 | 21269 | 21264 | | | |
| 1 | SORT ORDER BY | | 1 | 348K| 1 |00:00:00.50 | 21269 | 21264 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 348K| 1 |00:00:00.50 | 21269 | 21264 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
|* 4 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | | | |
|* 5 | TABLE ACCESS FULL | ORDERS | 1 | 348K| 1 |00:00:00.50 | 21266 | 21261 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1001
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CUSTOMER_ID"=:V1)
5 - filter(("O"."ORDER_STATUS"='PENDING' AND "O"."CUSTOMER_ID"=:V1))
--
-- Idee 3: Index auf order_status
--
SQL_ID 83ub7k219611h, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from CUSTOMERS C, ORDERS O where
O.CUSTOMER_ID = C.CUSTOMER_ID and C.customer_id = :v1 and
O.order_status = 'PENDING' order by order_date
Plan hash value: 716330983
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 | 7 | | | |
| 1 | SORT ORDER BY | | 1 | 16 | 1 |00:00:00.01 | 7 | 7 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS | | 1 | 16 | 1 |00:00:00.01 | 7 | 7 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 1 | 1 |00:00:00.01 | 3 | 3 | | | |
|* 4 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 | 1 |00:00:00.01 | 2 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 16 | 1 |00:00:00.01 | 4 | 4 | | | |
|* 6 | INDEX RANGE SCAN | ORDERS_CUST_STATUS | 1 | 16 | 1 |00:00:00.01 | 3 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1001
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CUSTOMER_ID"=:V1)
6 - access("O"."ORDER_STATUS"='PENDING' AND "O"."CUSTOMER_ID"=:V1)
Tuning of SQL Statements: Execution Plans
Subscribe to:
Posts (Atom)
No comments:
Post a Comment