DBMS_STATS package

Use DBMS_STATS to gather Oracle dictionary statistics. DBMS_STATS is the preferred and recommended method as it is more complete that the ANALYZE command which soon (read on http://asktom.oracle.com) will soon be depreciated. DBMS_STATS gathers more information about the objects it works on.

1. To gather table statistics with DBMS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'KUBILAY', tabname => 'PART_USER')

2. To gather table statistics with DBMS_STATS with estimate percent

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname => 'KUBILAY', tabname => 'USERS', estimate_percent =>50)

3. To gather schema statistics with estimate percent

SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('KUBILAY', estimate_percent =>50)

4. To gather a partition’s statistics in full

SQL> EXECUTE dbms_stats.gather_table_Stats( ownname => 'SCOTT', tabname => 'SOME_TABLE', partname => 'P0705');

* To gather statistics on a partition without calculating statistics for indexes, i.e. if the table partition is really big and there are lots of indexes and you want to skip them, use this:


EXECUTE dbms_stats.gather_table_stats
(
ownname=>'SCOTT',
tabname=>'SOME_TABLE',
partname=>'SOME_PARTITION',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>FALSE,
degree=>DBMS_STATS.AUTO_DEGREE,
granularity=>'PARTITION'
);


This will work exclusively and only on the partition specified, it will ignore all indexes of the partition!



The Oracle Way

Faster and neater, with more accurate estimates if you use Oracle recommended procedures, and parameters see below. These parameters have a mind of their own and they decided how much they should estimate and if they should run the operation in parallel or not.

1. On Schema with DBMS_STATS

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname =>'MY_SCHEMA', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

2. On Table with DBMS_STATS

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SESSIONS', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE => DBMS_STATS.AUTO_DEGREE);

I find using Oracle Recommended values (i.e. degree=>DBMS_STATS.AUTO_DEGREE) always makes the statistics gathering faster and more effective.


The documentation for the full package is here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036461

Add Oracle range partitions quickly

Find the partition with the maximum HIGH_VALUE and see if you need to add new RANGE partitions which use date partitioning.

I hear Oracle 11g will do this automatically. About time!

USER's partitions

select table_name, partition_name, high_value from user_Tab_partitions a
where partition_position in (
select max(partition_position) from user_tab_partitions b
where b.table_name=a.table_name
group by table_name)

You can enhance this script further by investigating all partitions in your database when logged in as SYSDBA and find out the maxiumum partition HIGH_VALUE for all user schemas (except SYS,SYSTEM) in your database as follows:

DBA (All) partitions

SELECT a.table_owner,
a.table_name ,
a.partition_position ,
b.high_value
FROM dba_tab_partitions b,
(SELECT table_owner ,
table_name ,
MAX(partition_position) partition_position
FROM
(SELECT p.table_owner ,
p.table_name ,
p.partition_position,
p.high_value
FROM dba_tab_partitions p
WHERE p.table_owner NOT IN ('SYS','SYSTEM')
ORDER BY p.partition_position DESC,
p.table_name
)
GROUP BY table_owner,
table_name
) a
WHERE a.table_owner = b.table_owner
AND a.table_name = b.table_name
AND a.partition_position = b.partition_position
order by a.table_owner


Change timestamp

Changing he timestamp of a file in linux

$ touch -t 200605101200 myfile.txt

Email Publishing is cool on blogger.

This thing put me back in blogging!

Just forward your email to the email address you create in blogger and get them published automatically. Now that is a cool use of SQL INSERT, wow.

Update a table from another table

How to update a table from another table via a 'join update' using column aliases

update
(select a.f falias1, b.f falias2 from a, b where a.id=b.id and a.falias1!=b.falias2 )
set falias1=falias2;