------------------------AWR/ADDm----------------------------------------------------------------
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
------------------------------------------------------------------------------------------------
------------------------Memory------------------------------------------------------------------
---------------------SGA Information------------------------------------------------------------
select name,ROUND(BYTES/(1024*1024)) "size MB"from V$SGAINFO ;
-------------------------------------------------------------------------------------------------
-------------PGA Information------------------------------------------------------------------
select name,ROUND(value/(1024*1024)) as "size MB" from V$PGASTAT where UNIT is not null;
select name,value from V$PGASTAT where UNIT is null;
------------------------------------------------------------------------------------------------
--------------Top SQL--------------------------------------------------------------------------
---------------Order by CPU---------------------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by CPU_TIME desc)
where rownum < 6;
-----------------Order by Elapsed Time----------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by ELAPSED_TIME desc)
where rownum < 6;
------------------Order by User I/O---------------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by USER_IO_WAIT_TIME desc)
where rownum < 6;
----------------------CPU for any Oracle user session------------------------------------
select name,
ss.username,
se.SID,SS.SERIAL#,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
--and
--NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
--------------------------------------------------------------------------------------------------
-----------------------Memory (pga and uga) for any Oracle user session----------------------------------
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,8) session_machine
,to_char(( sstat1.value/1024)/1024, '999G999G990D00') session_pga_memory_mb
,to_char(( sstat2.value/1024)/1024, '999G999G990D00') session_pga_memory_max_md
,to_char(( sstat3.value/1024)/1024, '999G999G990D00') session_uga_memory_md
,to_char(( sstat4.value/1024)/1024, '999G999G990D00') session_uga_memory_max_md
FROM
v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat1.sid
AND s.sid = sstat2.sid
AND s.sid = sstat3.sid
AND s.sid = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name = 'session pga memory'
AND statname2.name = 'session pga memory max'
AND statname3.name = 'session uga memory'
AND statname4.name = 'session uga memory max'
ORDER BY session_pga_memory_mb DESC
/
select
round(data_size,2) "Reserved space(GB)", round (data_size - Free_Space,2) "Used_Data_Space(GB)",round(Free_Space,2) "Free space (GB)"
from(
select
(select sum(bytes)/(1024*1024*1024) from dba_data_files) data_size,
(select nvl(sum(bytes),0)/(1024*1024*1024) temp_size from dba_temp_files ) temp_size,
(select sum(bytes)/(1024*1024*1024) redo_size from sys.v_$log ) redo_size,
(select sum(bytes)/(1024*1024*1024) from dba_free_space) Free_Space
from dual
);
--------------------------------------------------------------------------------------------------
---------------------------Table Size-------------------------------------------------------------
select OWNER, TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN)/(1024*1024)) MB
from DBA_TABLES
where owner='IFSAPP' and temporary='N'
and NUM_ROWS > 0
ORDER BY MB DESC;
---------------------------------------------------------------------------------------------------
------------------------------Tablespace Monitoring---------------------------------------------------------------------------
select DF.TABLESPACE_NAME "Tablespace Name"
,DF.Sizes "Size GB"
,FS.FREE "Free Size GB"
,case when DF.SIZES=0
then 0
else
round(((DF.SIZES-FS.FREE)/DF.SIZES)* 100)
end as "Used percentage"
from (
select TABLESPACE_NAME
,round(SUM(BYTES/(1024*1024*1024)),2) sizes
from DBA_DATA_FILES
group by TABLESPACE_NAME) DF
inner join(
select TABLESPACE_NAME
,round(SUM(BYTES/(1024*1024*1024)),2) Free
from DBA_FREE_SPACE
group by TABLESPACE_NAME) FS on DF.TABLESPACE_NAME=FS.TABLESPACE_NAME
order by 1;
-----------------------------------------------------------------------------------------------------------------------------------
------------------------Wait Objects------------------------------------------------------------------
select * from (select S.sid,S.BLOCKING_SESSION,S.USERNAME,S.ROW_WAIT_OBJ#,O.OBJECT_NAME "Blocking Object"
,o.object_type,
decode(s.state, 'WAITING','Waiting',
'Working') STATE,
decode(s.state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from V$SESSION S
inner join DBA_OBJECTS O on S.ROW_WAIT_OBJ#=O.OBJECT_ID
order by seconds_in_wait desc) where rownum < 11;
---------------------------------------------------------------------------------------------------------
--------------------Backup Monitoring------------------------------------------------------------------------
SELECT
to_char(start_time,'DD-MM-YYYY HH:MM:SS AM') start_t,
to_char(end_time,'DD-MM-YYYY HH:MM:SS AM') end_t,
INPUT_TYPE,
round(ELAPSED_SECONDS/3600 ,5) hrs,
OPTIMIZED, INPUT_BYTES_DISPLAY in_size, OUTPUT_BYTES_DISPLAY out_size,
STATUS
from V$RMAN_BACKUP_JOB_DETAILS
where TO_CHAR(START_TIME,'DD-MM-YYYY') = TO_CHAR(sysdate,'DD-MM-YYYY')
ORDER BY start_time desc;
--------------------------------------------------------------------------------------------------------------
----------------copy file from UNIX to windows machine --------------
use pscp.exe
cmd>pscp.exe -pw MyPassword root@127.0.0.1:/etc/myfolder/myfile.opt "C:\download files"
cmd>pscp.exe -pw pwd25@prod idalugama@10.209.10.41:/home/oracle/awrrpt_1_2024_2217.html "C:\"
C:\>pscp.exe -pw pwd25@prod idalugama@10.209.10.41:/u01/app/product/11.2.0.3/dbhome_1/bin/awrrpt_1_2024_2217.html C:\
http://serverfault.com/questions/295565/pscp-upload-an-entire-folder-windows-to-linux
------------ check log switch frequency-- it should be every 20min ----------------
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD');
----------------- who is generating more undo ------------------------------
select v$session.sid, username, value/1024/1024 redo_size_MB
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = 'redo size'
and value > 0
and username is not null
order by value desc;
------------ export/import job monitoring
select
round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target, sid;
--------- recompile all schema objects
EXEC DBMS_UTILITY.compile_schema(schema => 'IFSAPP');
---audit session
select * from v$parameter where name like '%audit%';
select f.identity,max(t.timestamp)
from sys.DBA_AUDIT_SESSION t,ifsapp.fnd_user_tab f
where t.username = f.identity
and f.active = 'TRUE'
group by f.identity
having max(t.timestamp) < (sysdate - 40);
--Users never logged to system
SELECT identity,description,oracle_user,web_user,active
FROM ifsapp.FND_USER_TAB f
LEFT OUTER JOIN SYS.DBA_AUDIT_SESSION d
ON (f.identity = d.username)
WHERE d.username IS NULL
and f.active = 'TRUE';
select * from sys.DBA_AUDIT_SESSION;
select * from DBA_AUDIT_TRAIL;
-- select * from ifsapp.fnd_user_tab;
--role previlages -------------------
-- check role and privilages for users
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
--Select a.grantee User_name, a.granted_role role, b.privilege from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b where
--a.granted_role=b.grantee and a.grantee not in ( 'SYS','SYSTEM','DATAPUMP_EXP_FULL_DATABASE','DBA','DATAPUMP_IMP_FULL_DATABASE') order by a.grantee;
---- db transaction load ------------
select name, value from v$sysstat where name like '%user%commit%';
select name,value from v$sysstat where name in ( 'user commits','user rollbacks','user calls');
-- per day
select value/up_days as tx_per_day
from (
select sum(value) as value from v$sysstat where name in ('user commits','user rollbacks')
),
(select (sysdate-startup_time) as up_days from v$instance);
--per second
-- per day
select value/up_days as tx_per_day
from (
select sum(value) as value from v$sysstat where name in ('user commits','user rollbacks')
),
(select (sysdate-startup_time)*86400 as up_days from v$instance);
select
-- round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target, sid;
--- sga tuning ---------
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM v$pgastat
WHERE name = 'maximum PGA allocated';
-- Calculate MEMORY_TARGET
SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga, -- this
(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;
select * from V$SGA_DYNAMIC_COMPONENTS;
select * from v$parameter where name like '%memory%';
---Tracking Oracle database growth
select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;
--- find a server process id for current user
select p.spid from v$process p , v$session s where p.addr=s.paddr and s.audsid = userenv('sessionid');
---tablespace growth and predict script
set serveroutput on;
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';
BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
---------------------
you can get an approximate count of the number of rows that have changed in each table since statistics were last gathered
SELECT table_owner,
table_name,
inserts,
updates,
deletes
FROM sys.dba_tab_modifications
-------------------how to see how much cpu your oracle instance consumes
col metric_name for a25
col metric_unit for a25
select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
----------------- memory utilization oracle instance
------------------------ Running Jobs:------------------
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS;
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
------------------------------------------------------------------------------------------------
------------------------Memory------------------------------------------------------------------
---------------------SGA Information------------------------------------------------------------
select name,ROUND(BYTES/(1024*1024)) "size MB"from V$SGAINFO ;
-------------------------------------------------------------------------------------------------
-------------PGA Information------------------------------------------------------------------
select name,ROUND(value/(1024*1024)) as "size MB" from V$PGASTAT where UNIT is not null;
select name,value from V$PGASTAT where UNIT is null;
------------------------------------------------------------------------------------------------
--------------Top SQL--------------------------------------------------------------------------
---------------Order by CPU---------------------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by CPU_TIME desc)
where rownum < 6;
-----------------Order by Elapsed Time----------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by ELAPSED_TIME desc)
where rownum < 6;
------------------Order by User I/O---------------------------------------------------------------
SELECT * FROM
(SELECT
sql_fulltext,
sql_id
from V$SQL
order by USER_IO_WAIT_TIME desc)
where rownum < 6;
----------------------CPU for any Oracle user session------------------------------------
select name,
ss.username,
se.SID,SS.SERIAL#,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
--and
--NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;
--------------------------------------------------------------------------------------------------
-----------------------Memory (pga and uga) for any Oracle user session----------------------------------
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,8) session_machine
,to_char(( sstat1.value/1024)/1024, '999G999G990D00') session_pga_memory_mb
,to_char(( sstat2.value/1024)/1024, '999G999G990D00') session_pga_memory_max_md
,to_char(( sstat3.value/1024)/1024, '999G999G990D00') session_uga_memory_md
,to_char(( sstat4.value/1024)/1024, '999G999G990D00') session_uga_memory_max_md
FROM
v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat1.sid
AND s.sid = sstat2.sid
AND s.sid = sstat3.sid
AND s.sid = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name = 'session pga memory'
AND statname2.name = 'session pga memory max'
AND statname3.name = 'session uga memory'
AND statname4.name = 'session uga memory max'
ORDER BY session_pga_memory_mb DESC
/
--------- session by programs
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where p.addr=s.paddr
group by s.program;
Got from below site
http://allappsdba.blogspot.com/2012/04/sql-queries-to-check-active-inactive.html
---------------------------DB Size-------------------------------------------------------------select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where p.addr=s.paddr
group by s.program;
Got from below site
http://allappsdba.blogspot.com/2012/04/sql-queries-to-check-active-inactive.html
select
round(data_size,2) "Reserved space(GB)", round (data_size - Free_Space,2) "Used_Data_Space(GB)",round(Free_Space,2) "Free space (GB)"
from(
select
(select sum(bytes)/(1024*1024*1024) from dba_data_files) data_size,
(select nvl(sum(bytes),0)/(1024*1024*1024) temp_size from dba_temp_files ) temp_size,
(select sum(bytes)/(1024*1024*1024) redo_size from sys.v_$log ) redo_size,
(select sum(bytes)/(1024*1024*1024) from dba_free_space) Free_Space
from dual
);
--------------------------------------------------------------------------------------------------
---------------------------Table Size-------------------------------------------------------------
select OWNER, TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN)/(1024*1024)) MB
from DBA_TABLES
where owner='IFSAPP' and temporary='N'
and NUM_ROWS > 0
ORDER BY MB DESC;
---------------------------------------------------------------------------------------------------
------------------------------Tablespace Monitoring---------------------------------------------------------------------------
select DF.TABLESPACE_NAME "Tablespace Name"
,DF.Sizes "Size GB"
,FS.FREE "Free Size GB"
,case when DF.SIZES=0
then 0
else
round(((DF.SIZES-FS.FREE)/DF.SIZES)* 100)
end as "Used percentage"
from (
select TABLESPACE_NAME
,round(SUM(BYTES/(1024*1024*1024)),2) sizes
from DBA_DATA_FILES
group by TABLESPACE_NAME) DF
inner join(
select TABLESPACE_NAME
,round(SUM(BYTES/(1024*1024*1024)),2) Free
from DBA_FREE_SPACE
group by TABLESPACE_NAME) FS on DF.TABLESPACE_NAME=FS.TABLESPACE_NAME
order by 1;
-----------------------------------------------------------------------------------------------------------------------------------
------------------------Wait Objects------------------------------------------------------------------
select * from (select S.sid,S.BLOCKING_SESSION,S.USERNAME,S.ROW_WAIT_OBJ#,O.OBJECT_NAME "Blocking Object"
,o.object_type,
decode(s.state, 'WAITING','Waiting',
'Working') STATE,
decode(s.state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from V$SESSION S
inner join DBA_OBJECTS O on S.ROW_WAIT_OBJ#=O.OBJECT_ID
order by seconds_in_wait desc) where rownum < 11;
---------------------------------------------------------------------------------------------------------
--------------------Backup Monitoring------------------------------------------------------------------------
SELECT
to_char(start_time,'DD-MM-YYYY HH:MM:SS AM') start_t,
to_char(end_time,'DD-MM-YYYY HH:MM:SS AM') end_t,
INPUT_TYPE,
round(ELAPSED_SECONDS/3600 ,5) hrs,
OPTIMIZED, INPUT_BYTES_DISPLAY in_size, OUTPUT_BYTES_DISPLAY out_size,
STATUS
from V$RMAN_BACKUP_JOB_DETAILS
where TO_CHAR(START_TIME,'DD-MM-YYYY') = TO_CHAR(sysdate,'DD-MM-YYYY')
ORDER BY start_time desc;
--------------------------------------------------------------------------------------------------------------
----------------copy file from UNIX to windows machine --------------
use pscp.exe
cmd>pscp.exe -pw MyPassword root@127.0.0.1:/etc/myfolder/myfile.opt "C:\download files"
cmd>pscp.exe -pw pwd25@prod idalugama@10.209.10.41:/home/oracle/awrrpt_1_2024_2217.html "C:\"
C:\>pscp.exe -pw pwd25@prod idalugama@10.209.10.41:/u01/app/product/11.2.0.3/dbhome_1/bin/awrrpt_1_2024_2217.html C:\
http://serverfault.com/questions/295565/pscp-upload-an-entire-folder-windows-to-linux
------------ check log switch frequency-- it should be every 20min ----------------
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD');
----------------- who is generating more undo ------------------------------
select v$session.sid, username, value/1024/1024 redo_size_MB
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = 'redo size'
and value > 0
and username is not null
order by value desc;
------------ export/import job monitoring
select
round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target, sid;
--------- recompile all schema objects
EXEC DBMS_UTILITY.compile_schema(schema => 'IFSAPP');
---audit session
select * from v$parameter where name like '%audit%';
select f.identity,max(t.timestamp)
from sys.DBA_AUDIT_SESSION t,ifsapp.fnd_user_tab f
where t.username = f.identity
and f.active = 'TRUE'
group by f.identity
having max(t.timestamp) < (sysdate - 40);
--Users never logged to system
SELECT identity,description,oracle_user,web_user,active
FROM ifsapp.FND_USER_TAB f
LEFT OUTER JOIN SYS.DBA_AUDIT_SESSION d
ON (f.identity = d.username)
WHERE d.username IS NULL
and f.active = 'TRUE';
select * from sys.DBA_AUDIT_SESSION;
select * from DBA_AUDIT_TRAIL;
-- select * from ifsapp.fnd_user_tab;
--role previlages -------------------
-- check role and privilages for users
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
--Select a.grantee User_name, a.granted_role role, b.privilege from DBA_ROLE_PRIVS a, DBA_SYS_PRIVS b where
--a.granted_role=b.grantee and a.grantee not in ( 'SYS','SYSTEM','DATAPUMP_EXP_FULL_DATABASE','DBA','DATAPUMP_IMP_FULL_DATABASE') order by a.grantee;
---- db transaction load ------------
select name, value from v$sysstat where name like '%user%commit%';
select name,value from v$sysstat where name in ( 'user commits','user rollbacks','user calls');
-- per day
select value/up_days as tx_per_day
from (
select sum(value) as value from v$sysstat where name in ('user commits','user rollbacks')
),
(select (sysdate-startup_time) as up_days from v$instance);
--per second
-- per day
select value/up_days as tx_per_day
from (
select sum(value) as value from v$sysstat where name in ('user commits','user rollbacks')
),
(select (sysdate-startup_time)*86400 as up_days from v$instance);
select
-- round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target, sid;
--- sga tuning ---------
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM v$pgastat
WHERE name = 'maximum PGA allocated';
-- Calculate MEMORY_TARGET
SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga, -- this
(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;
select * from V$SGA_DYNAMIC_COMPONENTS;
select * from v$parameter where name like '%memory%';
---Tracking Oracle database growth
select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;
--- find a server process id for current user
select p.spid from v$process p , v$session s where p.addr=s.paddr and s.audsid = userenv('sessionid');
---tablespace growth and predict script
set serveroutput on;
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';
BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
---------------------
you can get an approximate count of the number of rows that have changed in each table since statistics were last gathered
SELECT table_owner,
table_name,
inserts,
updates,
deletes
FROM sys.dba_tab_modifications
-------------------how to see how much cpu your oracle instance consumes
col metric_name for a25
col metric_unit for a25
select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
----------------- memory utilization oracle instance
select
decode( grouping(nm),
1, 'total', nm ) nm, round(sum(val/1024/1024))
mbfrom ( select 'sga' nm, sum(value)
val from v$sga union all select 'pga', sum(a.value)from v$sesstat a, v$statname
b where b.name = 'session
pga memory' and a.statistic#
= b.statistic#
)group by rollup(nm);------------------------ Running Jobs:------------------
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS;
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';
Good one Indika.. :) Really Useful for none EM users.
ReplyDeleteThanks Indika aiya.. It's more valuable for our day to day ofz work.
ReplyDelete