64 lines
2.2 KiB
SQL
64 lines
2.2 KiB
SQL
set echo off
|
|
set feed off
|
|
set verify off
|
|
-- No header and footer needed, as its just a wrapper script giving call to other scripts
|
|
-- @@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display top session by various statistics
|
|
* Version : 10.1 and above
|
|
* Parameters : 1 - Instance Number (Use % as wildcard)
|
|
* 2 - Number of top record counts (Default 20)
|
|
* 3 - Statname (Use % as wildcard, double quotes for spaces.
|
|
* 4 - Where clause
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 03-Aug-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
|
|
UNDEFINE inst_id2
|
|
UNDEFINE toprecordcount2
|
|
UNDEFINE statname2
|
|
UNDEFINE whereclause2
|
|
|
|
DEFINE inst_id2="&&1"
|
|
DEFINE toprecordcount2="&&2"
|
|
DEFINE statname2="&&3"
|
|
DEFINE whereclause2="&&4"
|
|
|
|
|
|
COLUMN _inst_id NEW_VALUE inst_id2 NOPRINT
|
|
COLUMN _toprecordcount NEW_VALUE toprecordcount2 NOPRINT
|
|
COLUMN _statname NEW_VALUE statname2 NOPRINT
|
|
COLUMN _whereclause NEW_VALUE whereclause2 NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&inst_id2','','%','&&inst_id2') "_inst_id"
|
|
, DECODE('&&toprecordcount2','','20','&&toprecordcount2') "_toprecordcount"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "redo size" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "CPU used by this session" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "physical read total bytes" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "%physical%writes" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "session pga memory" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "session pga memory max" "&&whereclause2"
|
|
@top_session_by_statname "&&inst_id2" "&&toprecordcount2" "bytes sent via SQL*Net to client" "&&whereclause2"
|
|
|
|
|
|
|
|
|
|
-- @@footer |