Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to influence the threshold for optimizer statistics to become stale

Re: how to influence the threshold for optimizer statistics to become stale

From: Ravi Gaur <ravigaur1_at_gmail.com>
Date: Thu, 24 May 2007 11:56:32 -0500
Message-ID: <289232290705240956q44a276ceyeeb56e84ec9ef069@mail.gmail.com>


We also do a "flush_database_monitoring_info" before identifying the candidates for stats collection (we use 2 thresholds -- %age based and the sum(inserts/updates/deletes) <-- helpful for larger tables where %age threshold would otherwise delay stats collection).

We echo this info into a SQL script and then execute it. Following are snippets from the main script --



...
echo "exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();" > $sqlFile stime=`date '+%m-%d-%Y %H:%M:%S'`
runSQL $tmpFile1
etime=`date '+%m-%d-%Y %H:%M:%S'`
echo "Flush Monitoring :: Start time: $stime End time: $etime" >> $logFile
...
...
echo "\nGenerating scripts for PARTITIONED tables" >> $logFile echo "
select 'exec
dbms_stats.gather_table_stats('''||'$own'||''','''||'$tab'||''','''||a.subpartition_name||''',cascade=>TRUE,degree=>3, method_opt=> '''||'FOR ALL COLUMNS SIZE
$bucketSize'''||',estimate_percent=>NULL,Granularity=>'''||'SUBPARTITION'''||');' from dba_tab_modifications a, dba_tab_subpartitions b where a.table_owner='$own' and a.table_name='$tab'
and a.table_owner=b.table_owner and a.table_name=b.table_name
and a.partition_name=b.partition_name
and a.subpartition_name=b.subpartition_name and b.num_rows > 0
and (((a.INSERTS+a.UPDATES+a.DELETES)*100/b.num_rows > $statsPer) or ((a.INSERTS+a.UPDATES+a.DELETES) > $statsIUD));  " > $sqlFile

runSQL $tmpFile2
...
...
echo "\nGenerating scripts for non-PARTITIONED tables" >> $logFile echo "
select 'exec
dbms_stats.gather_table_stats('''||a.owner||''','''||'\"'||replace( a.table_name,'\$','\\$')||'\"'||''','||'cascade=>TRUE,degree=>3, method_opt=> '''||'FOR ALL COLUMNS SIZE
1'''||',estimate_percent=>NULL,Granularity=>'''||'ALL'''||');' from dba_tables a, dba_tab_modifications b where a.owner='$own'
and a.owner=b.table_owner $cnd
and a.table_name = b.table_name
and (((b.INSERTS+b.UPDATES+b.DELETES)*100/decode(a.num_rows,0,1,a.num_rows)
> $statsPer)

or ((b.INSERTS+b.UPDATES+b.DELETES) > $statsIUD)) order by (b.INSERTS+b.UPDATES+b.DELETES)*100/decode(a.num_rows,0,1, a.num_rows) desc;" > $sqlFile

runSQL $tmpFile2
...
...


On 5/24/07, David Taft <oradbt054_at_gmail.com> wrote:
>
> Chris,
>
> Nice view. I never thought to check for truncated tables, but I just ran
> the following test scenario in 9i and indeed Oracle doesn't clear the stats
> when the table is truncated, but flags it as so. I am guessing that that
> the LIST STALE option in dbms_stats picks up on this flag, but when
> writing one's own list stale script, this needs to be checked.
>
> Good catch.
>
> David
>
>
> SQL> create table mydual as select * from dual where 1 =2;
>
> Table created.
>
> SQL> alter table mydual monitoring;
>
> Table altered.
>
> SQL> exec dbms_stats.gather_table_stats('SYS','MYDUAL');
>
> PL/SQL procedure successfully completed.
>
> SQL> SELECT table_name, inserts, truncated FROM user_tab_modifications;
>
> no rows selected
>
> SQL> insert into mydual values ('a');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> --flush all stats to user_tab_modifications.
> SQL> shutdown immediate;
>
> SQL> startup;
>
> SQL> SELECT table_name, inserts, truncated FROM user_tab_modifications;
>
> TABLE_NAME INSERTS TRU
> ------------------------------ ---------- ---
> MYDUAL 1 NO
> SQL> truncate table mydual;
>
> Table truncated.
>
> SQL> SELECT table_name, inserts, truncated FROM user_tab_modifications;
>
> TABLE_NAME INSERTS TRU
> ------------------------------ ---------- ---
> MYDUAL 1 YES
>
>
>
> On 5/24/07, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> >
> >
> > My query also included truncated tables
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 24 2007 - 11:56:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US