Don, Comments inline...
> Yes! IME, there ARE still problems in the CBO, especially with complex > subqueries. > I have more than a dozen systems where management insists on staying with > the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not work with it, they do not research it, and they do not understand the issues if technical people do not research, understand, and inform them. Management makes decisions based on information provided. That is their job. Bad information, bad decisions. > Every time we collect deep stats and histogram and switch optimizer_mode, > hundreds of statements generate poor plans. [TG]: Please, let's talk specific examples, not generalities. This list resolves specific examples almost every week, and never (in my recollection) has a resolution involved going to RBO. Someone please correct me if I've mis-spoken. To verify, some enterprising soul may choose to review the list archives going back over two years, which are available on "http://www.orafaq.com";. First of all, besides statistics, there are some init.ora parameters (besides OPTIMIZER_MODE) to be set appropriately, such as OPTIMIZER_INDEX_CACHING. > It would cost these clients many thousands of dollars to have adjusted these > plans, and management says "If it ain't broke, why fix it". [TG]: No doubt any application transitioning from RBO to CBO needs to be tested thoroughly. But how about the success stories of the CBO? How about all of the queries that were impossible to fix under the RBO but now magically performed well after implementing CBO, and how about the dozens of options for fixing bad situations using the myriad options available with the CBO? Function-based indexes? Materialized views and query rewrite? Etc, etc, etc... Personally, I can't understand why anyone would continue to bleed money away using the RBO. Certainly, legacy software that requires RBO should continue to use it until end-of-life. But advocating a return to the RBO for new applications is not rational. Again, please let's discuss specifics... > > We need look no further than Oracle Applications to see this issue. > Oracle made a big-deal about going to the CBO in 11i, yet when we look at > the SQL, a significant number of statement employ the "rule" hint! > Connect-the-dots and you can guess why the RBO IS NOT being removed from > Oracle10g. . . . [TG]: I can't even spell "10g", so I'll take your word for it... The OraApps 11i assertion did not sound right, so to verify I queried both the V$SQLAREA view as well as the STATSPACK repository (i.e. STATS$SQLTEXT) on a rather busy OraApps 11.5.8 system running Financials, ERP, HR/Payroll, Order Entry, and Inventory. The STATSPACK repository is only holding 14 days worth of data; I keep it purged pretty tight to keep it below 1Gb in size... In both V$SQLAREA and STATS$SQLTEXT, I found only nine (9) and eight (8) SQL statements, respectivley, using the "RULE" hint, all of which were querying the data dictionary objects only. 8-9 is not what I would call a significant number, not when V$SQLAREA has over 50,000 distinct SQL statements and STATS$SQLTEXT has almost 6,400 distinct SQL statements. Here is the query and results from the STATSPACK repository: SQL> break on hash_value SQL> select hash_value, sql_text from stats$sqltext 2 where upper(text_subset) like '%/*+%RULE%*/%' 3 order by hash_value, piece; HASH_VALUE SQL_TEXT ---------- ---------------------------------------------------------------- 296554613 Select /*+ RULE */ * FROM SYS.ALL_SYNONYMS WHERE ((OWNER = :own) OR (TABLE_OWNER = :own and OWNER = 'PUBLIC')) and db_link is nu ll and TABLE_NAME = 'OE_SOLD_TO_ORGS_V' ORDER BY SYNONYM_NAME 476032654 SELECT /*+ rule */O.SUBNAME PART_NAME,O.OBJ# OBJ_NUM FROM SYS .USER$ U,SYS.OBJ$ O WHERE U.NAME = :b1 AND O.OWNER# = U.USER# AND O.NAME = :b2 AND O.TYPE# = 19 ORDER BY PART_NAME 529775420 SELECT /*+ rule */C.NAME COL_NAME,C.TYPE# COL_TYPE,C.CHARSETFOR M COL_CSF,C.DEFAULT$ COL_DEF,C.PROPERTY COL_PROP,C.COL# COL_UNUM ,C.INTCOL# COL_INUM FROM SYS.USER$ U,SYS.OBJ$ O,SYS.COL$ C WH ERE U.NAME = :b1 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O .NAME = :b2 AND O.OBJ# = C.OBJ# 531307833 Select /*+ RULE */ t.*, o.status validity from SYS.ALL_TRIGGERS t, SYS.ALL_OBJECTS o where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER' and o. OWNER = :ow n AND ( t.table_name = 'OE_SOLD_TO_ORGS_V' OR o.ob ject_name = 'OE_SOLD_TO_ORGS_V' ) ORDER BY t.TRIGGER_NAME 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 2014200833 select /*+ RULE */ tabs.table_name, 'APPS' , partitioned, iot_type , TEMPORARY, table_type, table_type_owner ,tablespace_name from sys.user_All_tables tabs where 1=1 2079503989 select /*+ rule */ i.owner, i.index_name, i.uniqueness, c.co lumn_name from all_indexes i, all_ind_columns c where i.table_n ame = upper('ic_tran_pnd') and c.index_name = i.index_name and c .index_owner = i.owner order by i.owner, i.index_name, c.col umn_position 3923691408 SELECT /*+ rule */'"' || UI.NAME || '"' IND_OWNER,'"' || OI.N AME || '"' IND_NAME,OI.OBJ# OBJ_NUM FROM SYS.USER$ UT,SYS.OB J$ OT,SYS.IND$ I,SYS.OBJ$ OI,SYS.USER$ UI WHERE UT.NAME = :b1 AND UT.USER# = OT.OWNER# AND OT.NAME = :b2 AND OT.TYPE# = 2 A ND OT.OBJ# = I.BO# AND I.OBJ# = OI.OBJ# AND OI.OWNER# = UI.USE R# Here is the query and results from V$SQLAREA: SQL> select sql_text from v$sqlarea 2 where upper(sql_text) like '%/*+%RULE%*/%'; SQL_TEXT --------------------------------------------------------------------------- SELECT /*+ rule */'"' || OI.NAME || '"' FROM SYS.USER$ U,SYS.OBJ$ OT,SYS.IND$ I,SYS.OBJ$ OI WHERE U.NAME = :b1 AND OT.OWNER # = U.USER# AND OT.NAME = :b2 AND I.BO# = OT.OBJ# AND I.TYPE# = 4 AND I.OBJ# = OI.OBJ# SELECT /*+ rule */'"' || UI.NAME || '"' IND_OWNER,'"' || OI.NAME || '"' IND_NAME,OI.OBJ# OBJ_NUM FROM SYS.USER$ UT,SYS.OBJ$ OT,SYS.IND$ I,SYS.OBJ$ OI,SYS.USER$ UI WHERE UT.NAME = :b1 AND UT.USER# = OT.OWNER# AND OT.NAME = :b2 AND OT.TYPE# = 2 AND O T.OBJ# = I.BO# AND I.OBJ# = OI.OBJ# AND OI.OWNER# = UI.USER# SELECT /*+ rule */C.NAME COL_NAME,C.TYPE# COL_TYPE,C.CHARSETFORM COL_CSF,C.DEFAULT$ COL_DEF,C.PROPERTY COL_PROP,C.COL# COL_UNUM,C .INTCOL# COL_INUM FROM SYS.USER$ U,SYS.OBJ$ O,SYS.COL$ C WHERE U.NAME = :b1 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O.NAM E = :b2 AND O.OBJ# = C.OBJ# SELECT /*+ rule */O.SUBNAME PART_NAME,O.OBJ# OBJ_NUM FROM SYS.USER$ U,SYS.OBJ$ O WHERE U.NAME = :b1 AND O.OWNER# = U.USER# A ND O.NAME = :b2 AND O.TYPE# = 19 ORDER BY PART_NAME select /*+ RULE */ object_name from all_objects where object_name='DBMS_DEBUG' and object_type='PACKAGE' and owner='SYS' select /*+ RULE */ object_name from all_objects where object_name='DBMS_JAVA' and object_type='PACKAGE' and owner='SYS' select /*+ RULE */ object_name from all_objects where object_name='DBMS_PROFILER' and object_type='PACKAGE' and owner='SYS' select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, de nsity, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 > > JMHO. . . . > > Regards, > > Donald K. Burleson > www.dba-oracle.com > www.remote-dba.net JWIHO. . . . (Just What I Have Observed) -Tim -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
