Multi Threaded Server(MTS)

sys>show parameter spfile;
sys>create pfile from spfile;
sys>shut immediate
sys>exit
oracle@venu1 dbs>ls
oralce@venu1 dbs>mv spfilevenu1.ora spfilevenu1.ora.bkp
oracle@venu1 dbs>vi initvenu1.ora
DISPATCHERS="(PROTOCOL=TCP)(DISP=2)"
MAX_DISPATCHERS=5
SHARED_SERVERS=2
MAX_SHARED_SERVERS=5
LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=VENU1)(PORT=1521))"
:wq!
oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
sys>startup
oracle@venu1>ps -ef|grep smon
oracle@venu1>ps -ef|grep ora

Flashback Data Archive

FDA is used for auditing purpose and we need to create a separate tablespace for it.
FDA uses point in time recovery to recover the data where as Flashback Technology uses the flashback archive logs to recover and restore the data.

oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
sys>startup
sys>select name,open_mode,log_mode from v$database;
sys>select instance_name,status from v$instance;
sys>select name from v$datafile;
sys>select name from v$controlfile;
sys>select member from v$logfile;
sys>select name from v$tablespace;
sys>create tablespace fra_ts datafile '/u01/app/oracle/venu/fda_ts01.dbf' size 10m autoextend on;
sys>create flashback archive fda_1yr tablespace fda_ts retension 1year;
sys>desc dba_flashback_archive;
sys>select * from dba_flashback_archive;
sys>conn scott/tiger
scott>select * from tab;
scott>alter table salgrade flashback archive fda_1yr;
scott>conn / as sysdba
sys>grant flashback archive on fda_1yr to scott;
sys>conn scott/tiger
scott>alter table salgrade flashback archive fda_1yr;
scott>desc user_flashback_archive_tables;
scott>select * from user_flashback_archive_tables;
scott>select sysdate from dual;
scott>set time on;
scott>select * from salgrade;
scott>delete from salgrade where grade=1;
scott>commit;
scott>select * from salgrade;
scott>select * from salgrade as of timestamp to_timestamp('25-MAY-2013 23:09:28','dd-mm-yyyy hh24:mi:ss');
scott>select * from tab;
scott>select * from SYS_FBA_HIST_12345;
scott>select * from salgrade;
scott>insert into salgrade select * from salgrade as of timestamp to_timestamp('25-MAY-2013 23:09:28','dd-mm-yyyy hh24:mi:ss');
scott>set time off;
scott>alter table salgrade no flashback archive;
scott>grant flashback archive administer to scott;
scott>alter table salgrade no flashback archive;

Partitioning

Partitioning enhances the managability and availability of applications.
Partitioning is nothing but splitting a huge table into multiple partitions.
Once we implement the concept of partitioning, each partition access a independant segment on its own.
At the time of creating a partition, see that each partition resides in a separate tablespace and goes into separate file system.
Ideally, if the table size goes beyond 2gb, consider it as partitioning.
The column on which we create a partition is called "partition key".

We can create a local/global index on partitions.
If the partition itself becomes unmanagable, implement the concept of sub partitioning.
Different types of partitions are
1.Range partition
2.List partition
3.Hash partition
4.Composite partition

Range partition is generally implemented on number/date datatype columns.
List partition generally implemented on varchar2 datatype columns.
Note: If we are unable to decide the column on which to create a partition implement hash parttioning.
In case of hash partitioning, oracle equally distributes the data among all the partitions.
Combination of two or more partitions is called composite partitions.
We can take a backup of individual partitions if required, we can also merge two or more partitions if required.

Multi threaded server/Shared server

There are 2 types of server architectures :
1.Dedicated server architecture(DSA)
2.Shared server architecture(SSA)
In case of DSA, for every user process there will be a dedicated server process(DSP).

In an enterprise application environment, from 10g each server process occupies at least 5mb of memory, which may lead to wastage of memory resource.
In olden days, since RAM is very expensive we may go with SSA.
In case of SSA, for all user processes we enable few shared server processes.
Now a days since RAM has become very cheap, in most of the environments we go with dedicated server architecture.

In shared server configuration, we can give a dedicated connection to a specific user by configuring the following TNS entry :

to_hrms=
(Description=
(AddressList=(protocol=TCP)(host=venu1.oracle.com)(port=1521)
(server=dedicated)
(SID=hrms)))

Interpreting AWR Report

Elapsed time represents the time interval between the begin snapid and end snapid.
DB time indicates CPU time in processing + wait time. If the DB time is much greater than elapsed time drill down further in the report to understand the wait events.

1.Load Profile : In the load profile section we can identify TPS(Transaction Per Second)in the database.
In the load profile section, if the value for physical reads is much greater than logical reads, then we need to check weather a proper value is set for sga_target (or) memory_target.
if the hard parses value is muck bigger, increase the value for shared_pool_size parameter.

2.Instance Efficiency Percentage : In this section, we can understand the hit ratio of various sub components. Ideally, oracle recommands to have the hit ratio greater than or equal to 90%.
If ASMM/AMM is not enabled, if the hit ratios are less than 90%, if there is a possibility, increase the values of parameters db_cache_size and shared_pool_size.

3.Top 5 wait events :
3.1. DB file scatter read : This wait event indicates many full table scans.
Create indexes wherever possible.
Cache small historical tables and tune the joins.
3.2. DB file sequential read : This wait event indicates many index scans which is quiet natural.
Wherever possible tune the SQL code(joins).
3.3. Buffer busy : This wait event indicates some blocks are busy(data block,undo block).

Query to identify buffer busy waits :
SQL>select row_wait_object# from v$session where event='buffer busy waits';

Query to identify the object causing buffer busy waits :
SQL>select owner,object_name,subobject_name,object_type from dba_objects where data_object_id=&row_wait_obj;
-where row_wait is from previous query.
If the wait event is because of data block, move the segment to a tablespace where the block size is small.
-Implement ASMM.

Log file sync : This wait event indicates frequent commits(commit frequency is more).
-Commit more records at a time.
-Place redo log file in faster disks.

Free Buffer : This wait event is because of lack of free buffers in database buffer cache.
Increase db_cache_size parameter value.

4.SQL Statistics : In this section we can identify top 10 resource intensive SQL statements.
We can identify top 10 SQL statements consuming significant CPU resource.
Generate the execution plans of top 10 resource intensive SQL statements and tune them either by creating indexes/passing hints/creating materialized views etc..

oracle@venu1>export ORACLE_SID=venu1
oracle@venu1>sqlplus / as sysdba
sys>startup
sys>@$ORACLE_HOME/rdbms/admin/sqltrpt.sql
enter the value for sqlid :
5.Tablespace I/O statisics : This section indicates the tablespaces and their I/O's.
6.Advisory statistics :
6.1.PGA memory advisory : size factor 1 indicates the current value for the parameter pga_aggregate_target. Based on estimated cache_hit% we need to set a proper value for pga_aggregate_target.

6.2. Shared pool advisory : Shared pool size factor 1 indicates the current value for the parameter shared_pool_size. Based on estimated Library Cache(LC) memory object values we can set a proper value for the parameter shared_pool_size.

6.3. SGA target advisory : sga size factor 1 indicates the current value for the parameter sga_target. Based on estimated physical reads value, we can set a proper value for the parameter sga_target.

7.Undo statistics : Based on this section, we can understand, how many times snapshot too old error and how many times we got out of space errors. If the occurances of these values are more, increase undo tablespace size or create undo tablespace with retention guarantee.

8.Segment statistics : If we notice any small historical object and it is most frequently accessed, cache the object.