What is Cluster?
Cluster consists of two or more independent nodes. Cluster software hides structure of nodes. Due to cluster software all nodes are acting as single server.
What is Real Application Cluster?
It is component of Oracle for managing two or more instances on different node which are sharing single database. RAC software manages data access and makes consistent image of database.
What is Node?
Each node contains separate CPU and memory and self contained server in cluster. It contains single instance of RAC database.
What is Interconnect?
Using cluster interconnect each node communicates messages to other nodes. In short using interconnect all nodes are talking to each other. Interconnect transfers all messages of database from all connected nodes.
What is Shared Disk?
Oracle real application cluster database must be accessible from all connected nodes (Instances). Due to this database must be on shared disk which is accessed by all nodes. RAW device file system available in Unix/Linux and unformatted disk partition available in Windows for making shared disk.
What is Cluster Manager?
Cluster Manager manages and monitoring connected nodes. It regulates messages and activity of nodes in cluster.
What is Oracle Cluster file system (OCFS)?
OCFS is shared file system and made by Oracle itself for Oracle Real Application Cluster. It allows to accessing single Oracle Home for all nodes. Without using OCFS we should need to install separate Oracle Home on each nodes in Cluster.
What is Oracle Clusterware?
In previous version of oracle (Oracle 9i) it is called as Cluster Manager. Oracle clusterware monitors all components like instances and listeners. There are two components in Oracle clusterware, those are Voting Disk and OCR.
What is Voting Disk?
Voting Disk is shared disk component. Information of shared storage & nodes reside in Voting Disk. It is accessed by the all nodes during cluster operations. Every node pings Voting Disk, if will be failing to ping cluster immediate serves as communication failure & the node is evicted from cluster.
What is OCR?
OCR means Oracle Cluster Registry. It stores cluster configuration information. It is also shared disk component. It must be accessed by all nodes in cluster environment.It also keeps information of Which database instance run on which nodes and which service runs on which database.The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
Oracle: Query to find the 10 Largest Objects in DB
SELECT * FROM
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 10
Oracle: DBMS_STATS Gather Statistics of Schema, Tables, Indexes
In this post I'll try to cover all sorts of statistics in Oracle, I encourage you to read the full post cause I'm sure you will find a new information you may read it for the first time.
###############################
Database | Schema | Table | Index Statistics
###############################
Gather Database Stats:
====================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
degree => 4,
OPTIONS => 'GATHER STALE',
GATHER_SYS => TRUE
STATTAB => PROD_STATS);
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether
to collected or not.
DEGREE => 4 :Degree of parallelism.
OPTIONS =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats
objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT
STATISTICS section -last third in this post-.
Note: All parameters above are valid for all stats kind (schema,table,..) except Gather_SYS.
For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed
values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not
recommended to be gathered on all columns.
Removed "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU
overhead during gathering DB statistics.
This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;
Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'SCOTT',
estimate_percent=>10,
degree=>1,
cascade=>TRUE,
options=>'GATHER STALE');
Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';
SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'EMP',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2 :Degree of parallelism.
options =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE :(DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO :you can specify one column between "" instead of all
columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for
columns already have histograms.
FOR ALL COLUMNS :collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY : collect histo for columns have skewed value should test skewness first>.
FOR ALL INDEXED COLUMNS : collect histograms for columns have indexses only.
Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCOTT',
indname => 'EMP_I',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
###################
Fixed OBJECTS Statistics
###################
What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-also note that performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representative statistics.
How to gather stats on fixed objects:
------ ------------------------------
Firstly Check the last analyzed date:
------ --------------------------
select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';
Secondly Export the current fixed stats in a table: (in case you need to revert back)
------ - --------------------------------------
EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
EXEC dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Thirdly Gather the fixed objects stats:
------ ---------------------------
exec dbms_stats.gather_fixed_objects_stats;
Note In case of reverting back to the old stats:
---- ----------------------------------------
In case you experienced a bad performance on fixed tables after gathering the new statistics:
exec dbms_stats.delete_fixed_objects_stats();
exec DBMS_STATS.import_fixed_objects_stats(stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');
#################
SYSTEM STATISTICS
#################
What is system statistics:
-------------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.
Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):
NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD
statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();
WORKLOAD statistics:
----------------------
This will gather statistics during the current workload -which supposed to be representative of actual system I/O and CPU workload on the DB-.
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);
Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;
cpuspeedNW: Shows the noworkload CPU speed, in terms of the average number of CPU cycles per
second.
ioseektim: The sum of seek time, latency time, and OS overhead time.
iotfrspeed: I/O transfer speed,tells the optimizer how fast the database can read data in a single read
request.
cpuspeed: Stands for CPU speed during a workload statistics collection.
maxthr: The maximum I/O throughput.
slavethr: Average parallel slave I/O throughput.
sreadtim: The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim: The average time (seconds) for a sequential multiblock read.
mbrc: The average multiblock read count in blocks.
According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.
Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats();
####################
Data Dictionary Statistics
####################
Facts:
-----
>Dictionary tables are the tables owned by SYS and residing in the system tablespace.
>Normally data dictionary statistics in 9i is not required unless performance issues are detected.
>In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.
If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:
SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');
When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.
Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables where owner='SYS' and table_name like '%$' order by 2;
Gather Dictionary Statistics:
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; ->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS'); ->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE); ->Will gather stats on the whole DB+SYS schema.
#############
Extended Statistics "11g onwards"
#############
Extended statistics can be gathered on columns based on functions or column groups.
Gather extended stats on column function:
===================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';
In order to make optimizer work with function based terms you need to gather extended stats:
1-Create extended stats:
>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;
2-Gather histograms:
>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'EMP',
method_opt => 'for all columns size skewonly for columns (lower(ENAME))'
);
end;
To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');
Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement with the column group the optimizer will make use of column group statistics.
1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)') from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SH',
tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for columns (country_id,cust_state_province)'
);
end;
Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');
########
Histograms
########
What are Histograms?
------------------
>Holds data about values within a column in a table for number of occurrences for a specific value/range.
>Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
>Usually being used against columns have data being repeated frequently like country or city column.
>gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
>Two types of Histograms can be gathered:
>Frequency histograms: is when distinct values (buckets) in the column is less than 255 (# countries is always less than 254.
Height balanced histograms: are similar to frequency histograms in their design, but distinct values > 254
See Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms/
- Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
- Mainly being gathered on foreign key columns/columns in WHERE statement.
- Helps in SQL multi-table joins.
- Column histograms like statistics are being stored in data dictionary.
- If application exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histograms generation.
Caution:
– Do not create them on Columns that are not queried.
– Do not create them on every column of every table.
– Do not create them on PK of a table.
Verify the existence of histograms:
----------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics where owner='SCOTT' and table_name='EMP';
Creating Histograms:
----------------------
e.g.
SQL> Exec dbms_stats.gather_table_stats(
ownname => '',
tabname => '',
estimate_percent => dbms_stats.auto_sample_size,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO');
SQL> Exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
FOR COLUMNS SIZE AUTO => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT => to prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value.
FOR ALL INDEXES COLUMNS => collect histograms for columns have indexes.
Note: For AUTO & SKEWONLY Oracle will decide whatever to create Histograms or not.
Check existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms where table_name='SMFILECABINET' group by column_name;
Drop Histograms: 11g
-----------------
e.g.
SQL> Exec dbms_stats.delete_column_stats(ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=> HISTOGRAM);
Stop gathering Histograms: 11g
e.g.
SQL> Exec dbms_stats.set_table_prefs
('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for the PROD_ID column.
Drop Histograms: 10g
----------------
e.g.
SQL> exec dbms_stats.delete_column_stats( user, 'T', 'USERNAME' );
##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.
1-Create STATS TABLE:
- ----------------------
SQL> Exec dbms_stats.create_stat_table (ownname => 'SYSTEM', stattab => 'prod_stats', tblspace => 'USERS');
2-Export the statistics to the STATS table:
------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table:
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats (ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats (ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => 'SCOTT',stattab => 'prod_stats',statown => 'SYSTEM');
For Table stats and it's indexes:
SQL> Exec dbms_stats.import_TABLE_stats ( ownname => 'SCOTT', stattab => 'prod_stats', tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');
==============
Restore statistics: -From Dictionary-
==============
Old statistics are saved automatically in SYSAUX for 31 day.
Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1);
Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1);
Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1);
Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1);
Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1);
OR:
SQL> Exec dbms_stats.restore_schema_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=====
FACTS:
=====
To Check current Stats history retention period (days):
---------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
---------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60);
Purge statistics older than 10 days:
-------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:
Check Stats tables size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
/
Check Stats indexes size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
Move Stats tables in same tablespace:
>>>>>>
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
/
Rebuild Stats indexes:
>>>>>>
select 'alter index '||segment_name||' rebuild online;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
/
Check for un-usable indexes:
>>>>>>
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
Delete Statistics:
=============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS ();
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS ();
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS ();
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ();
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT');
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats (ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats ( ownname => 'SCOTT',indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.DELETE_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');
Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.
Pending Statistics: "11g onwards"
=============
Switch on pending statistics:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PENDING','TRUE');
Gather 11g statistics:
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Test your critical SQL statement with the pending stats:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
When proven, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();
###############################
Database | Schema | Table | Index Statistics
###############################
Gather Database Stats:
====================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL
COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
degree => 4,
OPTIONS => 'GATHER STALE',
GATHER_SYS => TRUE
STATTAB => PROD_STATS);
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether
to collected or not.
DEGREE => 4 :Degree of parallelism.
OPTIONS =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats
objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
GATHER_SYS => TRUE :Gathers statistics on the objects owned by the 'SYS' user.
STATTAB => PROD_STATS :Table will save the current statistics. see SAVE & IMPORT
STATISTICS section -last third in this post-.
Note: All parameters above are valid for all stats kind (schema,table,..) except Gather_SYS.
For faster execution:
------------------
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
What's new?
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed
values always gives excellent results.(DEFAULT).
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not
recommended to be gathered on all columns.
Removed "cascade => TRUE" To let Oracle determine whether index statistics to be collected or not.
Doubled "degree => 8" but this depends on the number of CPUs on the machine and accepted CPU
overhead during gathering DB statistics.
This task became an automated task starting from 10g, To check the status of that task:
SQL> select * from dba_autotask_client where client_name = "auto optimizer stats collection" ;
Gather SCHEMA Stats:
=====================
SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS (
ownname =>'SCOTT',
estimate_percent=>10,
degree=>1,
cascade=>TRUE,
options=>'GATHER STALE');
Gather TABLE Stats:
===================
Check table statistics date:
SQL> select table_name, last_analyzed from user_tables where table_name='T1';
SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'SCOTT',
tabname => 'EMP',
degree => 2,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
CASCADE => TRUE :Gather statistics on the indexes as well. If not used Oracle will determine whether to collect it or not.
DEGREE => 2 :Degree of parallelism.
options =>'GATHER' :Gathers statistics on all objects in the schema.
=>'GATHER AUTO' :Oracle determines which objects need new statistics, and determines how to
gather those statistics.
=>'GATHER STALE':Gathers statistics on stale objects. will return a list of stale objects.
=>'GATHER EMPTY':Gathers statistics on objects have no statistics.will return a list of no stats objects.
=>'LIST AUTO' : Returns a list of objects to be processed with GATHER AUTO.
=>'LIST STALE': Returns a list of stale objects as determined by looking at the
*_tab_modifications views.
=>'LIST EMPTY': Returns a list of objects which currently have no statistics.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE :(DEFAULT) Auto set the sample size % for skew(distinct) values (accurate and faster than setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO :
columns.
FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only for
columns already have histograms.
FOR ALL COLUMNS :
FOR ALL COLUMNS SIZE SKEWONLY : collect histo for columns have skewed value
FOR ALL INDEXED COLUMNS : collect histograms for columns have indexses only.
Gather Index Stats:
==================
SQL> exec DBMS_STATS.GATHER_INDEX_STATS(
ownname => 'SCOTT',
indname => 'EMP_I',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
###################
Fixed OBJECTS Statistics
###################
What are Fixed objects:
---------------------
-Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
-If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
-Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
How frequent to gather stats on fixed objects?
------------------------------------------
Only one time for a representative workload unless you've one of these cases:
-After a major database or application upgrade.
-After implementing a new module.
-After changing the database configuration. e.g. changing the size of memory pools (sga,pga,..).
-Poor performance/Hang encountered while querying dynamic views e.g. V$ views.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy) or after the peak hours but the sessions are still connected (even if they idle), to guarantee that the fixed object tables been populated and the statistics well represent the DB activity.
-also note that performance degradation may be experienced while the statistics are gathering.
-Having no statistics is better than having a non representative statistics.
How to gather stats on fixed objects:
------ ------------------------------
Firstly Check the last analyzed date:
------ --------------------------
select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';
Secondly Export the current fixed stats in a table: (in case you need to revert back)
------ - --------------------------------------
EXEC DBMS_STATS.CREATE_STAT_TABLE('OWNER','STATS_TABLE_NAME','TABLESPACE_NAME');
EXEC dbms_stats.export_fixed_objects_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');
Thirdly Gather the fixed objects stats:
------ ---------------------------
exec dbms_stats.gather_fixed_objects_stats;
Note In case of reverting back to the old stats:
---- ----------------------------------------
In case you experienced a bad performance on fixed tables after gathering the new statistics:
exec dbms_stats.delete_fixed_objects_stats();
exec DBMS_STATS.import_fixed_objects_stats(stattab =>'STATS_TABLE_NAME',STATOWN =>'OWNER');
#################
SYSTEM STATISTICS
#################
What is system statistics:
-------------------------
System statistics are statistics about CPU speed and IO performance, it enables the CBO to
effectively cost each operation in an execution plan. Introduced in Oracle 9i.
Why gathering system statistics:
------------------------------
Oracle highly recommends gathering system statistics during a representative workload,
ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.
You only have to gather system statistics once.
There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):
NOWORKLOAD statistics:
--------------------------
This will simulates a workload -not the real one but a simulation- and will not collect full statistics, it's less accurate than "WORKLOAD
statistics" but if you can't capture the statistics during a typical workload you can use noworkload statistics.
To gather noworkload statistics:
SQL> execute dbms_stats.gather_system_stats();
WORKLOAD statistics:
----------------------
This will gather statistics during the current workload -which supposed to be representative of actual system I/O and CPU workload on the DB-.
To gather WORKLOAD statistics:
SQL> execute dbms_stats.gather_system_stats('start');
Once the workload window ends after 1,2,3.. hours or whatever, stop the system statistics gathering:
SQL> execute dbms_stats.gather_system_stats('stop');
You can use time interval (minutes) instead of issuing start/stop command manually:
SQL> execute dbms_stats.gather_system_stats('interval',60);
Check the system values collected:
-------------------------------
col pname format a20
col pval2 format a40
select * from sys.aux_stats$;
cpuspeedNW: Shows the noworkload CPU speed, in terms of the average number of CPU cycles per
second.
ioseektim: The sum of seek time, latency time, and OS overhead time.
iotfrspeed: I/O transfer speed,tells the optimizer how fast the database can read data in a single read
request.
cpuspeed: Stands for CPU speed during a workload statistics collection.
maxthr: The maximum I/O throughput.
slavethr: Average parallel slave I/O throughput.
sreadtim: The Single Block Read Time statistic shows the average time for a random single block read.
mreadtim: The average time (seconds) for a sequential multiblock read.
mbrc: The average multiblock read count in blocks.
According to Oracle, collecting workload statistics doesn't impose an additional overhead on your system.
Delete system statistics:
---------------------
SQL> execute dbms_stats.delete_system_stats();
####################
Data Dictionary Statistics
####################
Facts:
-----
>Dictionary tables are the tables owned by SYS and residing in the system tablespace.
>Normally data dictionary statistics in 9i is not required unless performance issues are detected.
>In 10g Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window.
If you choose to switch off that job for application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET from AUTO to ORACLE using the procedure:
SQL> Exec DBMS_STATS.SET_PARAM(AUTOSTATS_TARGET,'ORACLE');
When to gather Dictionary statistics:
---------------------------------
-After DB upgrades.
-After creation of a new big schema.
-Before and after big datapump operations.
Check last Dictionary statistics date:
----------------------------------
SQL> select table_name, last_analyzed from dba_tables where owner='SYS' and table_name like '%$' order by 2;
Gather Dictionary Statistics:
-------------------------
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; ->Will gather stats on 20% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS'); ->Will gather stats on 100% of SYS schema tables.
or...
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE); ->Will gather stats on the whole DB+SYS schema.
#############
Extended Statistics "11g onwards"
#############
Extended statistics can be gathered on columns based on functions or column groups.
Gather extended stats on column function:
===================================
If you run a query having in the WHERE statement a function like upper/lower the optimizer will be off and index on that column will not be used:
SQL> select count(*) from EMP where lower(ename) = 'scott';
In order to make optimizer work with function based terms you need to gather extended stats:
1-Create extended stats:
>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SCOTT','EMP','(lower(ENAME))') from dual;
2-Gather histograms:
>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SCOTT','EMP', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'EMP',
method_opt => 'for all columns size skewonly for columns (lower(ENAME))'
);
end;
To check the Existance of extended statistics on a table:
---------------------------------------------------
SQL> select extension_name,extension from dba_stat_extensions where owner='SCOTT'and table_name = 'EMP';
SYS_STU2JLSDWQAFJHQST7$QK81_YB (LOWER("ENAME"))
Drop extended stats on column function:
-------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SCOTT','EMP','(LOWER("ENAME"))');
Gather extended stats on column group: -related columns-
=================================
Certain columns in a table that are part of a join condition (where statement are correlated e.g.(country,state). You want to make the optimizer aware of this relationship between two columns and more instead of using separate statistics for each columns. By creating extended statistics on a group of columns, the Optimizer can determine a more accurate the relation between the columns are used together in a where clause of a SQL statement. e.g. columns like country_id and state_name the have a relationship, state like Texas can only be found in USA so the value of state_name are always influenced by country_id.
If there are extra columns are referenced in the "WHERE statement with the column group the optimizer will make use of column group statistics.
1- create a column group:
>>>>>>>>>>>>>>>>>>>>>
SQL> select dbms_stats.create_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)') from dual;
2- Re-gather stats|histograms for table so optimizer can use the newly generated extended statistics:
>>>>>>>>>>>>>>>>>>>>>>>
SQL> exec dbms_stats.gather_table_stats ('SH','customers', method_opt=> 'for all columns size skewonly');
OR
--
*You can do it also in one Step:
>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> Begin
dbms_stats.gather_table_stats (
ownname => 'SH',
tabname => 'CUSTOMERS',
method_opt => 'for all columns size skewonly for columns (country_id,cust_state_province)'
);
end;
Drop extended stats on column group:
--------------------------------------
SQL> exec dbms_stats.drop_extended_stats('SH','CUSTOMERS', '(country_id,cust_state_province)');
########
Histograms
########
What are Histograms?
>Holds data about values within a column in a table for number of occurrences for a specific value/range.
>Used by CBO to optimize a query to use whatever index Fast Full scan or table full scan.
>Usually being used against columns have data being repeated frequently like country or city column.
>gathering histograms on a column having distinct values (PK) is useless because values are not repeated.
>Two types of Histograms can be gathered:
>Frequency histograms: is when distinct values (buckets) in the column is less than 255 (# countries is always less than 254.
See Example: http://aseriesoftubes.com/articles/beauty-and-it/quick-guide-to-oracle-histograms/
- Collected by DBMS_STATS (which by default doesn't collect histograms, it deletes them if you didn't use the parameter).
- Mainly being gathered on foreign key columns/columns in WHERE statement.
- Helps in SQL multi-table joins.
- Column histograms like statistics are being stored in data dictionary.
- If application exclusively uses bind variables, Oracle recommends deleting any existing Oracle histograms and disabling Oracle histograms generation.
– Do not create them on Columns that are not queried.
– Do not create them on every column of every table.
– Do not create them on PK of a table.
Verify the existence of histograms:
----------------------------------
SQL> select column_name,histogram from dba_tab_col_statistics where owner='SCOTT' and table_name='EMP';
Creating Histograms:
----------------------
e.g.
SQL> Exec dbms_stats.gather_table_stats(
ownname => '
tabname => '
estimate_percent => dbms_stats.auto_sample_size,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO
SQL> Exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7);
FOR COLUMNS SIZE AUTO => Fastest. you can specify one column instead of all columns.
FOR ALL COLUMNS SIZE REPEAT => to prevent deletion of histograms and collect it only for columns already have histograms.
FOR ALL COLUMNS => collect histograms on all columns
FOR ALL COLUMNS SIZE SKEWONLY => collect histograms for columns have skewed value
FOR ALL INDEXES COLUMNS => collect histograms for columns have indexes.
Note: For AUTO & SKEWONLY Oracle will decide whatever to create Histograms or not.
Check existence of Histograms:
SQL> select column_name, count(*) from dba_tab_histograms where table_name='SMFILECABINET' group by column_name;
Drop Histograms: 11g
-----------------
e.g.
SQL> Exec dbms_stats.delete_column_stats(ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=> HISTOGRAM);
Stop gathering Histograms: 11g
e.g.
SQL> Exec dbms_stats.set_table_prefs
('SH', 'SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
>Will continue to collect histograms as usual on all columns in the SALES table except for the PROD_ID column.
Drop Histograms: 10g
----------------
e.g.
SQL> exec dbms_stats.delete_column_stats( user, 'T', 'USERNAME' );
##################################
Save/IMPORT & RESTORE STATISTICS:
##################################
===================
Export /Import Statistics:
===================
In this way statistics will be exported into table then imported later from that table.
1-Create STATS TABLE:
- ----------------------
SQL> Exec dbms_stats.create_stat_table (ownname => 'SYSTEM', stattab => 'prod_stats', tblspace => 'USERS');
2-Export the statistics to the STATS table:
------------------------------------------
For Database stats:
SQL> Exec dbms_stats.export_database_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For System stats:
SQL> Exec dbms_stats.export_SYSTEM_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Dictionary stats:
SQL> Exec dbms_stats.export_Dictionary_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Fixed Tables stats:
SQL> Exec dbms_stats.export_FIXED_OBJECTS_stats (statown => 'SYSTEM', stattab => 'prod_stats');
For Schema stas:
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'STATS_TABLE_OWNER');
For Table:
SQL> Conn scott/tiger
SQL> Exec dbms_stats.export_TABLE_stats (ownname => 'SCOTT',tabname => 'EMP',stattab => 'prod_stats');
For Index:
SQL> Exec dbms_stats.export_INDEX_stats (ownname => 'SCOTT',indname => 'PK_EMP',stattab => 'prod_stats');
For Column:
SQL> Exec dbms_stats.export_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
3-Import the statistics from PROD_STATS table to the dictionary:
--------------------------------------------------------------------
For Database stats:
SQL> Exec DBMS_STATS.IMPORT_DATABASE_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For System stats:
SQL> Exec DBMS_STATS.IMPORT_SYSTEM_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Dictionary stats:
SQL> Exec DBMS_STATS.IMPORT_Dictionary_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Fixed Tables stats:
SQL> Exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS (stattab => 'prod_stats',statown => 'SYSTEM');
For Schema stats:
SQL> Exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => 'SCOTT',stattab => 'prod_stats',statown => 'SYSTEM');
For Table stats and it's indexes:
SQL> Exec dbms_stats.import_TABLE_stats ( ownname => 'SCOTT', stattab => 'prod_stats', tabname => 'EMP');
For Index:
SQL> Exec dbms_stats.import_INDEX_stats ( ownname => 'SCOTT', stattab => 'prod_stats', indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.import_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO',stattab=>'prod_stats');
4-Drop Stat Table:
-------------------
SQL> Exec dbms_stats.DROP_STAT_TABLE (stattab => 'prod_stats',ownname => 'SYSTEM');
==============
Restore statistics: -From Dictionary-
==============
Old statistics are saved automatically in SYSAUX for 31 day.
Restore Dictionary stats as of timestamp:
-----------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DICTIONARY_STATS(sysdate-1);
Restore Database stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_DATABASE_STATS(sysdate-1);
Restore SYSTEM stats as of timestamp:
--------------------------------------
SQL> Exec DBMS_STATS.RESTORE_SYSTEM_STATS(sysdate-1);
Restore FIXED OBJECTS stats as of timestamp:
-----------------------------------------------
SQL> Exec DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(sysdate-1);
Restore SCHEMA stats as of timestamp:
---------------------------------------
SQL> Exec dbms_stats.restore_SCHEMA_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>sysdate-1);
OR:
SQL> Exec dbms_stats.restore_schema_stats(ownname=>'SYSADM',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
Restore Table stats as of timestamp:
-----------------------------------
SQL> Exec DBMS_STATS.RESTORE_TABLE_STATS(ownname=>'SYSADM', tabname=>'T01POHEAD',AS_OF_TIMESTAMP=>sysdate-1);
=====
FACTS:
=====
To Check current Stats history retention period (days):
---------------------------------------------------
SQL> select dbms_stats.get_stats_history_retention from dual;
SQL> select dbms_stats.get_stats_history_availability from dual;
To modify current Stats history retention period (days):
---------------------------------------------------
SQL> Exec dbms_stats.alter_stats_history_retention(60);
Purge statistics older than 10 days:
-------------------------------
SQL> Exec DBMS_STATS.PURGE_STATS(SYSDATE-10);
Procedure To claim space after purging statstics:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Space will not be claimed automatically when you purge stats, you must claim it manually using this procedure:
Check Stats tables size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc
/
Check Stats indexes size:
>>>>>>
col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc
/
Move Stats tables in same tablespace:
>>>>>>
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='TABLE'
/
Rebuild Stats indexes:
>>>>>>
select 'alter index '||segment_name||' rebuild online;' from dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
/
Check for un-usable indexes:
>>>>>>
select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.table_name like '%OPT%'
order by 1 asc
/
Delete Statistics:
=============
For Database stats:
SQL> Exec DBMS_STATS.DELETE_DATABASE_STATS ();
For System stats:
SQL> Exec DBMS_STATS.DELETE_SYSTEM_STATS ();
For Dictionary stats:
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS ();
For Fixed Tables stats:
SQL> Exec DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ();
For Schema stats:
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS ('SCOTT');
For Table stats and it's indexes:
SQL> Exec dbms_stats.DELETE_TABLE_stats (ownname=>'SCOTT',tabname=>'EMP');
For Index:
SQL> Exec dbms_stats.DELETE_INDEX_stats ( ownname => 'SCOTT',indname => 'PK_EMP');
For COLUMN:
SQL> Exec dbms_stats.DELETE_COLUMN_stats (ownname =>'SCOTT',tabname=>'EMP',colname=>'EMPNO');
Note: This procedure can be rollback by restoring STATS using DBMS_STATS.RESTORE_ procedure.
Pending Statistics: "11g onwards"
=============
Switch on pending statistics:
SQL> Exec DBMS_STATS.SET_GLOBAL_PREFS('PENDING','TRUE');
Gather 11g statistics:
SQL> Exec DBMS_STATS.GATHER_TABLE_STATS('sh','SALES');
Test your critical SQL statement with the pending stats:
SQL> Alter session set optimizer_use_pending_statistics=TRUE;
When proven, publish the pending statistics:
SQL> Exec DBMS_STATS.PUBLISH_PENDING_STATS();
Create External Table from CSV File
To Create an External Table from CSV File, Follow these simple Steps
1) Create a Directory
2) Grant Read/Write Permission to that Directory
3) Place your CSV file in that directory at OS Level
4) Create EXTERNAL Table
Example:
1) create or replace directory MYCSV as '/home/oracle/mycsv';
Note: /home/oracle/mycsv has to be physical location on disk.
2) grant read, write on directory MYCSV to scott;
3) Put your csv file on /home/oracle/mycsv at OS level
4) create table my_ext_table
(
ENAME VARCHAR2(10),
DNAME VARCHAR2(14),
JOB VARCHAR2(9)
)
organization external
(
type ORACLE_LOADER
default directory MY_DIR
access parameters
(
records delimited by newline
fields terminated by "~"
)
location ('myfile.csv')
)
reject limit unlimited;
To create this CSV File you may use following command
set pagesize 0 newpage 0 feedback off
select ENAME || '~' || DNAME || '~' || JOB from SCOTT.EMP, SCOTT.DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
spool /home/oracle/test_dir/myfile.csv
/
spool off
How to find long running query in oracle?
SELECT l.sid, l.start_time, l.username, l.elapsed_seconds
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea a
WHERE a.elapsed = l. elapsed_seconds
AND l.elapsed_seconds > 1
a.sql_text, a.elapsed_time
FROM v$session_longops l, v$sqlarea a
WHERE a.elapsed = l. elapsed_seconds
AND l.elapsed_seconds > 1
Node evictions in RAC environment
Node eviction is quite sometimes happening in RAC
environment on any platform and troubleshooting and finding root cause for node
eviction is very important for DBAs to avoid same in the future. There are two
RAC processes which are basically deciding about node evictions and who will
initiate node evictions in almost all platforms.
1. OCSSD : This process is primary responsible for inter node health monitoring and instance endpoint recovery. It runs as oracle user. It also provides basic cluster locking and group services. It can run with or without vendor clusterware. The abnormal termination or killing this process will reboot the node by init.cssd script. If this script is killed then ocssd process will survive and node will keep functioning. This script is called by /etc/inittab entry and when it tries to respawn it and will try to start its own ocssd process. Since one ocssd process is already running, this 2nd time script calling ocssd starting will fail and 2nd init.cssd script will reboot the node.
2. OPROCD : This process is known as checking hangcheck and drive freezes on machine. On Linux, it is not available on 10.2.0.3 platform as this same function is performed by linux hangcheck timer module. Starting from 10.2.0.4, it will be started as part of clusterware startup and it runs as root. Killing this process will reboot the node. If a machine is hang for long time, this process needs to kill itself to avoid IO happening to disk so that rest of the nodes can remaster the resources. This executable sets a signal handler and sets the interval time bases on milliseconds parameter. It takes two parameters.
a. Timeout value –t : This is the length of time between executions. By default it’s 1000.
b. Margin –m : This is the acceptable difference between dispatches. By default, it’s 500.
When we set diagwait to 13, the margin becomes 13 -3 (reboottime seconds)= 10 seconds so value of m will be 10000.
There are two kinds of heartbeat mechanisms which are responsible for node reboot and reconfiguration of remaining clusteware nodes.
a. Network heartbit : This indicates that node can participate in cluster activities like group membership changes. When it’s missing for too long, cluster membership will change as a result of reboot. This too long value is determined by css miscount parameter value which is 30 seconds on most of platforms but can be changed depending on network configuration of particular environment. If at all it needs to be changed, it’s advisable to contact oracle support and take their recommendations on this.
b. Disk heartbit : This disk heartbit means heartbits to voting disk file which has the latest information about node members. Connectivity to a majority of voting files must be maintained for a node to stay alive. Voting disk file uses kill blocks to notify nodes they have been evicted and then remaining nodes can go for reconfiguration and a node with least no will become master as per Oracle algorithm generally. By default this value is 200 seconds which is css disktimeout parameter. Again changing this parameter requires oracle support’s recommendation. When node can no longer communicate through private interconnect, other nodes can see its heartbits in voting file then it’s being evicted by using voting disk kill block functionality.
Network split resolution : When network fails and nodes are not able to communicate to each other then one node has to fail to maintain data integrity. The surviving nodes should be an optimal subcluster of original cluster. Each node writes its own vote to voting file and Reconfiguration manager component reads these votes to calculate an optimal sub cluster. Nodes that are not to survive are evicted via communication through network and disk.
Causes of reboot by clusterware processes
======================================
Now we will briefly discuss about causes of reboot by these processes and at last, which files to review and upload to oracle support for further diagnosis.
Reboot by OCSSD.
============================
1. Network failure : 30 consecutive missed checkins will reboot a node where heartbits are issues once per second. Some kind of messages in occsd.log like heartbit fatal, eviction in xx seconds… Here there are two things.
a. If node eviction time in messages log file is less than missed checkins then node eviction is likely not due to missed checkins.
b. If node eviction time in messages log file is greater than missed checkins then node eviction is likely due to missed checkins.
2. Problems writing to voting disk file : some kind of hang in accessing voting disk.
3. High CPU utilization : When CPU is highly utilized then css daemon doesn’t get CPU on time to ping to voting disk and as a result, it cannot write to voting disk file its own vote and node is going to be rebooted.
4. Disk subsystem is unresponsive due to storage issues.
5. Killing ocssd process.
6. An oracle bug.
Reboot by OPROCD.
============================
When a problem is detected by oprocd, it’ll reboot the node for following reasons.
1. OS scheduler algorithm problem.
2. High CPU utilization due to which oprocd is not getting cpu to check hang check issues at OS level.
3. An oracle bug.
Also just to share with you, at one of the client sites, lms processes were running on low priority scheduling and lms were not getting cpu on time when there’s cpu is high utilized so lms couldn’t communicate through clusterware processes and node eviction got delayed and it was observed that oprocd rebooted node which should not have happened as lms was responsible to run at lower priority scheduling.
Determining cause of reboot by which process
==============================================
1. If there are below kind of messages in logfiles then it will be likely reboot by ocssd process.
a. Reboot due to cluster integrity in syslog file or messages file.
b. Any error prior to reboot in ocssd.log file.
c. Missed checkins in syslog file and eviction time is prior to node reboot time.
2. If there are below kind of messages in logfiles then it will be likely reboot by oprocd process.
a. Resetting message in messages logfile on linux.
b. Any error in oprocd log matching with timestamp of reboot or prior to reboot at /etc/oracle/oprocd directory.
3. If there are other messages like Ethernet issues or some kind of errors in messages or syslog file then please check with sysadmins. On AIX, errpt –a output gives lot of information about cause of reboot.
Log files collection while reboot of node
==============================================
Whenever node reboot occurs in clusterware environment, please review below logfiles for getting reason of reboot and these files are necessary to upload to oracle support for node eviction diagnosis.
a. CRS log files (For 10.2.0 and above 10.2.0 release)
=============================================
1. $ORACLE_CRS_HOME/log//crsd/crsd.log
2. $ORACLE_CRS_HOME/log//cssd/ocssd.log
3. $ORACLE_CRS_HOME/log//evmd/evmd.log
4. $ORACLE_CRS_HOME/log//alert.log
5. $ORACLE_CRS_HOME/log//client/cls*.log (not all files but only latest files matching with timestamp of node reboot)
6. $ORACLE_CRS_HOME/log//racg/ (Please check files and directories matching with timestamp of reboot and if found then copy otherwise not required)
7. The latest .oprocd.log file from /etc/oracle or /var/opt/oracle/oprocd (Solaris)
Note: We can use $ORACLE_CRS_HOME/bin/diagcollection.pl to collect above files but it doesn’t collect OPROCD logfiles, OS log files and OS watcher logfiles and also it may take lot of time to run and consume resources so it’s better to copy manually.
b. OS log files (This will get overwritten so we need to copy soon)
====================================================
1. /var/log/syslog
2. /var/adm/messages
3. errpt –a >error_.log (AIX only)
c. OS Watcher log files (This will get overwritten so we need to copy soon)
=======================================================
Please check in crontab where OSwatcher is installed. Go to that directory and then archive folder and then collect files from all directory matching with timestamp of node reboot.
1. OS_WATCHER_HOME/archive/oswtop
2. OS_WATCHER_HOME/archive/oswvmstat
3. OS_WATCHER_HOME/archive/oswmpstat
4. OS_WATCHER_HOME/archive/oswnetstat
5. OS_WATCHER_HOME/archive/oswiostat
6. OS_WATCHER_HOME/archive/oswps
7. OS_WATCHER_HOME/archive/oswprvtnet
1. OCSSD : This process is primary responsible for inter node health monitoring and instance endpoint recovery. It runs as oracle user. It also provides basic cluster locking and group services. It can run with or without vendor clusterware. The abnormal termination or killing this process will reboot the node by init.cssd script. If this script is killed then ocssd process will survive and node will keep functioning. This script is called by /etc/inittab entry and when it tries to respawn it and will try to start its own ocssd process. Since one ocssd process is already running, this 2nd time script calling ocssd starting will fail and 2nd init.cssd script will reboot the node.
2. OPROCD : This process is known as checking hangcheck and drive freezes on machine. On Linux, it is not available on 10.2.0.3 platform as this same function is performed by linux hangcheck timer module. Starting from 10.2.0.4, it will be started as part of clusterware startup and it runs as root. Killing this process will reboot the node. If a machine is hang for long time, this process needs to kill itself to avoid IO happening to disk so that rest of the nodes can remaster the resources. This executable sets a signal handler and sets the interval time bases on milliseconds parameter. It takes two parameters.
a. Timeout value –t : This is the length of time between executions. By default it’s 1000.
b. Margin –m : This is the acceptable difference between dispatches. By default, it’s 500.
When we set diagwait to 13, the margin becomes 13 -3 (reboottime seconds)= 10 seconds so value of m will be 10000.
There are two kinds of heartbeat mechanisms which are responsible for node reboot and reconfiguration of remaining clusteware nodes.
a. Network heartbit : This indicates that node can participate in cluster activities like group membership changes. When it’s missing for too long, cluster membership will change as a result of reboot. This too long value is determined by css miscount parameter value which is 30 seconds on most of platforms but can be changed depending on network configuration of particular environment. If at all it needs to be changed, it’s advisable to contact oracle support and take their recommendations on this.
b. Disk heartbit : This disk heartbit means heartbits to voting disk file which has the latest information about node members. Connectivity to a majority of voting files must be maintained for a node to stay alive. Voting disk file uses kill blocks to notify nodes they have been evicted and then remaining nodes can go for reconfiguration and a node with least no will become master as per Oracle algorithm generally. By default this value is 200 seconds which is css disktimeout parameter. Again changing this parameter requires oracle support’s recommendation. When node can no longer communicate through private interconnect, other nodes can see its heartbits in voting file then it’s being evicted by using voting disk kill block functionality.
Network split resolution : When network fails and nodes are not able to communicate to each other then one node has to fail to maintain data integrity. The surviving nodes should be an optimal subcluster of original cluster. Each node writes its own vote to voting file and Reconfiguration manager component reads these votes to calculate an optimal sub cluster. Nodes that are not to survive are evicted via communication through network and disk.
Causes of reboot by clusterware processes
======================================
Now we will briefly discuss about causes of reboot by these processes and at last, which files to review and upload to oracle support for further diagnosis.
Reboot by OCSSD.
============================
1. Network failure : 30 consecutive missed checkins will reboot a node where heartbits are issues once per second. Some kind of messages in occsd.log like heartbit fatal, eviction in xx seconds… Here there are two things.
a. If node eviction time in messages log file is less than missed checkins then node eviction is likely not due to missed checkins.
b. If node eviction time in messages log file is greater than missed checkins then node eviction is likely due to missed checkins.
2. Problems writing to voting disk file : some kind of hang in accessing voting disk.
3. High CPU utilization : When CPU is highly utilized then css daemon doesn’t get CPU on time to ping to voting disk and as a result, it cannot write to voting disk file its own vote and node is going to be rebooted.
4. Disk subsystem is unresponsive due to storage issues.
5. Killing ocssd process.
6. An oracle bug.
Reboot by OPROCD.
============================
When a problem is detected by oprocd, it’ll reboot the node for following reasons.
1. OS scheduler algorithm problem.
2. High CPU utilization due to which oprocd is not getting cpu to check hang check issues at OS level.
3. An oracle bug.
Also just to share with you, at one of the client sites, lms processes were running on low priority scheduling and lms were not getting cpu on time when there’s cpu is high utilized so lms couldn’t communicate through clusterware processes and node eviction got delayed and it was observed that oprocd rebooted node which should not have happened as lms was responsible to run at lower priority scheduling.
Determining cause of reboot by which process
==============================================
1. If there are below kind of messages in logfiles then it will be likely reboot by ocssd process.
a. Reboot due to cluster integrity in syslog file or messages file.
b. Any error prior to reboot in ocssd.log file.
c. Missed checkins in syslog file and eviction time is prior to node reboot time.
2. If there are below kind of messages in logfiles then it will be likely reboot by oprocd process.
a. Resetting message in messages logfile on linux.
b. Any error in oprocd log matching with timestamp of reboot or prior to reboot at /etc/oracle/oprocd directory.
3. If there are other messages like Ethernet issues or some kind of errors in messages or syslog file then please check with sysadmins. On AIX, errpt –a output gives lot of information about cause of reboot.
Log files collection while reboot of node
==============================================
Whenever node reboot occurs in clusterware environment, please review below logfiles for getting reason of reboot and these files are necessary to upload to oracle support for node eviction diagnosis.
a. CRS log files (For 10.2.0 and above 10.2.0 release)
=============================================
1. $ORACLE_CRS_HOME/log//crsd/crsd.log
2. $ORACLE_CRS_HOME/log//cssd/ocssd.log
3. $ORACLE_CRS_HOME/log//evmd/evmd.log
4. $ORACLE_CRS_HOME/log//alert.log
5. $ORACLE_CRS_HOME/log//client/cls*.log (not all files but only latest files matching with timestamp of node reboot)
6. $ORACLE_CRS_HOME/log//racg/ (Please check files and directories matching with timestamp of reboot and if found then copy otherwise not required)
7. The latest .oprocd.log file from /etc/oracle or /var/opt/oracle/oprocd (Solaris)
Note: We can use $ORACLE_CRS_HOME/bin/diagcollection.pl to collect above files but it doesn’t collect OPROCD logfiles, OS log files and OS watcher logfiles and also it may take lot of time to run and consume resources so it’s better to copy manually.
b. OS log files (This will get overwritten so we need to copy soon)
====================================================
1. /var/log/syslog
2. /var/adm/messages
3. errpt –a >error_.log (AIX only)
c. OS Watcher log files (This will get overwritten so we need to copy soon)
=======================================================
Please check in crontab where OSwatcher is installed. Go to that directory and then archive folder and then collect files from all directory matching with timestamp of node reboot.
1. OS_WATCHER_HOME/archive/oswtop
2. OS_WATCHER_HOME/archive/oswvmstat
3. OS_WATCHER_HOME/archive/oswmpstat
4. OS_WATCHER_HOME/archive/oswnetstat
5. OS_WATCHER_HOME/archive/oswiostat
6. OS_WATCHER_HOME/archive/oswps
7. OS_WATCHER_HOME/archive/oswprvtnet
Recovering drop table using RMAN
Hi,
The best method to recover the drop table is as given below:
Steps for Option 3 from MY Yodlee inteview questions:
1. To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.
2. Once the table export is complete, the table can be imported into the primary database. This dummy database can be a subset of the primary database. However,the ‘dummy’ database must include the SYSTEM, UNDO (or ROLLBACK), and the tablespace(s) where the dropped/truncated table resides.
The simpliest method to create this ‘dummy’ database is to use the RMAN duplicate command.
RMAN Duplicate Command
CONNECT TARGET SYS/oracle@trgt
CONNECT AUXILIARY SYS/oracle@dupdb
DUPLICATE TARGET DATABASE TO dupdb
NOFILENAMECHECK UNTIL TIME ‘SYSDATE-7′;
Assuming the following
•The target database trgt and duplicate database dupdb are on different hosts but have exactly the same directory structure.
•You want to name the duplicate database files the same as the target files.
•You are not using a recovery catalog.
•You are using automatic channels for disk and sbt, which are already configured.
•You want to recover the duplicate database to one week ago in order to view the data in prod1 as it appeared at that time (and you have the required backups and logs to recover the duplicate to that point in time).
Hope it helps.
Best regards,
The best method to recover the drop table is as given below:
Steps for Option 3 from MY Yodlee inteview questions:
1. To recover from a dropped or truncated table, a dummy database (copy of primary) will be restored and recovered to point in time so the table can be exported.
2. Once the table export is complete, the table can be imported into the primary database. This dummy database can be a subset of the primary database. However,the ‘dummy’ database must include the SYSTEM, UNDO (or ROLLBACK), and the tablespace(s) where the dropped/truncated table resides.
The simpliest method to create this ‘dummy’ database is to use the RMAN duplicate command.
RMAN Duplicate Command
CONNECT TARGET SYS/oracle@trgt
CONNECT AUXILIARY SYS/oracle@dupdb
DUPLICATE TARGET DATABASE TO dupdb
NOFILENAMECHECK UNTIL TIME ‘SYSDATE-7′;
Assuming the following
•The target database trgt and duplicate database dupdb are on different hosts but have exactly the same directory structure.
•You want to name the duplicate database files the same as the target files.
•You are not using a recovery catalog.
•You are using automatic channels for disk and sbt, which are already configured.
•You want to recover the duplicate database to one week ago in order to view the data in prod1 as it appeared at that time (and you have the required backups and logs to recover the duplicate to that point in time).
Hope it helps.
Best regards,
Writing DBA scripts :
Seven SQL and SQL*Plus tricks which work wonders
Whether you like it or not and however strongly tool providers - Oracle included - try to sell their wares, SQL*Plus remains and is likely to remain for a while, the tool of choice for DBAs. You obviously needn't be a SQL expert to be a good DBA - although it doesn't hurt but, expert or not, when you want to write usable SQL*Plus scripts there are a number of tricks worth knowing..
Here are seven of them which ,if you don't already know them, will probably save you a lot of hassle.
If in order to be a good DBA all you had to do was to type SELECT * from the ad hoc DBA_something view, check a few things and then execute a single DROP, ALTER or whatever to fix them, life would be easy. Unfortunately, most of the time you need to run a sequence of statements, which moreover changes over time. Therefore, one of the favorite tricks of experienced DBAs is to write SQL scripts which generate other SQL scripts which do the job.
Let's take an example to illustrate most of the SQL*Plus tricks you need.
Say that you need a script to execute an on-line backup of your database (we are assuming your database is running in the required ARCHIVELOG mode).
Basically, what you need to do for each database is to execute
ALTER TABLESPACE xxx BEGIN BACKUP;
then backup the file(s) associated with the tablespace, then execute
ALTER TABLESPACE xxx END BACKUP;
The brute force method would be to write a first SQL script in which the BEGIN BACKUP is hard-coded for each tablespace, some kind of operating-system script which deals with the file backup and a second SQL script for the END BACKUP part.
Very, very bad method indeed, chiefly because things evolve. It's highly likely that you will add files to your database and quite possibly you will have new tablespaces too, which means that you will have to maintain all your scripts. Are you ready to bet you (or, for that matter, the guy who looks after your database while you are on vacation) won't forget? And your initial backup script may run smoothly every day without telling you you're omitting some files. One of the irritating things with backup scripts is that it's only when you're in really dire straits and trying to recover your production database, that you realize that they have been out of date and therefore totally useless for months. This is typically a case where you MUST use a SQL script to generate what is needed; for the Oracle data dictionary contains all the information you need, and is ALWAYS up-to-date.
To generate the 'ALTER TABLESPACE' statements, no better place to look than DBA_TABLESPACES. You can easily type :
select 'alter tablespace ' || tablespace_name || chr(10) ||
'begin backup;'
from dba_tablespaces
where status <> 'INVALID';
(INVALID refers to tablespaces which were dropped).
Trick #1 : chr(10) is quite a useful thing to concatenate in strings, as it inserts a 'newline' character.
It's not really required here but it's very useful with lines which would otherwise be much too long and generally speaking improves the legibility of the scripts you generate.
To get the up-to-date list of files which constitute your database, you can refer to DBA_DATAFILES. Let's assume that our underlying system is Unix and that we want to copy files to the /backup directory, you can write something such as :
select 'cp ' || f.file_name || ' /backup'
from dba_datafiles f;
Trick #2 : WATCH THE LINE LENGTH!
SQL*Plus has the bad habit of 'wrapping' long lines (slicing them, in effect), which can generate invalid operating system commands. Don't forget that a full file name can be up to 255 (give or take one) characters in length and so can the name of the backup directory. If you really want to be safe, you will have to insert at the beginning of your script :
set linesize 600
This is the first but certainly not the last SQL*Plus command we meet.
We are beginning to see the generation of the required commands taking shape but we're certainly not done yet. For one thing, backup procedures are among the most sensitive procedures in operations. You must handle errors, either SQL errors or operating system errors - what about some change of protections preventing you from copying where you want, or a full file system ?
Trick #3 : Use WHENEVER OSERROR EXIT
and WHENEVER SQLERROR EXIT SQL.SQLCODE and end your SQL procedures with EXIT 0.
This will enable you to test return codes from SQL*Plus in an operating system script and will allow you to handle errors properly.
Now let's improve our script. We could of course generate and run BEGIN BACKUP on all the tablespaces, generate and run the copy of all the files, and then generate and run END BACKUP on all the tablespaces. This is fairly crude and (let's leave out the details) not very good in terms of database management. What should be done is for each tablespace
step 1 : execute BEGIN BACKUP
step 2 : copy the files associated with the tablespace
step 3 : execute END BACKUP
The difficulty is in ordering correctly a set of unrelated commands. In that case we need two more tricks :
Trick #4 : Use UNIONs to fetch commands generated by independent queries
and
Trick #5 : Use dummy, unprinted columns for ordering.
Here's how it works :
We want to order by tablespace, so we return the tablespace name but we use the COLUMN SQL*Plus comment to make it invisible in the output. We also want the steps to be returned in a given order, so we add a constant column, also invisible, which we shall use as a minor sorting key (note that in a UNION you need to give aliases to the columns in the first select list only):
column tablespace_name noprint
column step noprint
column text format A550
select tablespace_name,
'step_1' step,
'alter tablespace ' || tablespace_name || chr(10) ||
'begin backup;' text
from dba_tablespaces
where status <> 'INVALID'
union
select tablespace_name,
'step_2',
'cp ' || file_name || ' /backup'
from dba_datafiles
union
select tablespace_name,
'step_3',
'alter tablespace ' || tablespace_name || chr(10) ||
'end backup;'
from dba_tablespaces
where status <> 'INVALID'
order by 1, 2;
Trick #6 : Use the three required SQL*Plus commands needed to remove unwanted output!
Those three commands are :
set pagesize 0 -- Removes breaks on pages AND headings
set feedback off -- Removes the message about how many lines
-- were selected
set recsep off -- Removes unwanted blank lines.
With these settings, you can write your script in the following way :
spool do_backup.sql
spool off
set feedback on
@do_backup
to generate and immediately run a backup procedure which is always guaranteed to be up-to-date.
Well, we have what we want, so what about the seventh promised trick?
Don't forget that when you run something under SQL*Plus you may run several 'profile' files : glogin.sql which you Unix DBAs will find under $ORACLE_HOME/sqlplus/admin and possibly a login.sql file in the current directory. You don't know what is or ever will be in these scripts. So :
Trick #7 : code defensively and unset any potentially troublesome setting, even if the default is just right.
For instance :
set pause off
set echo off
set verify off -- If you are using parameters
set scan off
You now know most of the techniques you need to write truly useful scripts; then it's a matter of imagination, knowledge of the data dictionary and, of course, SQL skills. Beware that hot-backup as it is introduced here is still a prototype for tutorial purposes. If you are interested by the subject, a complete version named hotbackup.sql is freely available on this site, www.oriolecorp.com. The adventurous can also have a look at other SQL scripts, some of them are in the 'North Face' category...
Oracle ASM Queries for DBA
The GV$ASM views,Automatic Storage Management views are very important
to gather asm structure and components related details of Oracle ASM,I
find some time to have the queries which will help to gather all details
with regards to Oracle ASM.
Below are some dynamic views and queries for knowing Oracle asm structure and components details:
SQL> SELECT *FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%ASM%';
TABLE_NAME
------------------------------
V$ASM_ACFSSNAPSHOTS
V$ASM_ACFSVOLUMES
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_FILE
TABLE_NAME
------------------------------
V$ASM_FILESYSTEM
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER
V$ASM_VOLUME
V$ASM_VOLUME_STAT
GV$ASM_ACFSSNAPSHOTS
GV$ASM_ACFSVOLUMES
GV$ASM_ALIAS
TABLE_NAME
------------------------------
GV$ASM_ATTRIBUTE
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_DISK_IOSTAT
GV$ASM_DISK_STAT
GV$ASM_FILE
GV$ASM_FILESYSTEM
GV$ASM_OPERATION
GV$ASM_TEMPLATE
TABLE_NAME
------------------------------
GV$ASM_USER
GV$ASM_USERGROUP
GV$ASM_USERGROUP_MEMBER
GV$ASM_VOLUME
GV$ASM_VOLUME_STAT
38 rows selected.
1)GV$ASM_DISKGROUP:
This view displays asm instances related details along with one row for every ASM diskgroup discovered by the ASM instance on the node.
SQL> SELECT INST_ID||' '||NAME ||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP;
INST_ID||''||NAME||''||STATE||''||TOTAL_MB||''||FREE_MB
--------------------------------------------------------------------------------
2 GRID_DATA_01 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 GRID_DATA_01 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 CONNECTED 65296 40504
2)GV$ASM_ALIAS:
This view displays all system and user-defined aliases.
There is one row for every alias present in every diskgroup mounted by the ASM instance. The RDBMS instance displays no rows in this view.
SQL> SELECT INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
FROM GV$ASM_ALIAS;
INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
--------------------------------------------------------------------------------
2 USERS.323.750185521 323 N
2 ASMWORK.320.750263763 320 N
2 ASMMASTER.319.750263765 319 N
2 UNDOTBS3.306.768750281 306 N
2 TEMPFILE 4294967295 Y
2 TEMP.325.750185505 325 N
3)GV$ASM_ATTRIBUTE:
This Oracle Database 11g view displays one row for each ASM attribute defined.
These attributes are listed when they are defined in CREATE DISKGROUP or ALTER DISKGROUP statements. DISK_REPAIR_TIMER is an example of an attribute.
SQL> SELECT INST_ID||' '||NAME||' '|| GROUP_NUMBER
FROM GV$ASM_ATTRIBUTE;
no rows selected
4)GV$ASM_CLIENT:
This view displays one row for each RDBMS instance that has an opened ASM diskgroup.
SQL> SELECT INST_ID||' '||INSTANCE_NAME||' '||DB_NAME||' '||STATUS
FROM GV$ASM_CLIENT;
INST_ID||''||INSTANCE_NAME||''||DB_NAME||''||STATUS
--------------------------------------------------------------------------------
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
5)GV$ASM_DISK:
This view contains specifics about all disks discovered by the ASM isntance,
including mount status, disk state, and size.There is one row for every disk discovered by the ASM instance.
SQL> SELECT INST_ID||' '||STATE||' '||TOTAL_MB||' '||FREE_MB||' '||NAME
FROM GV$ASM_DISK;
INST_ID||''||STATE||''||TOTAL_MB||''||FREE_MB||''||NAME
--------------------------------------------------------------------------------
2 NORMAL 2014 1843 GRID_DATA_01_0000
2 NORMAL 2014 1844 GRID_DATA_01_0001
2 NORMAL 2014 1843 GRID_DATA_01_0002
2 NORMAL 2014 1878 GRID_DATA_01_0003
2 NORMAL 2014 1875 GRID_DATA_01_0004
2 NORMAL 2014 1869 GRID_DATA_01_0005
2 NORMAL 8162 5063 TESTASMDB_REDO_01_0000
2 NORMAL 8162 5061 TESTASMDB_REDO_01_0001
2 NORMAL 8162 5065 TESTASMDB_REDO_01_0002
6)GV$ASM_DISK_IOSTAT:
This displays information about disk I/O statistics for each ASM Client. If this view is queried from the database instance, only the rows for that instance are shown.
SQL> SELECT INST_ID||' '||INSTNAME||' '||DBNAME||' '||GROUP_NUMBER||' '||DISK_NUMBER||' '||READS||' '||WRITES||' '||READ_TIME||' '||WRITE_TIME
FROM GV$ASM_DISK_IOSTAT;
7)GV$ASM_DISK_STAT:
This view contains similar content as the v$ASM_DISK, except v$ASM_DISK_STAT reads disk information from cache and thus performs no disk discovery.
Thsi view is primarily used form quick acces to the disk information without the overhead of disk discovery.
8)GV$ASM_DISKGROUP_STAT:
This view contains all the similar view contents as the v$ASM_DISKGROUP,
except that v$ASM_DISK_STAT reads disk information from the cache and thus performs no disk discovery.
This view is primarily used for quick access to the diskgroup information without the overhead of disk discovery.
SQL> SELECT INST_ID||' '||NAME||' '||ALLOCATION_UNIT_SIZE||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP_STAT;
INST_ID||''||NAME||''||ALLOCATION_UNIT_SIZE||''||STATE||''||TOTAL_MB||''||FREE_M
--------------------------------------------------------------------------------
1 GRID_DATA_01 1048576 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 GRID_DATA_01 1048576 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
10 rows selected.
9)GV$ASM_FILE:
This view displays information about ASM files.
There is one row for every ASM file in every diskgroup mounted by the ASM instance. In a RDBMS instance, V$ASM_FILE displays no row.
SELECT INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
FROM GV$ASM_FILE;
INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
--------------------------------------------------------------------------------
1 335 512 17-MAR-12 UNPROT
1 336 512 15-MAR-12 UNPROT
1 337 512 15-MAR-12 UNPROT
1 339 512 18-MAR-12 UNPROT
1 340 512 23-MAR-12 UNPROT
10)GV$ASM_OPERATION:
This view describes the progress of an influx ASM rebalance operation.
In a RDBMS instance,GV$ASM_OPERATION displays no rows.
SQL> SELECT INST_ID||' '||OPERATION||' '||STATE||' '||POWER||' '||ACTUAL||' '||SOFAR
FROM GV$ASM_OPERATION;
11)GV$ASM_TEMPLATE:
This view contains information on user and system-defined templated.
GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance.
In a RDBMS instance,GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance with which the RDBMS instance communicates.
SQL> SELECT INST_ID||''||GROUP_NUMBER||''||STRIPE||''||SYSTEM
FROM GV$ASM_TEMPLATE;
SQL> SELECT FS_NAME||' '||VOL_DEVICE||' '||SNAP_NAME||' '||CREATE_TIME
FROM V$ASM_ACFSSNAPSHOTS;
no rows selected
Enjoy Oracle ASM learning........
Best regards,
Below are some dynamic views and queries for knowing Oracle asm structure and components details:
SQL> SELECT *FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE '%ASM%';
TABLE_NAME
------------------------------
V$ASM_ACFSSNAPSHOTS
V$ASM_ACFSVOLUMES
V$ASM_ALIAS
V$ASM_ATTRIBUTE
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_FILE
TABLE_NAME
------------------------------
V$ASM_FILESYSTEM
V$ASM_OPERATION
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER
V$ASM_VOLUME
V$ASM_VOLUME_STAT
GV$ASM_ACFSSNAPSHOTS
GV$ASM_ACFSVOLUMES
GV$ASM_ALIAS
TABLE_NAME
------------------------------
GV$ASM_ATTRIBUTE
GV$ASM_CLIENT
GV$ASM_DISK
GV$ASM_DISKGROUP
GV$ASM_DISKGROUP_STAT
GV$ASM_DISK_IOSTAT
GV$ASM_DISK_STAT
GV$ASM_FILE
GV$ASM_FILESYSTEM
GV$ASM_OPERATION
GV$ASM_TEMPLATE
TABLE_NAME
------------------------------
GV$ASM_USER
GV$ASM_USERGROUP
GV$ASM_USERGROUP_MEMBER
GV$ASM_VOLUME
GV$ASM_VOLUME_STAT
38 rows selected.
1)GV$ASM_DISKGROUP:
This view displays asm instances related details along with one row for every ASM diskgroup discovered by the ASM instance on the node.
SQL> SELECT INST_ID||' '||NAME ||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP;
INST_ID||''||NAME||''||STATE||''||TOTAL_MB||''||FREE_MB
--------------------------------------------------------------------------------
2 GRID_DATA_01 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 GRID_DATA_01 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 CONNECTED 65296 40504
2)GV$ASM_ALIAS:
This view displays all system and user-defined aliases.
There is one row for every alias present in every diskgroup mounted by the ASM instance. The RDBMS instance displays no rows in this view.
SQL> SELECT INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
FROM GV$ASM_ALIAS;
INST_ID||''||NAME||''||FILE_NUMBER||''||ALIAS_DIRECTORY
--------------------------------------------------------------------------------
2 USERS.323.750185521 323 N
2 ASMWORK.320.750263763 320 N
2 ASMMASTER.319.750263765 319 N
2 UNDOTBS3.306.768750281 306 N
2 TEMPFILE 4294967295 Y
2 TEMP.325.750185505 325 N
3)GV$ASM_ATTRIBUTE:
This Oracle Database 11g view displays one row for each ASM attribute defined.
These attributes are listed when they are defined in CREATE DISKGROUP or ALTER DISKGROUP statements. DISK_REPAIR_TIMER is an example of an attribute.
SQL> SELECT INST_ID||' '||NAME||' '|| GROUP_NUMBER
FROM GV$ASM_ATTRIBUTE;
no rows selected
4)GV$ASM_CLIENT:
This view displays one row for each RDBMS instance that has an opened ASM diskgroup.
SQL> SELECT INST_ID||' '||INSTANCE_NAME||' '||DB_NAME||' '||STATUS
FROM GV$ASM_CLIENT;
INST_ID||''||INSTANCE_NAME||''||DB_NAME||''||STATUS
--------------------------------------------------------------------------------
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
1 +ASM1 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
2 +ASM2 TESTASMDB CONNECTED
5)GV$ASM_DISK:
This view contains specifics about all disks discovered by the ASM isntance,
including mount status, disk state, and size.There is one row for every disk discovered by the ASM instance.
SQL> SELECT INST_ID||' '||STATE||' '||TOTAL_MB||' '||FREE_MB||' '||NAME
FROM GV$ASM_DISK;
INST_ID||''||STATE||''||TOTAL_MB||''||FREE_MB||''||NAME
--------------------------------------------------------------------------------
2 NORMAL 2014 1843 GRID_DATA_01_0000
2 NORMAL 2014 1844 GRID_DATA_01_0001
2 NORMAL 2014 1843 GRID_DATA_01_0002
2 NORMAL 2014 1878 GRID_DATA_01_0003
2 NORMAL 2014 1875 GRID_DATA_01_0004
2 NORMAL 2014 1869 GRID_DATA_01_0005
2 NORMAL 8162 5063 TESTASMDB_REDO_01_0000
2 NORMAL 8162 5061 TESTASMDB_REDO_01_0001
2 NORMAL 8162 5065 TESTASMDB_REDO_01_0002
6)GV$ASM_DISK_IOSTAT:
This displays information about disk I/O statistics for each ASM Client. If this view is queried from the database instance, only the rows for that instance are shown.
SQL> SELECT INST_ID||' '||INSTNAME||' '||DBNAME||' '||GROUP_NUMBER||' '||DISK_NUMBER||' '||READS||' '||WRITES||' '||READ_TIME||' '||WRITE_TIME
FROM GV$ASM_DISK_IOSTAT;
7)GV$ASM_DISK_STAT:
This view contains similar content as the v$ASM_DISK, except v$ASM_DISK_STAT reads disk information from cache and thus performs no disk discovery.
Thsi view is primarily used form quick acces to the disk information without the overhead of disk discovery.
8)GV$ASM_DISKGROUP_STAT:
This view contains all the similar view contents as the v$ASM_DISKGROUP,
except that v$ASM_DISK_STAT reads disk information from the cache and thus performs no disk discovery.
This view is primarily used for quick access to the diskgroup information without the overhead of disk discovery.
SQL> SELECT INST_ID||' '||NAME||' '||ALLOCATION_UNIT_SIZE||' '||STATE||' '||TOTAL_MB||' '||FREE_MB
FROM GV$ASM_DISKGROUP_STAT;
INST_ID||''||NAME||''||ALLOCATION_UNIT_SIZE||''||STATE||''||TOTAL_MB||''||FREE_M
--------------------------------------------------------------------------------
1 GRID_DATA_01 1048576 MOUNTED 12084 11152
1 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
1 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
1 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
1 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 GRID_DATA_01 1048576 MOUNTED 12084 11152
2 TESTASMDB_REDO_01 1048576 CONNECTED 65296 40504
2 TESTASMDB_REDO_02 1048576 CONNECTED 65296 40504
2 TESTASMDB_FRA_01 1048576 CONNECTED 261872 142831
2 TESTASMDB_DATA_01 1048576 CONNECTED 1048016 59453
10 rows selected.
9)GV$ASM_FILE:
This view displays information about ASM files.
There is one row for every ASM file in every diskgroup mounted by the ASM instance. In a RDBMS instance, V$ASM_FILE displays no row.
SELECT INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
FROM GV$ASM_FILE;
INST_ID||''||FILE_NUMBER||''||BLOCK_SIZE||''||CREATION_DATE||''||REDUNDANCY
--------------------------------------------------------------------------------
1 335 512 17-MAR-12 UNPROT
1 336 512 15-MAR-12 UNPROT
1 337 512 15-MAR-12 UNPROT
1 339 512 18-MAR-12 UNPROT
1 340 512 23-MAR-12 UNPROT
10)GV$ASM_OPERATION:
This view describes the progress of an influx ASM rebalance operation.
In a RDBMS instance,GV$ASM_OPERATION displays no rows.
SQL> SELECT INST_ID||' '||OPERATION||' '||STATE||' '||POWER||' '||ACTUAL||' '||SOFAR
FROM GV$ASM_OPERATION;
11)GV$ASM_TEMPLATE:
This view contains information on user and system-defined templated.
GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance.
In a RDBMS instance,GV$ASM_TEMPLATE displays one row for every template present in every diskgroup mounted by the ASM instance with which the RDBMS instance communicates.
SQL> SELECT INST_ID||''||GROUP_NUMBER||''||STRIPE||''||SYSTEM
FROM GV$ASM_TEMPLATE;
SQL> SELECT FS_NAME||' '||VOL_DEVICE||' '||SNAP_NAME||' '||CREATE_TIME
FROM V$ASM_ACFSSNAPSHOTS;
no rows selected
Enjoy Oracle ASM learning........
Best regards,
Subscribe to:
Posts (Atom)