Search My Oracle Blog

Custom Search

14 April, 2008

Complex View Merging -- 7

My final test. What happens if I disable Cost Based Transformation ?


SQL> alter session set "_optimizer_cost_based_transformation"=OFF;

Session altered.

SQL>
SQL> select /* disabled cost_based_trasnfm */ m.author, m.book_name from my_fat_book_list m
2 where
3 m.published_date
4 = (select max(l.published_date)
5 from library_fat_book_list l
6 where l.author=m.author
7 and l.book_name=m.book_name)
8 order by 1,2
9 /
(output deleted)
123 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1796690992

-------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
-------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 101K 7666K 3315 (2) 00:00:40
1 MERGE JOIN 101K 7666K 3315 (2) 00:00:40
2 SORT JOIN 101K 3982K 10M 3310 (2) 00:00:40
3 VIEW VW_SQ_1 101K 3982K 2250 (2) 00:00:28
4 HASH GROUP BY 101K 6969K 16M 2250 (2) 00:00:28
5 TABLE ACCESS FULL LIBRARY_FAT_BOOK_LIST 101K 6969K 556 (2) 00:00:07
* 6 SORT JOIN 260 9620 5 (20) 00:00:01
7 TABLE ACCESS FULL MY_FAT_BOOK_LIST 260 9620 4 (0) 00:00:01
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("AUTHOR"="M"."AUTHOR" AND "BOOK_NAME"="M"."BOOK_NAME" AND
"M"."PUBLISHED_DATE"="VW_COL_1")
filter("BOOK_NAME"="M"."BOOK_NAME" AND "AUTHOR"="M"."AUTHOR" AND
"M"."PUBLISHED_DATE"="VW_COL_1")


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2470 consistent gets
0 physical reads
0 redo size
5107 bytes sent via SQL*Net to client
483 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
123 rows processed

SQL>




Much more expensive. The 10.2 Cost Based Transformation WAS better.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016