Quick Note

Just noticed this on the Apress site, looks like can expect a new book from Tom Kyte in the summer …

http://apress.com/book/view/9781430229469

Thought he had been quiet recently.

Has there been a big change in data volume ?

Ever get requests from users/developers complaining that queries or feeds are suddenly running slower – and claiming nothing has changed ?  Are you  pretty sure its just going to data volume or some sort of change in table statistics?

One of the useful features introduced in 10g was DBMS_STATS automatically saving old statistics before it replaces them, this allows you run a query to see the history of the number of rows and sample size for a table and spot sudden changes or patterns.

SELECT  obj.owner
       ,obj.object_name                   table_name
       ,NVL(obj.subobject_name,'GLOBAL')  partition_name
       ,TO_CHAR(hist.savtime)             save_time
       ,rowcnt
       ,DECODE( rowcnt,0,100, ROUND( samplesize / rowcnt  * 100 )) SampleSize
  FROM  dba_objects obj
       ,sys.wri$_optstat_tab_history hist
 WHERE  hist.obj#          = obj.object_id
   AND  owner              LIKE  '%&OWNER%'
   AND  object_name        LIKE  '%&TABLE_NAME%'
 ORDER  BY obj.owner,
            obj.object_name,
            obj.subobject_name,
            hist.savtime;

Which would could produce output something like the following ( edited to fit ) …

OWNER      Table Name SAVE_TIME                               ROWCNT SAMPLESIZE
---------- ---------- ----------------------------------- ---------- ----------
TST        MYTABLE    21-DEC-09 10.48.49.657130 PM -08:00    4599783         35
                      22-DEC-09 08.51.03.804101 PM -08:00    4609457         35
                      22-DEC-09 10.25.03.426489 PM -08:00    4606449         35
                      23-DEC-09 10.53.17.058652 PM -08:00    4602537         35
                      24-DEC-09 11.01.33.769205 PM -08:00    4609786         35
                      25-DEC-09 10.29.19.524311 PM -08:00    4608197         35
                      26-DEC-09 10.29.57.033672 PM -08:00    4607169         35
                      27-DEC-09 08.42.30.261394 PM -08:00    4608031         35
                      28-DEC-09 09.01.04.458258 AM -08:00    4610931         35
                      28-DEC-09 08.58.45.685068 PM -08:00    4611994         35
                      29-DEC-09 08.54.35.136462 PM -08:00    4610089         35
                      <snip>
                      19-JAN-10 08.48.50.453766 PM -08:00    4621849         35
                      19-JAN-10 08.51.17.329342 PM -08:00    4625629         35
                      19-JAN-10 10.04.42.517133 PM -08:00    4625584        100
                      20-JAN-10 09.53.32.024996 PM -08:00    4622763         35
                      20-JAN-10 10.59.40.301349 PM -08:00    4627791        100
                      21-JAN-10 09.01.01.591324 PM -08:00    4624446         35

Here the data volumes aren’t changing much although steadily increasing, but you can see gather stats is getting run twice on days without much change in data volume, but with different sample sizes. It needs a little more investigation but this probably the scheduled automatic job (SYS.GATHER_STATS_JOB ) and a manually submitted gather_stats from a batch job or app support.

How much history is kept?  By default 31 days, but this can be increased.

SELECT  'History Retention'</pre>
       ,TO_CHAR(dbms_stats.get_stats_history_retention)
  FROM  dual

How much does space my stats history take ?

SELECT  occupant_name
       ,schema_name
       ,space_usage_kbytes Used_kb
  FROM  v$sysaux_occupants
 WHERE  OCCUPANT_NAME  = 'SM/OPTSTAT'

OCCUPANT_NAME        SCHEMA_NAME        USED_KB
-------------------- --------------- ----------
SM/OPTSTAT           SYS                1616384

I've used the history a number of times to spot sudden changes in data, and is a useful script to have in your back pocket.

Andy

Not So Read Only …

I’ve always assume making a tablespace READ ONLY meant NO changes could happen to its contents.  So for example if I create new tablespace, put a table in it and set it to be READ ONLY …

1:27:43 ops$oracle@ORA10204> create tablespace TEST;

Tablespace created.

11:27:58 ops$oracle@ORA10204> create table andy tablespace test as select * from dba_objects;

Table created.

11:28:21 ops$oracle@ORA10204> alter tablespace TEST READ ONLY;

Tablespace altered.

So I can’t change the rows in the table, good …

11:28:35 ops$oracle@ORA10204> update andy set object_name = NULL;
update andy set object_name = NULL
 *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/db1/oradata/ORA10204/datafile/o1_mf_test_5oft5vpq_.dbf'

But I can drop the table !

11:38:34 ops$oracle@ORA10204> drop table andy;

Table dropped.

One workaround for this could be to disable DDL locks on the tables to make dropping tables harder …

11:43:48 ops$oracle@ORA10204> alter table andy disable table lock;

Table altered.

11:45:37 ops$oracle@ORA10204> drop table andy;
drop table andy
 *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for ANDY