The following columns are returned in the below query:
Owner schema.
Object name and type (TABLE, INDEX, etc.).
Name of the table this object is associated with. For E.g. indexes are associated with their parent tables.
Database space is occupied by the object in megabytes.
Tablespace where an object is stored.
The number of extents allocated for the object.
Size of the initial extent in bytes.
Total database size occupied by the parent table. E.g. for indexes, it will be the size of the parent * table plus the sizes of all the indexes on that table.
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS MB,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB
FROM (
SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
segment_name AS table_name, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
i.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
l.table_name, s.bytes,
s.tablespace_name, s.extents, s.initial_extent
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
)
WHERE owner in UPPER('&TABLE_NAME')
)
WHERE total_table_MB > 10
ORDER BY total_table_MB DESC, MB DESC
/
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Top 10 by Buffer Gets
=====================
set linesize 1000
set pagesize 1000
col sql for a70
set long 5000
col hash_value for 9999999999999999999999
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions “Gets/Exec”,
hash_value,address
FROM GV$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <=10
;
==================================================================================================
Top 10 by Physical Reads
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions “Reads/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <=10;
==================================================================================================
Top 10 by Executions
====================
set linesize 1000
set pagesize 1000
col sql for a70
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions “Rows/Exec”,
hash_value,address
FROM GV$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <=10;
==================================================================================================
Top 10 by Parse Calls:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <=10
;
=================================================================================================
Top 10 by Sharable Memory:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <=10
;
==================================================================================================
Top 10 by Version Count:
========================
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <=10
;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
BLOCKING SESSION
==================
set pages 1000 lines 1000
col owner for a20
col object_name for a34
col osuser for a15
col machine for a25
col object_type for a10
col inst_id for a5
select c.owner,c.object_name,c.object_type,b.sid,b.serial#, b.status, b.osuser, b.machine,b.inst_id
from gv$locked_object a ,gv$session b,dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;select s.blocking_session,s.sid,s.serial#,s.seconds_in_wait from gv$session s
where blocking_session is not null;
select SID,SESS_SERIAL#,BLOCKER_SID,BLOCKER_SESS_SERIAL#,BLOCKER_INSTANCE_ID from gV$SESSION_BLOCKERS;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
KILL THE BLOCKING SESSION
===========================set head off;
select ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate;’
from gv$session where sid=’&Blocking_session_SID’;
set heading on;
set head off;
select ‘alter system kill session ”’||sid||’,’||serial#||’,@’||inst_id||”’ immediate;’
from gv$session where sid=’&SID_TO_BE_KILLED’;
set heading on;
set head off;
select ‘alter system kill session ”’||sid||’,’||serial#||’,@’||inst_id||”’ immediate;’
from gv$session where status=’INACTIVE’ and last_call_et/60 > 60;
set heading on;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
set pages 1000 lines 1000
col owner for a18
col object_name for a30
col osuser for a15
col machine for a25
col object_type for a10
col status for a10
select c.owner,c.object_name,c.object_type,b.sid,b.serial#, b.status,b.osuser,b.machine
from gv$locked_object a ,gv$session b,dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
