.
.
.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
23 November, 2009
21 November, 2009
SIZE specification for Column Histograms
When you use the method_opt parameter in DBMS_STATS.GATHER_TABLE_STATS specifying 'FOR [ALL] COLUMNS [column names] SIZE n' remember to lookup the syntax again. I made the mistake of not verifying the syntax for specifying SIZE when I name columns in the FOR ... SIZE clause.
Simply put, if you specify FOR ALL COLUMNS, the SIZE specification of 'n' does apply to every column. But if you specify individual column names and the SIZE is specified last, Oracle applies the SIZE specification only to the last named column while using it's own limit of 75 buckets where you do not specify a value for SIZE (and haven't specified SIZE AUTO). This means that if you have more than 75 (but less than 250) distinct values, Oracle creates a Height Balanced Histogram instead of the Frequency Histogram that you had desired. (Of course, if you have less than 75 distinct values, Oracle does create the right Frequency Histogram with the corresponding number of buckets to capture each distinct value).
Thus, in this demo, the first GATHER_TABLE_STATS run creates the proper histograms for all 5 columns because I specify "FOR ALL COLUMNS SIZE 250".
However, in the second run, when I name each individual column but do not attach a SIZE specification, Oracle trims columns C2_100D_NUMBERS and C4_100D_VARCHARS down to Height Balanced Histograms with 75 buckets only.
In the third run, I attache a SIZE specification to each column name and achieve the desired Histograms.
Therefore, be careful when you name individual columns in the method_opt parameter 'FOR COLUMNS ...'. Ensure that you specify the desired SIZE for each column separately (even if the SIZE is to be the same -- i.e. 250 in my case).
Simply put, if you specify FOR ALL COLUMNS, the SIZE specification of 'n' does apply to every column. But if you specify individual column names and the SIZE is specified last, Oracle applies the SIZE specification only to the last named column while using it's own limit of 75 buckets where you do not specify a value for SIZE (and haven't specified SIZE AUTO). This means that if you have more than 75 (but less than 250) distinct values, Oracle creates a Height Balanced Histogram instead of the Frequency Histogram that you had desired. (Of course, if you have less than 75 distinct values, Oracle does create the right Frequency Histogram with the corresponding number of buckets to capture each distinct value).
Thus, in this demo, the first GATHER_TABLE_STATS run creates the proper histograms for all 5 columns because I specify "FOR ALL COLUMNS SIZE 250".
However, in the second run, when I name each individual column but do not attach a SIZE specification, Oracle trims columns C2_100D_NUMBERS and C4_100D_VARCHARS down to Height Balanced Histograms with 75 buckets only.
In the third run, I attache a SIZE specification to each column name and achieve the desired Histograms.
SQL> col column_name format a30
SQL>
SQL> Drop Table TEST_SIZE_SPEC purge;
Table dropped.
SQL>
SQL> Create Table TEST_SIZE_SPEC (
2 c1_10d_numbers number not null,
3 c2_100d_numbers number not null,
4 c3_10d_varchars varchar2(5) not null,
5 c4_100d_varchars varchar2(5) not null,
6 c5_dummy number not null)
7 /
Table created.
SQL>
SQL> declare
2 i number;
3 begin
4 for i in 1..10000
5 loop
6 insert into TEST_SIZE_SPEC values (
7 mod(i,10), mod(i,100), 'C3_'||mod(i,10),'C4_'||mod(i,100), i
8 );
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(distinct(c1_10d_numbers)) from TEST_SIZE_SPEC;
COUNT(DISTINCT(C1_10D_NUMBERS))
-------------------------------
10
SQL> select count(distinct(c2_100d_numbers)) from TEST_SIZE_SPEC;
COUNT(DISTINCT(C2_100D_NUMBERS))
--------------------------------
100
SQL> select count(distinct(c3_10d_varchars)) from TEST_SIZE_SPEC;
COUNT(DISTINCT(C3_10D_VARCHARS))
--------------------------------
10
SQL> select count(distinct(c4_100d_varchars)) from TEST_SIZE_SPEC;
COUNT(DISTINCT(C4_100D_VARCHARS))
---------------------------------
100
SQL> select count(distinct(c5_dummy)) from TEST_SIZE_SPEC;
COUNT(DISTINCT(C5_DUMMY))
-------------------------
10000
SQL>
SQL>
SQL> REM =========================================================================================================
SQL> REM =========================================================================================================
SQL> -- Gather stats with 100% sampling but SIZE 250
SQL> exec dbms_stats.gather_table_stats('','TEST_SIZE_SPEC',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 250');
PL/SQL procedure successfully completed.
SQL>
SQL> -- Review column statistics, histograms
SQL> select column_name, sample_size, num_distinct, histogram, num_buckets
2 from user_tab_columns
3 where table_name = 'TEST_SIZE_SPEC'
4 order by column_name
5 /
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ----------- ------------ --------------- -----------
C1_10D_NUMBERS 10000 10 FREQUENCY 10
C2_100D_NUMBERS 10000 100 FREQUENCY 100
C3_10D_VARCHARS 10000 10 FREQUENCY 10
C4_100D_VARCHARS 10000 100 FREQUENCY 100
C5_DUMMY 10000 10000 HEIGHT BALANCED 250
SQL>
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'TEST_SIZE_SPEC'
4 group by column_name
5 order by column_name
6 /
COLUMN_NAME COUNT(*)
------------------------------ ----------
C1_10D_NUMBERS 10
C2_100D_NUMBERS 100
C3_10D_VARCHARS 10
C4_100D_VARCHARS 100
C5_DUMMY 251
SQL>
SQL> REM =========================================================================================================
SQL> REM =========================================================================================================
SQL> -- Now, reGather statistics but specifying column names
SQL> exec dbms_stats.gather_table_stats('','TEST_SIZE_SPEC',estimate_percent=>100,-
> method_opt=>'FOR COLUMNS C1_10D_NUMBERS, C2_100D_NUMBERS, C3_10D_VARCHARS, C4_100D_VARCHARS, C5_DUMMY SIZE 250');
PL/SQL procedure successfully completed.
SQL>
SQL> -- Review column statistics, histograms
SQL> select column_name, sample_size, num_distinct, histogram, num_buckets
2 from user_tab_columns
3 where table_name = 'TEST_SIZE_SPEC'
4 order by column_name
5 /
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ----------- ------------ --------------- -----------
C1_10D_NUMBERS 10000 10 FREQUENCY 10
C2_100D_NUMBERS 10000 100 HEIGHT BALANCED 75
C3_10D_VARCHARS 10000 10 FREQUENCY 10
C4_100D_VARCHARS 10000 100 HEIGHT BALANCED 75
C5_DUMMY 10000 10000 HEIGHT BALANCED 250
SQL>
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'TEST_SIZE_SPEC'
4 group by column_name
5 order by column_name
6 /
COLUMN_NAME COUNT(*)
------------------------------ ----------
C1_10D_NUMBERS 10
C2_100D_NUMBERS 76
C3_10D_VARCHARS 10
C4_100D_VARCHARS 76
C5_DUMMY 251
SQL>
SQL>
SQL> REM =========================================================================================================
SQL> REM =========================================================================================================
SQL> -- Now, reGather statistics but specifying column names and size each
SQL> exec dbms_stats.gather_table_stats('','TEST_SIZE_SPEC',estimate_percent=>100,-
> method_opt=>'FOR COLUMNS C1_10D_NUMBERS SIZE 250, C2_100D_NUMBERS SIZE 250, C3_10D_VARCHARS SIZE 250, C4_100D_VARCHARS SIZE 250, C5_DUMMY SIZE 250');
PL/SQL procedure successfully completed.
SQL>
SQL> -- Review column statistics, histograms
SQL> select column_name, sample_size, num_distinct, histogram, num_buckets
2 from user_tab_columns
3 where table_name = 'TEST_SIZE_SPEC'
4 order by column_name
5 /
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT HISTOGRAM NUM_BUCKETS
------------------------------ ----------- ------------ --------------- -----------
C1_10D_NUMBERS 10000 10 FREQUENCY 10
C2_100D_NUMBERS 10000 100 FREQUENCY 100
C3_10D_VARCHARS 10000 10 FREQUENCY 10
C4_100D_VARCHARS 10000 100 FREQUENCY 100
C5_DUMMY 10000 10000 HEIGHT BALANCED 250
SQL>
SQL> select column_name, count(*)
2 from user_tab_histograms
3 where table_name = 'TEST_SIZE_SPEC'
4 group by column_name
5 order by column_name
6 /
COLUMN_NAME COUNT(*)
------------------------------ ----------
C1_10D_NUMBERS 10
C2_100D_NUMBERS 100
C3_10D_VARCHARS 10
C4_100D_VARCHARS 100
C5_DUMMY 251
SQL>
Therefore, be careful when you name individual columns in the method_opt parameter 'FOR COLUMNS ...'. Ensure that you specify the desired SIZE for each column separately (even if the SIZE is to be the same -- i.e. 250 in my case).
UPDATE : As pointed out by Randolf, an alternative command is
"FOR COLUMNS SIZE 250 C1_10D_NUMBERS, C2_100D_NUMBERS, C3_10D_VARCHARS, C4_100D_VARCHARS, C5_DUMMY"
so that the SIZE n specification does not have to be repeated after every column name.
.
.
.
.
.
.
13 November, 2009
Sample Sizes : Table level and Column level
There appears to be some confusion about Sample Size that the "Auto" behaviour in DBMS_STATS determines.
The AUTO_SAMPLE_SIZE for the "ESTIMATE_PERCENT" parameter doesn't mean the size as "SIZE AUTO" for the "METHOD_OPT" parameter.
The former is for Table level statistics.
The latter is for Column Histograms. NULLs are not included in Histograms.
Here is a demonstration :
And why did Oracle now gather statistics on col_1 ? See my earlier postings on COL_USAGE influencing SIZE AUTO.
.
.
.
The AUTO_SAMPLE_SIZE for the "ESTIMATE_PERCENT" parameter doesn't mean the size as "SIZE AUTO" for the "METHOD_OPT" parameter.
The former is for Table level statistics.
The latter is for Column Histograms. NULLs are not included in Histograms.
Here is a demonstration :
SQL> drop table Test_Stats_Size purge;
Table dropped.
SQL>
SQL> create table Test_Stats_Size
2 as select object_id col_1, owner col_2, object_name col_3, created col_4
3 from dba_objects
4 where 1=2
5 /
Table created.
SQL> alter table test_stats_size add (padding_col varchar2(80));
Table altered.
SQL> alter table test_stats_size nologging;
Table altered.
SQL>
SQL> insert /*+ APPEND */ into test_stats_size
2 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
3 union all
4 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
5 union all
6 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
7 union all
8 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
9 union all
10 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
11 union all
12 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
13 union all
14 select mod(object_id,50), owner, object_name, created, dbms_random.string('X',60) from dba_objects
15 /
354501 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select count(*) from test_stats_size ;
COUNT(*)
----------
354501
SQL> select count(*) from test_stats_size where col_2 = 'HEMANT';
COUNT(*)
----------
245
SQL>
SQL>
SQL> -- Gather Stats with 100% sampling but Auto Size for columns
SQL> exec dbms_stats.gather_table_stats('','TEST_STATS_SIZE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, num_rows, sample_size, sample_size*100/num_rows Sample_Perc
2 from user_tables
3 where table_name = 'TEST_STATS_SIZE'
4 /
TABLE_NAME NUM_ROWS SAMPLE_SIZE SAMPLE_PERC
------------------------------ ---------- ----------- -----------
TEST_STATS_SIZE 354501 354501 100
SQL> REM We see above that Table Level Sampling is 100%
SQL>
SQL> select column_name, num_distinct, sample_size, histogram , num_buckets
2 from user_tab_columns
3 where table_name = 'TEST_STATS_SIZE'
4 order by column_name
5 /
COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ----------- --------------- -----------
COL_1 50 354494 NONE 1
COL_2 28 354501 FREQUENCY 28
COL_3 30311 354501 NONE 1
COL_4 1411 354501 NONE 1
PADDING_COL 354501 354501 NONE 1
SQL>
SQL> REM Was Column Level Sampling 100% ?
SQL> REM What type of Histograms and how many buckets did Oracle create ?
SQL> REM We see above that not necessarily all columns have the same number of rows sampled
SQL> REM Also, not necessarily all columns have a Histogram !
SQL>
SQL> REM Let's see what happens after a couple of queries on col_1
SQL>
SQL> select count(*) from test_stats_size where col_1 is null;
COUNT(*)
----------
7
SQL> select count(distinct(col_1)) from test_stats_size;
COUNT(DISTINCT(COL_1))
----------------------
50
SQL> select count(*) from test_stats_size where col_1 between 10 and 25;
COUNT(*)
----------
113092
SQL>
SQL> -- Gather Stats with 100% sampling but Auto Size for columns
SQL> exec dbms_stats.gather_table_stats('','TEST_STATS_SIZE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, num_rows, sample_size, sample_size*100/num_rows Sample_Perc
2 from user_tables
3 where table_name = 'TEST_STATS_SIZE'
4 /
TABLE_NAME NUM_ROWS SAMPLE_SIZE SAMPLE_PERC
------------------------------ ---------- ----------- -----------
TEST_STATS_SIZE 354501 354501 100
SQL> REM We see above that Table Level Sampling is 100%
SQL>
SQL> select column_name, num_distinct, sample_size, histogram , num_buckets
2 from user_tab_columns
3 where table_name = 'TEST_STATS_SIZE'
4 order by column_name
5 /
COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ----------- --------------- -----------
COL_1 50 354494 FREQUENCY 50
COL_2 28 354501 FREQUENCY 28
COL_3 30311 354501 NONE 1
COL_4 1411 354501 NONE 1
PADDING_COL 354501 354501 NONE 1
SQL>
SQL> REM However, Column Level Sampling and Histogram collection for col_1 is now different !
SQL> REM Also notice that the 7 NULLs in col_1 are excluded !
SQL>
SQL>
And why did Oracle now gather statistics on col_1 ? See my earlier postings on COL_USAGE influencing SIZE AUTO.
.
.
.
26 October, 2009
Some MORE Testing on Intra-Block Row Chaining
(note : See update on 31-Oct for a single-rows test on a 1,000 column table)
Following my first round of tests on Intra-Block Row Chaining (where a table has more than 255 columns), here are some results that are more suprising :
So a COUNT(col_vc1) is comparable to a COUNT(*).
Next we see :
Notice how the COUNT(col_vc94) seems to have done more than 5 times as many 'consistent gets' as the COUNT(col_vc93) ? (If you note from my previous tests, col_vc94 is the 96th column in the 350 column table (which makes it the 254th column if we count backwards).
Again, we see more than 5x 'consistent gets' with col_vc94 and col_vc348 as with col_vc1 and col_vc93.
The execution plan is a FullTableScan, yet the 'consistent gets' are so much higher.
What is my concern ? Whether the 'consistent gets' are *really* that high or not, they are likely significantly influencing "performance measures" -- e.g. whether AWR uses these (higher) figures or whether my own monitoring scripts use these (higher) figures, the fact would be that they may not be real indicators of performance ?
Soon, I will test with 400+, 500+ and 800+ columns !
.
.
UPDATE : 31-Oct-09
Here are some results with a 1,000 column table (in a 16KB tablespace). The table has been created with 1 single row :
I had as many as 3 additional 'consistent gets' !
For the same row, depending on which column I select, I see 1 or 2 or 3 or 4 consistent gets from the table.
.
.
.
Following my first round of tests on Intra-Block Row Chaining (where a table has more than 255 columns), here are some results that are more suprising :
SQL> set autotrace on
SQL> select count(*) from many_columns_table;
COUNT(*)
----------
50640
Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3019 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 3019 (1)| 00:00:37 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10370 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
SQL> select count(col_vc1) from many_columns_table;
COUNT(COL_VC1)
--------------
50640
Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3019 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3019 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
523 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
SQL>
So a COUNT(col_vc1) is comparable to a COUNT(*).
Next we see :
SQL> select count(col_vc93) from many_columns_table;
COUNT(COL_VC93)
---------------
50640
Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
524 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
SQL> select count(col_vc94) from many_columns_table;
COUNT(COL_VC94)
---------------
50640
Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61010 consistent gets
0 physical reads
0 redo size
524 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
SQL>
Notice how the COUNT(col_vc94) seems to have done more than 5 times as many 'consistent gets' as the COUNT(col_vc93) ? (If you note from my previous tests, col_vc94 is the 96th column in the 350 column table (which makes it the 254th column if we count backwards).
SQL> select count(distinct(col_vc1)) from many_columns_table;
COUNT(DISTINCT(COL_VC1))
------------------------
30892
Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3019 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3019 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
533 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(distinct(col_vc93)) from many_columns_table;
COUNT(DISTINCT(COL_VC93))
-------------------------
30965
Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(distinct(col_vc94)) from many_columns_table;
COUNT(DISTINCT(COL_VC94))
-------------------------
31029
Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
61010 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(distinct(col_vc348)) from many_columns_table;
COUNT(DISTINCT(COL_VC348))
--------------------------
30955
Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3042 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3042 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
61013 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Again, we see more than 5x 'consistent gets' with col_vc94 and col_vc348 as with col_vc1 and col_vc93.
The execution plan is a FullTableScan, yet the 'consistent gets' are so much higher.
What is my concern ? Whether the 'consistent gets' are *really* that high or not, they are likely significantly influencing "performance measures" -- e.g. whether AWR uses these (higher) figures or whether my own monitoring scripts use these (higher) figures, the fact would be that they may not be real indicators of performance ?
Soon, I will test with 400+, 500+ and 800+ columns !
.
.
UPDATE : 31-Oct-09
Here are some results with a 1,000 column table (in a 16KB tablespace). The table has been created with 1 single row :
SQL> analyze table many_columns_table compute statistics;
Table analyzed.
SQL> select blocks, num_rows, avg_row_len, chain_cnt from user_tables where table_name = 'MANY_COLUMNS_TABLE';
BLOCKS NUM_ROWS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ----------- ----------
5 1 4013 0
SQL> analyze table many_columns_table delete statistics;
Table analyzed.
SQL>
SQL> exec dbms_stats.gather_table_stats('','MANY_COLUMNS_TABLE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks, num_rows, avg_row_len from user_tables where table_name = 'MANY_COLUMNS_TABLE';
BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
5 1 4004
SQL>
SQL> create index many_cols_ndx_1 on many_columns_table(col_number) tablespace test_16k;
Index created.
SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE OOF
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
723 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
SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc235 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE NSP
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
724 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
SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc489 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE U85
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
724 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
SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc744 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE Z7R
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
724 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
SQL>
SQL> select col_vc1, col_vc3,col_vc5, col_vc998 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE JN8
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
724 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
SQL>
I had as many as 3 additional 'consistent gets' !
For the same row, depending on which column I select, I see 1 or 2 or 3 or 4 consistent gets from the table.
.
.
.
18 October, 2009
Some Testing on Intra-Block Row Chaining
Per MetaLink Note#1062906.6, Intra-Block Row Chaining occurs when a Table definition has more than 255 columns because each Row Piece can handle only 255 columns.
I have been running some (not conclusive ?) tests with dummy tables of 348 or so columns. In my tests, I find that if I access the 96th column or higher, then the counter for 'consistent gets' shows 2 gets for the table block. However, if I access the 95th column or lower, I have only 1 'consistent gets'.
These tests have been with columns of 3bytes/5bytes and 15bytes each. With 8KB blocks and 16KB blocks. With the row being accessed via an Index and by a ROWID lookup. An Indexed lookup has 4 'consistent gets', which include 3 for the index, for the 95th column or lower but 5 'consistent gets' for the 96th column or higher.
In all my test runs, I see 2 table block gets for the 96th column and 1 get for the 95th column.
Could this be related to the table definition ? (One number and date column followed by 346 varchar2 columns) ? I don't know for sure.
This is the output for a run where the varchar columns are defined as varchar2(5) with 3 characters stored (other tests have been with a definition of varchar2(15) and 13 characters stored) :
Column COL_VC93 is the 95th column in the table. Column COL_VC94 is the 96th column.
I have run the same test on 16K blocks. I confirm that the Chain_Count is 0 so the rows I fetch are not chained across blocks.
.
I have been running some (not conclusive ?) tests with dummy tables of 348 or so columns. In my tests, I find that if I access the 96th column or higher, then the counter for 'consistent gets' shows 2 gets for the table block. However, if I access the 95th column or lower, I have only 1 'consistent gets'.
These tests have been with columns of 3bytes/5bytes and 15bytes each. With 8KB blocks and 16KB blocks. With the row being accessed via an Index and by a ROWID lookup. An Indexed lookup has 4 'consistent gets', which include 3 for the index, for the 95th column or lower but 5 'consistent gets' for the 96th column or higher.
In all my test runs, I see 2 table block gets for the 96th column and 1 get for the 95th column.
Could this be related to the table definition ? (One number and date column followed by 346 varchar2 columns) ? I don't know for sure.
This is the output for a run where the varchar columns are defined as varchar2(5) with 3 characters stored (other tests have been with a definition of varchar2(15) and 13 characters stored) :
Column COL_VC93 is the 95th column in the table. Column COL_VC94 is the 96th column.
SQL>
SQL> create tablespace test_8K blocksize 8192
2 datafile '/oracle_fs/Databases/ORT24FS/test_8k.dbf' size 1000M autoextend on next 100M maxsize 2000M
3 extent management local autoallocate segment space management auto;
Tablespace created.
SQL>
SQL> drop table many_columns_table purge;
drop table many_columns_table purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table many_columns_table (
2 col_number number,
3 col_date date,
4 col_vc1 varchar2(5),
5 col_vc2 varchar2(5),
6 col_vc3 varchar2(5),
7 col_vc4 varchar2(5),
8 col_vc5 varchar2(5),
9 col_vc6 varchar2(5),
10 col_vc7 varchar2(5),
11 col_vc8 varchar2(5),
12 col_vc9 varchar2(5),
13 col_vc10 varchar2(5),
14 col_vc11 varchar2(5),
15 col_vc12 varchar2(5),
16 col_vc13 varchar2(5),
17 col_vc14 varchar2(5),
18 col_vc15 varchar2(5),
.....
344 col_vc341 varchar2(5),
345 col_vc342 varchar2(5),
346 col_vc343 varchar2(5),
347 col_vc344 varchar2(5),
348 col_vc345 varchar2(5),
349 col_vc346 varchar2(5),
350 col_vc347 varchar2(5),
351 col_vc348 varchar2(5)
352 )
353 tablespace test_8k
354 /
Table created.
SQL>
SQL>
SQL> insert into many_columns_table
2 select object_id, created,
3 dbms_random.string('X',3),
4 dbms_random.string('X',3),
5 dbms_random.string('X',3),
6 dbms_random.string('X',3),
7 dbms_random.string('X',3),
8 dbms_random.string('X',3),
9 dbms_random.string('X',3),
10 dbms_random.string('X',3),
11 dbms_random.string('X',3),
12 dbms_random.string('X',3),
13 dbms_random.string('X',3),
14 dbms_random.string('X',3),
15 dbms_random.string('X',3),
16 dbms_random.string('X',3),
17 dbms_random.string('X',3),
18 dbms_random.string('X',3),
...
341 dbms_random.string('X',3),
342 dbms_random.string('X',3),
343 dbms_random.string('X',3),
344 dbms_random.string('X',3),
345 dbms_random.string('X',3),
346 dbms_random.string('X',3),
347 dbms_random.string('X',3),
348 dbms_random.string('X',3),
349 dbms_random.string('X',3),
350 dbms_random.string('X',3)
351 from dba_objects ;
50640 rows created.
SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','MANY_COLUMNS_TABLE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks, num_rows, avg_row_len*num_rows*1.1/8192 from user_tables where table_name = 'MANY_COLUMNS_TABLE';
BLOCKS NUM_ROWS AVG_ROW_LEN*NUM_ROWS*1.1/8192
---------- ---------- -----------------------------
11117 50640 9546.92578
SQL> select blocks, bytes/1048576 from user_segments where segment_type = 'TABLE' and segment_name = 'MANY_COLUMNS_TABLE';
BLOCKS BYTES/1048576
---------- -------------
11264 88
SQL>
SQL> select count(*) from many_columns_table;
COUNT(*)
----------
50640
SQL> create index many_cols_ndx_1 on many_columns_table(col_number);
Index created.
SQL>
SQL> connect hemant/hemant
Connected.
SQL> set autotrace on
SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN 9ZP
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
1 physical reads
0 redo size
724 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
SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN 9ZP
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
724 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
SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN J7N
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
723 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
SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN J7N
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
723 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
SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN VXM
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
723 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
SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from many_columns_table where col_number = 1050;
COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL DW0 WUN VXM
Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MANY_COLS_NDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_NUMBER"=1050)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
723 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
SQL> set autotrace off
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2 and s.sid=(select sid from v$mystat where rownum = 1)
3 and (n.name = 'consistent gets' or n.name like 'table fetch%');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 339
table fetch by rowid 23
table fetch continued row 0
SQL>
SQL> select rowid from many_columns_table where col_number = 1050;
ROWID
------------------
AAANT0AAHAAAACvAAH
SQL>
SQL>
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2 and s.sid=(select sid from v$mystat where rownum = 1)
3 and (n.name = 'consistent gets' or n.name like 'table fetch%');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 344
table fetch by rowid 23
table fetch continued row 0
SQL> select col_vc93 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';
COL_V
-----
J7N
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2 and s.sid=(select sid from v$mystat where rownum = 1)
3 and (n.name = 'consistent gets' or n.name like 'table fetch%');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 345
table fetch by rowid 24
table fetch continued row 0
SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';
COL_V
-----
VXM
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2 and s.sid=(select sid from v$mystat where rownum = 1)
3 and (n.name = 'consistent gets' or n.name like 'table fetch%');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 347
table fetch by rowid 25
table fetch continued row 0
SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';
COL_V
-----
VXM
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
2 and s.sid=(select sid from v$mystat where rownum = 1)
3 and (n.name = 'consistent gets' or n.name like 'table fetch%');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 349
table fetch by rowid 26
table fetch continued row 0
SQL> analyze table many_columns_table compute statistics;
Table analyzed.
SQL> select blocks, num_rows, chain_cnt from user_tables where table_name ='MANY_COLUMNS_TABLE';
BLOCKS NUM_ROWS CHAIN_CNT
---------- ---------- ----------
11117 50640 0
SQL>
I have run the same test on 16K blocks. I confirm that the Chain_Count is 0 so the rows I fetch are not chained across blocks.
.
UPDATE : For more discussions on this issue follow the Oracle-L thread
.
15 October, 2009
Indexes Growing Larger After Rebuilds
Here's a note by Richard Foote about Indexes that can grow *larger* when rebuilt.
27 September, 2009
SQLs in Functions : Performance Impact
Taking the same case as I had in my previous demo "SQLs in Functions : Each Execution is Independent", I now demonstrate how calling a Function (which executes other SQL) for each row, from an SQL itself, can prove to be very detrimental to performance.
Recall from the previous demo that the INSERT statement had inserted 50,639 rows into the target table. This means that the Function, itself, was called 50,639 times. Could I have avoided having to execute the Function 50,639 times ? Yes, the INSERT statement could have used a Join instead of the Function.
These are the results of using the Function :
Notice how the query on MY_OBJ_TYPES_LOOKUP was executed 50,639 times !
Also, the CPU time for the "parent" INSERT statement itself is very high ! Each call to the Function required PLSQL. Within the Function, there was an SQL call. Oracle kept switching between SQL and PLSQL.
The INSERT took 16seconds to run.
These are the results of using a Join to perform the lookup :
The Join based INSERT took less than a second to run.
It was executed as a single SQL call. Much faster.
The function makes sense for single-row lookups (e.g. when called from a user-screen). It should NOT be used for a batch update which attempts the lookup more than a few times -- in this case, 50,639 times ! If possible, replace PLSQL calls with straightforward SQL.
(That is Tom Kyte's mantra as well : If it is possible to do it in SQL, do it in SQL !").
.
.
.
Recall from the previous demo that the INSERT statement had inserted 50,639 rows into the target table. This means that the Function, itself, was called 50,639 times. Could I have avoided having to execute the Function 50,639 times ? Yes, the INSERT statement could have used a Join instead of the Function.
********************************************************************************
These are the results of using the Function :
SQL> set timing on
SQL> insert into obj_names_and_descr
2 select owner, object_name, object_type, lookup_obj_descr (object_type)
3 from dba_objects
4 order by owner
5 /
50639 rows created.
Elapsed: 00:00:16.23
SQL>
insert into obj_names_and_descr
select owner, object_name, object_type, lookup_obj_descr (object_type)
from dba_objects
order by owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 0 11 0 0
Execute 1 11.24 11.73 1900 6164 4868 50639
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.27 11.79 1900 6175 4868 50639
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
50639 SORT ORDER BY (cr=359889 pr=0 pw=0 time=15694827 us)
50639 VIEW DBA_OBJECTS (cr=5385 pr=0 pw=0 time=1327397 us)
50639 UNION-ALL (cr=5385 pr=0 pw=0 time=1124834 us)
50638 FILTER (cr=5378 pr=0 pw=0 time=669040 us)
51827 HASH JOIN (cr=631 pr=0 pw=0 time=839952 us)
70 TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=858 us)
51827 TABLE ACCESS FULL OBJ$ (cr=625 pr=0 pw=0 time=312171 us)
1801 TABLE ACCESS BY INDEX ROWID IND$ (cr=4747 pr=0 pw=0 time=116996 us)
2368 INDEX UNIQUE SCAN I_IND1 (cr=2370 pr=0 pw=0 time=54337 us)(object id 39)
1 NESTED LOOPS (cr=3 pr=0 pw=0 time=101 us)
1 INDEX FULL SCAN I_LINK1 (cr=1 pr=0 pw=0 time=39 us)(object id 107)
1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=45 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=17 us)(object id 11)
********************************************************************************
SELECT OBJ_DESCR
FROM
MY_OBJ_TYPES_LOOKUP WHERE OBJ_TYPE = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50639 1.54 1.42 0 1 0 0
Fetch 50639 2.77 2.60 0 354473 0 50639
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101279 4.32 4.02 0 354474 0 50639
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64 (recursive depth: 1)
********************************************************************************
Notice how the query on MY_OBJ_TYPES_LOOKUP was executed 50,639 times !
Also, the CPU time for the "parent" INSERT statement itself is very high ! Each call to the Function required PLSQL. Within the Function, there was an SQL call. Oracle kept switching between SQL and PLSQL.
The INSERT took 16seconds to run.
********************************************************************************
These are the results of using a Join to perform the lookup :
SQL> set timing on
SQL> insert into obj_names_and_descr
2 select owner, object_name, object_type, obj_descr
3 from dba_objects, my_obj_types_lookup
4 where object_type = obj_type
5 order by owner
6 /
50639 rows created.
Elapsed: 00:00:00.64
SQL>
insert into obj_names_and_descr
select owner, object_name, object_type, obj_descr
from dba_objects, my_obj_types_lookup
where object_type = obj_type
order by owner
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.58 0.59 1900 6192 4873 50639
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.59 0.60 1900 6193 4873 50639
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Rows Row Source Operation
------- ---------------------------------------------------
50639 SORT ORDER BY (cr=5392 pr=0 pw=0 time=521559 us)
50639 HASH JOIN (cr=5392 pr=0 pw=0 time=1381766 us)
42 TABLE ACCESS FULL MY_OBJ_TYPES_LOOKUP (cr=7 pr=0 pw=0 time=175 us)
50639 VIEW DBA_OBJECTS (cr=5385 pr=0 pw=0 time=1021851 us)
50639 UNION-ALL (cr=5385 pr=0 pw=0 time=819289 us)
50638 FILTER (cr=5378 pr=0 pw=0 time=363509 us)
51827 HASH JOIN (cr=631 pr=0 pw=0 time=579119 us)
70 TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=534 us)
51827 TABLE ACCESS FULL OBJ$ (cr=625 pr=0 pw=0 time=156502 us)
1801 TABLE ACCESS BY INDEX ROWID IND$ (cr=4747 pr=0 pw=0 time=63373 us)
2368 INDEX UNIQUE SCAN I_IND1 (cr=2370 pr=0 pw=0 time=26582 us)(object id 39)
1 NESTED LOOPS (cr=3 pr=0 pw=0 time=86 us)
1 INDEX FULL SCAN I_LINK1 (cr=1 pr=0 pw=0 time=31 us)(object id 107)
1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=39 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=12 us)(object id 11)
********************************************************************************
The Join based INSERT took less than a second to run.
It was executed as a single SQL call. Much faster.
The function makes sense for single-row lookups (e.g. when called from a user-screen). It should NOT be used for a batch update which attempts the lookup more than a few times -- in this case, 50,639 times ! If possible, replace PLSQL calls with straightforward SQL.
(That is Tom Kyte's mantra as well : If it is possible to do it in SQL, do it in SQL !").
.
.
.
20 September, 2009
SQLs in Functions : Each Execution is Independent
Functions (Stored PLSQL code that return a single value at each call) are good for calculations, validation against business rules etc. But when you use them for lookups, you must watch out for underlying data being changed !
Thus, if you have a statement like "SELECT col_1, my_function(col_2) FROM ...", the function "my_function" is executed from every row that is returned from the query. Oracle guarantees Read Consistency at the Statement Level. However, insofar as the Function is executed, each execution of the Function is a separate SQL statement executed against the database. If underlying data has been changed in transaction from a different database session, the Function may start returning different values mid-way in a result set.
Here's a demonstration of how a Function being used for a lookup returns different values for the same lookup, within a single SQL statement.
Surely, the "insert into obj_names_and_descr" should have "seen" a Read Consistent image of data ? Yes. The image from the dba_objects view would have been consistent. However, the lookup function "lookup_obj_descr" was executed once against "my_obj_types_lookup" for *each* row from the query against dba_objects. Each execution of the function was independent of the other executions. It so happens that the underlying table "my_obj_types_lookup" was updated by someone else running :
mid-way through the INSERT ... SELECT operation.
Therefore, although some rows that were returned from dba_objects saw the obj_descr as "Is an TABLE" for obj_type = 'TABLE' , rows that were still being fetched from dba_objects after the other session committed his update to 'OOPS !', now saw the new value 'OOPS !' as being the description.
Therefore, the SELECT portion of the INSERT actually returned different values for obj_descr when using the Function !
.
.
.
Thus, if you have a statement like "SELECT col_1, my_function(col_2) FROM ...", the function "my_function" is executed from every row that is returned from the query. Oracle guarantees Read Consistency at the Statement Level. However, insofar as the Function is executed, each execution of the Function is a separate SQL statement executed against the database. If underlying data has been changed in transaction from a different database session, the Function may start returning different values mid-way in a result set.
Here's a demonstration of how a Function being used for a lookup returns different values for the same lookup, within a single SQL statement.
SQL> drop table my_obj_types_lookup purge;
Table dropped.
SQL> drop table obj_names_and_descr purge;
Table dropped.
SQL>
SQL> -- create the Lookups table
SQL> create table my_obj_types_lookup
2 (obj_type varchar2(30) not null, obj_descr varchar2(50) not null)
3 /
Table created.
SQL> insert into my_obj_types_lookup select distinct object_type, 'Is an ' || object_type from dba_objects;
42 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> -- REM Create a function that does Lookups
SQL> create or replace function lookup_obj_descr (obj_type_in varchar2)
2 return varchar2
3 as
4 obj_type_ret varchar2(50);
5 begin
6 select obj_descr into obj_type_ret from my_obj_types_lookup where obj_type = obj_type_in;
7 return obj_type_ret;
8 exception when others then return 'Unknown object type';
9 end;
10 /
Function created.
SQL>
SQL> -- REM create the target table
SQL> create table obj_names_and_descr
2 (obj_owner varchar2(30) not null, obj_name varchar2(128) not null, obj_type varchar2(30) not null, obj_descr varchar2(60) not null);
Table created.
SQL>
SQL> pause PRESS ENTER TO PROCEED
PRESS ENTER TO PROCEED
SQL> -- Now we populate OBJ_NAMES_AND_DESCR using our Lookup Function !
SQL> insert into obj_names_and_descr
2 select owner, object_name, object_type, lookup_obj_descr (object_type)
3 from dba_objects
4 order by owner
5 /
50639 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- REM Let's verify the count of different types of objects
SQL> select obj_type, obj_descr, count(*)
2 from obj_names_and_descr
3 where obj_type like 'TAB%'
4 group by obj_type, obj_descr
5 order by 1,2
6 /
OBJ_TYPE OBJ_DESCR COUNT(*)
------------------------------ ------------------------------------------------------------ ----------
TABLE Is an TABLE 897
TABLE OOPS ! 759
TABLE PARTITION Is an TABLE PARTITION 128
SQL>
SQL>
SQL> -- REM REM REM #!#!
SQL> -- REM Hey ! Where did the "OOPS !" rows come from ? My INSERT INTO ... SELECT should have given my Read Consistency
SQL> -- REM Therefore, every TABLE should have the same OBJ_DESCR !
SQL>
Surely, the "insert into obj_names_and_descr" should have "seen" a Read Consistent image of data ? Yes. The image from the dba_objects view would have been consistent. However, the lookup function "lookup_obj_descr" was executed once against "my_obj_types_lookup" for *each* row from the query against dba_objects. Each execution of the function was independent of the other executions. It so happens that the underlying table "my_obj_types_lookup" was updated by someone else running :
SQL> update my_obj_types_lookup set obj_descr = 'OOPS !' where obj_type = 'TABLE';
1 row updated.
SQL> commit;
Commit complete.
SQL>
mid-way through the INSERT ... SELECT operation.
Therefore, although some rows that were returned from dba_objects saw the obj_descr as "Is an TABLE" for obj_type = 'TABLE' , rows that were still being fetched from dba_objects after the other session committed his update to 'OOPS !', now saw the new value 'OOPS !' as being the description.
Therefore, the SELECT portion of the INSERT actually returned different values for obj_descr when using the Function !
.
.
.
12 September, 2009
RMAN can identify and catalog / use ArchiveLogs automagically
When doing RESTORE DATABASE and RECOVER DATABASE using RMAN, you may find that RMAN automagically
a) CATALOGs files (BackupPieces and even ArchiveLogs) in the FRA, if you are using an FRA
b) Reads ArchiveLogs from the destination identified by log_archive_dest_1 if you use this instead of an FRA
Knowledge of this can also be used when you are restoring to another server where you haven't created an FRA. Copy/Restore your ArchiveLogs (using non-RMAN methods) to any alternate log_archive_dest directory, designate it as log_archive_dest_1 in the parameter file of the database you are creating (i.e. pretending to restore and recover on the new server) and let RMAN identify the files.
I had just posted an example of this in this forums thread.
.
.
.
a) CATALOGs files (BackupPieces and even ArchiveLogs) in the FRA, if you are using an FRA
b) Reads ArchiveLogs from the destination identified by log_archive_dest_1 if you use this instead of an FRA
Knowledge of this can also be used when you are restoring to another server where you haven't created an FRA. Copy/Restore your ArchiveLogs (using non-RMAN methods) to any alternate log_archive_dest directory, designate it as log_archive_dest_1 in the parameter file of the database you are creating (i.e. pretending to restore and recover on the new server) and let RMAN identify the files.
I had just posted an example of this in this forums thread.
.
.
.
07 September, 2009
Table and Partition Statistics
When gathering statistics on Partitioned Tables, it is important to know the difference between Table Level and Partition Level (and, if applicable, SubPartition Level) statistics.
The Optimizer will use Partition Level statistics if the query predicates are on the Partition Keys. However, if the query predicates are not on these columns, (OR even if on the Partition Key do not allow for pruning to a *single partition* -- see update of 08-Sep below) the Optimizer relies on Table Level Statistics.
ANALYZE would aggregate Table Level statistics from Partition Level statistics. DBMS_STATS allows gathering of both or either statistics.
Here is an example showing how the Optimizer can badly estimate cardinality on a non-partition key column in the absence of Table Level statistics.
I have used the demo SH.SALES table which is Partitioned by TIME_ID. However, CUST_ID is a very relevant *dimension* column.
Let's compare the two runs.
The first run of queries is with granularity=>'PARTITION'.
In this case, for the CUST_ID, Oracle estimates 3,203 distinct values and no Histogram. It actually did not gather statistics based on any sampling of CUST_IDs. !
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 861 rows (while the actual count is 252 rows).
The second run of queries is with granularity=>'ALL'.
Here, for the CUST_ID, Oracle estimates 7,059 distinct values, based on a sampling of 918,843 rows. It hsa not yet created a Histogram.
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 390 rows, which is closer to the actual count of 252 rows.
Thus, although gathering statistics only at the Partition Level may make sense where your query predicates are always based on the Partition Key and Partition Pruning can happen, Table Level statistics are necessary for queries where Partition Pruning cannot be done.
.
.
And I have not even started improving the quality of the column statistics with the correct FOR COLUMNS SIZE specification for METHOD_OPT !
.
.
UPDATE : 08-Sep : Randolf Geist has pointed out "I think it's important to point out that partition or subpartition level statistics only get used if the optimizer is able to prune to a single partition/subpartition. Otherwise the next level statistics (partition/global) will get used.".
This also ties in with MetaLink Note#166215.1
So if I rerun the test where the query will hit a *single* partition only, I get :
Thus, when the query is against a single partition -- such that Oracle can apply partition pruning -- the statistics from that specific partition are used. The cardinality estimate is more accurate -- coming to 39,925 against the actual count of 39,924.
.
.
.
The Optimizer will use Partition Level statistics if the query predicates are on the Partition Keys. However, if the query predicates are not on these columns, (OR even if on the Partition Key do not allow for pruning to a *single partition* -- see update of 08-Sep below) the Optimizer relies on Table Level Statistics.
ANALYZE would aggregate Table Level statistics from Partition Level statistics. DBMS_STATS allows gathering of both or either statistics.
Here is an example showing how the Optimizer can badly estimate cardinality on a non-partition key column in the absence of Table Level statistics.
I have used the demo SH.SALES table which is Partitioned by TIME_ID. However, CUST_ID is a very relevant *dimension* column.
SQL> desc sh.sales
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PROD_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
TIME_ID NOT NULL DATE
CHANNEL_ID NOT NULL NUMBER
PROMO_ID NOT NULL NUMBER
QUANTITY_SOLD NOT NULL NUMBER(10,2)
AMOUNT_SOLD NOT NULL NUMBER(10,2)
SQL>
SQL> REM The SALES table is Range Partitioned by TIME_ID
SQL> REM CUST_ID is a Dimension
SQL>
SQL> -- Current Statistics
SQL> --- NOTE !! Warning : For simplicity of reading I have converted NULL statistics to 0 (zero) for NUM_ROWS
SQL> -- However, in reality there is a big difference between NULL NUM_ROWS (meaning no statistics) and 0 NUM_ROWS (really meaning zero rows) !
SQL> exec dbms_stats.delete_table_stats('SH','SALES');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');
PL/SQL procedure successfully completed.
SQL> select table_name, nvl(num_rows,0) from dba_tables where owner = 'SH' and table_name = 'SALES';
TABLE_NAME NVL(NUM_ROWS,0)
------------------------------ ---------------
SALES 0
SQL> select partition_name, nvl(num_rows,0) from dba_tab_partitions where table_owner = 'SH' and table_name = 'SALES' order by partition_position;
PARTITION_NAME NVL(NUM_ROWS,0)
------------------------------ ---------------
SALES_1995 0
SALES_1996 0
SALES_H1_1997 0
SALES_H2_1997 0
SALES_Q1_1998 0
SALES_Q2_1998 0
SALES_Q3_1998 0
SALES_Q4_1998 0
SALES_Q1_1999 0
SALES_Q2_1999 0
SALES_Q3_1999 0
SALES_Q4_1999 0
SALES_Q1_2000 0
SALES_Q2_2000 0
SALES_Q3_2000 0
SALES_Q4_2000 0
SALES_Q1_2001 0
SALES_Q2_2001 0
SALES_Q3_2001 0
SALES_Q4_2001 0
SALES_Q1_2002 0
SALES_Q2_2002 0
SALES_Q3_2002 0
SALES_Q4_2002 0
SALES_Q1_2003 0
SALES_Q2_2003 0
SALES_Q3_2003 0
SALES_Q4_2003 0
28 rows selected.
SQL>
SQL> REM REM ############################################################
SQL> -- Delete and re-gather statistics
SQL> -- statistics at partition level
SQL> exec dbms_stats.delete_table_stats('SH','SALES');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SH','SALES',estimate_percent=>100,granularity=>'PARTITION',cascade=>TRUE,no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows from dba_tables where owner = 'SH' and table_name = 'SALES';
TABLE_NAME NUM_ROWS
------------------------------ ----------
SALES 918843
SQL> select num_distinct, num_buckets, nvl(sample_size,0), histogram from dba_tab_col_statistics
2 where owner = 'SH' and table_name = 'SALES' and column_name = 'CUST_ID';
NUM_DISTINCT NUM_BUCKETS NVL(SAMPLE_SIZE,0) HISTOGRAM
------------ ----------- ------------------ ---------------
3203 1 0 NONE
SQL>
SQL>
SQL> explain plan for
2 select cust_id,time_id,quantity_sold
3 from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 279964487
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 134K| 2094K| 104 (4)| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR| | 134K| 2094K| 104 (4)| 00:00:02 | 17 | 19 |
|* 2 | TABLE ACCESS FULL | SALES | 134K| 2094K| 104 (4)| 00:00:02 | 17 | 19 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"<=TO_DATE(' 2001-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 rows selected. SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');
COUNT(*)
----------
145760
SQL>
SQL> explain plan for
2 select cust_id,time_id,quantity_sold
3 from sh.sales where cust_id in (10,100,1001);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2821443835
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 861 | 13776 | 226 (0)| 00:00:03 | | |
| 1 | PARTITION RANGE ALL | | 861 | 13776 | 226 (0)| 00:00:03 | 1 | 28 |
| 2 | INLIST ITERATOR | | | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 861 | 13776 | 226 (0)| 00:00:03 | 1 | 28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | | | 1 | 28 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUST_ID"=10 OR "CUST_ID"=100 OR "CUST_ID"=1001)
17 rows selected.
SQL>
SQL> select count(*) from sh.sales where cust_id in (10,100,1001);
COUNT(*)
----------
252
SQL>
SQL>
SQL>
SQL> REM REM ############################################################
SQL>
SQL> -- Delete and re-gather statistics
SQL> -- statistics at table and partition level
SQL> exec dbms_stats.delete_table_stats('SH','SALES');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CHANNEL_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROMO_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_PROD_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_CUST_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.delete_index_stats('SH','SALES_TIME_BIX');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SH','SALES',estimate_percent=>100,granularity=>'ALL',cascade=>TRUE,no_invalidate=>FALSE);
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows from dba_tables where owner = 'SH' and table_name = 'SALES';
TABLE_NAME NUM_ROWS
------------------------------ ----------
SALES 918843
SQL> select num_distinct, num_buckets, nvl(sample_size,0), histogram from dba_tab_col_statistics
2 where owner = 'SH' and table_name = 'SALES' and column_name = 'CUST_ID';
NUM_DISTINCT NUM_BUCKETS NVL(SAMPLE_SIZE,0) HISTOGRAM
------------ ----------- ------------------ ---------------
7059 1 918843 NONE
SQL>
SQL> explain plan for
2 select cust_id,time_id,quantity_sold
3 from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 279964487
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 134K| 2094K| 104 (4)| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR| | 134K| 2094K| 104 (4)| 00:00:02 | 17 | 19 |
|* 2 | TABLE ACCESS FULL | SALES | 134K| 2094K| 104 (4)| 00:00:02 | 17 | 19 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"<=TO_DATE(' 2001-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 rows selected. SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('31-JUL-2001','DD-MON-YYYY');
COUNT(*)
----------
145760
SQL>
SQL> explain plan for
2 select cust_id,time_id,quantity_sold
3 from sh.sales where cust_id in (10,100,1001);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2821443835
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 390 | 6240 | 158 (0)| 00:00:02 | | |
| 1 | PARTITION RANGE ALL | | 390 | 6240 | 158 (0)| 00:00:02 | 1 | 28 |
| 2 | INLIST ITERATOR | | | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 390 | 6240 | 158 (0)| 00:00:02 | 1 | 28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 5 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | | | 1 | 28 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CUST_ID"=10 OR "CUST_ID"=100 OR "CUST_ID"=1001)
17 rows selected.
SQL>
SQL> select count(*) from sh.sales where cust_id in (10,100,1001);
COUNT(*)
----------
252
SQL>
Let's compare the two runs.
The first run of queries is with granularity=>'PARTITION'.
In this case, for the CUST_ID, Oracle estimates 3,203 distinct values and no Histogram. It actually did not gather statistics based on any sampling of CUST_IDs. !
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 861 rows (while the actual count is 252 rows).
The second run of queries is with granularity=>'ALL'.
Here, for the CUST_ID, Oracle estimates 7,059 distinct values, based on a sampling of 918,843 rows. It hsa not yet created a Histogram.
For the query on CUST_ID IN (10,100,1001), Oracle estimates a Cardinality of 390 rows, which is closer to the actual count of 252 rows.
Thus, although gathering statistics only at the Partition Level may make sense where your query predicates are always based on the Partition Key and Partition Pruning can happen, Table Level statistics are necessary for queries where Partition Pruning cannot be done.
.
.
And I have not even started improving the quality of the column statistics with the correct FOR COLUMNS SIZE specification for METHOD_OPT !
.
.
UPDATE : 08-Sep : Randolf Geist has pointed out "I think it's important to point out that partition or subpartition level statistics only get used if the optimizer is able to prune to a single partition/subpartition. Otherwise the next level statistics (partition/global) will get used.".
This also ties in with MetaLink Note#166215.1
So if I rerun the test where the query will hit a *single* partition only, I get :
SQL> explain plan for
2 select cust_id,time_id,quantity_sold
3 from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('28-FEB-2001','DD-MON-YYYY');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4096232376
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39925 | 623K| 35 (3)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 39925 | 623K| 35 (3)| 00:00:01 | 17 | 17 |
|* 2 | TABLE ACCESS FULL | SALES | 39925 | 623K| 35 (3)| 00:00:01 | 17 | 17 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"<=TO_DATE(' 2001-02-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 14 rows selected. SQL>
SQL> select count(*) from sh.sales where time_id between to_date('01-JAN-2001','DD-MON-YYYY') and to_date('28-FEB-2001','DD-MON-YYYY');
COUNT(*)
----------
39924
SQL>
Thus, when the query is against a single partition -- such that Oracle can apply partition pruning -- the statistics from that specific partition are used. The cardinality estimate is more accurate -- coming to 39,925 against the actual count of 39,924.
.
.
.
Subscribe to:
Posts (Atom)