7 Deadly Sins Of Oracle Database Management (Sin 2)

Deadly Sin 2 has to do with the Oracle Cost Based Optimizer.

Early on oracle used a rules based optimizer. Which simply meant it made decisions on how to retrieve information based upon a pre-determined set of rules. For example:

When you issued the command

Select * from Table where columun1 = “Apple”

It was smart enough to use the index on column1 if it existed. If two indexes existed on column1 and one was a unique index, it would use the unique index first.

These rules governed how all data was retrieved. The retrieval of data was only as good as the rules that were used. It got even more complicated when two tables were joined together. Imagine a million row table being joined to a 3 row table.

For example:

Select bigtable.col1, smalltable.col2
From bigtable, smalltable
Where bigtable.col1 = smalltable.col2

The rules decided how these two tables got joined together. Joining a really small table to a big ran very fast. While joining a really big table to a small table ran really slow.

The rules never took into consideration the size of a table or how the data was distributed across the two tables. Some other vendors who recognized the major flaw in the rules based optimizer developed and implemented before Oracle a cost based optimizer

In the cost based optimizer tables sizes and distribution were taken into account when making decisions on how to query the data. Oracle knows the difference between a 3 row table and a 10,000,000 million row table. It knows record type a has 10 records and record type b has 100,000 records.

When Oracle first put the cost based optimizer into effect many people found their application ran much faster on the “old” rules based optimizer than the “new” cost based optimizer. This happened for a number of reasons. Many times the application developers had optimized the code better than the database could itself. Other times they had implemented the cost based optimizer poorly. When we check many databases today, we find that it is still being implemented poorly today causing your database to be sluggish and underperforms.

Oracle Database Management Deadly Sin 2 (Poor Implementation of the Cost Based Optimizer)

The cost base optimizer makes decisions based upon the information it is provided. When it lacks proper information it makes poor decisions. When it has proper information it makes optimal decisions and goes a long way to helping your database perform better.

To insure your database can perform optimality, its important it has the right information available to it. This means it needs to understand how data is physically distributed within the table. Here is a particularly useful feature that should be used whenever possible that will work in Oracle8i and up.

The GATHER EMPTY/GATHER STALE feature of DBMS_STATS should be used. When the GATHER EMPTY/GATHER STALE feature is in use, Oracle tracks insert, delete, and indexed column updates, making the counters accessible by viewing 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)

This is very important because based on object details in this view, if the row count of a table has changed more than 10%, DBMS_STATS will re-collect optimizer statistics. This will insure the database is always making performance decisions based upon current information that accurately reflects how the physical data is in your database at that time.. This is key to getting the most out of your Oracle database.

There are a few steps involved when implementing this functionality:

===============================================
= 1 Place tables in monitoring mode (monon.sql) ===============================================

— Start monon.sql
/ as sysdba

set pages 0 lines 999 trimsp on feed off spool monon

select /* Will run for releases 8i and 9i */
‘alter table ‘||owner||’.’||table_name||’ monitoring;’
from sys.dba_tables,(select * from v$version where rownum < 2) vv
where monitoring = ‘NO’
and owner not in (‘SYS’,’SYSTEM’)
and nvl(duration,’X’) not in (‘SYS$SESSION’,’SYS$TRANSACTION’)
and iot_type is null
and (owner,table_name) not in
(select owner,table_name from dba_external_tables)
and (instr(vv.banner,’9i’) > 0 or instr(vv.banner,’8i’) > 0);

select /* Will run for release 10g */
‘alter table ‘||owner||’.’||table_name||’ monitoring;’
from sys.dba_tables,(select * from v$version where rownum < 2) vv
where monitoring = ‘NO’
and nvl(duration,’X’) not in (‘SYS$SESSION’,’SYS$TRANSACTION’)
and iot_type is null
and (owner,table_name) not in
(select owner,table_name from dba_external_tables)
and (instr(vv.banner,’9i’) = 0 and instr(vv.banner,’8i’) = 0);

spool off
set echo on feed on

spool monon.log

@monon.lst

spool off

exit– End monon.sql

The monon.sql should be run daily. It will place tables in monitoring mode, including those belonging to SYS and SYSTEM if running Database 10g.

=============================================
= 2 Place a stake in the ground (startup.sql) =============================================
DBMS_STATS can start gathering insert/update/delete activity against tables after the collection of a fresh set of statistics. This should only be run once when starting to use the GATHER EMPTY/GATHER STALE functionality.

— Start startup.sql
{username/password} or / as sysdba

set pages 0 lines 999 trimsp on feed off spool startup

select /* Will run for releases 8i and 9i */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where owner not in (‘SYS’,’SYSTEM’) and (instr(vv.banner,’9i’) > 0 or instr(vv.banner,’8i’) > 0);

select /* Will run for release 10g */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where (instr(vv.banner,’9i’) = 0 and instr(vv.banner,’8i’) = 0);

spool off
set echo on feed on timi on

spool startup.log

@startup.lst

spool off

exit
— End startup.sql

The code below is run daily (or less often if desired) and will only collect statistics for objects with no previous statistics (i.e., new
tables) or tables whose current statistics are deemed to be stale.

— Start gegs.sql
{username/password} or / as sysdba

set pages 0 lines 999 trimsp on feed off spool gegs

select /* Will run for releases 8i and 9i */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2,’||
‘options=>’||””||’GATHER EMPTY’||””||
‘, granularity=>’||””||’ALL’||””||’)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where owner not in (‘SYS’,’SYSTEM’) and (instr(vv.banner,’9i’) > 0 or instr(vv.banner,’8i’) > 0);

select /* Will run for release 10g */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2,’||
‘options=>’||””||’GATHER EMPTY’||””||
‘, granularity=>’||””||’ALL’||””||’)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where (instr(vv.banner,’9i’) = 0 and instr(vv.banner,’8i’) = 0);

select /* Will run for releases 8i and 9i */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2,’||
‘options=>’||””||’GATHER STALE’||””||
‘, granularity=>’||””||’ALL’||””||’)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where owner not in (‘SYS’,’SYSTEM’) and (instr(vv.banner,’9i’) > 0 or instr(vv.banner,’8i’) > 0);

select /* Will run for release 10g */
distinct ‘exec dbms_stats.gather_schema_stats (ownname=>’||””||
owner||””||’,cascade=>TRUE,estimate_percent=>2,’||
‘options=>’||””||’GATHER STALE’||””||
‘, granularity=>’||””||’ALL’||””||’)’
from sys.dba_tables,(select * from v$version where rownum < 2) vv where (instr(vv.banner,’9i’) = 0 and instr(vv.banner,’8i’) = 0);

spool off
set echo on feed on timi on

spool ggegs.log

@gegs.lst

exit
— End gegs.sql

Posted Michael Corey, Ntirety

www.ntirety.com

View blog top tags

Add to Technorati Favorites

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.