58 lines
1.8 KiB
SQL
58 lines
1.8 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Parallel Query downgrades
|
|
* Version :
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 01-Aug-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
COLUMN px_maxdop HEADING "Max|DOP" FORMAT 99999
|
|
COLUMN px_servers_requested HEADING "Req|PX|Servers" FORMAT 9999999
|
|
COLUMN px_servers_allocated HEADING "Alloc|PX|Servers" FORMAT 9999999
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN SID HEADING "SID" FORMAT 999999
|
|
COLUMN serial# HEADING "Serial#" FORMAT 999999
|
|
COLUMN status HEADING "Status"
|
|
COLUMN logon_time HEADING "Logon Time"
|
|
COLUMN username HEADING "DB UserName"
|
|
COLUMN service_name HEADING "Service Name"
|
|
COLUMN machine HEADING "Client Machine"
|
|
COLUMN osuser HEADING "OS User"
|
|
COLUMN program HEADING "Program"
|
|
COLUMN sql_exec_start_time HEADING "SQL StartTime"
|
|
|
|
|
|
SELECT m.px_maxdop
|
|
, m.px_servers_requested
|
|
, m.px_servers_allocated
|
|
, m.username
|
|
, m.sid
|
|
, m.inst_id
|
|
, m.session_serial# serial#
|
|
, m.service_name
|
|
, TO_CHAR(m.sql_exec_start,'DD-MON-YY HH24:MI:SS') sql_exec_start
|
|
, m.sql_id
|
|
, m.SQL_PLAN_HASH_VALUE PHV
|
|
, m.program
|
|
, m.ELAPSED_TIME
|
|
, m.queuing_time
|
|
FROM gv$sql_monitor m
|
|
WHERE 1=1
|
|
AND m.status = 'EXECUTING'
|
|
AND m.px_servers_requested is not null
|
|
AND NVL(m.px_servers_allocated,0) < m.px_servers_requested
|
|
;
|
|
|
|
@@footer
|