Performance Tuning could be divided into two categories.
1) Oracle System Tuning : Oracle System Tuning, involves making sure the
parameters are set up correctly, for example the parameters related to
undo management, undo tablespace size, redo file sizes, memory sizes
for SGA, Large Pool, etc, here we would look at the various parameter
tuning advisors provided by oracle.
2) SQL Tuning : SQL Tuning involves the tuning of SQL Statements, here
we would look at things like indexes, Optimizer,Hints,Stored Outlines etc
Oracle System Tuning
1) Redo Log Sizing
2) Tuning Advisors
3) Optimizer 4) ASMM - Automatic Shared Memory Management 5) Statspack
1) Redo Log Sizing : We have to make sure that the redo logs are not
switching often, check in the alert log file for the frequency of the log
switches. You could increase the size of the redo log, switches within
interval of 30 minutes is considered to be good
FAST_START_MTTR_TARGET : Time in seconds we want the instance to startup default is 900. Smaller the Values, more intensive the Log Writer Activity. 2) Tuning Advisors : Oracle creates histogram of the various paramerts based
on their performance history and gives us probable parameter's value and
estimated cost that could be saved when that configuration is done.
(We could also use ASMM, dealt below) Various Advisors V$shared_pool_advice
V$pga_target_advice V$db_cache_advice V$java_pool_advice V$shared_pool_advice, the below mentioned gives us the estimated time saved for various sizes
select shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_memory_object_hits, estd_lc_size, estd_lc_time_saved from v$shared_pool_advice order by shared_pool_size_factor / For above to work we should have init parameter STATISTICS_LEVE= TYPICAL PGA_AGGREGATE_TARGET
when the workarea_size_policy is not included in init file it is defaulted to AUTO,implies AUTO PGA Memory Management is enabled this automatically resizes the various memory areas sort_area_size, hash_are_size, bitmap_merge_area_size create_bitmap_area_size, parameters, dynamically depending upon memory requirements When you don't specify the PGA_AGGREGATE_TARGET parameter even though workarea_size_policy is set to auto, then it defaults to 10MB or 20% or SGA whichever is higher 3) Optimizer It is the oracle internal mechanism using which it decides what execution path to take for SQL Statements RULE Based Optimizer = It is based on some complex rules and does not depend on Statistics of tables or indexes Cost Based Optimizer = It decised which execution path to take based on statistics of the tables and indexes which are referenced in the query We have initialization Parameter Optimizer_mode Optimizer_mode = rule | Choose | first_rows | all_rows Rule = Obsolete, rule based approach for all the SQL Statements, regardless of the presence of statistics Choose = Optimizer decides whether to go for RULE based plan i.e when statistics are not available or to use the cost based method if statistics are available First_rows = Bring rows as fast as possible mostly used in OLTP databases, used cost based approach best performance hogs resources All_rows = Uses Cost Based approach regardless of the presence of statistics, hogs minimal resource and gives output 5) ASMM ASMM - Automatic Shared Memory Management introduced with Oracle 10g dynamically adjusts the size of Shared_pool Large_pool Java_pool db_buffer_cache. To enable ASMM set SGA_target to higher valie than 0, ASMM is managed by mman background process If you set SGA_TARGET to a non-zero values, Note : Size of SGA_TARGET could increase up to the size specified in SGA_MAX_SIZE. Memory is distributed among the components depending upon the workload After setting SGA_TARGET, if you decrease the value of any auto tuned parameter, the value is not decreased immediately but a lower limit is set for the component. If you increase the component values the value is immediately increased Statistics_level should not be basic, if you want to enable ASMM When ASMM is enabled and you specify a lower value to any of the auto tuned Components, then oracle does not automatically decreases it, it does sets a lower cap on it. And when you increase value of any auto tuned component it increases it immediately by taking memory from other auto tuned components 4) Statspack : This is nice tool that gather statistical information on regular basis
To install these we have create a tablespace for this or you could create it in
sysaux tablespace.
Execute the following scripts to install the statspace
ORACLE_HOME\rdbms\admin\catdbsyn.sql
ORACLE_HOME\rdbms\admin\dbmspool.sql ORACLE_HOME\rdbms\admin\spcreate.sql
Of these the last script spcreate.sql is interactive script, here it asks for perfstat users
password, tablespace where to install the scripts, and temporary tablepace to use.
Then we could schedule the snapshot to occur on regular basis using
spauto.sql, default is one hour, for the job to work properly make sure the job_queue_processes
should not be equal to 0
To create report use the spreport.sql
Various Levels in Statspack
Level 0 -- This level is for gathering general performance information
Level 5 -- This is the default level, provides general information + also information on Executed SQL statements and their resource utilization Level 6 -- This level provides 1) General information 2) SQL statements and resouce utilization 3) SQL Plans and its usage Level 10 -- This level Provides
1) General information 2) SQL statements and resouce utilization 3) SQL Plans and its usage 4) Parent and child latches Also , one could enable statspack for particular session using the command
SQL> Exec Statspack.snap(i_session_id=>20); SQL Tuning
1) Indexes
2) Explain Plan
3) Hints
4) Stored Outlines
create [unique | bitmap] index <index_name> on <table_name>(<column_name>,<column_name>) = Columns which are referenced in the where clause nologging =No log generation compress =Eliminate duplicate values reverse =Reverse ordering in order to balance parallel <Number> = No of process to be involved compute statistics = Generate statistics tablespace <tablespace_name> = Tablespace on which to be created Explain Plan Execution path taken for your SQL Statements 1) First create Plan table SQL>@?/rdbms/admin/utlxplan.sql To get plan for particular sql statements for example sql>explain plan for 2>select * from scott.dept 3>where deptno=30; Explained... This fills the plan table with execution plan To see the execution plan SQL>select * from table(dbms_xplan.display); give us plan table output You could then delele the plan table rows SQL>delete from plan_table; Hints these are the hints that you give to oracle to which optimizer mode to use, Oracle provides number of hints select /*+ First_rows */ * from <table_name> Stored Outlines. Allow you to transfer the execution plan for a sql query from development database to production database =====Miscellaneous=========
To find the Most Read tables,
col TOTAL_READS for a9999999
SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads FROM (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='logical reads') lr, (SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical reads') pr, dba_tables t WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
AND lr.owner=t.owner AND lr.object_name=t.table_name ORDER BY 3 desc; To find the Tables with Most write activity
SELECT t.owner,t.table_name,pw.value+pd.value AS Total_Writes FROM
(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical writes') pw,(SELECT owner,object_name,value FROM v$segment_statistics WHERE statistic_name='physical writes direct') pd, dba_tables t WHERE pw.owner=pd.owner AND pw.object_name=pd.object_name AND pw.owner=t.owner AND pw.object_name=t.table_name ORDER BY 3 desc; ============= Oracle Waits db file sequential read : - Tune the SQL so that there is less I/O. Analyze all objects Distribute I/O accross disks. buffer busy waits : - Increase the db_cache_size (sys.v$bh) log buffer space : - Increase the log_buffer, or move log file to faster disks. db file sequential read & db file scattered read : These are important wait events tell us time waited for I/O read request to complete db file sequential read : Reads data into contiguous memory, normally results from index scan db file scattered read : Reads data into contiguous memory (p3=1), data from full table scan normally get scattered over the bugger area. |
Database Technologies > Oracle >