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