• About Me

    Maxwell Miranda
    Oracle DBA Consultant.
  • Category

  • Archives

  • My Blog Calendar

    February 2018
    M T W T F S S
    « Sep    
     1234
    567891011
    12131415161718
    19202122232425
    262728  
  • RSS Oracle Feeds

    • Can't access database after power outage
      I could access the database before the power outage. From what I've read, it is a problem with the environment variables, but they are correct. Not sure what else to add. C:\Documents and Settings\konoca>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 21 09:17:08 2018 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERR […]
    • Problems with RESET DATABASE
      Recently I wanted to do RESET DATABASE. Oracle documentation suggests the following commands connect target / startup force nomount reset database to incarnation 2 run { set until time "date time" restore controlfile from autobackup alter database mount restore database recover database } alter database open resetlogs In this view my db has given e […]
  • Advertisements

Recommendation Statistics generation for Siebel Database in 10g

Recommendation Statistics generation for Siebel Database in 10g: the optimizer mode is all_rows

1. For all tables that have more than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
estimate_percent => 20,
granularity => ‘ALL’,
cascade => TRUE,
degree => 8);

For large tables only take a 20 percent percent sample size, set 8 degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’ tells the db not to generate histograms for the indexed columns of these large tables.

2. For all tables that have more than 15 rows and less than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE);

For smaller tables let the db determine the estimate_percent and degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’ tells the db to generate histograms for the indexed columns of these tables (in addition to other index stats generating mechanisms).

3. For all tables that have 15 rows or less delete the stats from these tables:

EXEC DBMS_STATS.DELETE_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
force => TRUE);

The Oracle db optimizer is known to perform inefficiently if stats are generated on empty and very small tables. Therefore the solution is to delete any stats on these tables.

We generated the stats using the above mechanisms using the SQL below, this resulted in much better performance application wide.

Advertisements