--Run time Session logon and all logon since last reboot.
-------------------------------------------------------------
set lin 180
select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark= '||
b.sessions_highwater ||' avg logon per_day= ' ||round(to_number(rpad(substr(a.value,1,10),10))/(sysdate-d.STARTUP_TIME))
||' Startup_date= '||d.STARTUP_TIME Information
from
v$sysstat a,
v$license b,
v$database c,
v$instance d
where
a.name = 'logons cumulative'
/
set lin 180
---Another running Sql
set lin 10000
col sid for 999999999
col serial# for 9999999999
col osuer for a20
col SQL_TEXT for a70 wrap
col osuser for a10
col status for a10
--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sqlarea b
--where a.SQL_ADDRESS=b.ADDRESS
--and b.HASH_VALUE = a.SQL_HASH_VALUE
select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sql b
where a.SQL_ADDRESS=b.ADDRESS
and b.HASH_VALUE = a.SQL_HASH_VALUE
order by 6 desc,5 desc
/
COLUMN name FORMAT A20;
col value for 999999999999
set lin 10000
--CLEAR SCREEN;
PROMPT DATABASE BUFFER CACHE STATISTICS:
SELECT
SUM(DECODE(name, 'physical reads',value,0)) AS misses,
SUM(DECODE(name, 'db block gets',value,0)) +
SUM(DECODE(name, 'consistent gets',value,0)) AS hits,
ROUND(100*(1-SUM(DECODE(name, 'physical reads',value,0))/
(SUM(DECODE(name, 'db block gets',value,0)) +
SUM(DECODE(name, 'consistent gets',value,0))) ),2) AS "HIT RATIO"
FROM v$sysstat
WHERE name IN
('db block gets','consistent gets','physical reads');
PROMPT DICTIONARY CACHE STATISTICS:
SELECT SUM(getmisses) AS misses,
SUM(gets) AS "HITS (EXECUTIONS)",
ROUND(100*(1-(SUM(getmisses)/SUM(gets))),2) AS hit_ratio
FROM v$rowcache;
PROMPT LIBRARY CACHE STATISTICS:
SELECT SUM(reloads) AS misses,
SUM(pins) AS "HITS (EXECUTIONS)",
ROUND(100*(1-(SUM(reloads)/SUM(pins))),2) AS hit_ratio
FROM v$librarycache;
PROMPT SORTING STATISTICS:
col VALUE for 9999999999999999
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
set lin 1000
col "Command Type" for a20
select CTYP "Command Type", OBJ "Name", 0 - EXEM "Number of Executions" , GETS "Buffer Gets" ,
ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP
from ( select decode (S.COMMAND_TYPE , 2, 'Insert into ' , 3,'Select from ', 6, 'Update of ' , 7, 'Delete from ' ,
26,'Lock of ') CTYP , O.OWNER || '.' || O.NAME OBJ , sum(0 - S.EXECUTIONS)
EXEM , sum(S.BUFFER_GETS) GETS , sum(S.ROWS_PROCESSED) ROWP from V$SQL S ,
V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE O
where S.COMMAND_TYPE in (2,3,6,7,26)
and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER
and D.TO_NAME= O.NAME and O.TYPE = 'TABLE'
--and O.OWNER NOT IN ('SYS','SYSTEM')
---and O.OWNER = upper('&user_name')
group by S.COMMAND_TYPE , O.OWNER , O.NAME ) )
where ROWNUM <= 25
;
set lin 80
--- Sql cost with sql_id
set lin 1000
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200 wrap
select p.sql_id c1,
p.COST c2,
p.cpu_cost ,
p.time,
to_char(s.sql_text) c3
from
dba_hist_sql_plan p,
dba_hist_sqltext s
where p.id=0
and p.sql_id = s.sql_id
and p.cost is not null
and p.sql_id = trim('&sql_id')
order by p.cost desc
/
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200
set lin 10000
select sql_id , round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),5) avg_etime,
child_number, executions execs, CPU_TIME,ELAPSED_TIME, to_char(s.sql_text),
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text, LAST_ACTIVE_TIME,SQL_PROFILE,PARSING_SCHEMA_NAME ,ROWS_PROCESSED , LAST_LOAD_TIME
, disk_reads
from v$sql s
where sql_id = trim('&sql_id');
set lin 100
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a18 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
/
PRO Object list
break on report
compute sum of NO_OF_SEGS on report
compute sum of MB on report
col TEMP_TBS for a7
col DEFAULT_TABLESPACE for a10
col profile for a15
select profile, USERNAME, count(*)NO_OF_SEGS ,sum(b.bytes)/1024/1024 MB, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE TEMP_TBS ,CREATED
,ACCOUNT_STATUS from dba_users a, dba_segments b
where a.username=b.owner(+)
group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,ACCOUNT_STATUS,profile
order by created,NO_OF_SEGS desc,mb desc
/
set line 80
-------------------------------------------------------------
set lin 180
select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark= '||
b.sessions_highwater ||' avg logon per_day= ' ||round(to_number(rpad(substr(a.value,1,10),10))/(sysdate-d.STARTUP_TIME))
||' Startup_date= '||d.STARTUP_TIME Information
from
v$sysstat a,
v$license b,
v$database c,
v$instance d
where
a.name = 'logons cumulative'
/
set lin 180
---Another running Sql
set lin 10000
col sid for 999999999
col serial# for 9999999999
col osuer for a20
col SQL_TEXT for a70 wrap
col osuser for a10
col status for a10
--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sqlarea b
--where a.SQL_ADDRESS=b.ADDRESS
--and b.HASH_VALUE = a.SQL_HASH_VALUE
select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a, v$sql b
where a.SQL_ADDRESS=b.ADDRESS
and b.HASH_VALUE = a.SQL_HASH_VALUE
order by 6 desc,5 desc
/
COLUMN name FORMAT A20;
col value for 999999999999
set lin 10000
--CLEAR SCREEN;
PROMPT DATABASE BUFFER CACHE STATISTICS:
SELECT
SUM(DECODE(name, 'physical reads',value,0)) AS misses,
SUM(DECODE(name, 'db block gets',value,0)) +
SUM(DECODE(name, 'consistent gets',value,0)) AS hits,
ROUND(100*(1-SUM(DECODE(name, 'physical reads',value,0))/
(SUM(DECODE(name, 'db block gets',value,0)) +
SUM(DECODE(name, 'consistent gets',value,0))) ),2) AS "HIT RATIO"
FROM v$sysstat
WHERE name IN
('db block gets','consistent gets','physical reads');
PROMPT DICTIONARY CACHE STATISTICS:
SELECT SUM(getmisses) AS misses,
SUM(gets) AS "HITS (EXECUTIONS)",
ROUND(100*(1-(SUM(getmisses)/SUM(gets))),2) AS hit_ratio
FROM v$rowcache;
PROMPT LIBRARY CACHE STATISTICS:
SELECT SUM(reloads) AS misses,
SUM(pins) AS "HITS (EXECUTIONS)",
ROUND(100*(1-(SUM(reloads)/SUM(pins))),2) AS hit_ratio
FROM v$librarycache;
PROMPT SORTING STATISTICS:
col VALUE for 9999999999999999
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
set lin 1000
col "Command Type" for a20
select CTYP "Command Type", OBJ "Name", 0 - EXEM "Number of Executions" , GETS "Buffer Gets" ,
ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP
from ( select decode (S.COMMAND_TYPE , 2, 'Insert into ' , 3,'Select from ', 6, 'Update of ' , 7, 'Delete from ' ,
26,'Lock of ') CTYP , O.OWNER || '.' || O.NAME OBJ , sum(0 - S.EXECUTIONS)
EXEM , sum(S.BUFFER_GETS) GETS , sum(S.ROWS_PROCESSED) ROWP from V$SQL S ,
V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE O
where S.COMMAND_TYPE in (2,3,6,7,26)
and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER
and D.TO_NAME= O.NAME and O.TYPE = 'TABLE'
--and O.OWNER NOT IN ('SYS','SYSTEM')
---and O.OWNER = upper('&user_name')
group by S.COMMAND_TYPE , O.OWNER , O.NAME ) )
where ROWNUM <= 25
;
set lin 80
--- Sql cost with sql_id
set lin 1000
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200 wrap
select p.sql_id c1,
p.COST c2,
p.cpu_cost ,
p.time,
to_char(s.sql_text) c3
from
dba_hist_sql_plan p,
dba_hist_sqltext s
where p.id=0
and p.sql_id = s.sql_id
and p.cost is not null
and p.sql_id = trim('&sql_id')
order by p.cost desc
/
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200
set lin 10000
select sql_id , round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),5) avg_etime,
child_number, executions execs, CPU_TIME,ELAPSED_TIME, to_char(s.sql_text),
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text, LAST_ACTIVE_TIME,SQL_PROFILE,PARSING_SCHEMA_NAME ,ROWS_PROCESSED , LAST_LOAD_TIME
, disk_reads
from v$sql s
where sql_id = trim('&sql_id');
set lin 100
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a18 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
/
PRO Object list
break on report
compute sum of NO_OF_SEGS on report
compute sum of MB on report
col TEMP_TBS for a7
col DEFAULT_TABLESPACE for a10
col profile for a15
select profile, USERNAME, count(*)NO_OF_SEGS ,sum(b.bytes)/1024/1024 MB, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE TEMP_TBS ,CREATED
,ACCOUNT_STATUS from dba_users a, dba_segments b
where a.username=b.owner(+)
group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,ACCOUNT_STATUS,profile
order by created,NO_OF_SEGS desc,mb desc
/
set line 80
No comments:
Post a Comment