Thursday 17 November 2011

Perfomance Tuning Category


Archive for the ‘Performance Tuning’ Category
The below script will allow oracle to run database statistics on the time you create this script and performs this on daily basis. Also, this example shows to collect full database statistics, but for other levels like schema,table etc, refer to dbms_stats package options
SET SERVEROUTPUT ON
DECLARE
  l_job  NUMBER;
BEGIN
  DBMS_JOB.submit(l_job,’BEGIN DBMS_STATS.gather_database_stats; END;’,SYSDATE,’SYSDATE + 1′);
  COMMIT;
  DBMS_OUTPUT.put_line(‘Job: ‘ || l_job);
END;
/
Before performing any statistics job, please do question “Is my database is not performing well?”
SELECT to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) ||
nvl(lower(ssn.machine), ins.host_name) “SESSION”,
to_char(prc.spid, ’999999999′) “PID/THREAD”,
to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ ” CURRENT SIZE”,
to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ ” MAXIMUM SIZE”
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = ‘session pga memory’
AND se2.statistic# = stat2.statistic# and stat2.name = ‘session pga memory max’
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);
SELECT spid, program,
            pga_max_mem      max,
            pga_alloc_mem    alloc,
            pga_used_mem     used,
            pga_freeable_mem free
       FROM V$PROCESS
      WHERE spid = 2587;
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READS
  FROM V$DB_CACHE_ADVICE
    WHERE NAME          = ‘KEEP’
     AND BLOCK_SIZE    = (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘db_block_size’)
     AND ADVICE_STATUS = ‘ON’;
or
SELECT   ds.BUFFER_POOL,
         Substr(do.object_name,1,9) object_name,
         ds.blocks                  object_blocks,
         Count(* )                  cached_blocks
FROM     dba_objects do,
         dba_segments ds,
         v$bh v
WHERE    do.data_object_id = v.objd
         AND do.owner = ds.owner (+)
         AND do.object_name = ds.segment_name (+)
         AND do.object_type = ds.segment_type (+)
         AND ds.BUFFER_POOL IN (‘KEEP’,'RECYCLE’)
GROUP BY ds.BUFFER_POOL,
         do.object_name,
         ds.blocks
ORDER BY do.object_name,
         ds.BUFFER_POOL;
DECLARE
  vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  vMaxHeight NUMBER;                          /* Max tree height        */
  vMaxDel    NUMBER;                          /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name
     FROM dba_indexes WHERE OWNER NOT LIKE ‘SYS%’;
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName;
     EXIT WHEN cGetIdx%NOTFOUND;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := ‘ANALYZE INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ VALIDATE STRUCTURE’;
     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     DBMS_SQL.CLOSE_CURSOR(vCursor);
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
        FROM INDEX_STATS;
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
     ELSE
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
        DBMS_OUTPUT.PUT_LINE(‘ALTER INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ REBUILD;’);
     END IF;

  END LOOP;
  CLOSE cGetIdx;
END;
First get the top 10 CPU-intensive Oracle processes on the operating system with the first column giving the %CPU used, the second column unix PID, the third column USER , the fourth column TERMINAL, and the last column Unix PROCESS (works only for UNIX).
ps -eaf -o pcpu,pid,user,tty,comm | grep ora |
    grep -v \/sh | grep -v ora_ | sort -r | head -20
Now you can specify the found PID in the following SQL-Statement:
column username format a9
column sql_text format a70
SELECT a.username, b.sql_text
  FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = ‘&PID’ OR a.process = ‘&PID’)
   AND a.paddr = c.addr
   AND a.sql_address = b.address
/
create table births_REG1(CIRCLE_NO                 NUMBER(1) NOT NULL,
WARD_NO                                            VARCHAR2(10),
LOCALITY                                           VARCHAR2(50),
TYPE_OF_HOSPITAL                                   VARCHAR2(20),
HOSPITAL_NAME                                      VARCHAR2(100),
HOSPITAL_ADDRESS                                   VARCHAR2(150),
ATTENTION_TYPE                                     VARCHAR2(50),
DATE_OF_BIRTH                                      DATE NOT NULL,
CHILD_NAME                                         VARCHAR2(55),
SEX                                                VARCHAR2(1) NOT NULL,
BIRTH_ORDER                                        NUMBER(2),
BIRTH_TYPE                                         VARCHAR2(5),
STILL_BORN_CAUSE                                   VARCHAR2(100),
BABY_WEIGHT                                        NUMBER(5,3),
BABY_PER_DELIVERY                                  NUMBER(2),
REG_NO                                             VARCHAR2(40),
REG_DATE                                           DATE,
MOTHER_NAME                                        VARCHAR2(50),
MOTHER_RELIGION                                    VARCHAR2(15),
MOTHER_OCCUPATION                                  VARCHAR2(15),
MOTHER_LITERACY                                    VARCHAR2(25),
MOTHER_AGE_AT_MARRIAGE                             VARCHAR2(20),
MOTHER_AGE_AT_DELIVERY                             VARCHAR2(20),
PREGNANT_DURATION                                  NUMBER(2),
DELIVERY_METHOD                                    VARCHAR2(25),
AGE_GROUP                                          VARCHAR2(10),
MOTHER_NATIONALITY                                 VARCHAR2(20),
FATHER_NAME                                        VARCHAR2(65),
FATHER_RELIGION                                    VARCHAR2(15),
FATHER_OCCUPATION                                  VARCHAR2(15),
FATHER_LITERACY                                    VARCHAR2(25),
FATHER_NATIONALITY                                 VARCHAR2(20),
INFORMANT_NAME                                     VARCHAR2(100),
INFORMANT_ADDRESS                                  VARCHAR2(200),
REMARKS                                            VARCHAR2(250),
RESIDENCE_ADDRESS                                  VARCHAR2(150),
ACK_NO                                             NUMBER(12) NOT NULL,
ACK_DATE                                           DATE NOT NULL,
STATUS                                             VARCHAR2(2),
PAY_STATUS                                         VARCHAR2(1),
PRINT_STATUS                                       VARCHAR2(2),
UPDATE_DATE                                        DATE,
SYSTEM_IP                                          VARCHAR2(15),
FATHER_ADOPTION                                    VARCHAR2(1),
FATHER_CHANGE                                      VARCHAR2(1),
MOTHER_CHANGE                                      VARCHAR2(1),
UPDATE_FLAG                                        NUMBER,
PERMANENT_ADDRESS    VARCHAR2(200))
tablespace testtbs
PARTITION BY RANGE (DATE_OF_BIRTH)
SUBPARTITION BY LIST (CIRCLE_NO)
subpartition template(
SUBPARTITION SP1 values (1),
SUBPARTITION SP2 values (2),
SUBPARTITION SP3 values (3),
SUBPARTITION SP4 values (4),
SUBPARTITION SP5 values (5),
SUBPARTITION SP6 values (6),
SUBPARTITION SP7 values (7))
(
PARTITION p1 VALUES LESS THAN (TO_DATE(’01-JAN-1960′, ‘DD-MON-YYYY’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(’01-JAN-1965′, ‘DD-MON-YYYY’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(’01-JAN-1970′, ‘DD-MON-YYYY’)),
PARTITION p4 VALUES LESS THAN (TO_DATE(’01-JAN-1975′, ‘DD-MON-YYYY’)),
PARTITION p5 VALUES LESS THAN (TO_DATE(’01-JAN-1980′, ‘DD-MON-YYYY’)),
PARTITION p6 VALUES LESS THAN (TO_DATE(’01-JAN-1985′, ‘DD-MON-YYYY’)),
PARTITION p7 VALUES LESS THAN (TO_DATE(’01-JAN-1988′, ‘DD-MON-YYYY’)),
PARTITION p8 VALUES LESS THAN (TO_DATE(’01-JAN-1991′, ‘DD-MON-YYYY’)),
PARTITION p9 VALUES LESS THAN (TO_DATE(’01-JAN-1994′, ‘DD-MON-YYYY’)),
PARTITION p10 VALUES LESS THAN (TO_DATE(’01-JAN-1997′, ‘DD-MON-YYYY’)),
PARTITION p11 VALUES LESS THAN (TO_DATE(’01-JAN-2000′, ‘DD-MON-YYYY’)),
PARTITION p12 VALUES LESS THAN (TO_DATE(’01-JAN-2003′, ‘DD-MON-YYYY’)),
PARTITION p13 VALUES LESS THAN (TO_DATE(’01-JAN-2006′, ‘DD-MON-YYYY’)),
PARTITION p14 VALUES LESS THAN (TO_DATE(’01-JAN-2009′, ‘DD-MON-YYYY’)),
PARTITION p15 VALUES LESS THAN (TO_DATE(’01-JAN-2012′, ‘DD-MON-YYYY’)),
PARTITION p16 VALUES LESS THAN (TO_DATE(’01-JAN-2015′, ‘DD-MON-YYYY’))
);
CREATE INDEX BIRTHS_REG_INDEX ON BIRTHS_REG1
(DATE_OF_BIRTH, CIRCLE_NO, WARD_NO, SEX, ACK_NO,
BIRTH_TYPE, REG_DATE, REG_NO)
 TABLESPACE TESTTBS
 GLOBAL PARTITION BY RANGE (DATE_OF_BIRTH) (
PARTITION p1 VALUES LESS THAN (TO_DATE(’01-JAN-1960′, ‘DD-MON-YYYY’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(’01-JAN-1965′, ‘DD-MON-YYYY’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(’01-JAN-1970′, ‘DD-MON-YYYY’)),
PARTITION p4 VALUES LESS THAN (TO_DATE(’01-JAN-1975′, ‘DD-MON-YYYY’)),
PARTITION p5 VALUES LESS THAN (TO_DATE(’01-JAN-1980′, ‘DD-MON-YYYY’)),
PARTITION p6 VALUES LESS THAN (TO_DATE(’01-JAN-1985′, ‘DD-MON-YYYY’)),
PARTITION p7 VALUES LESS THAN (TO_DATE(’01-JAN-1988′, ‘DD-MON-YYYY’)),
PARTITION p8 VALUES LESS THAN (TO_DATE(’01-JAN-1991′, ‘DD-MON-YYYY’)),
PARTITION p9 VALUES LESS THAN (TO_DATE(’01-JAN-1994′, ‘DD-MON-YYYY’)),
PARTITION p10 VALUES LESS THAN (TO_DATE(’01-JAN-1997′, ‘DD-MON-YYYY’)),
PARTITION p11 VALUES LESS THAN (TO_DATE(’01-JAN-2000′, ‘DD-MON-YYYY’)),
PARTITION p12 VALUES LESS THAN (TO_DATE(’01-JAN-2003′, ‘DD-MON-YYYY’)),
PARTITION p13 VALUES LESS THAN (TO_DATE(’01-JAN-2006′, ‘DD-MON-YYYY’)),
PARTITION p14 VALUES LESS THAN (TO_DATE(’01-JAN-2009′, ‘DD-MON-YYYY’)),
PARTITION p15 VALUES LESS THAN (TO_DATE(’01-JAN-2012′, ‘DD-MON-YYYY’)),
PARTITION p16 VALUES LESS THAN (TO_DATE(’01-JAN-2015′, ‘DD-MON-YYYY’))
);
Do you know you can purge statspack data?
All you need to do is to run either sptrunc.sql (to truncate tables of perfstat user) or sppurge.sql (to delete rows from tables of perfstat user)
If you add the following script to sppurge.sql, it will purge the statspack data after every 2 weeks
column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select nvl(min(s.snap_id), 0) min_snap_id, nvl(max(s.snap_id), 0) max_snap_id
  from perfstat.stats$snapshot s
     , perfstat.stats$database_instance di
where s.dbid              = :dbid
   and di.dbid             = :dbid
   and s.instance_number   = :inst_num
   and di.instance_number  = :inst_num
   and di.startup_time     = s.startup_time
   and s.snap_time < sysdate-14;
right before:

–  Post warning
prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id’s specified, for the database instance
prompt you are connected to.
prompt
prompt You may wish to export this data before continuing.
prompt
and save that script as something ELSE (not as sppurge.sql), then running that script will
automagically delete all snapshots older then two weeks old.  You can add an EXIT at the
end of this new script and then use OEM or your OS job scheduler to schedule this script
to run every week.
GRANT CREATE SESSION TO &&user;
GRANT ANALYZE ANY TO &&user;
GRANT ANALYZE ANY DICTIONARY TO &&user;
GRANT SELECT ANY TABLE TO &&user;
GRANT SELECT ANY SEQUENCE TO &&user;
GRANT SELECT ANY DICTIONARY TO &&user;
GRANT EXECUTE ON DBMS_STATS TO &&user;
GRANT SELECT ON DBA_TAB_MODIFICATIONS TO &&user;
GRANT CREATE JOB TO &&user;
GRANT SCHEDULER_ADMIN TO &&user;
GRANT ALL ON SYS.GATHER_STATS_JOB TO &&user;
GRANT ALL ON SYS.AUTO_SPACE_ADVISOR_JOB TO &&user;
CREATE OR REPLACE PACKAGE &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
   the optimizer statistics for 9i and 10g
   databases. Once on 11g, each tables’ statistics
   setting can be customized and used by Oracle’s automatic
   job
   Assumptions:
   For 10g, STATISTICS_LEVEL is set to a level
   above basic, so that DBA_TAB_MODIFICATIONS
   is populated (i.e. tables are monitored)
   For 9i, relevant tables have their MONITORING
   attribute set, so that DBA_TAB_MODIFICATIONS
   view is populated
   This package is either compiled as a privileged user
   and/or it is compiled under someone else and
   all applicable grants on system packages and
   dynamic performance views are made
   Has a dependency to dbms_lock.sleep procedure – this is assumed to be present
   Explicit ANALYZE ANY grant has been made to DB_UTIL user
*/
AS
   –Globals change factor setting
   –This defines what % a segment must change by in order to update its statistics
   gn_change_factor number(16,2) := 5.00;
 
   –Removes all Statistic for the Database
   PROCEDURE delete_all_stats;
   –Procedure that disables the automatic gathering of stats
   –since, until 10g, this ‘canned’ procedure is not adequate
   –to gather statistics responsibly
   PROCEDURE disable_auto_stats_job;
   –Procedure that disables the automatic segment space advisor job
   –The running of this job can use up a lot of resources, hence
   –why many disable it
   PROCEDURE disable_auto_space_job;
   –Procedure gathers stats for SYSTEM and SYS schemas
   –Basically a wrapped calls to the applicable DBMS_STATS procedure
   PROCEDURE gather_data_dict_stats;
   –Procedure gathers fixed object stats
   –Whereas, data dictionary stats should be gathers constantly
   –fixed object stats only need to be gathered after upgrades, or
   –if the profile of the database’s usage changes significantly
 
   PROCEDURE gather_fixed_stats;
   –Procedure that gathers non-fixed, non-locked table, index, partition, and subpartition statistics
   –based upon whether there have been significant changes (i.e. DBA_TAB_MODIFICATIONS)
   –and or whether stats are empty
   –Package level defaults can be modified for your
   PROCEDURE gather_stats;
   –Procedure makes internal calls to first delete all stats
   –and then gather them, including a gathering of system statistics
   PROCEDURE start_over;
END dba_stats_maint_pkg;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY &&user..dba_stats_maint_pkg
/* Package is intended for use when managing
   the optimizer statistics for 9i and 10g
   databases. Once on 11g, each tables’ statistics
   setting can be customized and used by Oracle’s automatic
   job
   Assumptions:
   For 10g, STATISTICS_LEVEL is set to a level
   above basic, so that DBA_TAB_MODIFICATIONS
   is populated (i.e. tables are monitored)
   For 9i, relevant tables have their MONITORING
   attribute set, so that DBA_TAB_MODIFICATIONS
   view is populated
   This package is either compiled as a privileged user
   and/or it is compiled under someone else and
   all applicable grants on system packages and
   dynamic performance views are made
   */
AS
   –Removes all Statistic for the Database
   PROCEDURE delete_all_stats
   AS
   BEGIN
      DBMS_STATS.delete_dictionary_stats;
      DBMS_STATS.delete_database_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
   –Procedure that disables the automatic gathering of stats
   –since, until 10g, this ‘canned’ procedure is not adequate
   –to gather statistics responsibly
 
   PROCEDURE disable_auto_stats_job
   AS
   BEGIN
      dbms_scheduler.disable(‘SYS.GATHER_STATS_JOB’);
   END;
   –Procedure that disables the automatic segment space advisor job
   –The running of this job can use up a lot of resources, hence
   –why many disable it
   PROCEDURE disable_auto_space_job
   AS
   BEGIN
      dbms_scheduler.disable(‘SYS.AUTO_SPACE_ADVISOR_JOB’);
   END;
   PROCEDURE gather_data_dict_stats
   AS
   BEGIN
      DBMS_STATS.gather_dictionary_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
 
   PROCEDURE gather_fixed_stats
   AS
   BEGIN
      DBMS_STATS.gather_fixed_objects_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
 
   PROCEDURE gather_stats
   AS
      /* Cursor with the names of all the non-partitioned, non-system, non-locked, non-iot overflow type segments
         which don’t currently have statistics */
      lv_owner           dba_tables.owner%TYPE;
      lv_table_name      dba_tables.table_name%TYPE;
      lv_part_name       dba_tab_partitions.partition_name%TYPE;
      lv_change_factor   NUMBER (16, 2);
      lv_partitioned     VARCHAR (10);
      CURSOR lc_ns_np_nostats
      IS
         SELECT owner, table_name
           FROM dba_tables
          WHERE partitioned = ‘NO’
            AND NOT owner IN (‘SYS’,'SYSTEM’,'OUTLN’)
            AND num_rows IS NULL
            AND iot_type IS NULL
            AND NOT (owner, table_name) IN (SELECT owner, table_name
                                              FROM dba_tab_statistics
                                             WHERE stattype_locked = ‘ALL’)
            AND NOT (owner, table_name) IN (SELECT owner, table_name
                                              FROM dba_external_tables);
      CURSOR lc_ns_p_nostats
      IS
         SELECT dtp.table_owner, dtp.table_name, dtp.partition_name
           FROM dba_tab_partitions dtp, dba_tables dt
          WHERE dtp.num_rows IS NULL
            AND dtp.table_name = dt.table_name
            AND dtp.table_owner = dt.owner
            AND dt.iot_type IS NULL
            AND NOT dtp.table_owner IN (‘SYS’,'SYSTEM’,'OUTLN’)
            AND NOT (dtp.table_owner, dtp.table_name) IN (
                                                 SELECT owner, table_name
                                                   FROM dba_tab_statistics
                                                  WHERE stattype_locked =
                                                                         ‘ALL’)
            AND NOT (dtp.table_owner, dtp.table_name) IN (SELECT owner, table_name
                                                    FROM dba_external_tables);
      CURSOR lc_ss
      IS
         SELECT dtm.table_owner, dtm.table_name, dtm.partition_name,
                  ROUND (  (dtm.inserts + dtm.updates + dtm.deletes)
                         / dt.num_rows,
                         2
                        )
                * 100 “CHANGE_FACTOR”,
                dt.partitioned
           FROM sys.dba_tab_modifications dtm, dba_tables dt
          WHERE dtm.table_owner = dt.owner
            AND dtm.table_name = dt.table_name
            AND NOT dtm.table_owner IN (‘SYS’,'SYSTEM’,'OUTLN’)
            AND NOT dt.num_rows IS NULL
            AND iot_type IS NULL
            AND (   (dt.partitioned = ‘YES’ AND NOT dtm.partition_name IS NULL
                    )
                 OR (dt.partitioned = ‘NO’ AND dtm.partition_name IS NULL)
                )
            AND NOT (dtm.table_owner, dtm.table_name) IN (
                                             SELECT dts.owner, dts.table_name
                                               FROM dba_tab_statistics dts
                                              WHERE dts.stattype_locked =
                                                                         ‘ALL’)
            AND NOT (dtm.table_owner, dtm.table_name) IN (
                                              SELECT det.owner,
                                                     det.table_name
                                                FROM dba_external_tables det);
   BEGIN
      /**************************BEGIN EMPTYSTATS************************************/
    
      — First process the cursor above; alternatively, you could
      — do something similar via a call to DBMS_STATS with the ‘GATHER EMPTY’ option
    
      OPEN lc_ns_np_nostats;               — open the cursor before fetching
      LOOP
         FETCH lc_ns_np_nostats
          INTO lv_owner, lv_table_name;   — fetches 2 columns into variables
         EXIT WHEN lc_ns_np_nostats%NOTFOUND;
         — Call stats package
         DBMS_OUTPUT.put_line (   ‘Gathering Stats for Table and Indexes of ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘ because they are empty…’
                              );
         — For these tables we will use most of the oracle defaults
         — We are assuming that partitioned tables are to be treated differently in terms
         — of sample size, parallelism degree and the like
         –trap any errors by placing in its own block
         BEGIN
            DBMS_STATS.gather_table_stats
                              (ownname               => lv_owner,
                               tabname               => lv_table_name,
                               granularity           => ‘AUTO’,
                               method_opt            => ‘for all columns’,
                               CASCADE               => DBMS_STATS.auto_cascade,
                               estimate_percent      => DBMS_STATS.auto_sample_size
                              );  
         EXCEPTION
           WHEN OTHERS THEN
              DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);          
         END;                   
      END LOOP;
      CLOSE lc_ns_np_nostats;
      — Next we process table partitions whose statistics are null/empty
      — We will use smaller samples and parallelism when appropriate
    
      OPEN lc_ns_p_nostats;                 — open the cursor before fetching
      LOOP
         FETCH lc_ns_p_nostats
          INTO lv_owner, lv_table_name, lv_part_name;
         — fetches 3 columns into variables
         EXIT WHEN lc_ns_p_nostats%NOTFOUND;
         — Call stats package
         DBMS_OUTPUT.put_line (   ‘Gathering Stats for Partition ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘.’
                               || lv_part_name
                               || ‘ because they are empty…’
                              );
                            
         — For these partitioned tables we will use smaller sample sizes
         — Trap Errors in anonymous block
       
         BEGIN
         DBMS_STATS.gather_table_stats (ownname               => lv_owner,
                                        tabname               => lv_table_name,
                                        partname              => lv_part_name,
                                        granularity           => ‘AUTO’,
                                        method_opt            => ‘for all columns’,
                                        CASCADE               => DBMS_STATS.auto_cascade,
                                        DEGREE                => 1,
                                        estimate_percent      => .00001
                                       );
         EXCEPTION
           WHEN OTHERS THEN
             DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
         END;                            
                                  
      END LOOP;
      CLOSE lc_ns_p_nostats;
      –Execute Global Database Stats Gathering with GATHER EMPTY option
      –in order to catch any objects that were missed above because of errors, etc.
      DBMS_OUTPUT.put_line (‘Executing Global Stats Procedure to Catch Any Missed Empty Objects…’);
    
      BEGIN
         dbms_stats.gather_database_stats(method_opt=>’for all columns’,
                                          granularity=>’AUTO’,
                                          estimate_percent=>DBMS_STATS.auto_sample_size,
                                          degree=>DBMS_STATS.default_degree,
                                          cascade=>DBMS_STATS.auto_cascade,
                                          options=>’GATHER EMPTY’);
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
      END;    
      /**************************END EMPTYSTATS************************************/
      — Lastly we flush the database monitoring information
      — and then use the information therein to gather stats
      — on significantly changed objects
    
      /**************************BEGIN CHANGED OBJECTS STATS***********************/
    
      DBMS_STATS.flush_database_monitoring_info ();
    
      OPEN lc_ss;                           — open the cursor before fetching
      LOOP
         FETCH lc_ss
          INTO lv_owner, lv_table_name, lv_part_name, lv_change_factor,
               lv_partitioned;
         EXIT WHEN lc_ss%NOTFOUND;
         — If change factor is greater than change factor defined in the package header then we will analyze the changed object in question
         IF lv_change_factor >= gn_change_factor
         THEN
            — Now determine whether the Object is a partition
            IF lv_partitioned = ‘YES’
            THEN
               DBMS_OUTPUT.put_line
                                  (   ‘Gathering Stats for Partition ‘
                                   || lv_owner
                                   || ‘.’
                                   || lv_table_name
                                   || ‘.’
                                   || lv_part_name
                                   || ‘ because its change factor exceeds ‘||gn_change_factor||’%…’
                                  );
               BEGIN
               DBMS_STATS.gather_table_stats
                                          (ownname               => lv_owner,
                                           tabname               => lv_table_name,
                                           partname              => lv_part_name,
                                           granularity           => ‘AUTO’,
                                           method_opt            => ‘for all columns’,
                                           CASCADE               => DBMS_STATS.auto_cascade,
                                           DEGREE                => 1,
                                           estimate_percent      => .00001
                                          );
               EXCEPTION
                 WHEN OTHERS THEN
                   DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);                         
               END;                         
            ELSE
               DBMS_OUTPUT.put_line
                              (   ‘Gathering Stats for Table and Indexes of ‘
                               || lv_owner
                               || ‘.’
                               || lv_table_name
                               || ‘ because its change factor exceeds ‘||gn_change_factor||’%…’
                              );
               BEGIN
               DBMS_STATS.gather_table_stats
                              (ownname               => lv_owner,
                               tabname               => lv_table_name,
                               granularity           => ‘AUTO’,
                               method_opt            => ‘for all columns’,
                               CASCADE               => DBMS_STATS.auto_cascade,
                               estimate_percent      => DBMS_STATS.auto_sample_size
                              );
               EXCEPTION
                 WHEN OTHERS THEN
                    DBMS_OUTPUT.put_line (SQLCODE || ‘ – ‘ || SQLERRM);
               END;                 
            END IF;
         ELSE
            NULL;
         END IF;
      END LOOP;
      CLOSE lc_ss;
    
      /**************************END CHANGED OBJECTS STATS**************************/
   END;
   –Procedure makes internal calls to first delete all stats
   –and then gather them all over again
 
   PROCEDURE start_over
   AS
   BEGIN
      DBMS_OUTPUT.put_line (‘Deleting all stats…’);
      delete_all_stats;
      DBMS_OUTPUT.put_line (‘Gathering fixed stats…’);
      gather_fixed_stats;
      DBMS_OUTPUT.put_line (‘Gathering user stats…’);
      gather_stats;
      DBMS_OUTPUT.put_line (‘Gathering dictionary stats…’);
      gather_data_dict_stats;
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;
END dba_stats_maint_pkg;
/
SHOW ERRORS;
DECLARE
vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
vCursor  NUMBER;                            /* DBMS_SQL cursor        */
vNumRows INTEGER;                           /* DBMS_SQL return rows   */
vHeight  index_stats.height%TYPE;           /* Height of index tree   */
vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
vDLfPerc   NUMBER;                          /* Del lf Percentage      */
vMaxHeight NUMBER;                          /* Max tree height        */
vMaxDel    NUMBER;                          /* Max del lf percentage  */
CURSOR cGetIdx IS SELECT owner,index_name
FROM dba_indexes WHERE OWNER NOT LIKE ‘SYS%’;
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel    := 20;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := ‘ANALYZE INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ VALIDATE STRUCTURE’;
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding?  */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN         /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
DBMS_OUTPUT.PUT_LINE(‘ALTER INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ REBUILD;’);
END IF;
END LOOP;
CLOSE cGetIdx;
END;
Many a times, we may be in need to find fragmented indexes in the database. Here is one script which is helpful
select
‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,’||””||
rtrim(t.table_name)||””||’);’,
t.table_owner||’.’||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date(’&from_date’,’dd-mon-yyyy’) and
t.table_owner = a.owner and t.table_owner not in (’SYS’,’SYSTEM’) and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;
As DBA’s, one of our primary goal is to achieve optimal response time for the queries. But many a times we would be wondering how we can analyze whether a particular response time is optimal or not. If it is high, what issues are causing this large value etc….
For all such questions, here is one article for you which presents analyzing response time in a wonderful way.
It helped me alot in finding root causes in a performance problem in one of my database. Hope it will be same in your case…. :)
SELECT * FROM
(SELECT
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN ROWLEN,
BLOCKS,
ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
ROUND(BLOCKS * (8000 – 23 * INI_TRANS) *
(1 – PCT_FREE / 100) / 1000000, 0) GROSS_MB,
ROUND((BLOCKS * (8000 – 23 * INI_TRANS) * (1 – PCT_FREE / 100) -
(AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) “WASTED_MB”
FROM DBA_TABLES
WHERE
NUM_ROWS IS NOT NULL AND
OWNER LIKE ‘%OWNER%’ AND
PARTITIONED = ‘NO’ AND
(IOT_TYPE != ‘IOT’ OR IOT_TYPE IS NULL)
ORDER BY 7 DESC)
WHERE ROWNUM <=20;
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid                     FORMAT 999            HEADING ‘SID’
COLUMN oracle_username         FORMAT a12            HEADING ‘Oracle User’     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING ‘O/S User’        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING ‘Session Program’ TRUNC
COLUMN session_machine         FORMAT a8             HEADING ‘Machine’         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING ‘PGA Memory’
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING ‘PGA Memory Max’
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING ‘UGA Memory’
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING ‘UGA Memory MAX’
SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = ‘session pga memory’)        session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = ‘session pga memory max’)    session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = ‘session uga memory’)        session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = ‘session uga memory max’)    session_uga_memory_max
FROM
    v$session  s
ORDER BY session_pga_memory DESC
/
or
SET LINESIZE       200
SET PAGESIZE       500
SET FEEDBACK       OFF
SET VERIFY         OFF
SET SERVEROUTPUT   ON
SET TRIMSPOOL      ON
COL “SESSION”      FORMAT A50
COL “PID/THREAD”   FORMAT A10
COL “      CURRENT SIZE” FORMAT A18
COL “      MAXIMUM SIZE” FORMAT A18
REM Setting user variables values
SET    TERMOUT OFF
DEFINE sort_order = 3
DEFINE show_pga   = ‘ON’
DEFINE show_uga   = ‘ON’
COL    sort_column NEW_VALUE sort_order
COL    pga_column  NEW_VALUE show_pga
COL    uga_column  NEW_VALUE show_uga
COL    snap_column NEW_VALUE snap_time
SELECT nvl(:sort_choice, 3) “SORT_COLUMN”
FROM   dual
/
SELECT nvl(:pga_choice, ‘ON’) “PGA_COLUMN”
FROM   dual
/
SELECT nvl(:uga_choice, ‘ON’) “UGA_COLUMN”
FROM   dual
/
SELECT to_char(sysdate, ‘YYYYMMDD_HH24MISS’) “SNAP_COLUMN”
FROM   dual
/
REM Creating new snapshot spool file
SPOOL MEMORY_&snap_time
REM Showing PGA statistics for each session and background process
SET      TERMOUT &show_pga
PROMPT
PROMPT   :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::
SELECT   to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
             || nvl(lower(ssn.machine), ins.host_name) “SESSION”,
         to_char(prc.spid, ’999999999′) “PID/THREAD”,
         to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “      CURRENT SIZE”,
         to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “      MAXIMUM SIZE”
FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE    se1.statistic# = 20
AND      se2.statistic# = 21
AND      se1.sid        = ssn.sid
AND      se2.sid        = ssn.sid
AND      ssn.paddr      = bgp.paddr (+)
AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC
/
REM Showing UGA statistics for each session and background process
SET      TERMOUT &show_uga
PROMPT
PROMPT   :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::
SELECT   to_char(ssn.sid, ’9999′) || ‘ – ‘ || nvl(ssn.username, nvl(bgp.name, ‘background’)) || ‘: ‘
             || nvl(lower(ssn.machine), ins.host_name) “SESSION”,
         to_char(prc.spid, ’999999999′) “PID/THREAD”,
         to_char((se1.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “      CURRENT SIZE”,
         to_char((se2.value/1024)/1024, ’999G999G990D00′) || ‘ MB’ “      MAXIMUM SIZE”
FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins
WHERE    se1.statistic# = 15
AND      se2.statistic# = 16
AND      se1.sid        = ssn.sid
AND      se2.sid        = ssn.sid
AND      ssn.paddr      = bgp.paddr (+)
AND      ssn.paddr      = prc.addr  (+)
ORDER BY &sort_order DESC
/
REM Showing sort information
SET TERMOUT ON
PROMPT
BEGIN
    IF (&sort_order = 1) THEN
        dbms_output.put_line(‘Ordered by SESSION’);
    ELSIF (&sort_order = 2) THEN
        dbms_output.put_line(‘Ordered by PID/THREAD’);
    ELSIF (&sort_order = 3) THEN
        dbms_output.put_line(‘Ordered by CURRENT SIZE’);
    ELSIF (&sort_order = 4) THEN
        dbms_output.put_line(‘Ordered by MAXIMUM SIZE’);
    END IF;
END;
/
REM Closing current snapshot spool file
SPOOL OFF
REM Showing the menu and getting sort order and information viewing choice
PROMPT
PROMPT Choose the column you want to sort:        == OR ==        You can choose which information to see:
PROMPT … 1. Order by SESSION                                    … 5. PGA and UGA statistics (default)
PROMPT … 2. Order by PID/THREAD                                 … 6. PGA statistics only
PROMPT … 3. Order by CURRENT SIZE (default)                     … 7. UGA statistics only
PROMPT … 4. Order by MAXIMUM SIZE
PROMPT
ACCEPT choice NUMBER PROMPT ‘Enter the number of your choice or press  to refresh information: ‘
VAR    sort_choice NUMBER
VAR    pga_choice  CHAR(3)
VAR    uga_choice  CHAR(3)
BEGIN
    IF (&choice = 1 OR &choice = 2 OR &choice = 3 OR &choice = 4) THEN
        :sort_choice := &choice;
        :pga_choice  := ‘&show_pga’;
        :uga_choice  := ‘&show_uga’;
    ELSIF (&choice = 5) THEN
        :sort_choice := &sort_order;
        :pga_choice  := ‘ON’;
        :uga_choice  := ‘ON’;
    ELSIF (&choice = 6) THEN
        :sort_choice := &sort_order;
        :pga_choice  := ‘ON’;
        :uga_choice  := ‘OFF’;
    ELSIF (&choice = 7) THEN
        :sort_choice := &sort_order;
        :pga_choice  := ‘OFF’;
        :uga_choice  := ‘ON’;
    ELSE
        :sort_choice := &sort_order;
        :pga_choice  := ‘&show_pga’;
        :uga_choice  := ‘&show_uga’;
    END IF;
END;
/

No comments:

Post a Comment