Re: Delete Optimizer Statistics
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: Paul.Baumgartel@xxxxxxx
- Date: Wed, 16 Jun 2010 15:07:00 +0100
On Wed, Jun 16, 2010 at 2:55 PM, Niall Litchfield <
niall.litchfield@xxxxxxxxx> wrote:
> It depends on what you expect them to do. There are some interesting
> oddities in the little test I did for this. One I expected, but I suspect
> many people wouldn't and one that caught me out. This is 11.2.
>
> The test is pretty simple. Create a new copy of ALL_OBJECTS complete with
> PK, gather stats export and drop the object. Then import with
> STATISTICS=NONE.
>
For completeness I repeated the test with datapump since it's likely that
that's what the original poster is using given his use of the keyword
exclude. The same behaviour results (again ignore the errors re the other
object). For what it's worth these test cases only took me about 15 minutes
to set up complete and post here. I've also ignored the (not so) little
voice in my head begging me to ask why you would want to do such a thing.
NIALL @ db11gr2 >create table t1 as select * from all_objects;
Table created.
NIALL @ db11gr2 >create index i1 on t1(object_id);
Index created.
NIALL @ db11gr2 >exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
NIALL @ db11gr2 >exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
C:\Program Files\Windows Resource Kits\Tools>expdp userid=niall
directory=DATA_PUMP_DIR DUMPFILE=niall.dmp SCHEMA=NIALL
LRM-00101: unknown parameter name 'SCHEMA'
C:\Program Files\Windows Resource Kits\Tools>expdp userid=niall
directory=DATA_PUMP_DIR DUMPFILE=niall.dmp SCHEMAS=NIALL
Export: Release 11.2.0.1.0 - Production on Wed Jun 16 14:57:50 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
Starting "NIALL"."SYS_EXPORT_SCHEMA_01": userid=niall/********
directory=DATA_PUMP_DIR DUMPFILE=niall.dmp SCHEMAS=NIALL
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "NIALL"."T1" 6.911 MB 71485
rows
. . exported "NIALL"."CLIENT" 0 KB 0
rows
Master table "NIALL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for NIALL.SYS_EXPORT_SCHEMA_01 is:
C:\ORACLE\ADMIN\DB11GR2\DPDUMP\NIALL.DMP
Job "NIALL"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:59:06
C:\Program Files\Windows Resource Kits\Tools>sqlplus niall
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 16 14:59:56 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
NIALL @ db11gr2 >DROP TABLE T1 PURGE;
Table dropped.
NIALL @ db11gr2 >EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
- Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
C:\Program Files\Windows Resource Kits\Tools>impdp userid=niall
directory=DATA_PUMP_DIR DUMPFILE=niall.dmp exclude=STATI
STICS
Import: Release 11.2.0.1.0 - Production on Wed Jun 16 15:01:54 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights
reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
Master table "NIALL"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "NIALL"."SYS_IMPORT_FULL_01": userid=niall/********
directory=DATA_PUMP_DIR DUMPFILE=niall.dmp exclude=STATIST
ICS
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NIALL" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"NIALL"."SEQ_CLIENT" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "NIALL"."CLIENT" exists. All dependent metadata and data
will be skipped due to table_exists_action of
skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "NIALL"."T1" 6.911 MB 71485
rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "NIALL"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 15:02:08
C:\Program Files\Windows Resource Kits\Tools>sqlplus niall
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 16 15:02:21 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
NIALL @ db11gr2 >select index_name,leaf_blocks,blevel from user_indexes;
INDEX_NAME LEAF_BLOCKS BLEVEL
------------------------------ ----------- ----------
I1 158 1
PK_CLIENT 0 0
NIALL @ db11gr2 >
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
Other related posts:
