Friday, July 12, 2013

Performance data on 11g Standard edition as Oracle is not providing DIAGNOSTIC and TUNING packs with 11g Standard edition



Oracle is not providing DIAGNOSTIC and TUNING packs with 11g Standard edition any more. So..








1.As per Oracle Corp [Ref: SR 3-7512111031 :]:With the DIAGNOSTIC and TUNING packs, the best way to analyze performance issues, is to generate AWR reports.
Without this license, the best alternative to the AWR reports is to use Statspack reports, which will contain much of the same information, that is available in AWR reports.


So I followed the below steps and generated a test Statpack report:

2. Please execute the load on test server and capture snapshots with a gap of 20 mins  by executing
statspack.snap command as sysdba.

3. Do the same in the prod while load is running. Comparing the reports we could see the difference in DB behavior.

4.
a)    create tablespace tools datafile 'G:\TRANS_DATA\tools.dbf'
 size 1000M
autoextend on next 64M maxsize unlimited
extent management local uniform size 1M
segment space management auto;
c)    SQL>  connect / as sysdba
d)           SQL>  define default_tablespace='tools'
e)           SQL>  define temporary_tablespace='temp'
f)   
g)    SQL>@D:\oracle\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spcreate.sql





h)    execute statspack.snap;



i)   SQL> @D:\oracle\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\spreport.sql



Test report: Gathering a StatsPack snapshot
------------------------------

  The simplest interactive way to take a snapshot is to login to SQL*Plus as the
  PERFSTAT user, and execute the procedure statspack.snap:

     e.g.
       SQL>  connect perfstat/perfstat
       SQL>  execute statspack.snap;

  Note:  In an OPS environment, you must connect to the instance you wish to
  collect data for.  A snapshot must be taken on each instance so that later
  comparisons can be made.  A snapshot taken on one instance can only be
  compared to another snapshot taken on the same instance.

  Although statistics are cumulative ,this will store the current values for the
  performance statistics in the StatsPack schema tables, and can be used as a
  baseline snapshot for comparison with another snapshot taken at a later time.

  For easier performance analysis, set the init.ora parameter timed_statistics to
  true;  this way, statspack data collected will include important timing
  information.  The timed_statistics parameter is also dynamically changeable
  using the 'alter system' command.  Timing data is important and often required
  by Oracle support to diagnose performance problems. 
  Note:30824.1: "Init.ora Parameter "TIMED_STATISTICS" Reference Note"
  states that the small amount of  overhead from tuning this tracing on is worth
  the benefits gained.

Parameters able to be passed in to the statspack.snap and
statspack.modify_statspack_parameter procedures
---------------------------------------------------------

=============================================================================                    Range of      Default
Parameter Name      Valid Values  Value    Meaning
=============================================================================
i_snap_level        0, 5, 6, 10   5        Snapshot Level
                                           *Level 6 NOT VALID in 8i
                                           *Level 7 is available in 9.2
------------------------------------------------------------------------------
i_ucomment          Text          Blank    Comment to be stored with Snapshot
------------------------------------------------------------------------------
i_executions_th     Integer >=0   100      SQL Threshold: number of times
                                           the statement was executed
------------------------------------------------------------------------------
i_disk_reads_th     Integer >=0   1,000    SQL Threshold: number of disk reads
                                           the statement made
------------------------------------------------------------------------------
i_parse_calls_th    Integer >=0   1,000    SQL Threshold: number of parse
                                           calls the statement made
------------------------------------------------------------------------------
i_buffer_gets_th    Integer >=0   10,000   SQL Threshold: number of buffer
                                           gets the statement made
------------------------------------------------------------------------------
i_session_id        Valid sid     0 (no    Session Id of the Oracle Session
                    from          session) to capture session granular
                    v$session              statistics for
------------------------------------------------------------------------------
i_modify_parameter  True, False   False    Save the parameters specified for
                                           future snapshots?
=============================================================================

Configuring the amount of data captured
---------------------------------------

 - Snapshot Level

  It is possible to change the amount of information gathered by the package,
  by specifying a different snapshot 'level'.  In other words, the level
  chosen (or defaulted) will decide the amount of data collected. 

    Levels  = 0   General performance statistics
       Statistics gathered:
       This level and any level greater than 0 collects general  performance statistics, such as: wait statistics, system events,  system statistics, rollback segment data, row cache, SGA,  background events, session events, lock statistics, buffer pool statistics, parent latch statistics.

    Levels  = 5  Additional data:  SQL Statements
       This level includes all statistics gathered in the lower level(s),  and additionally gathers the performance data on high resource  usage SQL statements.

       SQL 'Thresholds'
          The SQL statements gathered by Statspack are those which exceed one of
          four predefined threshold parameters:
           - number of executions of the SQL statement            (default 100)
           - number of disk reads performed by the SQL statement  (default 1,000)
           - number of parse calls performed by the SQL statement (default 1,000)
           - number of buffer gets performed by the SQL statement (default 10,000)

          The values of each of these threshold parameters are used when
          deciding which SQL statements to collect - if a SQL statement's
          resource usage exceeds any one of the above threshold values, it
          is captured during the snapshot.

          The SQL threshold levels used are either those stored in the table
          stats$statspack_parameter, or by the thresholds specified when
          the snapshot is taken.

    Levels  = 6
       This level includes all statistics gathered in the lower level(s).
       Additionally, it gathers SQL plans and plan usage data for each of the  high resource usage SQL statements captured. Therefore, level 6 snapshots should be used whenever there is the possibility that a plan may change. To gather the plan for a SQL statement, the statement must be in the shared  pool at the time the snapshot is taken, and it must exceed one of the SQL  thresholds. To gather plans for all statements in the shared pool, specify  the executions threshold to be zero (0) for those snapshots.

   Level   = 7
       Levels >= 7 Additional data: Segment Level Statistics
       This level includes all statistics gathered in the lower levels, and additionally  gathers the performance data on highly used segments. RAC specific segment level  statistics are also captured with level 7.
       A level 7 snapshot gathers information which determines what segments are more  heavily accessed and contended. With this information, you can decide to modify  the physical layout of some segments or of the tablespaces they reside in. For example,
       to better spread the segment IO load, you can add files residing on different disks to a
       tablespace storing a heavily accessed segment or you can partition a segment. This information
       can also help decide on changing segment attributes values such as PCTFREE or INITRANS.
       On a RAC environment, this information allows us to easily spot the segments responsible for much of the cross-instance traffic.
       Level 7 includes the following segment statistics:

       Logical reads
       Db block changes
       Physical reads
       Physical writes
       Physical reads direct
       Physical writes direct
       Global cache consistent read blocks served (RAC specific)
       Global cache current blocks served (RAC specific)
       Buffer busy waits
       ITL waits
       Row lock waits

    Levels  = 10 Additional statistics:  Child latches
       This level includes all statistics gathered in the lower levels, and
       additionally gathers high Child Latch information.  Data gathered at
       this level can sometimes cause the snapshot to take longer to complete
       i.e. this level can be resource intensive, and should only be used
       when advised by Oracle personnel.

- Snapshot SQL thresholds

  There are other parameters which can be configured in addition to the level.
  These parameters are used as thresholds when collecting SQL statements;
  if any SQL statements breach the threshold, these are the statements which
  are captured during the snapshot.

  Snapshot level and threshold information used by the package is stored
  in the stats$statspack_parameter table.


- Changing the default values for Snapshot Level and SQL Thresholds

  The default parameters used for taking snapshots can be adjusted/modified so
  that they better capture data about an instance's workload. 

  This can be done either by:

  o  Taking a snapshot, and specifying the new defaults to be saved to the
      database (using statspack.snap, and using the i_modify_parameter
      input variable).

     SQL>execute statspack.snap -
           (i_snap_level=>10, i_modify_parameter>'true');

     Setting the i_modify_parameter value to true will save the new
     thresholds in the stats$statspack_parameter table; these thresholds
     will be used for all subsequent snapshots.

     If the i_modify_parameter was false or omitted, the snapshot taken at
     that point will use the specified values, any subsequent snapshots
     use the preexisting values in the stats$statspack_parameter table.

  o  Changing the defaults immediately without taking a snapshot, using the
      statspack.modify_statspack_parameter procedure.  For example to change
      the snapshot level to 10, and the SQL thresholds for buffer_gets and
      disk_reads, the following statement can be issued:

      SQL>  execute statspack.modify_statspack_parameter -
                 (i_snap_level=>10, i_buffer_gets_th=>10000, i_disk_reads_th>1000);

      This procedure changes the values permananently, but does not
      take a snapshot.

      The full list of parameters which can be passed into the
      modify_statspack_parameter procedure are the same as those for
      the snap procedure. 

- Specifying a Session Id

  If session statistics are needed for a particular session, it is possible to
  specify the session id in the call to StatsPack.  The statistics gathered for
  the session will include session statistics, session events and lock activity. 
  The default behavior is to not to gather session level statistics.

      SQL>  execute statspack.snap(i_session_id=>3);

No comments:

Post a Comment