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;
/
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 (+);
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;
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’;
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;
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;
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
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
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
/
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’))
);
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’))
);
(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;
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
– 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
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.
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;
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
/* 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)
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
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
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;
*/
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;
–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;
–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;
–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;
–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;
–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;
/
–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
/* 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)
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
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
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;
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;
–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
–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;
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);
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);
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);
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
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
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;
— 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
— 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;
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;
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************************************/
–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
— 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;
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;
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;
/**************************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;
/
–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;
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;
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;
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;
‘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;
(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
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’
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
/
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;
/
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