=======
In the production database (8.1.7.4), there is a view called CILI_VW, which is created with a hint /*+ RULE */. I call it RBO view, the purpose of this experiment is to compare the effects between with and without this hints
RESULTS
=======
-----------------------------------------------------
Metric View Simple sql Complex query
-----------------------------------------------------
Elaps Time CBO 00:00:25.49 00:00:00.08
RBO 00:01:30.49 00:00:50.77
-----------------------------------------------------
Consistent CBO 100084 50
Get RBO 6555101 4456996
----------------------------------------------------
CONCLUSIONS
===========
1. RBO view performs far better than CBO view in 10g
2. Sql based on RBO maybe work well in 8i, however, when used in 10g, care must be taken
3. The complex query executed in less time than the simple query in this experiment indicates that for a query consisting of views, when it gets executed, Oracle does not generate these views first; instead, these views are replaced by sql statments. Oracle optimizes the expanded query as a whole
METHODS
=======
1. Created a new view called CILI_VW_CBO, which has same definition as the CILI_VW except without /*+ RULE */. This two views are referred to as RBO view and CBO view, respectively.
(a) RBO View
CILI_VW
SELECT /*+ RULE */
li.INVOICE_LINE_ITEM_ID,
li.INVOICE_LINE_ITEM_TYPE,
li.ACCOUNT_ID,
li.ITEM_ID,
li.FINANCE_TERM_ID,
li.INVOICE_ID,
g.GL_ACCOU
...
(b) CBO View
CILI_VW_CBO
SELECT
li.INVOICE_LINE_ITEM_ID,
li.INVOICE_LINE_ITEM_TYPE,
li.ACCOUNT_ID,
li.ITEM_ID,
li.FINANCE_TERM_ID,
li.INVOICE_ID,
g.GL_ACCOUNT_ID,
...
2. Comparing a simple select statement using AUTOTRACE
(a) For CBO view
select count(*) from cili_vw_cbo
Elapsed: 00:00:25.49
Execution Plan
----------------------------------------------------------
Plan hash value: 1691688664
-----------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-----------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 31 36417 (5) 00:07:18
1 SORT AGGREGATE 1 31
* 2 FILTER
* 3 HASH JOIN RIGHT OUTER 1571K 46M 52M 36417 (5) 00:07:18
4 TABLE ACCESS FULL CF_GL_ACCOUNT 3035K 17M 7805 (6) 00:01:34
* 5 FILTER
* 6 HASH JOIN OUTER 1514K 36M 43M 23200 (5) 00:04:39
* 7 FILTER
* 8 HASH JOIN OUTER 1459K 26M 12M 10481 (4) 00:02:06
9 INDEX FAST FULL SCAN XPK_CILI 746K 3644K 376 (6) 00:00:05
* 10 TABLE ACCESS FULL CF_GL_ACCOUNT 1459K 19M 7617 (4) 00:01:32
11 TABLE ACCESS FULL CF_GL_ACCOUNT 3035K 17M 7805 (6) 00:01:34
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("CR"."GL_ACCOUNT_TYPE_CODE",'CHR')='CHR')
3 - access("G"."GL_ACCOUNT_ID"="CR"."PARENT_GL_ACCOUNT_ID"(+))
5 - filter(NVL("CO"."GL_ACCOUNT_TYPE_CODE",'CHO')='CHO')
6 - access("G"."GL_ACCOUNT_ID"="CO"."PARENT_GL_ACCOUNT_ID"(+))
7 - filter(NVL("G"."GL_ACCOUNT_TYPE_CODE",'NRV')='NRV' AND NVL("G"."BEGINNING_BALANCE_AMT",1)>0)
8 - access("LI"."INVOICE_LINE_ITEM_ID"="G"."INVOICE_LINE_ITEM_ID"(+))
10 - filter("G"."INVOICE_LINE_ITEM_ID"(+) IS NOT NULL)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
100084 consistent gets
3007 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(b) For RBO view
select count(*) from cili_vw
Elapsed: 00:01:30.49
Execution Plan
----------------------------------------------------------
Plan hash value: 3993977882
-------------------------------------------------------------------
Id Operation Name
-------------------------------------------------------------------
0 SELECT STATEMENT
1 SORT AGGREGATE
* 2 FILTER
3 NESTED LOOPS OUTER
* 4 FILTER
5 NESTED LOOPS OUTER
* 6 FILTER
7 NESTED LOOPS OUTER
8 TABLE ACCESS FULL CILI
9 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 10 INDEX RANGE SCAN XIF504_CF_GL_ACCOUNT
11 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 12 INDEX RANGE SCAN XIF967_CF_GL_ACCOUNT
13 TABLE ACCESS BY INDEX ROWID CF_GL_ACCOUNT
* 14 INDEX RANGE SCAN XIF967_CF_GL_ACCOUNT
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("CO"."GL_ACCOUNT_TYPE_CODE",'CHO')='CHO')
4 - filter(NVL("CR"."GL_ACCOUNT_TYPE_CODE",'CHR')='CHR')
6 - filter(NVL("G"."BEGINNING_BALANCE_AMT",1)>0 AND
NVL("G"."GL_ACCOUNT_TYPE_CODE",'NRV')='NRV')
10 - access("LI"."INVOICE_LINE_ITEM_ID"="G"."INVOICE_LINE_ITEM_ID"(+))
12 - access("G"."GL_ACCOUNT_ID"="CR"."PARENT_GL_ACCOUNT_ID"(+))
14 - access("G"."GL_ACCOUNT_ID"="CO"."PARENT_GL_ACCOUNT_ID"(+))
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6555101 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3. A Complex query
SELECT DISTINCT
it.item_id "itemId",
it.model_number "itemModelNumber",
it.serial_number "itemSerialNumber",
decode(it.item_status_code,null,'',
CodeTable.getDescription(it.item_status_code,'Item Status')) "itemStatus",
it.description "itemDescription",
inv.invoice_id "invoiceId",
inv.invoice_number "invoiceNumber",
ac.account_id "invFinAccountId",
ac.name "invFinAccountName",
lp.lender_product_id "invFinLoanId",
lp.name "invFinLoanName",
bs.entity_id "businessId",
bs.name "businessName",
it.vin_number "chassisNumber",
it.stock_number "stockNumber",
ilit.invoice_line_item_id "invLineItemId"
FROM
cf_item it,
cili_vw_cbo ilit, -- or: cili_vw
cf_invoice inv,
cf_account ac,
cf_lender_product_vw lp,
cf_business_vw bs
WHERE upper(lp.lender_product_type_code) = 'DLC'
AND upper(inv.invoice_status_code) = 'PFD'
AND UPPER(ilit.invoice_line_item_status_code)<>'REM'
AND UPPER(it.serial_number) = UPPER('W08124')
AND it.item_id = ilit.item_id
AND ilit.invoice_id = inv.invoice_id
AND inv.account_id = ac.account_id
AND ac.lender_product_id = lp.lender_product_id
AND lp.entity_id = bs.entity_id
ORDER BY
bs.name ASC
/
SQL> @sql_cbo.sql
Elapsed: 00:00:00.08
....
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
50 consistent gets
10 physical reads
0 redo size
1738 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> @sql_rbo.sql
Elapsed: 00:00:50.77
Execution Plan
....
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
4456996 consistent gets
0 physical reads
0 redo size
1738 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
No comments:
Post a Comment