jeudi 12 mars 2015
11g cursor sharing
11g cursor sharing
cursor sharing off
--alter system set "cursor_sharing"=exact scope=both sid='*';
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=memory sid='*';
alter system set "_optimizer_extended_cursor_sharing"=none scope=memory sid='*';
alter system set "_optimizer_adaptive_cursor_sharing"=false scope=memory sid='*';
alter system set "_optim_peek_user_binds"=false scope=memory sid='*';
cursor sharing on
--alter system set "cursor_sharing"=exact scope=both sid='*';
alter system set "_optimizer_extended_cursor_sharing_rel"=SIMPLE scope=both sid='*';
alter system set "_optimizer_extended_cursor_sharing"=UDO scope=both sid='*';
alter system set "_optimizer_adaptive_cursor_sharing"=true scope= both sid='*';
alter system set "_optim_peek_user_binds"=true scope=both sid='*';
RAC CONFIG DATABASE (SOLARIS)
RAC CONFIG DATABASE (SOLARIS)
cat /var/opt/oracle/oratab | grep -v '^#'| grep -v 'ASM' | awk -F':' '{print $1}' | sed '/^$/d' |while read line
do
echo ----------------------------------------------------------
$ORACLE_HOME/bin/srvctl config database -d $line
echo --------------------------------------------------------
done
cat /var/opt/oracle/oratab | grep -v '^#'| grep -v 'ASM' | awk -F':' '{print $1}' | sed '/^$/d' |while read line
do
echo ----------------------------------------------------------
$ORACLE_HOME/bin/srvctl config database -d $line
echo --------------------------------------------------------
done
used_options_details.sql
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
------- Name : used_options_details.sql
------- Usage : sqlplus -s <UserName/Password> @used_options_details.sql
------- > <output file>
------- Description : This script provides detailed report of features used by each
------- Database Options/Management Packs for Oracle Database
------- 11g Release 2 only. You need DBA role to execute the
------- script.The report is based on DBA_FEATURE_USAGE_STATISTICS view.
------- Note that the feature usage data in the view is updated once
------- a week, so it may take up to 7 days for the report to show
------- recent usage of options and/or packs.
------- The "Currently Used" column is derived from currently_used column
------- of DBA_FEATURE_USAGE_STATISTICS View. It denotes if the feature in
------- question was used during the last sampling interval by version.
-------
------- Disclaimer : The following report will provide you an overview of the
------- licensable Database Options and Enterprise Management
------- Packs that were identified as used by your organization.
------- This is to be used for informational purposes only and
------- this does not represent your license entitlement or
------- requirement. If any discrepancy is noticed in the
------- Options usage reporting please contact
------- License Management Services (LMS) representative at
------- http://www.oracle.com/us/corporate/license-management-services/index.html
-------
------- The Options Usage data in some cases return false
------- positives, please see MOS DOC ID 1309070.1 for more
------- information. This is may be due to inclusion of usage
------- by sample schemas (such as HR, PM, SH...) or system/
------- /internal usage. If you find a discrepancy in the
------- report, use the supplied < used_options_details.sql > to narrow
------- down the cause of incorrect reporting. Please report it to
------- Oracle Support and contact LMS representative.
----------
---- 1 - catnofus.sql 2- catdbfus.sql
------- 3 - EXEC dbms_feature_usage_internal.exec_db_usage_sampling(curr_date => SYSDATE)
---- http://psoug.org/reference/dbms_mgmt_packs.html
------ wri$_dbu_usage_sample samp, wri$_dbu_feature_usage fu, wri$_dbu_feature_metadata mt
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
SET LINESIZE 350;
SET PAGESIZE 1000;
SET FEEDBACK OFF;
SET COLSEP '|';
WHENEVER SQLERROR EXIT SQL.SQLCODE;
COL "Option/Management Pack" FORMAT A60;
COL "Used" FORMAT A5;
COL "Feature being Used" FORMAT A50;
COL "Currently Used" FORMAT A14;
COL "Last Usage Date" FORMAT A18;
COL "Last Sample Date" FORMAT A18;
COL "Host Name" FORMAT A30;
with features as(
select a OPTIONS, b NAME from
(
select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual
union all
select 'Advanced Compression', 'HeapCompression' from dual
union all
select 'Advanced Compression', 'Backup BZIP2 Compression' from dual
union all
select 'Advanced Compression', 'Backup DEFAULT Compression' from dual
union all
select 'Advanced Compression', 'Backup HIGH Compression' from dual
union all
select 'Advanced Compression', 'Backup LOW Compression' from dual
union all
select 'Advanced Compression', 'Backup MEDIUM Compression' from dual
union all
select 'Advanced Compression', 'Backup ZLIB, Compression' from dual
union all
select 'Advanced Compression', 'SecureFile Compression (user)' from dual
union all
select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual
union all
select 'Advanced Compression', 'Data Guard' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual
union all
select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual
union all
select 'Advanced Security', 'ASO native encryption and checksumming' from dual
union all
select 'Advanced Security', 'Transparent Data Encryption' from dual
union all
select 'Advanced Security', 'Encrypted Tablespaces' from dual
union all
select 'Advanced Security', 'Backup Encryption' from dual
union all
select 'Advanced Security', 'SecureFile Encryption (user)' from dual
union all
select 'Change Management Pack (GC)', 'Change Management Pack (GC)' from dual
union all
select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual
union all
select 'Data Mining', 'Data Mining' from dual
union all
select 'Diagnostic Pack', 'Diagnostic Pack' from dual
union all
select 'Diagnostic Pack', 'ADDM' from dual
union all
select 'Diagnostic Pack', 'AWR Baseline' from dual
union all
select 'Diagnostic Pack', 'AWR Baseline Template' from dual
union all
select 'Diagnostic Pack', 'AWR Report' from dual
union all
select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual
union all
select 'Diagnostic Pack', 'Baseline Static Computations' from dual
union all
select 'Tuning Pack', 'Tuning Pack' from dual
union all
select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual
union all
select 'Tuning Pack', 'SQL Tuning Advisor' from dual
union all
select 'Tuning Pack', 'SQL Access Advisor' from dual
union all
select 'Tuning Pack', 'SQL Profile' from dual
union all
select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual
union all
select 'Database Vault', 'Oracle Database Vault' from dual
union all
select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual
union all
select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual
union all
select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual
union all
select 'Exadata', 'Exadata' from dual
union all
select 'Label Security', 'Label Security' from dual
union all
select 'OLAP', 'OLAP - Analytic Workspaces' from dual
union all
select 'Partitioning', 'Partitioning (user)' from dual
union all
select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual
union all
select 'Real Application Testing', 'Database Replay: Workload Capture' from dual
union all
select 'Real Application Testing', 'Database Replay: Workload Replay' from dual
union all
select 'Real Application Testing', 'SQL Performance Analyzer' from dual
union all
select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual
union all
select 'Total Recall', 'Flashback Data Archive' from dual
)
)
select
t.o "Option/Management Pack",
t.u "Used",
t.n "Feature being Used",
t.v "Version",
t.cu "Currently Used",
t.du "Detected Usage",
t.lud "Last Usage Date",
t.ts "Total Samples",
t.lsd "Last Sample Date",
d.DBID "DBID",
d.name "DB Name",
i.version "Curr DB Version",
i.host_name "Host Name",
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time"
from (
select f.OPTIONS o,
'YES' u,
f_stat.version v,
case when f_stat.name in ('Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') then 'Data Pump Compression'
when f_stat.name in ('Data Guard') then 'Data Guard Network Compression'
else f_stat.name
end n,
f_stat.CURRENTLY_USED cu,
(f_stat.DETECTED_USAGES) du,
to_char(f_stat.LAST_USAGE_DATE, 'DD-MON-YY HH24:MI:SS') lud,
(f_stat.TOTAL_SAMPLES) ts,
to_char(f_stat.LAST_SAMPLE_DATE, 'DD-MON-YY HH24:MI:SS') lsd
from features f,
sys.dba_feature_usage_statistics f_stat
where f.name = f_stat.name and
( (f_stat.currently_used = 'TRUE' and
f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0
)
or
(f_stat.detected_usages > 0 and
(sysdate - f_stat.last_usage_date) < 366 and
f_stat.total_samples > 0)
) and
( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')
or
(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and
f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0')
)
) t,
v$instance i,
v$database d
order by t.o,t.n,t.v
;
SELECT a.*
FROM (
SELECT dfs.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY version
DESC) rno
FROM DBA_FEATURE_USAGE_STATISTICS dfs
WHERE detected_usages > 0
AND name IN
(
'Advanced Security',
'Automatic Database Diagnostic Monitor',
'Data Mining',
'Diagnostic Pack',
'Label Security',
'Partitioning (user)',
'RMAN - Tape Backup',
'Real Application Clusters (RAC)',
'SQL Access Advisor',
'SQL Tuning Advisor',
'SQL Tuning Set',
'Spatial',
'Transparent Gateway'
)
) a
WHERE a.rno = 1
;
Solution:
1.shutdown immediate
2.startup upgrade
3.run catalog and catproc scritp
4.run utlrp
5.shutdown immediate
6.startup
7.select comp_name,version,status from dba_registry;
Another tips
Procedure :
1 - init or spfile : reset parameter
control_management_pack_access
2 - purge : exec dbms_management_packs.purge('TUNING');
exec dbms_management_packs.purge('DIAGNOSTIC');
3 - $ORACLE_HOME/rdbms/admin/catnofus.sql
$ORACLE_HOME/rdbms/admin/catdbfus.sql
UPGRADE RMAN CATALOG FROM 11G TO 12C
=============================================
UPGRADE CATALOG RMAN FROM 11G TO 12C
=============================================
The doc tells you to run this step first:
SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql
Please ignore the "-all" option printed in the doc - this is a known docu bug
If you miss this step the upgrade of the catalog will fail with a warning that your user lacks privileges
$ rman CATALOG my_catalog_owner@catdb
recovery catalog database Password:
RMAN> UPGRADE CATALOG;
RMAN> EXIT;
It should work now :-)
statistic oracle
statistic oracle :
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;
set params :
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$;
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_
trace oracle
trace oracle :
===================
activer la trace
===================
The program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.
Example
Set SQL tracing on in a session identified by sid = 15 and serial number = 4567 like this:
BEGIN
S (15,4567,TRUE);
END;
The sid, serial number, and username for all sessions currently connected to the database can be obtained using the following query:
SELECT sid, serial#, username
FROM v$session;
Trace files generated when SQL tracing is turned on are created in the directory specified by the USER_DUMP_DEST
initialization parameter for the Oracle database to which the session is connected.
SQL trace files can be formatted for readability using the TKPROF utility.
-------------------
GRANT EXECUTE ON DBMS_SYSTEM TO SYSTEM;
time_statistic =true
select * from v$session;
BEGIN
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,22,true);
END;
effectue les commandes sql/prgrm
BEGIN
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,22,FALSE);
END;
tkprof <source> <log>
---------------
issu d 1 tar
Hello
Please can you perform the following:
1. select count(*) from so0@BM -->OK
select sysadm.count(*) from so0@BM ;
2. alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
update SYSADM.so0@BM set num4=
(select codeitac from ITAC.itac_pm@ITAC where code_client = '100');
exit;
This will produce a dump file in the USER_DUMP_DEST, please send this in.
Thanks
Richard
##------------------------------------------------------
Par PACKAGE
##------------------------------------------------------
ou
Francisco,
you can use dbms_support package or you can run:
SQL>exec sys.dbms_system.set_ev( sid, serial#, 10046, 8, '' );
where sid and serial# are obtained from v$session. To stop tracing replase 8 with 0 (this is the level for tracing).
---The SET_EV procedure is used to set trace event levels in another user session.
It is equivalent to having that session issue the ALTER SESSION SET EVENTS
event syntax command. Since the other session may be engaged in an application,
setting an event may not be possible. Here’s the header for this program:
PROCEDURE DBMS_SYSTEM.SET_EV
(si BINARY_INTEGER
,se BINARY_INTEGER
,ev BINARY_INTEGER
,le BINARY_INTEGER
,nm IN VARCHAR2);
Parameters:
si User session id
se User session serial number
ev Trace event number
le Trace event level
nm Trace event name
Exceptions
The program does not raise any exceptions.
Restrictions
w The SET_EV procedure should not be invoked by end users or called by PL/SQL programs. Its usage is limited to Oracle internal purposes or under instruction of Oracle Technical Support. Improper use of the procedure by end users can have adverse effects on database performance or cause database crashes.
w The program does not assert a purity level with the RESTRICT_REFERENCES pragma.
========================
dbms_monitor
========================
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
SQL> exec dbms_monitor.SESSION_TRACE_ENABLE(2216,39,true,true);
tkprof bal2wdp0_ora_1036496.trc trace01.tkp sys=n waits=y explain=balifr/balifradm sort=exeela
trace a service amdbs
exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE('rdxsupsvc');
===================
activer la trace
===================
The program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.
Example
Set SQL tracing on in a session identified by sid = 15 and serial number = 4567 like this:
BEGIN
S (15,4567,TRUE);
END;
The sid, serial number, and username for all sessions currently connected to the database can be obtained using the following query:
SELECT sid, serial#, username
FROM v$session;
Trace files generated when SQL tracing is turned on are created in the directory specified by the USER_DUMP_DEST
initialization parameter for the Oracle database to which the session is connected.
SQL trace files can be formatted for readability using the TKPROF utility.
-------------------
GRANT EXECUTE ON DBMS_SYSTEM TO SYSTEM;
time_statistic =true
select * from v$session;
BEGIN
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,22,true);
END;
effectue les commandes sql/prgrm
BEGIN
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(9,22,FALSE);
END;
tkprof <source> <log>
---------------
issu d 1 tar
Hello
Please can you perform the following:
1. select count(*) from so0@BM -->OK
select sysadm.count(*) from so0@BM ;
2. alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
update SYSADM.so0@BM set num4=
(select codeitac from ITAC.itac_pm@ITAC where code_client = '100');
exit;
This will produce a dump file in the USER_DUMP_DEST, please send this in.
Thanks
Richard
##------------------------------------------------------
Par PACKAGE
##------------------------------------------------------
ou
Francisco,
you can use dbms_support package or you can run:
SQL>exec sys.dbms_system.set_ev( sid, serial#, 10046, 8, '' );
where sid and serial# are obtained from v$session. To stop tracing replase 8 with 0 (this is the level for tracing).
---The SET_EV procedure is used to set trace event levels in another user session.
It is equivalent to having that session issue the ALTER SESSION SET EVENTS
event syntax command. Since the other session may be engaged in an application,
setting an event may not be possible. Here’s the header for this program:
PROCEDURE DBMS_SYSTEM.SET_EV
(si BINARY_INTEGER
,se BINARY_INTEGER
,ev BINARY_INTEGER
,le BINARY_INTEGER
,nm IN VARCHAR2);
Parameters:
si User session id
se User session serial number
ev Trace event number
le Trace event level
nm Trace event name
Exceptions
The program does not raise any exceptions.
Restrictions
w The SET_EV procedure should not be invoked by end users or called by PL/SQL programs. Its usage is limited to Oracle internal purposes or under instruction of Oracle Technical Support. Improper use of the procedure by end users can have adverse effects on database performance or cause database crashes.
w The program does not assert a purity level with the RESTRICT_REFERENCES pragma.
========================
dbms_monitor
========================
PROCEDURE SESSION_TRACE_ENABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SESSION_ID BINARY_INTEGER IN DEFAULT
SERIAL_NUM BINARY_INTEGER IN DEFAULT
WAITS BOOLEAN IN DEFAULT
BINDS BOOLEAN IN DEFAULT
SQL> exec dbms_monitor.SESSION_TRACE_ENABLE(2216,39,true,true);
tkprof bal2wdp0_ora_1036496.trc trace01.tkp sys=n waits=y explain=balifr/balifradm sort=exeela
trace a service amdbs
exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE('rdxsupsvc');
REPORT RMAN
REPORT RMAN querying catalog RMAN not each db:
Report day to day:
[/appl/oracle/BIN]# cat report_rman.sql
set lines 200
set pages 250
prompt '-------------------------------------------------------------------------'
prompt 'backup bilan'
prompt '-------------------------------------------------------------------------'
select status, count(status) from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME) >trunc(sysdate -2) group by status;
prompt '-------------------------------------------------------------------------'
prompt 'backup ok'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='COMPLETED'
and b.db_key=a.db_key order by 4;
prompt '-------------------------------------------------------------------------'
prompt 'backup a controler'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status!='COMPLETED' and status !='FAILED'
and b.db_key=a.db_key order by STATUS;
prompt '-------------------------------------------------------------------------'
prompt 'backup FAILED'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='FAILED'
and b.db_key=a.db_key order by STATUS;
Report day to day:
[/appl/oracle/BIN]# cat report_rman.sql
set lines 200
set pages 250
prompt '-------------------------------------------------------------------------'
prompt 'backup bilan'
prompt '-------------------------------------------------------------------------'
select status, count(status) from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS where trunc(START_TIME) >trunc(sysdate -2) group by status;
prompt '-------------------------------------------------------------------------'
prompt 'backup ok'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='COMPLETED'
and b.db_key=a.db_key order by 4;
prompt '-------------------------------------------------------------------------'
prompt 'backup a controler'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status!='COMPLETED' and status !='FAILED'
and b.db_key=a.db_key order by STATUS;
prompt '-------------------------------------------------------------------------'
prompt 'backup FAILED'
prompt '-------------------------------------------------------------------------'
select b.DB_KEY, b.DB_NAME,a.dbid,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
input_type,STATUS,ELAPSED_SECONDS,OUTPUT_DEVICE_TYPE --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -2)
and status='FAILED'
and b.db_key=a.db_key order by STATUS;
Duree pour faire un diagramme de gant :
select input_type||'_'||b.DB_NAME||'_'||a.dbid database,to_char(START_TIME,'DD/MM/YYYY HH24:MI') debut ,
--to_char(END_TIME,'DD/MM/YYYY HH24:MI') fin,
--input_type,STATUS,
ELAPSED_SECONDS,
--OUTPUT_DEVICE_TYPE,
TIME_TAKEN_DISPLAY --,SESSION_KEY,SESSION_RECID,SESSION_STAMP
from CATALOG_RMAN.RC_RMAN_BACKUP_JOB_DETAILS b, catalog_rman.RC_DATABASE a where trunc(START_TIME) >trunc(sysdate -3)
and ELAPSED_SECONDS >300
and b.db_key=a.db_key order by start_time;
============
Keep
============
select c.DB_NAME,b.bs_key,KEEP,KEEP_UNTIL,KEEP_OPTIONS ,b.db_key,b.backup_type,b.START_TIME
,MEDIA
from RC_BACKUP_SET_details b ,RC_BACKUP_PIECE_DETAILS c
where
c.db_key=b.db_key
and trunc(b.START_TIME) <trunc(sysdate -30)
and KEEP_UNTIL is not null
and b.db_name=c.db_name
and b.BS_KEY=c.bs_key
and c.db_name ='PRD'
and trunc (c.START_TIME) =trunc(b.START_TIME)
--order by b.start_time, KEEP_UNTIL;
and KEEP_UNTIL is not null
order by b.start_time;
Keep
============
select c.DB_NAME,b.bs_key,KEEP,KEEP_UNTIL,KEEP_OPTIONS ,b.db_key,b.backup_type,b.START_TIME
,MEDIA
from RC_BACKUP_SET_details b ,RC_BACKUP_PIECE_DETAILS c
where
c.db_key=b.db_key
and trunc(b.START_TIME) <trunc(sysdate -30)
and KEEP_UNTIL is not null
and b.db_name=c.db_name
and b.BS_KEY=c.bs_key
and c.db_name ='PRD'
and trunc (c.START_TIME) =trunc(b.START_TIME)
--order by b.start_time, KEEP_UNTIL;
and KEEP_UNTIL is not null
order by b.start_time;
Inscription à :
Articles (Atom)