OraFAQ Forum: Server Utilities » impdp execution but not statistics in target schema

Home » RDBMS Server » Server Utilities » impdp execution but not statistics in target schema (11.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

impdp execution but not statistics in target schema [message #683909]

Thu, 04 March 2021 04:48

fipnova51
Messages: 3
Registered: March 2021

Junior Member

Hello community,

I imported a schema with impdp command.
Everything went smoothly but when I look at the statistics information in the table dba_tables for my schema, the column LAST_ANALYZED is null for all entries.

So it looks to me that statistics weren't imported.

Is there a way for me to check if those statistics were exported by looking at the dump file available?

Thanks

Simon

Report message to a moderator

Re: impdp execution but not statistics in target schema [message #683910 is a reply to message #683909]

Thu, 04 March 2021 09:55

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select table_name, comments from dict where upper(comments) like '%STATISTICS%' and table_name like 'USER%';
TABLE_NAME                     COMMENTS
------------------------------ ----------------------------------------------------------------------
USER_COL_PENDING_STATS         Pending statistics of tables, partitions, and subpartitions
USER_IND_PENDING_STATS         Pending statistics of tables, partitions, and subpartitions
USER_IND_STATISTICS            Optimizer statistics for user's own indexes
USER_REPRESOLUTION_STATISTICS  Statistics for conflict resolutions for user's replicated tables
USER_REPRESOL_STATS_CONTROL    Information about statistics collection for conflict resolutions for u
                               ser's replicated tables
USER_STAT_EXTENSIONS           Optimizer statistics extensions
USER_TAB_HISTGRM_PENDING_STATS Pending statistics of tables, partitions, and subpartitions
USER_TAB_PENDING_STATS         History of table statistics modifications
USER_TAB_STATISTICS            Optimizer statistics of the user's own tables
USER_TAB_STATS_HISTORY         History of table statistics modifications
USER_TAB_STAT_PREFS            Statistics preferences for tables
USER_USTATS                    All statistics on tables or indexes owned by the user

SQL> select view_name from all_views where view_name like 'USER%STATISTICS%' order by 1;
VIEW_NAME
------------------------------
USER_IND_STATISTICS
USER_PART_COL_STATISTICS
USER_REPRESOLUTION_STATISTICS
USER_SQLTUNE_STATISTICS
USER_SUBPART_COL_STATISTICS
USER_TAB_COL_STATISTICS
USER_TAB_STATISTICS

Report message to a moderator

Re: impdp execution but not statistics in target schema [message #683921 is a reply to message #683910]

Fri, 05 March 2021 01:17

fipnova51
Messages: 3
Registered: March 2021

Junior Member

Thanks Michel for your answer, it's helpful to have a quick description about '%STAT% tables.

Neverthless, I don't see how this can help me with my quesiton Laughing

I should provide the context at the beginning, would have been helpfull

A colleague did some work on a schema then export it so we can all benefit from his task. Before executin expdp, I asked him to make sure the statistics are updated (because in my mind expdp will export statistics unless it's filtered out but I don't expect it to be the case
When I imported the dump file, I noticed there were no statistics at all on the schema

So now, I want to know if there's a way to know if statistics were exported? if the source schema is not there at all, can we check directly in the dump file if the statistics were exported?

Thanks

Report message to a moderator

Re: impdp execution but not statistics in target schema [message #683922 is a reply to message #683921]

Fri, 05 March 2021 01:27

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

If you post the commands used for the export and for the import, all may become clear. In general however, are you sure that you want the statistics? They will likely no longer be appropriate after the import, better to gather them yourself.

Report message to a moderator

Re: impdp execution but not statistics in target schema [message #683923 is a reply to message #683921]

Fri, 05 March 2021 01:29

fipnova51
Messages: 3
Registered: March 2021

Junior Member

Hi Michel, all,

I was able to find the expdp log file of my colleague who launched the command and I can see this argument exclude=statistics logfile=xxx

I think that solves my issue

Thanks all for your time and sorry for not checking further before posting my question

Report message to a moderator

Re: impdp execution but not statistics in target schema [message #683924 is a reply to message #683921]

Fri, 05 March 2021 02:59

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

fipnova51 wrote on Fri, 05 March 2021 08:17

Neverthless, I don't see how this can help me with my quesiton
...

Sorry I didn't read the last part of your sentence: "by looking at the dump file".

You can do it using McDP, here's an example.
First export SCOTT.EMP table including statistics:

C:\>expdp michel/michel dumpfile=emp.dmp directory=my_dir tables=scott.emp

expdp listing

Now check if there are statistics (the option "keep=y" means the result file is kept and not displayed on screen, the default is "display on screen and remove the result file"):

C:\>McDP michel/michel -c my_dir:emp.dmp -opt include=statistics keep=y 

McDP Utility by Michel Cadot: Version 2020.06.12

Copyright (c) Michel Cadot, 2016-2020. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on myserver on 5-MARS-2021 09:33:15

05/03/2021 09:33:15.562 Getting the content of following file(s):
    my_dir:emp.dmp
05/03/2021 09:33:15.562 Defining the job...
05/03/2021 09:33:17.375 Adding file: MY_DIR:emp.dmp
05/03/2021 09:33:17.703 Result file will be McDPsql_20210305093315.sql
05/03/2021 09:33:17.703 Starting Data Pump job...
05/03/2021 09:33:18.375 Data Pump job started successfully
05/03/2021 09:33:18.437 Master table "MICHEL"."SYS_SQL_FILE_FULL_07" successfully loaded/unloaded
05/03/2021 09:33:18.453
  McDP MICHEL: Display content of file(s):
    my_dir:emp.dmp
  VERSION=COMPATIBLE
  SQL FILE: DATA_PUMP_DIR:McDPsql_20210305093315.sql
  INCLUDE_PATH_EXPR='STATISTICS'
05/03/2021 09:33:18.500 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
05/03/2021 09:33:18.703 Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
05/03/2021 09:33:19.562 Job "MICHEL"."SYS_SQL_FILE_FULL_07" successfully completed at Ven. Mars 5 09:33:18 2021 elapsed
0 00:00:02
05/03/2021 09:33:19.562 Result file DATA_PUMP_DIR/McDPsql_20210305093315.sql kept

No error and you can see it could process the "statistics" objects so the statistics are in the dump file and you will see the internal statements in the named result file.

result file

-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DECLARE I_N VARCHAR2(60); 
  I_O VARCHAR2(60); 
  NV VARCHAR2(1); 
  c DBMS_METADATA.T_VAR_COLL; 
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; 
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS"; 
  i_n := 'PK_EMP'; 
  i_o := 'SCOTT'; 
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,1,0,14,NV,NV,TO_DATE('2010-10-13 22:00:12',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); 
  DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
DECLARE I_N VARCHAR2(60); 
  I_O VARCHAR2(60); 
  NV VARCHAR2(1); 
  c DBMS_METADATA.T_VAR_COLL; 
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; 
 stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS"; 
  i_n := 'TI'; 
  i_o := 'MICHEL'; 
  EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,13,1,6,1,1,1,0,13,NV,NV,TO_DATE('2021-01-14 10:47:58',df),NV;

  DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); 
  DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
DECLARE 
  c varchar2(60); 
  nv varchar2(1); 
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; 
  s varchar2(60) := 'SCOTT'; 
  t varchar2(60) := 'EMP'; 
  p varchar2(1); 
  sp varchar2(1); 
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  DELETE FROM "SYS"."IMPDP_STATS"; 
  INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,
               14,5,38,14,0,NULL,NULL,NULL,
               TO_DATE('2010-10-13 22:00:12',df));
  c := 'EMPNO'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               14,.0714285714285714,14,14,0,7369,7934,4,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'C24A46','C25023',nv,2,nv;
  c := 'ENAME'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               14,.0714285714285714,14,14,0,3.38883673419062E+35,4.53054701071074E+35,6,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'4144414D53','57415244',nv,2,nv;
  c := 'JOB'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               5,.2,5,14,0,3.39086497213261E+35,4.32285038678150E+35,8,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'414E414C595354','53414C45534D414E',nv,2,nv;
  c := 'MGR'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               6,.166666666666667,6,13,1,7566,7902,4,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'C24C43','C25003',nv,2,nv;

END; 
/

DECLARE 
  c varchar2(60); 
  nv varchar2(1); 
  df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; 
  s varchar2(60) := 'SCOTT'; 
  t varchar2(60) := 'EMP'; 
  p varchar2(1); 
  sp varchar2(1); 
  stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)';
BEGIN
  NULL; 
  c := 'HIREDATE'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               13,.0769230769230769,13,14,0,2444591,2446939,8,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'77B40C11010101','77BB0517010101',nv,2,nv;
  c := 'SAL'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               12,.0833333333333333,12,14,0,800,5000,4,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'C209','C233',nv,2,nv;
  c := 'COMM'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               4,.25,4,4,10,0,1400,2,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'80','C20F',nv,2,nv;
  c := 'DEPTNO'; 
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,
               3,.333333333333333,3,14,0,10,30,3,0,nv,nv,
               TO_DATE('2010-10-13 22:00:12',df),'C10B','C11F',nv,2,nv;

  DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"EMP"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"'); 
  DELETE FROM "SYS"."IMPDP_STATS"; 
END; 
/

Now export without the statistics:

C:\>expdp michel/michel dumpfile=emp.dmp directory=my_dir tables=scott.emp exclude=statistics

expdp listing

Then check the dump:

C:\>McDP michel/michel -c my_dir:emp.dmp -opt include=statistics keep=y 

McDP Utility by Michel Cadot: Version 2020.06.12

Copyright (c) Michel Cadot, 2016-2020. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Data Mining, OLAP, Partitioning and Real Application Testing options

Connected as MICHEL on database MIKB2 instance mikb2 on myserver on 5-MARS-2021 09:34:50

05/03/2021 09:34:50.937 Getting the content of following file(s):
    my_dir:emp.dmp
05/03/2021 09:34:50.937 Defining the job...
05/03/2021 09:34:54.796 Adding file: MY_DIR:emp.dmp
05/03/2021 09:34:55.296 Result file will be McDPsql_20210305093450.sql
05/03/2021 09:34:55.296 Starting Data Pump job...
05/03/2021 09:34:56.328 Data Pump job started
05/03/2021 09:34:56.546 API call succeeded but more information is available
05/03/2021 09:34:56.546 no data or metadata objects selected for job
05/03/2021 09:34:56.562 Job aborted
*** CONTENT: Job aborted

There is an error saying "no data or metadata objects selected for job" as we have selected only STATISTICS, this means the statistics are not in the dump.

[Updated on: Fri, 05 March 2021 07:17]

Report message to a moderator

Previous Topic:	Slow application
Next Topic:	Taking backup of your own Schema without DBA provs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 15:46:05 CST 2025