183 lines
9.0 KiB
SQL
183 lines
9.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Sort Segment usage
|
|
* Parameters : 1 - tablespace_name (Default Value - %, Use % as wildcard)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 03-Jul-12 Vishal Gupta Added Max columns in the output
|
|
* 02-Jul-12 Vishal Gupta Added tablespace name as input parameter
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE tablespace_name="&&1"
|
|
|
|
COLUMN _TABLESPACE_NAME NEW_VALUE TABLESPACE_NAME NOPRINT
|
|
|
|
set term off
|
|
SELECT UPPER(DECODE('&&TABLESPACE_NAME','','%','&&TABLESPACE_NAME')) "_TABLESPACE_NAME"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
DEFINE BYTES_LARGE_FORMAT="99,999,999"
|
|
DEFINE BYTES_FORMAT="99,999,999"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024"
|
|
--DEFINE BYTES_HEADING="GB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
|
|
DEFINE ROUND_PRECISION=0
|
|
DEFINE STAR=4 --Defines how much %usage is represented by one star on Graph
|
|
|
|
|
|
|
|
PROMPT ******************************************************************************
|
|
PROMPT * S O R T S E G M E N T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Tablespace Name = '&&TABLESPACE_NAME'
|
|
PROMPT ******************************************************************************
|
|
|
|
COLUMN seperator HEADING "!|!|!|!" FORMAT a1
|
|
COLUMN tablespace_name HEADING "TablespaceName" FORMAT a20
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN current_users HEADING "Current|Users|(#)" FORMAT 9999
|
|
COLUMN total_size HEADING "Total|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN used_size HEADING "Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN free_size HEADING "Free|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN percent_free HEADING "%Free" FORMAT 999.00
|
|
COLUMN percent_used HEADING "%Used" FORMAT 999.00
|
|
COLUMN max_total_size HEADING "Max|Total|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN max_used_size HEADING "Max|Used|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN max_sort_used_size HEADING "Max|Used|(BySort)|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN largest_sort_size HEADING "Largest|Sort|(&&BYTES_HEADING)" FORMAT &&BYTES_FORMAT
|
|
COLUMN max_percent_used HEADING "Max||%Used" FORMAT 999.00
|
|
COLUMN max_percent_free HEADING "Max||%Free" FORMAT 999.00
|
|
COLUMN free_requests HEADING "Free|Requests|(#)" FORMAT 999,999
|
|
COLUMN added HEADING "Space|Added|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
|
|
COLUMN freed HEADING "Space|Freed|(&&BYTES_HEADING)" FORMAT &&BYTES_LARGE_FORMAT
|
|
|
|
COLUMN UsageGraphWidth NOPRINT old_value UGWidth
|
|
COLUMN alloc HEADING "Alloc|(&&BYTES_HEADING)" FORMAT 9,999,999
|
|
COLUMN used HEADING "Used&&BYTES_HEADING)" FORMAT 999,999
|
|
COLUMN free HEADING "Free|(&&BYTES_HEADING)" FORMAT 999,999
|
|
COLUMN pct_Used HEADING "%Used" FORMAT 999
|
|
COLUMN pct_Free HEADING "%Free" FORMAT 999
|
|
COLUMN MAXSIZE HEADING "MaxSize|(&&BYTES_HEADING)" FORMAT 9,999,999
|
|
COLUMN Maxfree HEADING "MaxFree|(&&BYTES_HEADING)" FORMAT 9,999,999
|
|
COLUMN Max_pct_Used HEADING "MAX|%Used" FORMAT 999
|
|
COLUMN Max_pct_Free HEADING "MAX|%Free" FORMAT 999
|
|
COLUMN "Usage Graph" FORMAT a27
|
|
COLUMN "MaxUsage Graph" FORMAT a27
|
|
|
|
|
|
BREAK ON TABLESPACE_NAME SKIP 2 ON ALLOC ON FREE ON MAXFREE
|
|
COMPUTE SUM LABEL 'Total' OF total_size used_size free_size FORMAT &&BYTES_FORMAT ON TABLESPACE_NAME
|
|
|
|
WITH tbs AS
|
|
(
|
|
SELECT c.tablespace_name
|
|
, c.alloc
|
|
, c.used
|
|
, c.free
|
|
, c.pct_used
|
|
, c.pct_free
|
|
, c.MAXSIZE
|
|
, c.maxfree
|
|
, c.max_pct_used
|
|
, c.max_pct_free
|
|
FROM
|
|
( SELECT /*+ CHOOSE */
|
|
ts.tablespace_name
|
|
--, ROUND((ts.bytes_used + ts.bytes_free) / &&BYTES_DIVIDER , &&ROUND_PRECISION) alloc
|
|
, ROUND( tf.bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION) alloc
|
|
, ROUND(ts.bytes_used / &&BYTES_DIVIDER , &&ROUND_PRECISION) used
|
|
, ROUND(ts.bytes_free / &&BYTES_DIVIDER , &&ROUND_PRECISION) free
|
|
, ROUND(( ts.bytes_used / (ts.bytes_used + ts.bytes_free))
|
|
* 100, &&ROUND_PRECISION) pct_Used
|
|
, 100 - ROUND(( ts.bytes_used / (ts.bytes_used + ts.bytes_free)) * 100, &&ROUND_PRECISION) pct_Free
|
|
, ROUND( tf.max_bytes / &&BYTES_DIVIDER , &&ROUND_PRECISION) MaxSize
|
|
, ROUND( ( ts.bytes_free + (tf.max_bytes - (ts.bytes_used + ts.bytes_free) )
|
|
) / &&BYTES_DIVIDER , &&ROUND_PRECISION) Maxfree
|
|
, ROUND( ( ts.bytes_used /tf.max_bytes) * 100, &&ROUND_PRECISION) Max_pct_Used
|
|
, 100 - ROUND( ( ts.bytes_used /tf.max_bytes )* 100, &&ROUND_PRECISION ) Max_pct_Free
|
|
FROM (SELECT tablespace_name
|
|
, sum(bytes_used) bytes_used
|
|
, sum(bytes_free) bytes_free
|
|
FROM v$temp_space_header
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
) ts
|
|
, (SELECT tablespace_name
|
|
, SUM(bytes) bytes
|
|
, SUM(DECODE(autoextensible, 'YES',GREATEST(maxbytes,bytes),'NO', bytes)) max_bytes
|
|
FROM dba_temp_files
|
|
WHERE tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY tablespace_name
|
|
)tf
|
|
, (SELECT ss.tablespace_name
|
|
, sum(ss.free_blocks * t.block_size) free_bytes
|
|
FROM gv$sort_segment ss
|
|
, dba_tablespaces t
|
|
WHERE ss.tablespace_name = t.tablespace_name
|
|
AND ss.tablespace_name LIKE '&&tablespace_name'
|
|
GROUP BY ss.tablespace_name
|
|
) ss
|
|
, dba_tablespaces t
|
|
WHERE t.tablespace_name = ts.tablespace_name
|
|
AND t.tablespace_name = tf.tablespace_name
|
|
AND t.tablespace_name = ss.tablespace_name (+)
|
|
AND t.tablespace_name LIKE '&&tablespace_name'
|
|
) c
|
|
)
|
|
SELECT s.tablespace_name
|
|
, t.alloc
|
|
--, t.used
|
|
, t.free
|
|
--, t.MAXSIZE
|
|
, t.maxfree
|
|
, '|' seperator
|
|
, s.inst_id
|
|
, s.current_users current_users
|
|
, '|' seperator
|
|
, (s.total_blocks * p.value)/&&BYTES_DIVIDER total_size
|
|
, (s.free_blocks * p.value)/&&BYTES_DIVIDER free_size
|
|
, (s.used_blocks * p.value)/&&BYTES_DIVIDER used_size
|
|
, ROUND( (s.used_blocks/s.total_blocks) * 100 , 2) percent_used
|
|
--, ROUND( (s.free_blocks/s.total_blocks) * 100 , 2) percent_free
|
|
, '|' seperator
|
|
, (s.max_blocks * p.value)/&&BYTES_DIVIDER max_used_size
|
|
, (s.max_used_blocks * p.value)/&&BYTES_DIVIDER max_sort_used_size
|
|
, (s.max_sort_blocks * p.value)/&&BYTES_DIVIDER largest_sort_size
|
|
, '|' seperator
|
|
, s.free_requests
|
|
, (s.freed_extents * extent_size * p.value)/&&BYTES_DIVIDER freed
|
|
, (s.added_extents * extent_size * p.value)/&&BYTES_DIVIDER added
|
|
, '|' seperator
|
|
FROM gv$sort_segment s
|
|
, v$parameter p
|
|
, tbs t
|
|
WHERE p.name = 'db_block_size'
|
|
AND s.tablespace_name = t.tablespace_name
|
|
AND s.total_blocks <> 0
|
|
AND s.tablespace_name like '&&tablespace_name'
|
|
ORDER BY s.tablespace_name
|
|
, s.inst_id
|
|
/
|
|
|
|
UNDEFINE tablespace_name
|
|
|
|
@@footer |