Database
Growth
This
script is designed to snapshoot the size of the database on a daily
basis.
CREATE TABLE dba1.dbgrowth
(
SDate DATE NOT NULL,
FullDBS NUMBER,
DBS NUMBER,
UsedSP NUMBER,
FreeSP NUMBER
)
TABLESPACE USERS;
-- add
primary key to sdate column
ALTER TABLE dba1.dbgrowth ADD(CONSTRAINT PK_DATABASE_GROWTH PRIMARY KEY (sdate));
--Fill
the database growth table by select statement
Add this entry to script.sql
INSERT INTO dba1.dbgrowth
SELECT SYSDATE "Log
Date",
(SUM (all_df.bytes) / 1024 / 1024 / 1024) "Full
Database Size",
(df.p / 1024 / 1024 / 1024) "Database
space",
(used.p / 1024 / 1024 / 1024) "Used
space",
(free.p / 1024 / 1024 / 1024) "Free
space"
FROM (SELECT bytes FROM v$datafile
UNION ALL
SELECT bytes FROM v$tempfile
UNION ALL
SELECT bytes FROM v$log) all_df,
(SELECT SUM (bytes) AS p FROM dba_data_files) df,
(SELECT SUM (bytes) AS p FROM dba_free_space) free,
(SELECT SUM (bytes) AS p FROM dba_segments) used
GROUP BY free.p, used.p, df.p;
Commit;
Add this entry to dbreads.sh
ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1; export
ORACLE_HOME
ORACLE_UNQNAME=imcpr; export ORACLE_UNQNAME
ORACLE_SID=imcpr2; export ORACLE_SID
$ORACLE_HOME/bin/sqlplus / as sysdba
@/u02/app/oracle/DBScripts/dbreads.sql
Schedule
this job as daily script on crontab
00 01 *
* * /u02/app/oracle/DBScripts/dbreads.sh -cron -silent start
Tablespace Growth
This script is designed to snapshoot the details of all
tablespaces.
create table dba1.tsreads as
Select trunc(SYSDATE) "Log
Date",ts.tablespace_name, ts.status, ts.contents,
size_info.megs_alloc "Size MB", size_info.megs_free "Free
MB", size_info.megs_used "Used
MB",
size_info.pct_free "Used%", size_info.pct_used "Free%"
From
(
select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used
from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
) size_info,
sys.dba_tablespaces ts
where ts.tablespace_name = size_info.tablespace_name
dbreads.sh
dbreads.sql
Scheduled once every day
Table Growth
This script is designed to snapshoot
the biggest tables from number of rows or size aspects.
create table dba1.tbreads as
Select trunc(sysdate) "Log
Date", segment_name,round(sum(bytes)/1024/1024/1024,4) "GB", b.num_rows
from sys.dba_segments a, DBA_TABLES
b
Where segment_type in ('TABLE', 'TABLE
PARTITION', 'TABLE SUBPARTITION')
and a.OWNER = 'PHOENIX'
and a.owner=b.owner
and a.segment_name=b.table_name
group by segment_name,b.num_rows
having round(sum(bytes)/1024/1024/1024,2)>2 or b.num_rows>2000000
order by 2 desc
dbreads.sh
dbreads.sql
Scheduled
once every day
Number & Active
Sessions Load
This script is designed to snapshoot
the load and number of session every hour.
Create table dba1.sessionstat as
SELECT SYSDATE "Log
Date",
COUNT (*) AS imcpr1,
(SELECT COUNT (*) AS imcpr2 FROM gv$session WHERE inst_id = 2) imcpr2,
round((select value from Gv$osstat where stat_name='LOAD' AND INST_ID=1),2) LOAD1,
round((select value from Gv$osstat where stat_name='LOAD' AND INST_ID=2),2) LOAD2
FROM gv$session
WHERE inst_id = 1
GROUP BY inst_id;
And then insert
the same statement
Sessionstat.sh
Sessionstat.sql
Schedule every
one hour
CPU Utilization
This script is designed to
snapshot the CPU usage every hour.
create table dba1.cpureads as
with AASSTAT as (
select
decode(n.wait_class,'User I/O','User I/O',
'Commit','Commit',
'Wait') CLASS,
sum(round(m.time_waited/m.INTSIZE_CSEC,3))
AAS,
BEGIN_TIME ,
END_TIME
from gv$waitclassmetric m,
gv$system_wait_class n
where m.wait_class_id=n.wait_class_id
and n.wait_class != 'Idle'
and n.inst_id=&&1 and m.inst_id=&&1
group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME
union
select 'CPU_ORA_CONSUMED' CLASS,
round(value/100,3)
AAS,
BEGIN_TIME ,
END_TIME
from gv$sysmetric
where metric_name='CPU Usage Per Sec'
and group_id=2
and inst_id=&&1
union
select 'CPU_OS'
CLASS ,
round((prcnt.busy*parameter.cpu_count)/100,3)
AAS,
BEGIN_TIME ,
END_TIME
from
( select value busy, BEGIN_TIME,END_TIME from gv$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 and inst_id=&&1 ) prcnt,
( select value cpu_count from gv$parameter where name='cpu_count' and inst_id=&&1) parameter
union
select
'CPU_ORA_DEMAND'
CLASS,
nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,
cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,
cast(max(SAMPLE_TIME) as date) END_TIME
from gv$active_session_history ash
where inst_id=&&1 and SAMPLE_TIME >= (select BEGIN_TIME from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 and inst_id=&&1 )
and SAMPLE_TIME < (select END_TIME from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 and inst_id=&&1 )
)
select trunc(sysdate) "LOG_DATE", &&1 INST_ID,
to_char(BEGIN_TIME,'HH:MI:SS') BEGIN_TIME,
to_char(END_TIME,'HH:MI:SS') END_TIME,
( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
CPU_ORA_CONSUMED +
decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
CPU_ORA_CONSUMED CPU_ORA,
decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
COMMIT,
READIO,
WAIT
from (
select trunc(sysdate) "LOG_DATE", &&1 INST_ID,
min(BEGIN_TIME) BEGIN_TIME,
max(END_TIME) END_TIME,
sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND,
sum(decode(CLASS,'CPU_OS'
,AAS,0)) CPU_OS,
sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT,
sum(decode(CLASS,'User I/O' ,AAS,0)) READIO,
sum(decode(CLASS,'Wait' ,AAS,0)) WAIT
from AASSTAT)
-- Reference; http://www.kylehailey.com/oracle-cpu-time/
/
Then add entry in crontab to run every hour
Cpureads.sql
