Oracle BEST Practices I

A best practice is a method or technique that has consistently shown results superior to those achieved with other means, and that is used as a benchmark. In addition, a “best” practice can evolve to become better as improvements are discovered. Best practice is considered by some as
a business buzzword, used to describe the process of developing and following a standard way of doing things that multiple organizations can use.

ORACLE company write his own “Best Practices” here que can summarize in a list some:

1- Put your Database into ARCHIVELOG Mode
2- Use DBMS_STATS for statistic collection
3- Using Recovery Manager(RMAN) for Backup and Recovery needs
4- Resumable Statements and Space Allocation
5- Maintenance of Global Partitioned Indexes
6- Default Tablespaces
7- PL/SQL Bulk Operations
8- Locking Issues
9- Oracle System Event Triggers
10- Autonomous Transactions
11- Defining Application Services for Oracle Database 10g
12- Cost Based Optimizer Best Practices and Advised Strategy
in this post we are goint to see first two points.

1- Put your Database into ARCHIVELOG Mode

Oracle can be run in two modes:
* ARCHIVELOG – Oracle archives the filled online redo log files before reusing them in the cycle.
* NOARCHIVELOG – Oracle does not archive the filled online redo log files before reusing them in the cycle.

Running the database in ARCHIVELOG mode has the following benefits:
* The database can be completely recovered from both instance and media failure.
* The user can perform online backups, i.e., back up tablespaces while the database is open and available for use.
* Archived redo logs can be transmitted and applied to the standby database
* Oracle supports multiplexed archive logs to avoid any possible single point of failure on the archive logs.
* The user has more recovery options, such as the ability to perform tablespace-point-in-time recovery (TSPITR)

Running the database in NOARCHIVELOG mode has the following consequences:
* The user can only back up the database while it is completely closed after a clean shutdown.
* Typically, the only media recovery option is to restore the whole database, which causes the loss of all transactions issued since the last backup.

How enable Archive log mode:

— checking the current archive mode:

archive log list

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Current log sequence 25

— close the database

shutdown immediate;

— now startup the database at mount mode, switch to archivelog and open it

startup mount exclusive;
alter database archivelog;
alter database open;

alter system switch logfile;
archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Next log sequence to archive 25
Current log sequence 26

— Parameter configuration:

show parameter log_archive_start;
alter system set LOG_ARCHIVE_START=TRUE SCOPE=spfile;

2- Use DBMS_STATS for statistic collection

Very important for performance of the querys, info collected by dbms_stats package and used by query optimizer to estimate cost of execution plans, CPU I/O costs access/join method.

Here we have 3 type of optimizer statistics by object: Table,Index & Column.
Staistics by System: I/O and CPU performance and utilization.

In Oracle 10g statustics are automatic collected with GATHER_STATS_JOB, can be collected manual way with DBMS_STATS package or old and obsolete ANALYZE statement. In 10g ORACLE introduced Dynamic sampling automatically collect statistics when: query executed many times or cost collecting statistics is minimal compared execution time.
How to check statistics collected?

— Table based
SELECT last_analyzed analyzed, sample_size, monitoring
FROM dba_tables WHERE table_name =’DEPARTMENT’;

— Column based
SELECT column_name, num_distinct,histogram, num_buckets, density, last_analyzed
FROM dba_tab_col_statistics WHERE table_name =’DEPARTMENT’
ORDER BY column_name;

— Index based
SELECT index_name , num_rows , last_analyzed , distinct_keys , leaf_blocks , avg_leaf_blocks_per_key, join_index
FROM dba_indexes WHERE table_name = ‘DEPARTMENT’ ORDER BY index_name;
One adventage over Analyze is that u can copy stats stored in one database to another.
How to copy statistics with DBMS_STATS? You can copy statistics from a production to a test database to facilitate tuning. For example, to copy a schema’s statistics:

1. Use the DBMS_STATS.CREATE_STAT_TABLE procedure in the production database to create a user-defined statistics table.
2. Use the DBMS_STATS.EXPORT_SCHEMA_STATS procedure in the production database to copy statistics from the data dictionary to the user-defined statistics table from step 1.
3. Use the Export and Import utilities to transfer the statistics to a corresponding user-defined statistics table in the test database.
4. Use the DBMS_STATS.IMPORT_SCHEMA_STATS procedure to import the statistics into the data dictionary in the test database.
The DBMS_STATS package can also be used to back up statistics prior to analyzing objects. The backup can used to:
– Restore old statistics and
– Study changes in data characteristics over time

— Step 1. Create the table to hold the statistics:
DBMS_STATS.CREATE_STAT_TABLE
(‘HR’ /* schema name */
,’STATS’ /* statistics table name */
,’SAMPLE’ /* tablespace */
);

— Step 2. Copy the statistics into the table:
DBMS_STATS.EXPORT_TABLE_STATS
(’HR’ /* schema name */
,’EMP’ /* table name */
, NULL /* no partitions */
,’STATS’ /* statistics table name */
,’CRS990601’ /* id for statistics */
, TRUE /* index statistics */
);

— Step 3. Export the STATS table, and then import it into the second database.

— Step 4. Copy the statistics into the data dictionary:
DBMS_STATS.IMPORT_TABLE_STATS
(’HR’ /* schema name */
,’EMP’ /* table name */
, NULL /* no partitions */
,’STATS’ /* statistics table name */
,’CRS990601’ /* id for statistics */
, TRUE /* index statistics */
);

DBMS_STATS grant the option to exclude any table using DBMS_STATS.LOCK_TABLE_STATS, prevents automatic statistics gathering on that table.
Next post we will write about next Best Practices.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s