Table High Water Mark and How Empty the Table Is April 30, 2012
Posted by mwidlake in performance, statistics.Tags: data dictionary, performance, statistics
1 comment so far
On a recent post about reducing the high water mark in a table by shrinking it someone asked if you could tell how much “space” there was in a table that could be recovered by compressing it.
Yes. As pseudo-code:
select number_of_rows*average_row_length. That is how much data there is.
Divided by the block size, to give how many blocks-worth of data you have.
Divided this by a “overhead” fudge factor to allow for wasted space (see later) of about 20%
Compare to the number of blocks used by the table.
Here is some code to do it, showing some extra data and how we calculate the effective percentage of space used (pct_spc_used). It runs against an example table ‘HWM’
select uts.blocks blks_used
,uts.avg_space
,uts.num_rows
,uts.avg_row_len
,uts.empty_blocks empty_blks
,usse.blocks alloc_blks
,greatest(uts.blocks,1)/greatest(usse.blocks,1) pct_hwm
,uts.num_rows*uts.avg_row_len data_in_bytes
,(uts.num_rows*uts.avg_row_len)/8192 data_in_blks
,((uts.num_rows*uts.avg_row_len)/8192)*1.25 mod_data_in_blks
,(((uts.num_rows*uts.avg_row_len)/8192)*1.25)/usse.blocks pct_spc_used
from user_tab_statistics uts
,user_segments usse
where uts.table_name='HWM'
and uts.table_name=usse.segment_name
/
--
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 1221 50000 213 34 1664 .97957
10650000 1300.04883 1625.06104 .97659918
I am collecting the data from USER_TAB_STATISTICS and USER_SEGMENTS. For this code to work you must have reasonably good stats against the table.
I’d like to run through some of the columns I have selected:
First of all, columns AVG_SPACE and EMPTY_BLKS are not populated by dbms_stats.gather_table_stats.. They are populated by the deprecated ANALYZE command that you should NOT use to gather table stats since V10 came along. These columns are populated as I did an ANALYZE to get the data in there, as well as a dbms_stats.
Next, I collect BLOCKS_ALLOCATED from DBA_SEGMENTS {and for this demo I just ignored the potential for partitioned tables) and I compare this to the BLOCKS_USED to get the High Water Mark, as a percentage of the table. I do this as EMPTY_BLOCKS is set to zero if you have never used ANALYZE and, even if you did, unless you use this deprecated command all the time, the value will not change.
On the second line of output I calculate the DATA_IN_BYTES as a simple num_rows*avg_row_len, convert it into blocks {for simplicity I do not collect the block size, I know it is 8k}. I then apply my “Overhead” fudge factor. A block has a header, using around 100 bytes {I’ve not checked the exact figure for years}, pctfree can be varied but defaults to 10% and as only whole rows fit, then an average of half a row of space is empty in each “full” block. Thus I reduce the space available by 20-25%. In this case, 25% as my rows are large.
Finally, I compare this modified data volume to the used blocks to get the actual space
Below I run through creating some test data, looking at the stats and my calculated High Water Mark and pct_space_used and finally shrink my table to see if my guesstimate is a reasonable guesstimate:
populate table
drop table hwm purge;
prompt populate table
set feed on
create table hwm
(id number(10)
,num1 number(2)
,vc1 varchar2(100)
,vc2 varchar2(100)
)
/
insert into hwm
select rownum
,trunc(dbms_random.value(1,100))
,lpad('A',100,'A')
,lpad('B',100,'B')
from dual connect by level < 50001
/
50000 rows created.
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'HWM')
--where is the HWM compared to total segment size
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 0 50000 210 0 1664 .97957
10500000 1281.73828 1602.17285 .962844262
NB AVG_SPC and EMPTY_BLKS are NULL.
The high water mark is 1630 blocks out of 1664 in the segment
My calculated PCT_SPC_USED is 96%. That is probably close enough.
{remember, the last used block will be only partly used, accounting for a bit of the difference}
-- I will use ANALYZE to fill the missing columns
analyze table hwm compute statistics;
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 1150 50000 213 34 1664 .97957
10650000 1300.04883 1625.06104 .97659918
Now those two columns are populated.
Not the slightly different AVG_ROW_LEN even though dbms_stats used 100% (as the table is so small)
and ANALYZE was compute
-- clear 90% of the data randomly
45461 rows deleted.
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 1150 4539 210 34 1664 .97957
953190 116.356201 145.445251 .087407002
PCT_HWM is not altered of course but PCT_SPC_USED has dropped dramatically.
The table is now only 8.7% used, according to my calculations (compared to
90% empty)
The BLKS_USED does not change. The AVG_SPACE and EMPTY_BLOCKS are the same as I
used dbms_stats to update the statistics and it DOES NOT ALTER the columns that it does not
populate. Thus you have no idea how recent those columns are if you use a mixture of commands.
-- clear some blocks completely by deleting a range
2181 rows deleted.
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 1150 2358 210 34 1664 .97957
495180 60.4467773 75.5584717 .045407735
Now the PCT_SPC_USED is down to 4.5%
-- has EMPTY_BLOCKS changed if I use ANALYZE?
analyze table hwm compute statistics;
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 7682 2358 213 34 1664 .97957
502254 61.3103027 76.6378784 .046056417
As you can see, if I use ANALYZE AVG_SPACE alters. But EMPTY_BLOCKS does not, even though I cleared
a chunk of the table. So there are blocks that can be reused but not listed as empty.
I'll just take a quick side-step and show a quick "oddity" about dbms_stats
--deleting the stats (using dbms_stats)
exec dbms_stats.delete_table_stats(ownname=>user,tabname=>'HWM')
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1664
dbms_stats.delete_table_statistics clears ALL statistics, even the ones it does not populate
--and now collect them via dbms_stats again
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
1630 0 2358 210 0 1664 .97957
495180 60.4467773 75.5584717 .045407735
--now to shrink the table
alter table hwm enable row movement;
alter table hwm shrink space;
BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM
---------- ---------- ---------- ----------- ---------- ---------- --------
DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED
------------- ------------ ---------------- ------------
72 0 2358 210 0 80 .90000
495180 60.4467773 75.5584717 .944480896
So I calculated that there was about 75 blocks of data in that table. having shrunk it, I was
a little bit out.
Having run through those examples we can see that the accuracy of the PCT_SPC_USED is down to the fudge factor employed but is probably close enough at 25%. After all, you are only likely to shrink a table that very clearly would benefit from it.
DBA_TAB_MODIFICATIONS July 2, 2010
Posted by mwidlake in internals, performance, statistics.Tags: data dictionary, SQL, statistics
21 comments
I’ve mentioned DBA_TAB_MODIFICATIONS (and there is also of course ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS) in a couple of posts, but mostly buried in long, rambling posts about other things. So here is an overview and demonstration of DBA_TAB_MODIFICATIONS. Skip to the end if you just want to see an example of how it works.
The below example is from 10.2.0.3 but for V11 it is pretty much the same, as is 9 except where I say otherwise.
SYS.DBA_TAB_MODIFICATIONS is used by oracle internally to track how many inserts, updates and deletes have been done to a table, table partition or table subpartition since the segment last had stats gathered on it with dbms_stats. It also records if the segment has been truncated since the last stats gather. Why since the last stats gather? Because Oracle uses it internally to detect how much a segment has changed and if it is 10% or more oracle will deem it stale for stats gathering purposes. (under 11 you can alter the stale percentage). Note I said SYS.DBA_TAB_MODIFICATIONS – on 10 at least the public synonym for it is missing, so unlike most other DBA_ views you need to state the owner (or create a synonym).
OWNER.TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU ------------------- ---------- ---------- ---------- ----------------- --- XXXXXXX.YYYYYYYYYYY 22598264 0 1 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 5 0 0 19-SEP-2007 01:47 NO XXXXXXX.YYYYYYYYYYY 888766 0 0 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 3191 1486 0 27-NOV-2009 05:11 NO XXXXXXX.YYYYYYYYYYY 34742 0 0 08-MAR-2010 15:16 NO XXXXXXX.YYYYYYYYYYY 0 7192 0 02-JUL-2010 05:00 NO XXXXXXX.YYYYYYYYYYY 0 1 0 10-MAR-2010 15:16 NO XXXXXXX.YYYYYYYYYYY 8 8 8 26-JAN-2010 08:05 NO XXXXXXX.YYYYYYYYYYY 1533536 0 2 01-MAR-2010 02:08 NO XXXXXXX.YYYYYYYYYYY 281 0 0 11-SEP-2009 03:00 NO
Under oracle 9 you have to register a table as MONITORED before this information is gathered. Under 10 and 11 all tables/partitions/subpartitions are monitored and you can’t turn that monitoring off {you can try, but oracle ignores you 🙂 }.
The information can be very useful for checking how volatile a segment is, if it has been changed a lot since the last time stats were gathered on it and you can also add the values held in DBA_TAB_MODIFICATIONS to the value for NUM_ROWS held for the segment and get a very accurate estimate of the current number of rows. It is a lot, lot faster than actually counting them!
The information on inserts/updates and deletes is gathered for pretty much all DML against tables (see an up-coming post for an example of this not being true). Direct load SQL*Loader and other direct-io activity can skip being recorded but insert-append, using the /*+ append */ hint is recorded correctly {I suspect this was not true for V9 and 10.1 but am no longer sure}. This information is initially held in memory and only later pushed into DBA_TAB_MODIFICATIONS and so you may not see the latest information. Under oracle 9 this information is flushed down every 15 minutes I believe, under 10.1 it is 3 hours and under 10.2 onwards the information is only flushed down when stats are gathered against the segment OR you manually flush the information down to the database.
flushing the latest information is achieved with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. It is relatively quick to run, normally taking less than a few seconds.
When statistics are gathered on a segment, any corresponding rows in DBA_TAB_MODIFOCATIONS is deleted, not updated to zeros, and is recreated only when relevent inserts,updates, deletes or truncates occur on the segment.
the DBA_TAB_MODIFICATIONS view sits on top of sys.mon_mods_all$ as well as obj$,user$ and the usual suspects. sys.mon_mods_all$ does not contain any more information that the view exposes.
desc sys.dba_tab_modifications
Name Null? Type
----------------------------------------------------- -------- ------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER
--View description
TEXT
-----------------------------------------------------------------------
SYS DBA_TAB_MODIFICATIONS 9
select u.name, o.name, null, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
-- underlying sys.$ table
desc sys.mon_mods_all$
Name Null? Type
----------------------------------------------------- -------- ------
OBJ# NUMBER
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
FLAGS NUMBER
DROP_SEGMENTS NUMBER
Here is a demo of it in action:
TDB>-- clear down the test table.
TDB>-- if you do not have access to DBA_TAB_MODIFICATIONS change to ALL_TAB_MODIFICATIONS
TDB>drop table test_1 purge;
Table dropped.
TDB>create table test_1
2 as select * from all_objects where rownum<1
3 /
Table created.
TDB>select count(*) from test_1;
COUNT(*)
----------
0
1 row selected.
TDB>-- WHAT IS IN DBA_TAB_MODIFICATIONS
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- OK, let's flush down the information
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>-- Still nothing as no activity has occurred on the table.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- Now create some data
TDB>insert into test_1
2 select * from dba_objects where rownum <= 100
3 /
100 rows created.
TDB>commit;
Commit complete.
TDB>select count(*) from test_1;
COUNT(*)
----------
100
1 row selected.
TDB>-- WHAT IS IN DBA_TAB_MODIFICATIONS
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- OK, let's flush down the information
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 100 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- The information in DBA_TAB_MODIFICATIONS is used by Oracle to detect if a table
TDB>-- (or partition) in a tables is stale - changed by 10%
TDB>-- Gathering statistics on an object DELETES the record from DBA_TAB_MODIFICATIONS
TDB -- rather than setting all the values to zero.
TDB>--
TDB>exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_1')
PL/SQL procedure successfully completed.
TDB>--
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
no rows selected
TDB>-- now do some activity again and flush it down to the dictionary
TDB>insert into test_1
2 select * from dba_objects where rownum <= 150
3 /
150 rows created.
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 150 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- Direct inserts via insert-append are also captured (I think this might have changed)
TDB>insert /*+ append */ into test_1
2 select * from dba_objects where rownum <= 170
3 /
170 rows created.
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 320 0 0 NO 02-JUL-2010 10:21
1 row selected.
TDB>-- now a mixed bag of inserts, updates and deletes
TDB>insert into test_1
2 select * from dba_objects where rownum <= 75
3 and owner not in ('SYS','SYSTEM')
4 /
75 rows created.
TDB>--
TDB>update test_1 set created=sysdate
2 where object_type !='TABLE'
3 /
289 rows updated.
TDB>delete from test_1
2 where object_type='SEQUENCE'
3 /
10 rows deleted.
TDB>commit;
Commit complete.
TDB>--
TDB>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
PL/SQL procedure successfully completed.
TDB>select table_name,inserts,updates,deletes,truncated,timestamp
2 from sys.dba_tab_modifications
3 where table_owner=user and table_name= 'TEST_1'
4 /
TABLE_NAME INSERTS UPDATES DELETES trnc TIMESTAMP
------------ ---------- ---------- ---------- ---- -----------------
TEST_1 395 289 10 NO 02-JUL-2010 10:21
1 row selected.
If you want to see what else I said about DBA_TAB_MODIFICATIONS then the end of this {way too long} post shows how to use it to get a very fast count of rows in a table and if you have issues with permissions flushing database monitoring see this link about permissions needed
Question. As the data gets flushed from memory to a data dictionary table, it persists the database being bounced. What happens to the data in memory about table changes when thers is a controlled shutdown and when the database crashes?
dbms_stats.set_table_stats “defaults” June 21, 2010
Posted by mwidlake in internals, statistics.Tags: performance, SQL, statistics
2 comments
What happens if you call dbms_stats.set_table_stats without passing in any of the values to set?
I know, why would you do it anyway? Well, I did so by accident. If I want to gather quick stats on a test table I execute something like:
exec dbms_stats.gather_table_stats(OWNNAME =>user,TABNAME =>’TEST_TAB1′)
If I am feeling generous I might state ESTIMATE_PERCENT too.
I was doing some testing work and was gathering stats and also setting stats manually. Then I started to see several of my test tables all had 2000 rows and were 100 blocks in size – at least according to the stats. I knew this was not possible. It turned out to be Cut ‘n’ Paste fingerf the trouble and I was issuing.
exec dbms_stats.set_table_stats(OWNNAME =>user,TABNAME =>’TEST_TAB1′)
If the table did not already have stats this set the stats on the table to default values of 2000 rows, 100 blocks. If the table already had stats then they were left as they were.
If those figures ring a bell, then that is because they are the default values used if a table has no stats and you have no dynamic sampling. See this table of defaults
Anyway, below is a little worked example of these default values being set. Oh, version is 10.2.0.3.
TDB> drop table TEST1 purge 2 / TDB> select sysdate from dual; SYSDATE ----------------- 21-JUN-2010 16:52 TDB> CREATE TABLE TEST1 2 AS SELECT ROWNUM ID 3 ,OBJECT_NAME OBJ_NAME 4 FROM DBA_OBJECTS TDB> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE 2 FROM DBA_TABLES 3 WHERE OWNER=USER AND TABLE_NAME = 'TEST1' 4 / TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ----------- TEST1 -- New table, no stats yet gathered, the columns hold null -- Call dbms_stats.SET_TABLE_STATS, setting nothing TDB> EXEC dbms_stats.set_table_stats(ownname => user,tabname => 'TEST1') TDB> SELECT TABLE_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE 2 FROM DBA_TABLES 3 WHERE OWNER=USER AND TABLE_NAME = 'TEST1' TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ----------- TEST1 2000 100 2000 -- The columns are set to defaults -- Gather proper stats TDB> exec dbms_stats.gather_table_stats(ownname =>user,tabname =>'TEST1',estimate_percent=>10) TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ----------- TEST1 205430 956 20543 -- Now use SET_TABLE_STATS as intended, setting numrows to a value TDB> EXEC dbms_stats.set_table_stats(ownname => user,tabname => 'TEST1',numrows=>5000) TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ----------- TEST1 5000 956 20543 -- Try the naked SET_TABLE_STATS TDB> EXEC dbms_stats.set_table_stats(ownname => user,tabname => 'TEST1') TABLE_NAME NUM_ROWS BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ----------- TEST1 5000 956 20543 TDB> select sysdate from dual; Any Key> SYSDATE ----------------- 21-JUN-2010 16:52 -- And let us see how the stats have changed over the last few minutes. TDB> select table_name,stats_update_time 2 from dba_tab_stats_history 3 where table_name = 'TEST1' 4 / Any Key> TABLE_NAME STATS_UPDATE_TIME ------------------------------ -------------------------------- TEST1 21-JUN-10 16.52.03.028086 +00:00 TEST1 21-JUN-10 16.52.05.109905 +00:00 TEST1 21-JUN-10 16.52.06.906204 +00:00 TEST1 21-JUN-10 16.52.08.329664 +00:00
Dynamic Sampling Hint Ignored May 20, 2010
Posted by mwidlake in internals, performance.Tags: Hints, statistics
27 comments
This is a bit of an odd one. On V10.2.0.3 (I have not tried other versions yet) then the dynamic sampling hint appears to be ignored if the stats for the table say there are zero rows in the table. Even if there are in fact lots of rows.
I’ve not had chance to dig into Metalink etc yet {I’m putting together this post before I yet again get overwhelmed and fail to put up something I think is of interest} but if this turns out to be a general bug impacting other versions, it could catch people out. This is because one tactic to handle queries where the data volumes shifts all the time is to not gather stats and use the dynamic sampling hint. “Not gathering stats” could also be “gather stats when the table is empty and ignore it – the dynamic sampling hint will take care of everything”.
Here is my worked example. I use two tables, PERSON and PERSON_NAME, related on a column PERS_ID. {If you want the exact table definitions etc, mail me}.
--preserve my data
create table pena_backup as select * from person_name;
Table created.
--ensure I have stats
exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON')
PL/SQL procedure successfully completed.
exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME')
PL/SQL procedure successfully completed.
--Show I have data
select table_name,num_rows,last_analyzed from dba_tables
where owner=USER AND TABLE_NAME IN ('PERSON','PERSON_NAME');
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON 71913 20-MAY-2010 11:06
PERSON_NAME 107567 20-MAY-2010 11:06
--Explain a simple select
explain plan for
select /* */ pers.surname, pers.first_forename
from person pers, person_name pena
where pena.pers_id =pers.pers_id
and pena.surname = 'BROWN'
/
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 1 | HASH JOIN | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| PERSON_NAME | 1427 | 17124 | 320 (5)| 00:00:02 |
| 3 | TABLE ACCESS FULL| PERSON | 71913 | 1404K| 314 (4)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
2 - filter("PENA"."SURNAME"='BROWN')
16 rows selected.
--Now explain with dynamic sampling
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from person pers, person_name pena
where pena.pers_id =pers.pers_id
and pena.surname = 'BROWN'
/
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 1 | HASH JOIN | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| PERSON_NAME | 1427 | 17124 | 320 (5)| 00:00:02 |
| 3 | TABLE ACCESS FULL| PERSON | 71913 | 1404K| 314 (4)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
2 - filter("PENA"."SURNAME"='BROWN')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
-- Note the appearance of the NOTE about dynamic sampling
-- If I did this again, I would add extra data to the table to get different stats
-- from the dynamic sampling
--truncate my person_name table and re-gather stats
truncate table person_name;
Table truncated.
exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME')
PL/SQL procedure successfully completed.
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON 71913 20-MAY-2010 11:06
PERSON_NAME 0 20-MAY-2010 11:06
--now try and dynamic sample a known empty table
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from person pers, person_name pena
where pena.pers_id =pers.pers_id
and pena.surname = 'BROWN';
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1295262714
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 50 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PERSON_NAME | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PENA_SN | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | PERS_PEID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PENA"."SURNAME"='BROWN')
5 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
18 rows selected.
-- I initially thought Oracle might be detecting that the
-- table was empty - never had data inserted or had been truncated
-- and thus knew there was no point sampling...But...
-- put the data back
insert into person_name select * from pena_backup;
107567 rows created.
commit;
Commit complete.
--and explain again
Explained.
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from person pers, person_name pena
where pena.pers_id =pers.pers_id
and pena.surname = 'BROWN';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1295262714
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | PERSON | 1 | 20 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 50 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| PERSON_NAME | 1 | 30 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PENA_SN | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | PERS_PEID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PENA"."SURNAME"='BROWN')
5 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
18 rows selected.
-- Note, nothing about dynamic sampling, the rows and bytes expected are the same as
-- the query with no dynamic sampling hint
-- now gather stats again so oracle knows there is some data
exec dbms_stats.gather_table_stats(ownname=>user,tabname =>'PERSON_NAME')
PL/SQL procedure successfully completed.
select table_name,num_rows,last_analyzed from dba_tables
where owner=USER AND TABLE_NAME IN ('PERSON','PERSON_NAME');
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------
PERSON 71913 20-MAY-2010 11:06
PERSON_NAME 107567 20-MAY-2010 11:09
--and now try dynamic sampling again
explain plan for
select /*+ dynamic_sampling (pena 6) */ pers.surname, pers.first_forename
from person pers, person_name pena
where pena.pers_id =pers.pers_id
and pena.surname = 'BROWN';
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2653984949
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 1 | HASH JOIN | | 1427 | 45664 | 637 (5)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| PERSON_NAME | 1427 | 17124 | 320 (5)| 00:00:02 |
| 3 | TABLE ACCESS FULL| PERSON | 71913 | 1404K| 314 (4)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PENA"."PERS_ID"="PERS"."PERS_ID")
2 - filter("PENA"."SURNAME"='BROWN')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
What prompted me to find this is I had to provide a DYNAMIC SAMPLING hint to a development team and I wanted to make sure it was working correctly first (it was a MERGE statement and I was not sure where to best put it). As you get no warnings about syntax errors with hints, it is best to prove they are correct before handing them over 🙂
I was doing this in a dev system and, try as I might I could not get the hint to work. Because, as I now know, the dev system had no data in the driving table and stats had been gathere on it. I wasted 30 mins in a critical fix-on-fail due to this oddity.
I have not tried to see if DYNAMIC SAMPLING stated at the instance level is also ignored if there are zero-row stats against the table.
Performance Tipping Points April 13, 2010
Posted by mwidlake in performance, statistics.Tags: partitions, performance, statistics
add a comment
This week I came across a nice example of a performance tipping point. This is where Everything is OK until you reach a point where it all quickly cascades to Not OK.
The below shows the timings for a regulalry run “Alert”. Four times an hour we want to know if something quite important has happened on the system.
ID S START_DATE END_DATE DUR TIME_RAN START_PARAM END_PARAM ---- - ------------- ------------- --------- ------------- -------------------- ----------------- 292 S 0410 03:06:00 0410 03:06:31 .000359 0410 03:06:00 20100410 02:51:00 20100410 03:06:00 292 S 0410 03:21:00 0410 03:21:35 .000405 0410 03:21:00 20100410 03:06:00 20100410 03:21:00 292 S 0410 03:36:00 0410 03:36:38 .000440 0410 03:36:00 20100410 03:21:00 20100410 03:36:00 292 S 0410 03:51:00 0410 03:51:33 .000382 0410 03:51:00 20100410 03:36:00 20100410 03:51:00 292 S 0410 04:06:00 0410 04:06:28 .000324 0410 04:06:00 20100410 03:51:00 20100410 04:06:00 292 S 0410 04:21:00 0410 04:21:44 .000509 0410 04:21:00 20100410 04:06:00 20100410 04:21:00 292 S 0410 04:36:00 0410 04:36:27 .000313 0410 04:36:00 20100410 04:21:00 20100410 04:36:00 292 S 0410 04:51:00 0410 04:51:34 .000394 0410 04:51:00 20100410 04:36:00 20100410 04:51:00 292 S 0410 05:06:00 0410 05:06:44 .000509 0410 05:06:00 20100410 04:51:00 20100410 05:06:00 292 S 0410 05:21:00 0410 05:21:43 .000498 0410 05:21:00 20100410 05:06:00 20100410 05:21:00 292 S 0410 05:36:00 0410 05:37:01 .000706 0410 05:36:00 20100410 05:21:00 20100410 05:36:00 292 S 0410 05:51:00 0410 05:52:21 .000938 0410 05:51:00 20100410 05:36:00 20100410 05:51:00 292 S 0410 06:06:00 0410 06:08:09 .001493 0410 06:06:00 20100410 05:51:00 20100410 06:06:00 292 S 0410 06:21:01 0410 06:24:00 .002072 0410 06:21:01 20100410 06:06:00 20100410 06:21:01 292 S 0410 06:36:00 0410 06:40:12 .002917 0410 06:36:00 20100410 06:21:01 20100410 06:36:00 292 S 0410 06:51:00 0410 06:56:54 .004097 0410 06:51:00 20100410 06:36:00 20100410 06:51:00 292 S 0410 07:06:00 0410 07:13:17 .005058 0410 07:06:00 20100410 06:51:00 20100410 07:06:00 292 S 0410 07:21:00 0410 07:29:42 .006042 0410 07:21:00 20100410 07:06:00 20100410 07:21:00 292 S 0410 07:36:00 0410 07:47:48 .008194 0410 07:36:00 20100410 07:21:00 20100410 07:36:00 292 S 0410 07:51:00 0410 08:08:07 .011887 0410 07:51:00 20100410 07:36:00 20100410 07:51:00 292 S 0410 08:08:07 0410 08:29:43 .015000 0410 08:08:07 20100410 07:51:00 20100410 08:08:07 292 S 0410 08:29:43 0410 08:50:10 .014201 0410 08:29:43 20100410 08:08:07 20100410 08:29:43 292 S 0410 08:50:10 0410 09:22:28 .022431 0410 08:50:10 20100410 08:29:43 20100410 08:50:10 292 S 0410 09:22:28 0410 10:27:11 .044942 0410 09:22:28 20100410 08:50:10 20100410 09:22:28 292 S 0410 10:27:11 0410 12:57:16 .104225 0410 10:27:11 20100410 09:22:28 20100410 10:27:11 292 F 0410 12:57:16 0410 14:50:26 .078588 0410 12:57:16 20100410 10:27:11 20100410 12:57:16 292 F 0410 14:50:26 0410 16:49:42 .082824 0410 14:50:26 20100410 10:27:11 20100410 14:50:26 292 F 0410 16:49:42 0410 19:06:10 .094769 0410 16:49:42 20100410 10:27:11 20100410 16:49:42
The first half dozen records show the program ID 292 running in half aminute or so. It varies a little, from just under 30 seconds to 45 seconds. Each run kicks off 15 minutes after the previous and, if you check the START_PARAM and END_PARAM on the right of the listing, you can see that the report runs for the previous 15 minutes. ie the period since the last run ( including the running time of the last itteration).
Then, at 05:36 something happens. The execution takes a full minute. The next run takes 1 minute and 20 seconds. The next run takes over two minutes and each following execution takes a little longer and longer.
At 07:51, 1.5 hours later, something significant happens. The execution takes more than 15 minutes to run. This report is run every 15 minutes. I’m sure you can appreciate that this is a problem. What happens now depends on your architecture.
If you have a scheduler that simply kicks off the report every 15 minutes, at the next scheduled time (08:06 in my case) a new execution will start and you will have two version of the report running for a couple of minutes. As time progresses this overlap increases. When the run time reaches 30 minutes you will now start having 3 reports running at the same time. And soon you will get three, four, five etc version running at the same time. Depending on the number of CPUs and IO bandwidth of your system, how long will it be until it will be doing nothing but running this code?
If the code is doing somthing more than simply selecting data, the chance of the concurrent versions competing for locks or resources is high and can mean that the slow-down of the code escalates rapidly and in a very, very short time, your system is on it’s knees.
In our case, we have some protection against this. The scheduler detects that the previous version has not finished and it waits until it has done so before starting the next execution. so the next execution is delayed. In my example the 08:06 execution is delayed until 08:08, 2 minutes late.
We still have two possible situations. The report could simply continue to slow down at the previous rate and just be delayed longer and longer. In my example it has been slowing down at around 1 minute 20 seconds per run for the last 5 runs.
However, The next execution is further delayed until 08:29 – which is 6 minutes beyond the 15 minute window from 08:08. This latets run of the code is 5 minutes 30 seconds slower than the previous execution, not 1 minute 20 seconds slower. We still have a tipping point. If you remember, this report runs to cover the period since the last execution. As the last execution took more than 15 minutes, the next run has to report on more than 15 minutes. 21 minutes in this case.
The slow-down now rapidly escalates due to not just whatever was slowing the report down initially but also the growing reporting period.
Just 5 runs and 5 hours later, at 12:57:16, the report fails. It now runs for so long that it cannot recreate the data as it looked at the start of the run and we get snapshot-too-old errors. In the listing you just see the second column go from S to F.
It is game over. Thankfully only one copy of the report is running at any time (A version is kicked off as soon as the previous one fails) and so, worst case, is using only one process on the box, one CPU maximum and the IO that one thread can demand.
What was the root cause? Time and statistics and data volume.
Statistics because the job that collects statistics failed earlier.
Time because at 05:50 or so, the run window of 15 minutes was far enough out of the range of known date-times for a partition that the CBO decided only a few rows would be found and swapped to a nested-loop execution.
Data volume as data was coming in quicker as the day progressed and the nested loop plan performance was highly susceptible to increases in data volume.
For an excellent posting on why code suddenly changes it’s execution plan when nothing has happened but time passing, seethis excellent post and comments on the topic on Richard Foote’s blog
{And isn’t his blog so much skinnier than mine? 🙂 }
Friday Philosophy – I killed a presentation April 1, 2010
Posted by mwidlake in Friday Philosophy, performance, statistics.Tags: Blogging, Meeting, performance, statistics
9 comments
Well, this week saw the latest Management and Infrastructure SIG. I won’t go into the SIG meeting itself just now, maybe later this weekend. If you want an opinion on it, you might like to see what Graham Oaks said.
Being Chair of the SIG I have to find presenters. Being also one of those rare people who enjoys presenting, I know I can always tap one person to present. Me. {If only I could sing, play an instrument, act, do impression, put both my ankles behind my ears(*) or anything that was of any general interest, getting an audience would be so much easier}.
I decided to tackle a topic I have tried before and which I previously did a very poor show of: “General principles on gathering database statistics”.
It is not a title to strike joy into the soul, I know, but it is really important to getting good and reliable performance out of any modern Oracle database. Even other Oracle DBA types tend to find the topic worthy but boring, but I seem to have been forced to know a lot about it and I’ve become sadly very passionate about it.
So, I tried again. I mean, how long should it take to describe the issues with database statistics and the general principles to gathering them? I took my old presentation and stripped out all code, all SQL syntax, all listing of tables and got it down to describing the process. Then I started adding the bits and pieces you need to know to get this half-right. 132 slides. Hmmmm
I tried again and took it higher level. 48 slides. I can do 48 slides in something between 45 minutes and an hour. I was ready.
I had the last presentation slot of the day. I figure if I am going to foist myself on the audience, they should have the right to leave before I start, without missing any of the good stuff. I had 3/4 of the day’s attendees still with me when I started.
I think I killed my audience. Actually, I know I killed my audience. I lost a few as the slot progressed {I kidded myself they had to get back to the office/catch a train} and I made the fatal mistake of not shutting up when I had done 45 minutes. You see, there was all this important stuff I had not mentioned yet! Thankfully, I had a friend in the second row and when I saw him lose the will to live, I stopped. I’d been at it for 70 minutes. What we really needed was the Chair to shut me up but I was the chair.
Anyway, I came away from the talk with two realisations.
- The topic of gathering database statistics is too large to cover in one presentation session.
- It really is very boring, even when you are passionate about it.
I think I have no choice but to make this a blog thread (as I threatened to do so about 1 month ago). But I have also promised myself to finish off SQL Audit before I start a new thread and that was 3 months ago.
So I think I am going to have to put some time into this blogging lark. It’s OK though, I seem to go on about database statistics so much that even my cat is staring to avoid me. I’ll just stop watching Star Trek and drinking wine in the evenings and switch to preparing technical blogs. And drinking wine of course.
(*) Up until 2007 I could do this. I get out more now. Despite the stats fixation
Fun with Filters March 9, 2010
Posted by mwidlake in performance.Tags: performance, SQL, statistics
9 comments
{Please note – this post has been used to test changes to layout, thus the duplicated sections}
This post is about the importance of filter statements in execution plans and how they can indicate problems that are not always apparent.
I had a problem recently with some code that had gone rogue – it had been running in a few seconds, being executed every 10 minutes or so. Now it ran until either someone killed it off or it got “snapshot too old” errors. I pretty much knew it was prompted by stats being gathered on the tables in question as we had just gathered stats on the tables in that schema.
The code was something like this (it is not too important what exactly the code was). Oh, and this is on 10.2.0.3, enterprise edition with partitioning.
select accounted, max(recorded_date),count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
and 1= 1 – this is pointless, it replaces a check of “run”=”run”
group by accountid,DATA_SRC_COUNTRY_ID
Those tables are quite large, several million rows in each. The code is basically scanning all of the rows in the table as there are no indexes to support the query and it is written in a way that, well, I would not write it.
Digging around in some monitoring tools I use I confirmed that the code has swapped plan
{original layout, having hacked it to fit}
inst SQL_ID Plan Hash First Load execs
---- ------------- ----------- ------------ ------
TOT_ROWS TOT_BG TOT_DR TOT_CPU CREATED_DT
---------- ------------ ----------- -------------- ------------
3 1t7ma4xn3rw46 3183284037 100203 09:10 125
7,854 6,457,885 1208,149 816,476,585 091020 10:27
3 1t7ma4xn3rw46 3127554972 100206 09:13 2
0 1260,936,642 980 94599,678,960 100205 09:19
{newly possible layout, with wider screen}
inst SQL_ID Plan Hash First Load execs TOT_ROWS TOT_BG TOT_DR TOT_CPU CREATED_DT ---- ------------- ----------- ------------ ------ ---------- ------------ ----------- -------------- ------------ 3 1t7ma4xn3rw46 3183284037 100203 09:10 125 7,854 6,457,885 1208,149 816,476,585 091020 10:27 3 1t7ma4xn3rw46 3127554972 100206 09:13 2 0 1260,936,642 980 94599,678,960 100205 09:19
Version 1 comes back in 5 or 6 seconds. Version 2 does not effectively come back, which is why it records 0 rows. You can see that the Disk Gets are pretty low in version 2 (allowing that it was never left to finish) but Buffer Gets and CPU are both massively up. So much so, it exceeds where I format the numbers with comas (as, if they are THAT big, they need fixing anyway).
I looked at the plans and, though they were different, nothing jumped out at me. So I trimmed down the code and built it up section by section until I saw something significant change. This is my general mode of tuning code if nothing initially occurs to me.
As usual, I started with any scalar or inline code, in this case that SELECT…MINUS…SELECT in the where clause.
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ Minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
This came back in 3.145 seconds, with all of 62 rows. That is fine.
Now I added back the use of the MINUS code as a WHERE clause to the outer query.
select accountid
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
This came back in 5.22 seconds with 4468 records. Again, I can live with that, we do not need sub-second response, we need sub-minute response.
So I now added back in the group by accountid…
select accountid,count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
group by accountid
This does not come back before I have fetched and consumed a cup of tea. That is a significant change.
I was not expecting this, why would sorting under 5 thousand rows stress a database that can scan multi-million row tables in seconds? There is all that CPU being burned up and huge numbers of buffer gets, but not much in the way of disc IO, so it is probably doing something that can be done in memory a very, very large number of times.
What is the difference in plan? {Oh HELL, they look rubbish – sorry, click on the images to blow them up, I’d suggest right-click and open in a new tab, and, YES, I am working on making my blog a little more “wide and friendly”.}. Good plan first, bad plan second
{Original compressed – width=”459″ height=”122″}
{Largest size I can use – width=”800″ height=”212″}
{Natural “overflow” size – width=”866″ height=”230″}
{ was width=”460″ height=”132″ now 800*242. Orig is 839* 242}
The view step has disappeared, which was an expensive step so should help not hinder.
The “SORT GROUP BY” has appeared, which it would do as we introduced the “GROUP BY” clause.
The hash join has been replaced with a filter. In a more complex plan you might miss this replacement, you would maybe notice the hash join disappearing but a filter, well, it is checking some simple “WHERE” clause isn’t it?
Well, the hash join was joining the results coming from the two steps feeding into it, the PARTITION RANGE FULL and the VIEW (which is the in-memory construct of the SELECT..MINUS…SELECT statement).
Now the Filter is filtering the results from the PARTITION RANGE ALL with the results from the MINUS. At this point I’d like to highlight that the predicted cardinality and bytes coming back for the steps within the union have reduced by a factor of 100 from the good and bad plans. And I’ll also admit I hid some key detail in the screen shot. I am not showing the access and filter predicates.
{image is 989*246, I am trying 800*246. Original 460*114}
The above is a screen shot showing that in the new code there are no filter predicates but an access predicate, for the access to the view (ie the select…minus…select). For easier reading, the full access predicate is below, rather than in the screen shot:
“$nso_col_1″=TO_CHAR(“ACCOUNTID”)||TO_CHAR(“DATA_SRC_COUNTRY_ID”)
However, for the slow code, there are no access predicated but are filter predicates. Again, the screen shot shows that there are predicates and I show the full text below. (Screen shots are from PL/SQL developer, btw).
{image 953*238, trying 800*238, original 460*114}
2- EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")
||TO_CHAR("DATA_SRC_COUNTRY_ID")
FROM "W_LCG_OPENING_" "W_LCG_OPENING_"
WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2))
MINUS (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")
FROM "W_LCG_CLIENT_" "W_LCG_CLIENT_"
WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B3)||TO_CHAR(:B4)))
8- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)
10- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)
Basically, the filter is to run the union query for every row from the driving query, passing in the relevant filter clause as an extra predicate to each of the individual queries of the SELECT…MINUS…SELECT
Clever, but not of much help to us as the performance is awful.
I showed this to a colleague and their immediate response was “why does that not show up as a nested loop? How am I supposed to spot that the “filter” is doing so much?” There were a couple of robust Saxon words mixed in with that statement.
So, Beware Filters replacing joins and get in the habit of checking out the filter and access predicates
If you use an old-style template for showing plans {like I do, a guilty sin of mine}, or a GUI where you have not selected that the filter and access predicates be shown, you may well not get them displayed. If you use autotrace in SQL*Plus, you will though:
db3_mw> select accountid,count(*)
2 from W_LCG_OPENING_
3 where accountid||DATA_SRC_COUNTRY_ID in
4 (
5 SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
6 minus
7 SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
8 )
9 group by accountid
10 /
Execution Plan
----------------------------------------------------------
Plan hash value: 397856216
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 911 | 7288 |
15M (7)| 17:25:41 | | |
| 1 | SORT GROUP BY | | 911 | 7288 |
15M (7)| 17:25:41 | | |
|* 2 | FILTER | | | |
| | | |
| 3 | PARTITION RANGE ALL | | 3153K| 24M| 83
11 (4)| 00:00:34 | 1 | 840 |
| 4 | TABLE ACCESS FULL | W_LCG_OPENING_ | 3153K| 24M| 83
11 (4)| 00:00:34 | 1 | 840 |
| 5 | MINUS | | | |
| | | |
| 6 | SORT UNIQUE NOSORT | | 31535 | 246K| 86
15 (7)| 00:00:35 | | |
| 7 | PARTITION RANGE ALL | | 31535 | 246K| 86
11 (7)| 00:00:35 | 1 | 840 |
|* 8 | TABLE ACCESS FULL | W_LCG_OPENING_ | 31535 | 246K| 86
11 (7)| 00:00:35 | 1 | 840 |
| 9 | SORT UNIQUE NOSORT | | 132 | 1056 |
17 (18)| 00:00:01 | | |
|* 10 | INDEX FAST FULL SCAN| W_LCG_CLIENT__PK | 132 | 1056 |
16 (13)| 00:00:01 | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_
") FROM
"W_LCG_OPENING_" "W_LCG_OPENING_" WHERE
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B1)|
|TO_CHAR(:B2))MINUS (SELECT /*+ */
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")
FROM "W_LCG_CLIENT_"
"W_LCG_CLIENT_" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_S
RC_")=TO_CHAR(:B3)||TO_CHAR(:B4) )))
8 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B1)|
|TO_CHAR(:B2))
10 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B1)|
|TO_CHAR(:B2))
{re-worked layout}
db3_mw> select accountid,count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
( SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT )
group by accountid
Execution Plan
----------------------------------------------------------
Plan hash value: 397856216
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 911 | 7288 |15M (7) |17:25:41 | | |
| 1 | SORT GROUP BY | | 911 | 7288 |15M (7) |17:25:41 | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ALL | | 3153K| 24M| 8311 (4)|00:00:34 | 1 | 840 |
| 4 | TABLE ACCESS FULL | W_LCG_OPENING_ | 3153K| 24M| 8311 (4)| 00:00:34 | 1 | 840 |
| 5 | MINUS | | | | | | | |
| 6 | SORT UNIQUE NOSORT | | 31535 | 246K| 8615 (7)| 00:00:35 | | |
| 7 | PARTITION RANGE ALL | | 31535 | 246K| 8611 (7)| 00:00:35 | 1 | 840 |
|* 8 | TABLE ACCESS FULL | W_LCG_OPENING_ | 31535 | 246K| 8611 (7)| 00:00:35 | 1 | 840 |
| 9 | SORT UNIQUE NOSORT | | 132 | 1056 |17 (18) | 00:00:01 | | |
|* 10 | INDEX FAST FULL SCAN| W_LCG_CLIENT__PK | 132 | 1056 |16 (13) | 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID") FROM
"W_LCG_OPENING_" "W_LCG_OPENING_" WHERE
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B1)||TO_CHAR(:B2))MINUS (SELECT /*+ */
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")
FROM "W_LCG_CLIENT_" "W_LCG_CLIENT_"
WHERE TO_CHAR ("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B3)||TO_CHAR(:B4) )))
8 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2))
10 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_")=TO_CHAR(:B1)||TO_CHAR(:B2))
What did I do to fix the problem? Well, even though the code originally went bad due to stats being gathered, I could not force it back to a nice plan after an hour or two playing with gathering new stats so, in this case, I used an UNNEST hint.
select accountid,count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT /*+ unnest */ accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
group by accounted
And it forced the plan back to the version using a HASH join.
The hinted plan
I’m a little unhappy about that hint, but the code needed fixing and the hint did what I wanted it to {I think it was poor of me to only hint one of the two minus statements and I do not like resorting to hints just because I can’t work out what is “wrong” with the stats – If I work out what is wrong and still need the hint, fair enough}. However, I had to be pragmatic and get the code fixed and working in Live, so it has gone in with a hint
Update to Decoding High and Low values February 24, 2010
Posted by mwidlake in internals, performance.Tags: data dictionary, statistics
12 comments
After what seems an age, I finally got a little time to enhance my col_stats code with the extra info people provided as comments to
this original posting on it. I also now handle the translation of dates correctly and also timestamps.
I had a real struggle with timestamps. I knew I could find the answer in Tom Kyte’s “Expert Oracle Database Architecture” as one of the comments said so, but I tried to work it out myself as my copy of the book was at my Mothers {no, she is not learning Oracle Databases, I left it there by accident}. As the other elements of timestamps are held as numerical elements stored as hex (number of days, number of months) I tried to interpret it like that. I was being too complex, it is just an 8-digit hex number – to_number(value,’XXXXXXXX’).
Anyway, this is new-improved, cleanes whiter-than-white script {I’ve increased the width of myt blog to better show code like the belwo, but try clicking on “show source” if you want a plain text version}:
-- col_stats
-- Martin Widlake mdw 21/03/2003
-- MDW 11/12/09 enhanced to include more translations of low_value/high_value
-- pilfered from Gary Myers blog
-- MDW 20/02/10 added in the handling of timestamps.
col owner form a6 word wrap
col table_name form a15 word wrap
col column_name form a22 word wrap
col data_type form a12
col M form a1
col num_vals form 99999,999
col dnsty form 0.9999
col num_nulls form 99999,999
col low_v form a30
col low_v2 form a18
col hi_v form a30
col data_type form a10
col low_value form a25
col high_value form a25
set lines 110
break on owner nodup on table_name nodup
spool col_stats.lst
select --owner
-- ,table_name
column_name
,data_type
,decode (nullable,'N','Y','N') M
,num_distinct num_vals
,num_nulls
,density dnsty
,decode(substr(data_type,1,9) -- as there are several timestamp types
,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value))
,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(low_value))
,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(low_value))
,'DATE',rtrim(
to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
+ (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00'))
,'TIMESTAMP',rtrim(
to_char(100*(to_number(substr(low_value,1,2),'XX')-100)
+ (to_number(substr(low_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(low_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(low_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(low_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(low_value,13,2),'XX')-1,'fm00')
||'.'||to_number(substr(low_value,15,8),'XXXXXXXX') )
) low_v
,decode(substr(data_type,1,9) -- as there are several timestamp types
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value))
,'BINARY_DO',to_char(utl_raw.cast_to_binary_double(high_value))
,'BINARY_FL' ,to_char(utl_raw.cast_to_binary_float(high_value))
,'DATE',rtrim(
to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
+ (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00'))
,'TIMESTAMP',rtrim(
to_char(100*(to_number(substr(high_value,1,2),'XX')-100)
+ (to_number(substr(high_value,3,2),'XX')-100),'fm0000')||'-'||
to_char(to_number(substr(high_value,5,2),'XX'),'fm00')||'-'||
to_char(to_number(substr(high_value,7,2),'XX'),'fm00')||' '||
to_char(to_number(substr(high_value,9,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,11,2),'XX')-1,'fm00')||':'||
to_char(to_number(substr(high_value,13,2),'XX')-1,'fm00')
||'.'||to_char(to_number(substr(low_value,15,8),'XXXXXXXX')))
, high_value
) hi_v
,low_value,high_value
from dba_tab_columns
where owner like upper('&tab_own')
and table_name like upper(nvl('&tab_name','WHOOPS')||'%')
ORDER BY owner,table_name,COLUMN_ID
/
clear colu
spool off
clear breaks
Yes, I know, it is a considerable chunk of code just to get the high and low values for columns, as identified by the last stats gather to be run on the table. {it is important to remember that these high and low values will only be as accurate as the stats gather that was run and when. An “estimate_percent=> 100” run a minute ago will give probaby give accurate values, an “estimate_percent=>0.1′” run last month is not – which is usually what I am checking for}. I could make the script a lot neater by writing a set of functions to call to get the translation, as was suggested as a comment on the original posting and is in fact somethign I have done in the past, but one is often not allowed to put new stored PL/SQL code onto production systems, so a script which does it is, for me, best.
I should show some proof of it working, I guess. The below will create a table with a nice spread of column type and three records with a reasonable spread of data:
-- test_col_hilo
-- M Widlake 11/12/09
--
--testing script to show high and low values for various column types
--
set lines 90 pages 1000 trims on pause off
--set sqlpro 'test102>'
set sqlpro'>'
set echo on
set autotrace off
spool test_col_hilo.lst
-- basic setup info
col name form a30
col value form a20
select substr(name,1,30) name,substr(value,1,20) value
from v$parameter
where name in ('db_block_size','compatible','cpu_count','db_file_multiblock_read_count'
,'optimizer_mode','sga_target','sort_area_size')
order by name
/
drop table test_hilo purge;
--
--
COL TABLE_NAME FORM A15
COL TABLE_OWNER FORM A10
create table test_hilo
(id number(8) not null
,numsmall number(4)
,numlarge number
,datesmall date
,datelarge date
,ts timestamp
,ts0 timestamp(0)
,ts3 timestamp(3)
,ts6 timestamp(6)
,ts9 timestamp(9)
,vcsmall varchar2(10)
,vclarge varchar2(100)
,vcodd varchar2(20)
,nvcsmall nvarchar2(10)
,nvclarge nvarchar2(100)
,bfsmall binary_float -- NB precision is only 6 or 7 digits
,bflarge binary_float
,bdsmall binary_double
,bdlarge binary_double
)
tablespace users
/
-- insert low values
insert into test_hilo
(id,numsmall,numlarge
,datesmall,datelarge
,ts,ts0,ts3
,ts6,ts9
,vcsmall,vclarge
,vcodd
,nvcsmall,nvclarge
,bfsmall,bflarge
,bdsmall,bdlarge
)
values
(1
,-10,-123456789.12345
,trunc(sysdate-1000),sysdate-500000
,systimestamp,systimestamp
,to_timestamp('01-FEB-2010 12:34:56.123','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('01-FEB-2010 12:34:56.1234567','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('01-FEB-2010 12:34:56.987654321','DD-MON-YYYY HH24:MI:SS.FF')
,'ABCDE','AABCABCDEABCDEFGABCDEFGHI'
,'Abc!"£$%^&*()deF'
,'ABCDE','AABCABCDEABCDEFGABCDEFGHI'
,-1.23,-12345.6
,12345,1234567890
)
/
-- insert mid values
insert into test_hilo
(id,numsmall,numlarge
,datesmall,datelarge
,ts,ts0,ts3
,ts6,ts9
,vcsmall,vclarge
,vcodd
,nvcsmall,nvclarge
,bfsmall,bflarge
,bdsmall,bdlarge
)
values
(2
,15,1515151515151
,trunc(sysdate-10),sysdate-5000
,systimestamp,systimestamp
,to_timestamp('05-FEB-2010 12:34:56.123','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('05-FEB-2010 12:34:56.1234567','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('05-FEB-2010 12:34:56.987654321','DD-MON-YYYY HH24:MI:SS.FF')
,'CCCCC','CCBCABCDEABCDEFGABCDEFGHI'
,'CbaaBC'
,'EFGHI','ABCDEFGHIJKLMNOPQRSTUV'
,1.23,12345.6
,54321,5432112345
)
/
--insert high values
insert into test_hilo
(id,numsmall,numlarge
,datesmall,datelarge
,ts,ts0,ts3
,ts6,ts9
,vcsmall,vclarge
,vcodd
,nvcsmall,nvclarge
,bfsmall,bflarge
,bdsmall,bdlarge
)
values
(3
,99,9898989898989
,trunc(sysdate-1),sysdate+1000
,systimestamp+10,systimestamp+10
,to_timestamp('20-FEB-2010 18:17:16.876','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('21-FEB-2010 17:16:15.555555','DD-MON-YYYY HH24:MI:SS.FF')
,to_timestamp('22-FEB-2010 16:15:14.123456789','DD-MON-YYYY HH24:MI:SS.FF')
,'ZYXWV','ZZZZZYZZXZZWZZVZZUZZTZZSZZRZZQZZP'
,'Z;#[]{}~@:'
,'VWXYZ','ZYXWVUTSRQPONMLKJIHGFEDCBA'
,9.87,98765.4
,987654321,987654321.1234567
)
/
commit;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> USER, TABNAME=>'TEST_HILO')
set autotrace off
set echo off
--
spool off
clear colu
--
And the below is the translation of the values – I used a cut-down version of my script, so it woud fit on the blog better:
COLUMN_NAME DATA_TYPE LOW_V HI_V
------------ ------------- ----------------------------- -----------------------------
LOW_VALUE HIGH_VALUE
------------------------- -------------------------
ID NUMBER 1 3
C102 C104
NUMSMALL NUMBER -10 99
3E5B66 C164
NUMLARGE NUMBER -123456789.12345 9898989898989
3A644E38220C59433366 C70A5A5A5A5A5A5A
DATESMALL DATE 2007-05-31 00:00:00 2010-02-23 00:00:00
786B051F010101 786E0217010101
DATELARGE DATE 0641-03-10 17:36:47 2012-11-20 17:36:47
6A8D030A122530 78700B14122530
TS TIMESTAMP(6) 2010-02-24 17:36:47.255015000 2010-03-06 17:36:47.255015000
786E02181225300F333858 786E0306122530
TS0 TIMESTAMP(0) 2010-02-24 17:36:47. 2010-03-06 17:36:47.
786E0218122530 786E0306122530
TS3 TIMESTAMP(3) 2010-02-01 12:34:56.123000000 2010-02-20 18:17:16.123000000
786E02010D23390754D4C0 786E02141312113436B300
TS6 TIMESTAMP(6) 2010-02-01 12:34:56.123457000 2010-02-21 17:16:15.123457000
786E02010D2339075BCDE8 786E0215121110211D18B8
TS9 TIMESTAMP(9) 2010-02-01 12:34:56.987654321 2010-02-22 16:15:14.987654321
786E02010D23393ADE68B1 786E021611100F075BCD15
VCSMALL VARCHAR2 ABCDE ZYXWV
4142434445 5A59585756
VCLARGE VARCHAR2 AABCABCDEABCDEFGABCDEFGHI ZZZZZYZZXZZWZZVZZUZZTZZSZZRZZ
QZZ
4141424341424344454142434 5A5A5A5A5A595A5A585A5A575
4454647414243444546474849 A5A565A5A555A5A545A5A535A
5A525A5A515A5A
VCODD VARCHAR2 Abc!"£$%^&*()deF Z;#[]{}~@:
4162632122C2A324255E262A2 5A3B235B5D7B7D7E403A
829646546
NVCSMALL NVARCHAR2 ABCDE VWXYZ
00410042004300440045 0056005700580059005A
NVCLARGE NVARCHAR2 AABCABCDEABCDEFG ZYXWVUTSRQPONMLK
0041004100420043004100420 005A005900580057005600550
0430044004500410042004300 0540053005200510050004F00
44004500460047 4E004D004C004B
BFSMALL BINARY_FLOAT 3.53999972E+000 -9.86999989E+000
40628F5B C11DEB85
BFLARGE BINARY_FLOAT 3.64494306E-004 -9.87653984E+004
39BF1999 C7C0E6B3
BDSMALL BINARY_DOUBLE -1.2345E+004 -9.87654321E+008
C0C81C8000000000 C1CD6F3458800000
BDLARGE BINARY_DOUBLE -9.8765432112345672E+008 -5.432112345E+009
C1CD6F34588FCD6E C1F43C774D900000
I could go on and look at handling intervals and time zones but I leave this to you as an exercise. In other words, I have no real need for those data types right now and can’t justify the time! I hope the above is enough to answer whatever questions you may have about the high and low values of your columns…
Richard Foote on the Impact of stats staying the same. February 16, 2010
Posted by mwidlake in performance.Tags: Blogging, performance, statistics
add a comment
I just wanted to highlight this very good posting by Richard Foote. He is talking about how SQL execution plans can change when “nothing else does”. Not the table structures, not the code, note the initialisation parameters, not even the table and index stats.
But something does change, which is what day it is (or what hour it is or what week it is). Time moves on and our data does too. If the statistics on the tables does NOT move on, then the CBO thinks that the range of data in the table does not change. So, the CBO thinks your queries are getting further and further “out of range” and so would expect to find less and less data in the tables to bring back. That will lead to plan changes.
If you have noticed my preoccupation with identifying the contents of histograms and high/low values in column stats, you may appreciate that this topic is one I have been finding is a large part of my day job.
Richard explains the point very well, as always, so go have a look.
Stats Need Stats to Gather Stats February 16, 2010
Posted by mwidlake in performance.Tags: partitions, performance, statistics
10 comments
Did you know that you sometimes need good stats so that the stats-gathering package can gather stats in an efficient way? This is a recent, quite extreme example.
I’ve been forced to learn a lot about gathering Oracle stats using DBMS_STATS over the last 4 or 5 years. But no matter how much I learn about the “challengingly bizarre” way in which it works, it seems every week or two there is a new oddity. I plan a whole series on the topic “soon”.
This particular example is from a 10.2.0.3 system.
I am gathering partition-only table stats as we are using the ability of Oracle to roll up Partition stats to Global stats under certain specific conditions. One of the conditions is that you need stats for every partition. Plus, to get global column stats, each partition must have stats for each column. Some of our partitions lacked or had very bad stats.
So I quickly knocked up a script-generating script to create DBMST_STATS.GATHER_TABLE_STATS statements that collected, for those partitions:
- ONLY partition stats.
- NO cascade down to indexes
- BLOCK sampling so it is fast {and poor, but there you go}
- ESTIMATE_PERCENT of 2, which is quite low for block sampling
- collect histograms as we decided the devil of having them was better than the devil of not having them.
the above is not ideal to get “good stats”, but it is quick and gets OK stats which is what we need right now. An example statement is:
begin -- part SD_INFO-DY07032004 rows 34554 dbms_stats.gather_table_stats(ownname => 'ERIC',tabname=> 'SD_INFO' ,partname=> 'DY07032004' ,granularity=> 'PARTITION', estimate_percent => 2,block_sample=> true ,cascade=> FALSE ,method_opt=> 'FOR ALL COLUMNS SIZE AUTO' ,no_invalidate=> false); END; /
Not a big partition (34554 rows and not very wide rows) and so did not take long to gather:
PARTITION_NAME LAST_ANALYZED ------------------------------ -------------------- DY04012004 12-FEB-2010 16:26:57 DY05012004 12-FEB-2010 16:27:00 DY06012004 12-FEB-2010 16:27:04 DY07012004 12-FEB-2010 16:27:07 DY08012004 12-FEB-2010 16:27:11 -- 4 seconds difference DY09012004 12-FEB-2010 16:27:15
I’ve collected statistics for a few thousand partitions over the last couple of days and the time taken is anything between just under half a second to 10 seconds per partition, the odd unusually large partition taking a minute or so. {I believe it takes half a second to gather stats on an empty partition, on our system at least, due to the time it takes for the internal housekeeping, including copying the old statistics information to the SYS.WRI$_OPSTAT_… tables to support restoring stats}. Sorry, I drift away from my main point.
This partition took a lot longer than 10 seconds:
begin -- part W_ACTIVITY_FAILURE-DY02092008 rows 49425 dbms_stats.gather_table_stats(ownname => 'ERIC',tabname=> 'W_ACTIVITY_FAILURE' ,partname=> 'DY02092008' ,granularity=> 'PARTITION', estimate_percent => 2,block_sample=> true ,cascade=> FALSE ,method_opt=> 'FOR ALL COLUMNS SIZE AUTO' ,no_invalidate=> false); END; /
After 10 minutes it was still running. WHY? I quickly checked the number of rows in the partition and then the size of the partition segment, incase either was much larger than I expected. Neither were.
select count(*) from eric.W_ACTIVITY_FAILURE partition (DY07092008)
COUNT(*)
———-
42182
From dba_segments.
BYTES BLOCKS
———- ———-
2621440 320
There is one advantage of a DBMS_STATS statement running for a long time – you can grab from the SGA the actual code being executed for the DBMS_STATS statement. I saw this.
select substrb(dump(val,16,0,32),1,120) ep, cnt
from (select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring */
"ACCOUNT_ID" val,count(*) cnt
from "ERIC"."W_ACTIVITY_FAILURE" t
where TBL$OR$IDX$PART$NUM("ERIC"."W_ACTIVITY_FAILURE",0,4,0,"ROWID") = :objn
and "ACCOUNT_ID" is not null
group by "ACCOUNT_ID")
order by val
DBMS_STATS is scanning the ACCOUNT_ID column on this table and it is taking a long time about it. The only index on the table is on ACCOUNT_ID. It then struck us.
The index is a global index.
INDEX_NAME PARTITIONED
—————————— ————-
API_XXXXXXXXXX_ACCOUNTS_IND NO
And that global index actually lacked stats {another feature of DBMS_STATS and rolling up partition stats had led to that}.
INDEX_NAME TYP UNQ BL L_BLKS DIST_KEYS CLUSTF LB_KEY DB_KEY LST_ANL
————— ——— — —- ———- ———– ———– ———- ———- ——–
API_XXXXXXX_ NOR NON
ACCOUNTS_IND
By this time 3 partitions for this table had been processed by my code, taking around 15 minutes each one. Incredibly slow.
I did a very quick 0.01% sample size DBMS_STATS.GATHER_INDEX_STATS on that index which took about 1 minute. As soon as the partition DBMS_STATS.GATHER_TABLE_STATS statement that was in flight finished, the following similar statements on that table’s partitions took under 3 seconds each. I’ll buy a pint for the first person to guess (within 10 minutes) WHEN I collected the global index stats {You can collect from any pub in central London if you give me a few day’s notice 🙂 }.
PARTITION_NAME NUM_ROWS BLOCKS LAST_ANALYZED -------------- ---------- ---------- --------------------- DY01092008 31461 452 16-FEB-2010 09:51:41 DY02092008 49425 686 16-FEB-2010 10:03:44 DY03092008 54472 719 16-FEB-2010 10:16:31 DY04092008 35762 491 16-FEB-2010 10:30:52 DY05092008 42182 587 16-FEB-2010 10:44:24 DY06092008 21186 312 16-FEB-2010 10:56:13 DY07092008 20898 313 16-FEB-2010 11:12:59 DY08092008 469500 1233 16-FEB-2010 11:13:02 DY09092008 480300 741 16-FEB-2010 11:13:04 DY10092008 15724 223 16-FEB-2010 11:31:01 DY11092008 55671 732 16-FEB-2010 11:31:06 DY12092008 820100 1779 16-FEB-2010 11:31:08 DY13092008 80215 1113 16-FEB-2010 11:31:16 DY14092008 10094 155 16-FEB-2010 11:31:18 DY15092008 10268 158 16-FEB-2010 11:31:20 DY16092008 24578 330 16-FEB-2010 11:31:22 DY17092008 21012 290 16-FEB-2010 11:31:24 DY18092008 22755 318 16-FEB-2010 11:31:27 DY19092008 21276 293 16-FEB-2010 11:31:29 DY20092008 20882 281 16-FEB-2010 11:31:31 DY21092008 24131 323 16-FEB-2010 11:31:34
I will investigate this oddity further if I get time this week, but the lesson to myself so far is:
Global indexes lacking stats can result in very long stats gathering times for partitions EVEN WHEN YOU DO NOT CASCADE DOWN TO INDEXES.
Martin Widlake's Yet Another Oracle Blog 