Segment Availability during Shrink

The segment shrink is done online. During shrink operation, the conventional DML operations can coexist but parallel DMLs cannot.

During segment shrink, data will be moved as part of the compaction phase.
During compaction locks will be held on individual rows and/or blocks containing the data. This will cause the concurrent DMLs like updates and deletes to serialize on the locks. The compaction will be done in units of smaller transactions, so the availability of the object will not be impacted significantly.

However during certain phases of segment shrink (when the HWM is adjusted), the segment will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the object less significantly.

NOTE: Shrinking space of a large segment can take a lot of time, e.g. tens of hours, and can generate lots of redo. Therefore, it is not advised to interrupt a shrink statement to prevent a possibly massive rollback.


Thanks

Drop Database in Oracle 10g

It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles, redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba
SQL> Shutdown immediate;
SQL> Startup Mount Exclusive Restrict;
SQL> Drop Database;

Thanks

Block Change Tracking

RMAN Incremental Backups backup only the blocks that were changed since the lastest base incremental backups. But RMAN had to scan the whole database to find the changed blocks. Hence the incremental backups read the whole database and writes only the changed blocks. Thus the incremental backups saves space but the reduction in the time is fairly neglegible.


Block Change Tracking (BCT) is a new feature in Oracle 10g. BCT enables RMAN to read only the blocks that were changed since the lastest base incremental backups. Hence by enabling BCT, RMAN reads only the changed blocks and writes only the changed blocks.


Without BCT, RMAN has to read every block in the database and compare the SCN in the block with the SCN in the base backup. If the block's SCN is greater than the SCN in the base backup then the block is a candidate for the new incremental backup. Usually only few blocks are changed between backups and the RMAN has to do unncessary work of reading the whole database.


BCT Stores the information about the blocks being changed inthe BlockChange Tracking File. The background process that does this logging is Change Tracking Writer (CWTR).


BlockChange Tracking File


BCT File is one per database and in the case RAC, it is shared among all the instances. BCT File is created in the location defined by the parameter DB_CREATE_FILE_DEST as OMF file.


To enable BCT


SQL> Alter Database Enable Block Change Tracking;


To disable BCT


SQL> Alter Database Disable Block Change Tracking;


To specify the BCT file location


SQL> Alter Database enable Block Change Tracking using File '/Backup/BCT/bct.ora';


A useful query,

SQL> Select Completion_time, datafile_blocks, blocks_read, blocks, used_change_tracking
From v$backup_datafile
where to_char(completion_time, 'dd/mon/yy') = to_char(sysdate, 'dd/mon/yy');

Where,
datafile_blocks is the total number of blocks in the datafile.
blocks_read is the total number of blocks read by RMAN
blocks is the total number of blocks backed up by the RMAN.
used_change_tracking if yes BCT is used, if no BCT is not used.

Thanks

Sampling in Statistics Gathering

Gathering statistics on any object involves a full table scan and sorts which uses more resources. To reduce the resource spent for gathering statistics use sampling.

The ESTIMATE_PERCENT attribute of DBMS_STATS package is used to set the sample size. Oracle recommends to set the value to be AUTO_SAMPLE_SIZE. This value lets oracle to decide the best sample size.

SQL> DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', DBMS_STATS.AUTO_SAMPLE_SIZE);

Thanks

Manual Statistics Gathering

1. To Gather Statistics on System Schemas (SYS, SYSTEM, etc)

SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS

2. To Gather Statistics on all database objects including System schemas.

SQL> Exec DBMS_STATS.GATHER_DATABASE_STATS
SQL> Exec DBMS_STATS.DELETE_ DATABASE _STATS

3. To Gather Statistics on schemas other than System schemas

SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘Ownername’);
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS(‘Ownername’);

4. To Gather Statistics on individual tables

SQL> Exec DBMS_STATS.GATHER_TABLE_STATS(‘Ownername’,’Tablename’);
SQL> Exec DBMS_STATS.DELETE_TABLE_STATS(‘Ownername’,’Tablename’);

5. To Gather Statistics on indexes

SQL> Exec DBMS_STATS.GATHER_INDEX_STATS(‘Ownername’,’Indexname’);
SQL> Exec DBMS_STATS.DELETE_INDEX_STATS(‘Ownername’, ’Indexname’);

6. To Gather Statistics on individual columns of tables

SQL> Exec DBMS_STATS.GET_COLUMN_STATS(‘Ownername’,’Tablename’,’Column’);
SQL> Exec DBMS_STATS.DELETE_ COLUMN _STATS(‘Owner’,’Tablename’,’column’);

Note: Oracle invalidates the parsed SQL statements when new statistics are updated.

Thanks

Automatic Statistic Gathering

From Oracle 10g, any DML operations performed on any objects are monitored by oracle. The information is stored in the view SYS.DBA_TAB_MODIFICATIONS. Based on the amount of changes, oracle decides whether to gather new statistics for an object.

When you create the database using DBCA, oracle automatically creates a Job, GATHER_STATS_JOB, automatically and is scheduled to run during the maintenance window.

The following query is used to find whether the job is running,

SQL> Select Job_name from dba_scheduler_jobs where job_name like 'GATHER_STAT%';

Maintenance Windows: Oracle Scheduler have two Maintenance windows,

Weeknight window : Between 10 pm and 6 am (monday - friday)
Weekend window: Between 12 am saturday and 12 am sunday

The job is closed when the maintenance window closes even if the job has not finished.

To enable statistics collection to continue even after the maintenance window closes, set the stop_on_window_close attribute to false.

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE (‘GATHER_STATS_JOB’, ‘STOP_ON_WINDOW_CLOSE’, ‘FALSE’);

Candidates for Statistics Gathering: The job gathers statistics for objects with

Missing Statistics : Objects without statistics
Stale Statistics : If more than 10% of the rows in an object is modified, its statistics are considered stale.

Note: System Statistics and the Fixed table (X$ tables) statistics are not gathered automatically.

To Disable Automatic Statistic Gathering

SQL> Exec DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

To get the details about the maintenance window

SQL> Select window_name, repeat_interval, duration, next_start_date
From dba_scheduler_windows;

To change the period of Maintenance Windows

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘WEEKNIGHT_WINDOW’, ‘repeat_interval’, 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0; byminute=0; bysecond=0');

Lock Statistics: If you dont want to gather statistics for certain object or schema then you can lock it as follows,

SQL> Exec DBMS_STATS.LOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.LOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

To unlock

SQL> Exec DBMS_STATS.UNLOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.UNLOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

Note: When you perform bulk loads on tables gather statistics on those tables immediately.

Thanks

Hit Ratio in Oracle 10g

From Oracle 10g, there is no need to run huge scripts to get the hit ratios. You can query the v$sysmetric view to get the hit ratios.

SQL> select * from gv$sysmetric
where metric_name like '%Ratio%'
and intsize_csec in (Select max(intsize_csec) from gv$sysmetric where inst_id =&id)
and inst_id = &&id;

Thanks

AWR Basic Info

By default the Automatic Workload Repository (AWR) is populated every hour.

Tablespace: SYSAUX (Data is stored in this tablespace)
Background Process : MMON (Flushes data from Memory to Disk)

To find the space used by AWR query the V$SYSAUX_OCCUPANTS view.

SQL> Select * From V$SYSAUX_OCCUPANTS where occupant_name like '%AWR%';

The space_usage_kbytes column gives the amount of space used by AWR in kilobytes.

To find the oldest AWR information

SQL> Select dbms_stats.get_stats_history_availability From dual;

To find the retention period for AWR. Default is 7 days.

SQL> Select dbms_stats.get_stats_history_retention From dual;

To change the retention period for AWR

SQL> Exec dbms_stats.alter_stats_history_retention(10);

Thanks

Segment Shrink Restrictions

Init.ora Parameter compatible >= 10.0

The tablespace must be Locally Managed with Automatic Segment Space Management (ASSM) enabled.

You cannot shrink:

• UNDO segments
• Temporary segments
• Clustered tables and Compressed tables
• Tables with a colmn of datatype LONG
• LOB segments (belwo 10.2)
• Tables with Function based Indexes or Bitmap Join Indexes
• IOT mapping tables and IOT overflow segments
• Tables with domain Indexes (below 11.1)
• Tables with MVIEWS with ON COMMIT
• Tables with MVIEWS which are based on ROWIDs

Incase of tables with function based indexes or bitmap join indexes, drop the index the shrink and then recreate the index.

The following query will identify the segments that are shrinkable and non-shrinkable...


SELECT dt.owner, dt.table_name,
(CASE WHEN NVL(ind.cnt, 0) < 1 THEN 'Y' ELSE 'N'END) AS can_shrink
FROM dba_tables dt,
(SELECT table_name, COUNT(*) cnt
FROM dba_indexes di
WHERE index_type in ( 'FUNCTION-BASED NORMAL','FUNCTION-BASED DOMAIN','DOMAIN')
GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
AND dt.table_name NOT LIKE 'AQ$%'
AND dt.table_name NOT LIKE 'BIN$%'
AND dt.owner = '&owner'
ORDER BY 1, 2;

Note: Change the values of index type according to the version you are running...

Thanks

Segment Shrinking

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use Automatic Segment Space Management and locally managed.

Steps to perform segment shrink.

Step 1: Enable Row movement for the segment

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Step 2: Recover space and amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE;

Step 2a: Recover space, but don't amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Step 3: Recover space for the object and all dependant objects.

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Note: The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any dependant SQL statements will need to be reparsed.

Thanks

ADDM Views

The following views are useful for ADDM analysis.

DBA_ADVISOR_TASKS - Basic information about existing tasks
DBA_ADVISOR_LOG - Status information about existing tasks
DBA_ADVISOR_FINDINGS - Findings identified for an existing tasks
DBA_ADVISOR_RECOMMENDATIONS – Reco for problems identified by tasks
DBA_ADVISOR_ACTIONS - Actions to be taken for the existing tasks
DBA_ADVISOR_RATIONALE - Rationale for ADDM recommendations.

Thanks

ADDM Findings, Recos and Actions

Query to find ADDM Findings, Recommendations and Actions

SQL> Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'b.message MESSAGE,
'Command To correct:'c.command COMMAND,
'Action Message:'c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='12/06/2009'
Order by 3 desc;

Thanks

Directory Objects

Directory Objects

Directory Object is a logical structure that represents a physical location of a file system.

It is used in External Tables, UTL_FILE package and in Data Pump.

Important Points

• Always owned by SYS user (Even created by another user).
• Names must be unique.
• You must have CREATE ANY DIRECTORY privilege to create Directory object.
• By default the owner gets the READ WRITE privilege on the directory object.
• Grant necessary privilege on this object if you want other user to access.
• READ and WRITE privilege means only oracle database will read and write on behalf of a user. The user has no direct access to these files.

Creating Directory Object

SQL> Create or Replace Directory data_pump_dir As ‘/oracle/export/dpump’;

SQL> Grant Read,Write on directory data_pump_dir to public;

Data Dictionary Views

SQL> Select * from ALL_DIRECTORIES;
It returns owner, directory name and directory path.

SQL> Select * from ALL_TAB_PRIVS where privilege in (‘READ’, ‘WRITE’);
It shows whether you have required read/write privilege.

Thanks