| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Should we stop analyzing?
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 */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: tim_at_sagelogix.com 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: ListGuru_at_fatcity.com (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).Received on Sun Jan 11 2004 - 22:49:25 CST
 |
 |