CBO????????
- by Liu Maclean(???)
???Itpub????????CBO??????????, ????????:
SQL> create table maclean1 as select * from dba_objects;
Table created.
SQL> update maclean1 set status='INVALID' where owner='MACLEAN';
2 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_maclean1 on maclean1(status);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN1',cascade=>true);
PL/SQL procedure successfully completed.
SQL> explain plan for select * from maclean1 where status='INVALID';
Explained.
SQL> set linesize 140 pagesize 1400
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 987568083
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11320 | 1028K| 85 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| MACLEAN1 | 11320 | 1028K| 85 (0)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='INVALID')
13 rows selected.
10053 trace
Access path analysis for MACLEAN1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MACLEAN1[MACLEAN1]
Column (#10): STATUS(
AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.500000
Table: MACLEAN1 Alias: MACLEAN1
Card: Original: 22639.000000 Rounded: 11320 Computed: 11319.50 Non Adjusted: 11319.50
Access Path: TableScan
Cost: 85.33 Resp: 85.33 Degree: 0
Cost_io: 85.00 Cost_cpu: 11935345
Resp_io: 85.00 Resp_cpu: 11935345
Access Path: index (AllEqRange)
Index: IND_MACLEAN1
resc_io: 185.00 resc_cpu: 8449916
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 185.24 Resp: 185.24 Degree: 1
Best:: AccessPath: TableScan
Cost: 85.33 Degree: 1 Resp: 85.33 Card: 11319.50 Bytes: 0
?????10053????????????,?????Density = 0.5 ?? 1/ NDV ???
??????????????STATUS='INVALID"???????????,
?????????????????
????”STATUS”=’INVALID’ condition???2?,?status??????,??????dbms_stats?????????????,???CBO????INDEX Range ind_maclean1,???????,??????opitimizer??????
?????????????????????????,????????,??????????status=’INVALID’???????card??,????????:
[oracle@vrh4 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 17 19:15:45 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter optimizer_fea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.2.0.2
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com & www.askmaclean.com
SQL> drop table maclean;
Table dropped.
SQL> create table maclean as select * from dba_objects;
Table created.
SQL> update maclean set status='INVALID' where owner='MACLEAN';
2 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_maclean on maclean(status);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN',cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 2');
PL/SQL procedure successfully completed.
???????2?bucket????, ???????????????
???Quest???Guy Harrison???????FREQUENCY????????,??????:
rem
rem Generate a histogram of data distribution in a column as recorded
rem in dba_tab_histograms
rem
rem Guy Harrison Jan 2010 : www.guyharrison.net
rem
rem hexstr function is from From http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707586567563
set pagesize 10000
set lines 120
set verify off
col char_value format a10 heading "Endpoint|value"
col bucket_count format 99,999,999 heading "bucket|count"
col pct format 999.99 heading "Pct"
col pct_of_max format a62 heading "Pct of|Max value"
rem col endpoint_value format 9999999999999 heading "endpoint|value"
CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER)
RETURN VARCHAR2
AS
l_str LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x'));
l_return VARCHAR2 (4000);
BEGIN
WHILE (l_str IS NOT NULL)
LOOP
l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx'));
l_str := SUBSTR (l_str, 3);
END LOOP;
RETURN (SUBSTR (l_return, 1, 6));
END;
/
WITH hist_data AS (
SELECT endpoint_value,endpoint_actual_value,
NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
endpoint_number,
endpoint_number,
endpoint_number
- NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
bucket_count
FROM dba_tab_histograms
JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
WHERE owner = '&owner'
AND table_name = '&table'
AND column_name = '&column'
AND histogram='FREQUENCY')
SELECT nvl(endpoint_actual_value,endpoint_value) endpoint_value ,
bucket_count,
ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
FROM hist_data;
WITH hist_data AS (
SELECT endpoint_value,endpoint_actual_value,
NVL(LAG (endpoint_value) OVER (ORDER BY endpoint_value),' ') prev_value,
endpoint_number,
endpoint_number,
endpoint_number
- NVL (LAG (endpoint_number) OVER (ORDER BY endpoint_value), 0)
bucket_count
FROM dba_tab_histograms
JOIN dba_tab_col_statistics USING (owner, table_name,column_name)
WHERE owner = '&owner'
AND table_name = '&table'
AND column_name = '&column'
AND histogram='FREQUENCY')
SELECT hexstr(endpoint_value) char_value,
bucket_count,
ROUND(bucket_count*100/SUM(bucket_count) OVER(),2) PCT,
RPAD(' ',ROUND(bucket_count*50/MAX(bucket_count) OVER()),'*') pct_of_max
FROM hist_data
ORDER BY endpoint_value;
?????,??????????FREQUENCY?????:
??dbms_stats ?????STATUS=’INVALID’ bucket count=9 percent = 0.04 ,??????10053 trace????????:
SQL> explain plan for select * from maclean where status='INVALID';
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 3087014066
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 837 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MACLEAN | 9 | 837 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MACLEAN | 9 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='INVALID')
??????????????CBO???????STATUS=’INVALID’?cardnality?? , ??????????? ,??index range scan??Full table scan?
????????????????10053 trace:
SQL> alter system flush shared_pool;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10053 trace name context forever ,level 1;
Statement processed.
SQL> explain plan for select * from maclean where status='INVALID';
Explained.
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for MACLEAN[MACLEAN]
Column (#10):
NewDensity:0.000199, OldDensity:0.000022 BktCnt:22640, PopBktCnt:22640, PopValCnt:2, NDV:2
???NewDensity= bucket_count / SUM(bucket_count) /2
Column (#10): STATUS(
AvgLen: 7 NDV: 2 Nulls: 0 Density: 0.000199
Histogram: Freq #Bkts: 2 UncompBkts: 22640 EndPtVals: 2
Table: MACLEAN Alias: MACLEAN
Card: Original: 22640.000000 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00
Access Path: TableScan
Cost: 85.30 Resp: 85.30 Degree: 0
Cost_io: 85.00 Cost_cpu: 10804625
Resp_io: 85.00 Resp_cpu: 10804625
Access Path: index (AllEqRange)
Index: IND_MACLEAN
resc_io: 2.00 resc_cpu: 20763
ix_sel: 0.000398 ix_sel_with_filters: 0.000398
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IND_MACLEAN
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 9.00 Bytes: 0
???????????2 bucket?????CBO????????????,???????????????????,???dbms_stats.DEFAULT_METHOD_OPT?????????????????????
???dbms_stats?????????????????????col_usage$??????predicate???????,??col_usage$??<????????SMON??(?):??col_usage$????>?
??????????dbms_stats????????,col_usage$????????????predicate???,??dbms_stats??????????????????, ?:
SQL> drop table maclean;
Table dropped.
SQL> create table maclean as select * from dba_objects;
Table created.
SQL> update maclean set status='INVALID' where owner='MACLEAN';
2 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_maclean on maclean(status);
Index created.
??dbms_stats??method_opt??maclean?
SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');
PL/SQL procedure successfully completed.
@histogram.sql
Enter value for owner: SYS
old 12: WHERE owner = '&owner'
new 12: WHERE owner = 'SYS'
Enter value for table: MACLEAN
old 13: AND table_name = '&table'
new 13: AND table_name = 'MACLEAN'
Enter value for column: STATUS
old 14: AND column_name = '&column'
new 14: AND column_name = 'STATUS'
no rows selected
????col_usage$?????,????????status?????
declare
begin
for i in 1..500 loop
execute immediate ' alter system flush shared_pool';
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
execute immediate 'select count(*) from maclean where status=''INVALID'' ' ;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select obj# from obj$ where name='MACLEAN';
OBJ#
----------
97215
SQL> select * from col_usage$ where OBJ#=97215;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
97215 1 1 0 0 0 0 0 17-OCT-11
97215 10 499 0 0 0 0 0 17-OCT-11
SQL> exec dbms_stats.gather_table_stats('SYS','MACLEAN');
PL/SQL procedure successfully completed.
@histogram.sql
Enter value for owner: SYS
Enter value for table: MACLEAN
Enter value for column: STATUS
Endpoint bucket Pct of
value count Pct Max value
---------- ----------- ------- --------------------------------------------------------------
INVALI 2 .04
VALIC3 5,453 99.96 *************************************************
