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

 

 

 

 

 

             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

              

 

 

 

 

 

 

 

 

 

 

 

 

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;