Top 10 - Biggest Tables and Indexes
set lines 170
set pages 20
col owner format a20
col object format a30
col type format a6
col tablespace format a20
col size_in_gb format 999,999.9
select *
from (select owner owner,
segment_name object,
segment_type type,
tablespace_name tablespace,
round(bytes/1024/1024/1024,1) size_in_gb
from dba_segments
where owner not in ('SYS','SYSTEM')
order by bytes desc
)
where rownum < 11
order by size_in_gb desc;
Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts
Tuesday, September 22, 2015
SQL Outlines or SQL Profiles
SQL OUTLINES or PROFILES
set lines 170
set pages 100
col created format a14
col type format a6
col status format a10
col fm format a3
col profile_name format a30
col sql_text format a50
col comp_data format a50
SELECT created,
type,
status,
force_matching fm,
profile_name,
sql_text,
comp_data
FROM DBA_SQL_PROFILES PROF,
DBMSHSXP_SQL_PROFILE_ATTR ATTR
WHERE prof.name = attr.profile_name
ORDER BY status, created desc;
set lines 170
set pages 100
col created format a14
col type format a6
col status format a10
col fm format a3
col profile_name format a30
col sql_text format a50
col comp_data format a50
SELECT created,
type,
status,
force_matching fm,
profile_name,
sql_text,
comp_data
FROM DBA_SQL_PROFILES PROF,
DBMSHSXP_SQL_PROFILE_ATTR ATTR
WHERE prof.name = attr.profile_name
ORDER BY status, created desc;
Parallel Query Processes - Parent / Child Details
Parallel Process Details including Master/Slave relationships
SQL
set lines 200
set pages 100
col username format a10
col qcslave format a10
col slaveset format a8
col program format a30
col sid format a5
col slvinst format a7
col state format a8
col waitevent format a30
col qcsid format a5
col qcinst format a6
col reqdop format 999
col actdop format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME,
DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
TO_CHAR( px.server_set) SLAVESET,
s.program PROGRAM,
TO_CHAR(s.SID) SID,
TO_CHAR(px.inst_id) SLVINST,
DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
CASE sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
TO_CHAR(px.qcinst_id) QCINST,
px.req_degree REQDOP,
px.DEGREE ACTDOP,
DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
FROM gv$px_session px,
gv$session s,
gv$px_process pp,
gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
SQL
set lines 200
set pages 100
col username format a10
col qcslave format a10
col slaveset format a8
col program format a30
col sid format a5
col slvinst format a7
col state format a8
col waitevent format a30
col qcsid format a5
col qcinst format a6
col reqdop format 999
col actdop format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME,
DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
TO_CHAR( px.server_set) SLAVESET,
s.program PROGRAM,
TO_CHAR(s.SID) SID,
TO_CHAR(px.inst_id) SLVINST,
DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
CASE sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
TO_CHAR(px.qcinst_id) QCINST,
px.req_degree REQDOP,
px.DEGREE ACTDOP,
DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
FROM gv$px_session px,
gv$session s,
gv$px_process pp,
gv$session_wait sw
WHERE px.SID=s.SID (+)
AND px.serial#=s.serial#(+)
AND px.inst_id = s.inst_id(+)
AND px.SID = pp.SID (+)
AND px.serial#=pp.serial#(+)
AND sw.SID = s.SID
AND sw.inst_id = s.inst_id
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
Friday, July 19, 2013
Friday, March 15, 2013
Session PGA Memory Usage
A helpful script to find sessions using large amounts of system memory:
SET LINESIZE 140
SET PAGESIZE 100
COL session HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB' FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
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",
se1.value/1024/1024 current_size,
se2.value/1024/1024 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 (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/
Sample output:
SID - User - Client PID/THREAD Current MB Max MB
----------------------------------- ---------- ----------- -----------
cut...
867 - XXXX - server01 9615 3.47 223.60
729 - XXXX - server01 9611 3.47 223.78
469 - XXXX - server01 3479 3.16 223.85
605 - XXXX - server03 4007 10.97 1,373.78
1039 - XXXX - server01 21216 11.16 1,373.97
448 - XXXX - server02 3852 10.22 1,378.91
-----------
Total 980.91
total used free shared buffers cached
Mem: 132085672 125739556 6346116 0 836680 24065240
-/+ buffers/cache: 100837636 31248036
Swap: 8193140 9968 8183172
Location:
Tempe, AZ, USA
Monday, February 18, 2013
Tablespace usage over time
If you have OEM, this repository query may be very helpful:
SELECT KEY_VALUE Tablespace_Name,
ROLLUP_TIMESTAMP Sample_date,
METRIC_COLUMN metric,
AVERAGE
FROM sysman.MGMT$METRIC_DAILY
WHERE metric_name='tbspAllocation'
and TARGET_NAME='<dbname>'
and KEY_VALUE = '<tablespacename>'
ORDER BY 1,2,3;
SELECT KEY_VALUE Tablespace_Name,
ROLLUP_TIMESTAMP Sample_date,
METRIC_COLUMN metric,
AVERAGE
FROM sysman.MGMT$METRIC_DAILY
WHERE metric_name='tbspAllocation'
and TARGET_NAME='<dbname>'
and KEY_VALUE = '<tablespacename>'
ORDER BY 1,2,3;
Subscribe to:
Posts (Atom)