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

From: David Taft <oradbt054_at_gmail.com>
Date: Thu, 24 May 2007 12:28:12 -0400
Message-ID: <69b058e80705240928s71e113efs498350947cff8f1b@mail.gmail.com>

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:28:12 CDT