SELECT dbms_stats.get_stats_history_retention() AS retention FROM dual; --31 jours par defaut
dbms_stats.alter_stats_history_retention(retention => 14):
dbms_stats.purge_stats(before_timestamp => systimestamp-14)
info sur les stats
SELECT operation, start_time,
(end_time-start_time) DAY(1) TO SECOND(0) AS duration
FROM dba_optstat_operations where trunc(start_time)=trunc(sysdate)
ORDER BY start_time DESC;
===================
export/import stats
===================
Export / import de statistiques
On considère que l'on a une base de Production, avec plein de données dedans (...), et une base de développement, quasiment vide; ce qu'on souhaite, c'est faire en sorte que les plans d'exécutions de nos requêtes soient identiques. Comme on utilise l'optimiseur statistique (ben oui, on est en 10g, hé!), on va pouvoir gruger les traitements de la base de développements en leur faisant croire qu'il y a tout plein de données.
Exemple ici pour une table MATABLE, identique sur les 2 bases. On peut bien-sûr faire la même chose au niveau d'un schéma (dbms_stats.export_schema_stats).
Sur la base de données source
- calcul des stats
> analyze table matable compute statistics;
- création de la table de statistiques
exec dbms_stats.create_stat_table('SAPR3','stat_ARFCRSTATE');
- sauvegarde des stats dans la table MATABLEDESTATS
exec dbms_stats.EXPORT_TABLE_STATS(ownname=>'SAPR3',tabname=>'ARFCRSTATE',stattab=>'stat_ARFCRSTATE');
exec dbms_stats.EXPORT_SCHEMA_STATS(ownname=>'BALIFR',stattab=>'STATTAB');
exec dbms_stats.IMPORT_TABLE_STATS(ownname=>'SAPR3',tabname=>'ARFCRSTATE',stattab=>'stat_ARFCRSTATE');
- export de la table de sauvegarde des statistiques
$ exp MG/mgmg file=exp_matabledestats.dmp log=exp_matabledestats tables=radairprd.matabledestats
Sur la base de données cible
- import de la table de sauvegarde des statistiques
$ imp MG/mgmg file=exp_matabledestats.dmp log=imp_matabledestats.log tables=ods.matabledestats
- restauration des statistiques
> exec dbms_stats.import_table_stats(ownname=>'ODS',tabname=>'ODSPNR_DET_PASSAGER',stattab=>'MATABLEDESTATS');
Trucs & astuces
- suppression d'une table de statistiques
> exec dbms_stats.drop_stat_table('MONUSER','MATABLEDESTATS);
- suppression du contenu d une table de statistiques
> exec dbms_stats.delete_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');
exec dbms_stats.LOCK_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');
exec dbms_stats.unLOCK_table_stats(ownname=>'MONUSER',tabname=>'MATABLE',stattab=>'MATABLEDESTATS');
exec dbms_stats.unLOCK_schema_stats(ownname=>'AIR_PP');
===================================
dbms_stats get param
===================================
select dbms_stats. ('autostats_target') from dual;
select dbms_stats.get_param('estimate_percent') from dual;
select dbms_stats.get_param('method_opt') from dual;
select dbms_stats.get_param('degree') from dual;
select dbms_stats.get_param('cascade') from dual;
select dbms_stats.get_param('granularity') from dual;
select dbms_stats.get_param('autostats_target') from dual;
select dbms_stats.get_param('STALE_PERCENT') from dual;
exec dbms_stats.set_param('CASCADE', 'TRUE');
exec dbms_stats.set_param('ESTIMATE_PERCENT','30');
exec dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT'); --repeat ==> si histogram il recalcule avec histogram
exec dbms_stats.gather_schema_stats(ownname =>'REFMODEBATCH_QUALIF');
exec dbms_stats.set_param('STALE_PERCENT','2');
stats sans histograme
exec dbms_stats.gather_schema_stats(ownname=>'PBSTSC_PI' ,cascade=>TRUE,estimate_percent=> 100,method_opt=>'FOR ALL COLUMNS SIZE 1',OPTIONS=>'GATHER');
stats avec historgrame si la table a deja un historgrame:
exec dbms_stats.gather_schema_stats(ownname=>'CREDO_INT',cascade=>TRUE,estimate_percent=> 30,method_opt=>'FOR ALL COLUMNS SIZE REPEAT',OPTIONS=>'GATHER STALE');
stats avec histogram sur une colonne:
exec dbms_stats.gather_table_stats(ownname =>'SAPR3',tabname =>'ARFCRSTATE',estimate_percent =>30,degree =>DBMS_STATS.DEFAULT_DEGREE
,method_opt =>'FOR COLUMNS CODEVALEURISIN SIZE 254',granularity =>'ALL',cascade =>true);
enlever les stat sur les tables:
select 'dbms_stats.delete_table_stats(ownname => ''ADMDEO'',tabname =>''' || A.TABLE_NAME||''');'
from sys.dba_tables a where a.owner='ADMDEO' and and A.NUM_ROWS=0
select distinct 'exec dbms_stats.gather_index_stats(ownname => '''|| owner||''',indname=>'''||index_name||''',estimate_percent=>100,stattab=>''STATTAB'||owner||''',statown=>'''||owner||''');'
stat system a partir de 10g :
exec dbms_stats.gather_system_stats('noworkload');
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
select sname , pname , pval1 , pval2 from sys.aux_stats$;
=========================================
-- Hakan
-- restauration de stats (10g)
-- pour voir la durée demandé
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
-- pour voir la durée max a laquel on peut faire une restauration
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
select TABLE_NAME,PARTITION_NAME,STATS_UPDATE_TIME from DBA_TAB_STATS_HISTORY where OWNER='RADAIR_QA' and table_name='E_INTERLINE_MSGS' order by STATS_UPDATE_TIME;
select * from DBA_OPTSTAT_OPERATIONS order by START_TIME
select sname,sval1,sval2,spare1,spare4 from sys.OPTSTAT_HIST_CONTROL$;
==============================
stats oracle table volatile
==============================
The first option is to set statistics to NULL. As part of Oracle’s query optimization, any table
with no statistics will have them generated dynamically via the dynamic sampling feature. This
“just-in-time” statistics generation ensures that no query will be executed without statistics. The
parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher
to enable this feature.
SQL> exec DBMS_STATS.DELETE_TABLE_STATS('ADMDEO','TRANSACTION_DATA');
PL/SQL procedure successfully completed
SQL> exec DBMS_STATS.LOCK_TABLE_STATS('BUTERTB', 'VOLATILE_TABLE');
========================
histogram dbms_stats
========================
dbms_stats.gather_table_stats(<schema_name>, <table_name>,
METHOD_OPT => FOR COLUMN SIZE <integer> <column_name>
exple histogram sur la colonne status avec 254 buckets
exec dbms_stats.delete_table_stats(ownname =>'ODS',tabname =>'ODSPDP_DOC_JOURNAL_PROD_HISTO');
exec dbms_stats.gather_table_stats(ownname =>'RADAIR_PPR',tabname =>'LOOKUP',cascade =>false ,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,degree =>DBMS_STATS.DEFAULT_DEGREE,
method_opt =>'FOR COLUMNS STATUT SIZE 254 ',granularity =>'ALL',cascade =>true,stattab =>'STATTAB');
verif histogram :
SELECT a.owner,a.table_name,column_name, num_distinct, num_buckets,a.LAST_ANALYZED,DENSITY,num_rows,HISTOGRAM
FROM dba_TAB_COL_STATISTICS a,dba_tables b WHERE num_buckets>1 and A.table_name=b.table_name and A.owner ='RADAIR_PPR' ; and a.table_name='LOOKUP';
and b.table_name'CX_USER';
SELECT table_name,ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM DBA_HISTOGRAMS
WHERE TABLE_NAME ='CX' AND COLUMN_NAME='STATUT'
ORDER BY ENDPOINT_NUMBER;
SELECT sum(case when max_cnt > 2 then 1 else 0 end) histograms,
sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms
FROM (
SELECT table_name, max(cnt) max_cnt
FROM (
SELECT table_name, column_name, count(*) cnt
FROM dba_tab_histograms
GROUP BY table_name, column_name
) GROUP BY table_name
);
select table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed
from dba_tab_col_statistics where owner='BSG' order by 1,2;
SELECT table_name, sample_size, num_rows,
round(sample_size/num_rows*100,1) AS "%"
FROM dba_tables
WHERE num_rows > 0
ORDER BY table_name;
SELECT sname AS parameter, nvl(spare4,sval1) AS default_value
FROM sys.optstat_hist_control$
WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE',
'METHOD_OPT','NO_INVALIDATE','GRANULARITY');
wich table are lock
SELECT table_name
FROM user_tab_statistics
WHERE stattype_locked IS NOT NULL;
===================================
OPTSTAT historique des rowcount
au moment des stat oracle
===================================
pour les tables
select ie.table_name, ih.savtime, ih.rowcnt, ih.BLKCNT---*8192 "taille octets"
from sys.WRI$_OPTSTAT_TAB_HISTORY ih, dba_objects ob, dba_tables ie
where ih.obj# = ob.object_id
and ob.object_name = ie.table_name
and ob.owner = ie.owner
and ob.object_type = 'TABLE'
--and ie.table_name = 'ADM_USER_ACTION'
and ob.owner='RADAIR_DEV'
order by 2;
Pour les indexes :
select ie.index_name, ih.savtime, ih.rowcnt, ih.blevel, ih.leafcnt
from sys.WRI$_OPTSTAT_IND_HISTORY ih, dba_objects ob, dba_indexes ie
where ih.obj# = ob.object_id
and ob.object_name = ie.index_name
and ob.owner = ie.owner
and ob.object_type = 'INDEX'
and ie.table_name = 'T5'
order by 2;
====================================================
How to find the tables that have stale statistics?
====================================================
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/
================================
Stats individual thresholds
================================
In Oracle database 11g there is a new procedure in the DBMS_STATS package we can use to even set individual thresholds on segment level.
Here is how it works:
lutz@ora11gBETA5 _SQL > exec dbms_stats.set_table_prefs('HR', 'EMPS', 'STALE_PERCENT', '15')
The previous statement adjusts the threshold for the table EMPS owned by HR to 15%.
All togehter there are three new parameters in DBMS_STATS:
- STALE_PERCENT which overwrites the default threshold of 10%
- INCREMENTAL for statistics on partitions incrementally gathered
- PUBLISH in order to populate the data dictionary with the newly gathered statistics or not, whcih can conme in handy if you want to test with the new statistics on another system before really making them effective on production.
I think that this is exactely what a lot of people have been waiting for since a long time
===================================================================================
SAP extended statistics on function
You want to create statistics on an expression such as an user-created function.
You’re aware that certain columns from a table that are part of a join condition are correlated.
You want to make the optimizer aware of this relationship.
===================================================================================
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'AUSP', '(MANDT, KLART, ATINN)') FROM DUAL;
-- BKPF --
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'BKPF', '(MANDT, BUKRS, BSTAT)') FROM DUAL;
-- HRP1001 --
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'HRP1001', '(RELAT, SCLAS, OTYPE, PLVAR)') FROM DUAL;
-- MSEG --
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, MATNR, WERKS, LGORT)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, MBLNR, MJAHR)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, BWART)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, BWART, LGORT)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(:OWNER, 'MSEG', '(MANDT, WERKS, LGORT)') FROM DUAL;
exec dbms_stats.seed_col_usage(null,null,1800); --1800 secondes as sysdba
select dbms_stats.report_col_usage(user,'RES_PAYMENTS') from dual; --as radair_
Aucun commentaire:
Enregistrer un commentaire