Skip to content

Posts from Май 2017

how to copy schema statistics from prod to dev stand

how to copy schema statistics from prod to dev stand published on Комментариев к записи how to copy schema statistics from prod to dev stand нет

hello, dev team ask me to look at their stand under load testing and advice how-to improve results. So first thing that I found was statistics locked for last 3 years =)

So fist thing that I have done it’s to copy stats from prod database.
To archive this I’ve done this:

create stats table and export schema stats to it:

exec dbms_stats.create_stat_table ( ownname => 'XXXX' , stattab => 'XXXX_STATS_TABLE' ) ;
exec dbms_stats.export_schema_stats ( ownname => 'XXXX' , stattab => 'XXXX_STATS_TABLE') ;

next thing was transfering dump file to destination host:

[oracle@p00XXXXdb01 ~]$ expdp \'/ as sysdba\' tables=XXXX.XXXX_STATS_TABLE  directory=temp_dump dumpfile=XXXX_STATS_TABLE.dmp logfile=XXXX_STATS_TABLE.log reuse_dumpfiles=y

on destination (dev stand) host I’ve prepared scripts for lock and unlock statistics:

select distinct(' exec dbms_stats.lock_table_stats(''XXXX'',''' || table_name ||''');' ) from dba_tab_statistics where owner='XXXX' and STATTYPE_LOCKED='ALL' ; 


exec dbms_stats.unlock_table_stats('XXXX','BKG_CFG');
exec dbms_stats.unlock_table_stats('XXXX','PSO');
exec dbms_stats.unlock_table_stats('XXXX','OBJ');
exec dbms_stats.unlock_table_stats('XXXX','AUT_PAR');
exec dbms_stats.unlock_table_stats('XXXX','IT_RES');
exec dbms_stats.unlock_table_stats('XXXX','CTY');
exec dbms_stats.unlock_table_stats('XXXX','SRV_PVI');
exec dbms_stats.unlock_table_stats('XXXX','ADR');
exec dbms_stats.unlock_table_stats('XXXX','ACC_AUT_PAR');
exec dbms_stats.unlock_table_stats('XXXX','REG_AUT');
exec dbms_stats.unlock_table_stats('XXXX','CFM_COD');
exec dbms_stats.unlock_table_stats('XXXX','CTT');
exec dbms_stats.unlock_table_stats('XXXX','CTZ_SHP');
exec dbms_stats.unlock_table_stats('XXXX','APV');
exec dbms_stats.unlock_table_stats('XXXX','CRL');
exec dbms_stats.unlock_table_stats('XXXX','AUT');
exec dbms_stats.unlock_table_stats('XXXX','ACC_BKP');
exec dbms_stats.unlock_table_stats('XXXX','ACC_AUT');
exec dbms_stats.unlock_table_stats('XXXX','SAG');
exec dbms_stats.unlock_table_stats('XXXX','OPN_HIS');
exec dbms_stats.unlock_table_stats('XXXX','OBJ_TEC');
exec dbms_stats.unlock_table_stats('XXXX','ACS_TKN');
exec dbms_stats.unlock_table_stats('XXXX','SES_CXT');
exec dbms_stats.unlock_table_stats('XXXX','LGN_BKG');
exec dbms_stats.unlock_table_stats('XXXX','RSP');
exec dbms_stats.unlock_table_stats('XXXX','GRP');
exec dbms_stats.unlock_table_stats('XXXX','ORG');
exec dbms_stats.unlock_table_stats('XXXX','REQ');
exec dbms_stats.unlock_table_stats('XXXX','TAS');
exec dbms_stats.unlock_table_stats('XXXX','ACT_SES');
exec dbms_stats.unlock_table_stats('XXXX','CTT_TEC');
exec dbms_stats.unlock_table_stats('XXXX','ACS_LST');
exec dbms_stats.unlock_table_stats('XXXX','STF_UNT');
exec dbms_stats.unlock_table_stats('XXXX','OPN_HIS_PAR');
exec dbms_stats.unlock_table_stats('XXXX','BKG_LOG');
exec dbms_stats.unlock_table_stats('XXXX','DOC');
exec dbms_stats.unlock_table_stats('XXXX','IT_SYS');

select distinct('exec dbms_stats.unlock_table_stats(''XXXX'',''' || table_name ||''');' ) from dba_tab_statistics where owner='XXXX' and STATTYPE_LOCKED='ALL' ;
exec dbms_stats.lock_table_stats('XXXX','CTT');
exec dbms_stats.lock_table_stats('XXXX','AUT_PAR');
exec dbms_stats.lock_table_stats('XXXX','ACT_SES');
exec dbms_stats.lock_table_stats('XXXX','BKG_LOG');
exec dbms_stats.lock_table_stats('XXXX','TAS');
exec dbms_stats.lock_table_stats('XXXX','CTZ_SHP');
exec dbms_stats.lock_table_stats('XXXX','CTT_TEC');
exec dbms_stats.lock_table_stats('XXXX','REG_AUT');
exec dbms_stats.lock_table_stats('XXXX','ACS_TKN');
exec dbms_stats.lock_table_stats('XXXX','CFM_COD');
exec dbms_stats.lock_table_stats('XXXX','ORG');
exec dbms_stats.lock_table_stats('XXXX','ACS_LST');
exec dbms_stats.lock_table_stats('XXXX','OPN_HIS');
exec dbms_stats.lock_table_stats('XXXX','OBJ');
exec dbms_stats.lock_table_stats('XXXX','REQ');
exec dbms_stats.lock_table_stats('XXXX','AUT');
exec dbms_stats.lock_table_stats('XXXX','ACC_BKP');
exec dbms_stats.lock_table_stats('XXXX','IT_RES');
exec dbms_stats.lock_table_stats('XXXX','CTY');
exec dbms_stats.lock_table_stats('XXXX','GRP');
exec dbms_stats.lock_table_stats('XXXX','SAG');
exec dbms_stats.lock_table_stats('XXXX','ADR');
exec dbms_stats.lock_table_stats('XXXX','RSP');
exec dbms_stats.lock_table_stats('XXXX','STF_UNT');
exec dbms_stats.lock_table_stats('XXXX','OPN_HIS_PAR');
exec dbms_stats.lock_table_stats('XXXX','OBJ_TEC');
exec dbms_stats.lock_table_stats('XXXX','CRL');
exec dbms_stats.lock_table_stats('XXXX','LGN_BKG');
exec dbms_stats.lock_table_stats('XXXX','ACC_AUT_PAR');
exec dbms_stats.lock_table_stats('XXXX','BKG_CFG');
exec dbms_stats.lock_table_stats('XXXX','PSO');
exec dbms_stats.lock_table_stats('XXXX','APV');
exec dbms_stats.lock_table_stats('XXXX','DOC');
exec dbms_stats.lock_table_stats('XXXX','ACC_AUT');
exec dbms_stats.lock_table_stats('XXXX','SRV_PVI');
exec dbms_stats.lock_table_stats('XXXX','SES_CXT');
exec dbms_stats.lock_table_stats('XXXX','IT_SYS');

after unlocking stats run:

[oracle@u00XXXXdb01 ~]$impdp \'/ as sysdba\' directory=dump dumpfile=XXXX_STATS_TABLE.dmp  logfile=imp.log

check stats:

14:14:12 (1)[u00XXXX]system@u00XXXX> @table_stats XXXX.acc %

OWNER           | TABLE_NAME                     |   NUM_ROWS |     BLOCKS | S_LOCK               | S_STALE              |  AVG_SPACE | AVG_ROW_LEN
--------------- | ------------------------------ | ---------- | ---------- | -------------------- | -------------------- | ---------- | -----------
XXXX            | ACC                            |    7189756 |     139407 | ALL                  | YES                  |          0 |         129

1 row selected.

Elapsed: 00:00:00.06

OWNER           | TABLE_NAME                     |  COLUMN_ID | COLUMN_NAME                    |    DENSITY | NUM_DISTINCT |  NUM_NULLS | HISTOGRAM       | LAST_ANALYZED
--------------- | ------------------------------ | ---------- | ------------------------------ | ---------- | ------------ | ---------- | --------------- | -------------------
XXXX            | ACC                            |          1 | ID_OBJ                         | 1.3909E-07 |      7189756 |          0 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          2 | LGN                            | 1.3862E-07 |      7189756 |          0 | HEIGHT BALANCED | 10.01.2014 22.02.18
XXXX            | ACC                            |          3 | STU                            | 6.9311E-08 |            2 |          0 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |          4 | SCT_QSN                        | 2.8562E-06 |       350112 |     348895 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          5 | SCT_ANR                        | 6.7852E-07 |      1473792 |     350288 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          6 | CRT_ON                         | 1.3909E-07 |      7189756 |          0 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          7 | UPD_ON                         | 1.3862E-07 |      7120896 |          0 | HEIGHT BALANCED | 10.01.2014 22.02.18
XXXX            | ACC                            |          8 | DSC                            |          1 |            1 |     294329 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |          9 | SBJ_TYP                        | 6.9299E-08 |            2 |          0 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |         10 | PSV                            | 7.2131E-08 |            1 |     294329 | FREQUENCY       | 10.01.2014 22.02.18
XXXX            | ACC                            |         11 | OTP_AUT                        |         .5 |            2 |    1643025 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         12 | OTP_MTD                        |          1 |            1 |    7181747 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         13 | OTP_DVC_ID                     | .000125203 |         7987 |    7181747 | NONE            | 10.01.2014 22.02.18
XXXX            | ACC                            |         14 | NO_PWD                         |          1 |            1 |    7187683 | NONE            | 10.01.2014 22.02.18

14 rows selected.

Elapsed: 00:00:00.93

OWNER           | TABLE_NAME                     | EXTENSION_NAME                      | EXTENSION                                                                                  | CREATO | DRO
--------------- | ------------------------------ | ----------------------------------- | ------------------------------------------------------------------------------------------ | ------ | ---
XXXX            | ACC                            | SYS_NC00017$                        | (UPPER("LGN"))                                                                             | SYSTEM | NO

import stats ^

14:42:48 (1)[u00XXXX]system@u00XXXX>  EXEC DBMS_STATS.import_schema_stats ('XXXX','XXXX_STATS_TABLE',null,'XXXX');

check results:

14:51:41 (1)[u00XXXX]system@u00XXXX> @table_stats XXXX.acc %

OWNER           | TABLE_NAME                     |   NUM_ROWS |     BLOCKS | S_LOCK               | S_STALE              |  AVG_SPACE | AVG_ROW_LEN
--------------- | ------------------------------ | ---------- | ---------- | -------------------- | -------------------- | ---------- | -----------
XXXX            | ACC                            |   48444484 |     518959 | <NULL>               | NO                   |          0 |          86

1 row selected.

Elapsed: 00:00:00.12

OWNER           | TABLE_NAME                     |  COLUMN_ID | COLUMN_NAME                    |    DENSITY | NUM_DISTINCT |  NUM_NULLS | HISTOGRAM       | LAST_ANALYZED
--------------- | ------------------------------ | ---------- | ------------------------------ | ---------- | ------------ | ---------- | --------------- | -------------------
XXXX            | ACC                            |          1 | ID_OBJ                         | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          2 | LGN                            |          0 |     48444484 |          0 | HYBRID          | 27.05.2017 13.01.50
XXXX            | ACC                            |          3 | STU                            | 1.0321E-08 |            3 |          0 | FREQUENCY       | 27.05.2017 13.01.50
XXXX            | ACC                            |          4 | SCT_QSN                        | 5.9769E-06 |       167312 |   47750066 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          5 | SCT_ANR                        | 3.6648E-06 |       272864 |   47750066 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          6 | CRT_ON                         | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          7 | UPD_ON                         |          0 |     47513600 |          0 | HYBRID          | 27.05.2017 13.01.50
XXXX            | ACC                            |          8 | DSC                            |          1 |            1 |   48413684 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |          9 | SBJ_TYP                        | 1.0321E-08 |            2 |          0 | FREQUENCY       | 27.05.2017 13.01.50
XXXX            | ACC                            |         10 | PSV                            |          1 |            1 |   48413684 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         11 | OTP_AUT                        |         .5 |            2 |          0 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         12 | OTP_MTD                        |          1 |            1 |    8911005 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         13 | OTP_DVC_ID                     | 2.6103E-08 |     38309888 |    8911005 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         14 | NO_PWD                         |          1 |            1 |   48441875 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         15 | LOGIN_BY_DS_ALLOWED            |         .5 |            2 |   47807130 | NONE            | 27.05.2017 13.01.50
XXXX            | ACC                            |         16 | FRST_LGN                       |          1 |            1 |   47951901 | NONE            | 27.05.2017 13.01.50
XXXX            | <NULL>                         | <NULL>     | SYS_NC00017$                   | 2.0642E-08 |     48444484 |          0 | NONE            | 27.05.2017 13.01.50

17 rows selected.

Elapsed: 00:00:01.35

OWNER           | TABLE_NAME                     | EXTENSION_NAME                      | EXTENSION                                                                                  | CREATO | DRO
--------------- | ------------------------------ | ----------------------------------- | ------------------------------------------------------------------------------------------ | ------ | ---
XXXX            | ACC                            | SYS_NC00017$                        | (UPPER("LGN"))                                                                             | SYSTEM | NO

and lock stats using scripts we generate before

Primary Sidebar