Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to influence the threshold for optimizer statistics to become stale
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 --
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 > 0and (((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-lReceived on Thu May 24 2007 - 11:56:32 CDT
![]() |
![]() |