WORKLOAD REPOSITORY report for

DB Name	DB Id	Unique Name	Role	Edition	Release	RAC	CDB
NNESP01P	1998298958	NNESP01P_A	PRIMARY	EE	19.0.0.0.0	NO	NO

Instance	Inst Num	Startup Time	User Name	System Data Visible
NNESP01P	1	22-May-23 23:33	ADMIN01	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
ip-172-20-2-190	Linux x86 64-bit	16	8	1	61.92

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	34842	15-Sep-23 13:00:28	236	10.2
End Snap:	34844	15-Sep-23 15:00:51	236	9.8
Elapsed:		120.38 (mins)
DB Time:		555.49 (mins)

Report Summary

Top ADDM Findings by Average Active Sessions

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	4.6	0.1	0.00	0.01
DB CPU(s):	0.5	0.0	0.00	0.00
Background CPU(s):	0.1	0.0	0.00	0.00
Redo size (bytes):	9,598,617.3	272,829.0
Logical read (blocks):	59,615.4	1,694.5
Block changes:	5,556.4	157.9
Physical read (blocks):	970.8	27.6
Physical write (blocks):	1,179.0	33.5
Read IO requests:	110.5	3.1
Write IO requests:	49.6	1.4
Read IO (MB):	7.6	0.2
Write IO (MB):	9.2	0.3
IM scan rows:	0.0	0.0
Session Logical Read IM:	0.0	0.0
User calls:	481.9	13.7
Parses (SQL):	411.8	11.7
Hard parses (SQL):	6.3	0.2
SQL Work Area (MB):	87.8	2.5
Logons:	0.4	0.0
User logons:	0.4	0.0
Executes (SQL):	2,070.0	58.8
Rollbacks:	3.8	0.1
Transactions:	35.2

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	98.92
Buffer Hit %:	99.85	In-memory Sort %:	100.00
Library Hit %:	99.44	Soft Parse %:	98.47
Execute to Parse %:	80.11	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	87.27	% Non-Parse CPU:	98.56
Flash Cache Hit %:	0.00

Top 10 Foreground Events by Total Wait Time

Wait Classes by Total Wait Time

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
16	8	1	0.67	0.89	0.6	0.7	0.3	95.4

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
3.7	81.5	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	313.1	175.8	137.3
Database Requests:	160.1	110.5	49.6
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	84.7	10.8	74.0
Total (MB):	49.3	21.1	28.2
Database (MB):	16.8	7.6	9.2
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	19.1	9.6	9.5
Database (blocks):	2,149.8	970.8	1,179.0
Via Buffer Cache (blocks):	142.4	88.6	53.9
Direct (blocks):	2,007.4	882.3	1,125.1

Memory Statistics

	Begin	End
Host Mem (MB):	63,403.7	63,403.7
SGA use (MB):	47,488.0	47,488.0
PGA use (MB):	3,432.9	2,620.8
% Host Mem used for SGA+PGA:	80.31	79.03

Database Resource Limits

	Begin	End
CPUs:	16	16
SGA Target:	49,794,777,088	49,794,777,088
PGA Target:	8,284,232,704	8,284,232,704
Memory Target	0	0

Cache Sizes

	Begin	End
Buffer Cache:	41,088M	41,088M	Std Block Size:	8K
Shared Pool Size:	4,800M	4,812M	Log Buffer:	108,516K
In-Memory Area:	0M	0M

Shared Pool Statistics

	Begin	End
Memory Usage %:	78.26	79.73
% SQL with executions>1:	95.18	94.20
% Memory for SQL w/exec>1:	88.62	88.05

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Replication Statistics (GoldenGate, XStream)
• Advanced Queuing
• Resource Limit Statistics
• Shared Server Statistics
• Initialization Parameters
• Active Session History (ASH) Report
• ADDM Reports

Wait Events Statistics

• Time Model Statistics
• Operating System Statistics
• Operating System Statistics - Detail
• Foreground Wait Class
• Foreground Wait Events
• Background Wait Events
• Wait Event Histogram
• Wait Event Histogram (up to 64 us)
• Wait Event Histogram (up to 32 ms)
• Wait Event Histogram (up to 2 sec)
• Wait Event Histogram (up to 2 min)
• Wait Event Histogram (up to 1 hr)
• Service Statistics
• Service Wait Class Stats
• Top 10 Channel Waits
• Top Process Types by Wait Class
• Top Process Types by CPU Used

Time Model Statistics

• DB Time represents total time in user calls
• DB CPU represents CPU time of foreground processes
• Total CPU Time represents foreground and background processes
• Statistics including the word "background" measure background process time, therefore do not contribute to the DB time statistic
• Ordered by % of DB time in descending order, followed by Statistic Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics

• *TIME statistic values are diffed. All others display actual values. End Value is displayed if different
• ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

• s - second, ms - millisecond, us - microsecond, ns - nanosecond
• ordered by wait time desc, waits desc
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
• Captured Time accounts for 99.1% of Total DB time 33,329.21 (s)
• Total FG Wait Time: 29,216.81 (s) DB CPU time: 3,797.45 (s)

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

• s - second, ms - millisecond, us - microsecond, ns - nanosecond
• Only events with Total Wait Time (s) >= .001 are shown
• ordered by wait time desc, waits desc (idle events last)
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)
• Only events with Total Wait Time (s) >= .001 are shown
• %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: us is microseconds
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (idle events last)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 64 us)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: us is microseconds
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 32 ms)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: us is microseconds, ms is 1024 microseconds (approximately 1 millisecond)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 2 sec)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 2 min)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.12 minutes)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 1 hr)

• Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
• Units for % of Total Waits: m is 64*1024 milliseconds (approximately 67 seconds or 1.12 minutes) h is 4096*1024 milliseconds (approximately 72 minutes or 1.19 hours)
• % of Total Waits: total waits for all wait classes, including Idle
• % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
• Ordered by Event (only non-idle events are displayed)

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in seconds

Back to Wait Events Statistics
Back to Top

Top 10 Channel Waits

• Top 10 Channel Waits by wait time

Back to Wait Events Statistics
Back to Top

Top Process Types by Wait Class

• This table displays top process types ordered by wait class

Back to Wait Events Statistics
Back to Top

Top Process Types by CPU Used

• This table displays top process types ordered by CPU time

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by User I/O Wait Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Physical Reads (UnOptimized)
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
• %Total - Elapsed Time as a percentage of Total DB time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 156.6% of Total DB Time (s): 33,329
• Captured PL/SQL account for 66.2% of Total DB Time (s): 33,329

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - CPU Time as a percentage of Total DB CPU
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 54.2% of Total CPU Time (s): 3,797
• Captured PL/SQL account for 90.9% of Total CPU Time (s): 3,797

Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - User I/O Time as a percentage of Total User I/O Wait time
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Captured SQL account for 37.9% of Total User I/O Wait Time (s): 683
• Captured PL/SQL account for 13.4% of Total User I/O Wait Time (s): 683

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• %Total - Buffer Gets as a percentage of Total Buffer Gets
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Buffer Gets: 430,581,205
• Captured SQL account for 62.8% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• %Total - Physical Reads as a percentage of Total Disk Reads
• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Disk Reads: 7,011,775
• Captured SQL account for 12.7% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

• UnOptimized Read Reqs = Physical Read Reqs - (Optimized Read Reqs - Cell Flash Cache Read Hits for Controlfile)
• %Opt - Optimized Reads as percentage of SQL Read Requests
• %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
• Total Physical Read Requests: 797,891
• Captured SQL account for 98.2% of Total
• Total UnOptimized Read Requests: 797,891
• Captured SQL account for 98.2% of Total
• Total Optimized Read Requests: 1
• Captured SQL account for 0.0% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• %CPU - CPU Time as a percentage of Elapsed Time
• %IO - User I/O Time as a percentage of Elapsed Time
• Total Executions: 14,950,849
• Captured SQL account for 24.3% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 2,974,190
• Captured SQL account for 27.8% of Total

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

• Only Statements with Sharable Memory greater than 1048576 are displayed

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
020cka0d658gv	SELECT LAYER_CPR_ID FROM CPR_LAYER WHERE COUNTRY_ID = :B2 AND COMPANY_ID IS NULL AND CUSTOMER_GRP IS NULL AND AFFINITY_GRP IS NULL AND SALES_CHANNEL IS NULL AND CAMPAIGN_ID IS NULL AND :B1 BETWEEN VALID_FROM AND NVL(VALID_TO, :B1 )
07m0chpnnrwxb	SELECT CPR_RULE_ID, SEQUENCE_TYPE AS SEQ_TYPE, NVL(RETURN_NAME, :B6 ) AS RETURN_NAME, DECODE( SEQUENCE_TYPE, :B3 , :B5 , SEQUENCE_TYPE ) AS SEQUENCE_TYPE FROM CPRS_RULE_SEQUENTIAL WHERE MAIN_RULE_ID = :B4 ORDER BY DECODE( SEQ_TYPE, :B3 , LPAD( RULE_ORDER, :B2 , :B1 ), RETURN_NAME ), RULE_ORDER, SEQ_TYPE
0f6fqmbd77s1v	SELECT INSIS_GEN_BLC_V10.BLC_TRANSACTIONS_TYPE( BT.TRANSACTION_ID , BT.TRANSACTION_CLASS , BT.TRANSACTION_TYPE , BT.TRANSACTION_DATE , BT.GL_DATE , BT.CURRENCY , BT.AMOUNT , BT.ASSESSED_VALUE , BT.RATE , BT.RATE_DATE , BT.RATE_TYPE , BT.VARIANCE , BT.FC_CURRENCY , BT.FC_AMOUNT , BT.FC_VARIANCE , BT.ITEM_NAME , BT.NOTES , BT.DUE_DATE , BT.GRACE , BT.GRACE_EXTRA , BT.LEGAL_ENTITY , BT.ORG_ID , BT.ACCOUNT_ID , BT.ITEM_ID , BT.DOC_ID , BT.APPLICATION_ID , BT.TAX_ID , BT.PARENT_TRX_ID , BT.BILLING_RUN_ID , BT.STATUS , BT.CREATED_ON , BT.CREATED_BY , BT.UPDATED_ON , BT.UPDATED_BY , BT.ATTRIB_0 , BT.ATTRIB_1 , BT.ATTRIB_2 , BT.ATTRIB_3 , BT.ATTRIB_4 , BT.ATTRIB_5 , BT.ATTRIB_6 , BT.ATTRIB_7 , BT.ATTRIB_8 , BT.ATTRIB_9 , BT.OPEN_BALANCE , BT.PAID_STATUS , BT.CHARGE_TO , BT.REF_DOC_ID , BT.PAY_WAY_ID , BT.PAY_INSTR , BT.ACTUAL_OPEN_BALANCE , BT.LINE_NUMBER , BT.POSTPROCESS , BT.LOB , BT.FRACTION_TYPE , BT.COMM_FLAG , BT.PRIORITY , BT.PERIOD_FROM , BT.PERIOD_TO , BT.ATTRIB_10 , BT.ATTRIB_11 , BT.ATTRIB_12 , BT.ATTRIB_13 , BT.ATTRIB_14 , BT.ATTRIB_15 , BT.ATTRIB_16 , BT.ATTRIB_17 , BT.ATTRIB_18 , BT.ATTRIB_19 , BT.N_ATTRIB_0 , BT.N_ATTRIB_1 , BT.N_ATTRIB_2 , BT.N_ATTRIB_3 , BT.N_ATTRIB_4 , BT.N_ATTRIB_5 , BT.N_ATTRIB_6 , BT.N_ATTRIB_7 , BT.N_ATTRIB_8 , BT.N_ATTRIB_9 , BT.D_ATTRIB_0 , BT.D_ATTRIB_1 , BT.D_ATTRIB_2 , BT.D_ATTRIB_3 , BT.D_ATTRIB_4 , BT.D_ATTRIB_5 , BT.D_ATTRIB_6 , BT.D_ATTRIB_7 , BT.D_ATTRIB_8 , BT.D_ATTRIB_9 , BT.W_ATTRIB_0 , BT.W_ATTRIB_1 , BT.ANNEX , BT.EXTERNAL_ID , BT.GRC_ORG_ID , BT.EXTERNAL_LINK_ID , SUBJECT_OF_PAYMENT , INITIAL_LOB , COVER_TYPE ) AS BLC_TRANSACTIONS_TYPE FROM BLC_TRANSACTIONS BT INNER JOIN INSIS_GEN_BLC_V10.BLC_TRX_ACC BTA ON BT.TRANSACTION_ID = BTA.TRANSACTION_ID INNER JOIN BLC_ITEMS BI ON BTA.ITEM_ID = BI.ITEM_ ID WHERE BTA.AGREEMENT = :B5 AND BT.DUE_DATE BETWEEN :B4 AND :B3 AND BTA.I_ITEM_TYPE = :B2 AND BTA.STATUS NOT IN ('C', 'R', 'D') AND BI.ATTRIB_9 = :B1 AND BT.ACTUAL_OPEN_BALANCE <> 0 AND BTA.DOC_ID NOT IN ( SELECT BP.BILLING_REFERENCE FROM BLC_PAYMENTS BP WHERE BP.BILLING_REFERENCE = BTA.DOC_ID AND BP.STATUS IN ('S', 'A') )
0rc203ntss5f0	SELECT BLC_SOURCE FROM HST_CLAIM_PROD_LOB_BLC_SOURCE G WHERE G.BLC_SOURCE = :B1
0xphvddu9d7wc	INSERT INTO P_PEOPLE_CHANGES ( CHANGE_ID, MAN_ID, MAN_COMP, EGN, NAME, GNAME, SNAME, FNAME, BIRTH_DATE, SEX, NOTES, NATIONALITY, COMP_TYPE, NAME_SUFFIX, NAME_PREFIX, DATA_SOURCE, LANGUAGE, HOME_COUNTRY, INDUSTRY_CODE, SUB_INDUSTRY_CODE, FISCAL_PERIOD, CLASS_CODE, CLASS_SUB_CODE, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, VALID_FROM, VALID_TO, USERNAME, REGISTRATION_DATE, ATTRC1, ATTRC2, ATTRC3, ATTRC4, ATTRC5, ATTRC6, ATTRC7, ATTRC8, ATTRC9, ATTRC10, ATTRN1, ATTRN2, ATTRN3, ATTRN4, ATTRN5, ATTRN6, ATTRN7, ATTRN8, ATTRN9, ATTRN10, ATTRD1, ATTRD2, ATTRD3, ATTRD4, ATTRD5 ) VALUES ( :B57 , :B56 , :B55 , :B54 , :B53 , :B52 , :B51 , :B50 , :B49 , :B48 , :B47 , :B46 , :B45 , :B44 , :B43 , :B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , : B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
0za9fv0j1vgkk	WITH MONITOR_DATA AS (SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance') AS INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_FULL_PLAN_HASH_VALUE, SESSION_SERIAL#, SQL_TEXT, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, QUEUING_TIME, CPU_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, IO_INTERCONNECT_BYTES IO_INTER_BYTES, PHYSICAL_READ_REQUESTS READ_REQS, PHYSICAL_READ_BYTES READ_BYTES, PHYSICAL_WRITE_REQUESTS WRITE_REQS, PHYSICAL_WRITE_BYTES WRITE_BYTES, NVL(PHYSICAL_READ_BYTES, 0) + NVL(PHYSICAL_WRITE_BYTES, 0) IO_BYTES, NVL(PHYSICAL_READ_REQUESTS, 0) + NVL(PHYSICAL_WRITE_REQUESTS, 0) IO_REQS, IO_CELL_UNCOMPRESSED_BYTES IO_UNC_BYTES, IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_ELIG_BYTES, IO_CELL_OFFLOAD_RETURNED_BYTES IO_RET_BYTES, USER# USERID, USERNAME, MODULE, ACTION, SERVICE_NAME, CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PLSQL_OBJECT_ID PL_OID, PLSQL_SUBPROGRAM_ID PL_PROGID, PLSQL_ENTRY_OBJECT_ID PL_ENTRY_OID, PLSQL_ENT RY_SUBPROGRAM_ID PL_ENTRY_PROGID, PX_MAXDOP MAX_DOP, PX_IS_CROSS_INSTANCE, PX_MAXDOP_INSTANCES MAX_DOP_INSTANCES, PX_SERVERS_REQUESTED SERVERS_REQUESTED, PX_SERVERS_ALLOCATED SERVERS_ALLOCATED, ERROR_NUMBER, ERROR_FACILITY, ERROR_MESSAGE, NVL2(OTHER_XML, 'Y', NULL) HAS_OTHER_XML, NVL2(BINDS_XML, 'Y', NULL) HAS_BINDS_XML, NVL2(RM_CONSUMER_GROUP, NVL2(RM_LAST_ACTION_TIME, TO_CHAR(RM_LAST_ACTION_TIME, :B12 ), '00/00/0000 00:00:01') || XMLELEMENT( "rminfo", XMLATTRIBUTES( RM_LAST_ACTION AS "rmlastact", RM_LAST_ACTION_REASON AS "rmlastreason", TO_CHAR(RM_LAST_ACTION_TIME, :B12 ) AS "rmlasttime", RM_CONSUMER_GROUP AS "rmcg")).GETSTRINGVAL(), '00/00/0000 00:00:00') RM_INFO, CON_NAME, CON_ID FROM V$ALL_SQL_MONITOR MO1 WHERE USERENV('INSTANCE') BETWEEN :B11 AND :B10 AND MO1.SQL_ID = NVL(:B9 , MO1.SQL_ID) AND MO1.SQL_EXEC_START = NVL(:B8 , MO1.SQL_EXEC_START) AND MO1.SQL_EXEC_ID = NVL(:B7 , MO1.SQL_EXEC_ID) OR (MO1.D BOP_NAME = NVL(:B6 , MO1.DBOP_NAME) AND MO1.DBOP_EXEC_ID = NVL(:B5 , MO1.DBOP_EXEC_ID)) AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SET = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#))) ))) ), MONITOR_AGG AS (SELECT MAX_PX_QCSID, MAX_KEY, MAX_INST_ID, MAX_SESSION_ID, MAX_SESSION_SERIAL, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, MAX_PLAN_HASH_VALUE, MAX_FULL_PLAN_HASH_VALUE, MAX_USERID, MAX_PROGRAM, MAX_USERNAME, MAX_MODULE, MAX_ACTION, MAX_SERVICE_NAME, MAX_CLIENT_ID, MAX_CLIENT_INFO, MAX_ERROR_NUMBER, MAX_ERROR_FACILITY, MAX_ERROR_MESSAGE, QC_HAS_OTHER_XML, QC_HAS_BINDS_XML, MAX_PL_OID, MAX_PL_PROGID, MAX_PL_ENTRY_OID, MAX_PL_ENTRY_PROGID, MAX_SQL_ID, MAX_SQL_EXEC_START, MAX_SQL_EXEC_ID, MAX_LAST_REFRESH_TIME, MAX_DBOP_NAME, MAX_DBOP_EXEC_ID, CASE WHEN MAX_PL_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = MAX_PL_OID AND P.SUBPROGRAM_ID = MAX_PL_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_NAME, CASE WHEN MAX_PL_ENTRY_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = MAX_PL_ENTRY_OID AND P.SUBPROGRAM_ID = MAX_PL_ENTRY_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_ENTRY_NAME, MAX_STATUS, SUM_REFRESH_COUNT, MIN_FIRST_REFRESH_TIME, SQLMON_TEXT, SUM_ELAPSED_TIME, MAX_ELAPSED_TIME, MAX_QUEUING_TIME, SUM_CPU_TIME, SUM_USER_IO_WAIT_TIME, SUM_APPLICATION_WAIT_TIME, SUM_CONCURRENCY_WAIT_TIME, SUM_CLUSTER_WAIT_TIME, SUM_PLSQL_EXEC_TIME, SUM_JAVA_EXEC_TIME, SUM_OTHER_WAIT_TIME, SUM_FETCHES, SUM_BUFFER_GETS, SUM_READ_REQS, SUM_READ_BYTES, SU M_WRITE_REQS, SUM_WRITE_BYTES, SUM_IO_BYTES, SUM_IO_INTER_BYTES, SUM_IO_UNC_BYTES, SUM_IO_ELIG_BYTES, SUM_IO_RET_BYTES, DECODE(:B14 , 1, 'db_name', SYS_CONTEXT('userenv', 'db_unique_name')) DB_UNIQUE_NAME, DECODE(:B14 , 1, 'platform_name', SYS.DBMS_UTILITY.PORT_STRING) PLATFORM_NAME, DECODE(:B14 , 1, 'host_name', SYS_CONTEXT('userenv', 'server_host')) HOST_NAME, AGG_RM_INFO MAX_RM_INFO, MAX_CON_NAME, DECODE(MAX_CON_ID, 0, NULL, MAX_CON_ID) MAX_CON_ID FROM (SELECT MAX(PX_QCSID) MAX_PX_QCSID, MAX(CASE WHEN PX_QCSID IS NULL THEN KEY ELSE NULL END) MAX_KEY, MAX(CASE WHEN PX_QCSID IS NULL THEN INST_ID ELSE NULL END) MAX_INST_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SID ELSE NULL END) MAX_SESSION_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SESSION_SERIAL# ELSE NULL END) MAX_SESSION_SERIAL, MAX(MAX_DOP) MAX_PX_DOP, MAX(MAX_DOP_INSTANCES) MAX_PX_DOP_INSTANCES, MAX(PX_IS_CROSS_INSTANCE) MAX_PX_IS_CROSS_INSTANCE, SUM(SERVERS_REQUESTED) SUM_SERVERS_REQUESTED, SUM(SER VERS_ALLOCATED) SUM_SERVERS_ALLOCATED, COUNT(DISTINCT INST_ID) DIST_INST_COUNT, COUNT(DISTINCT PX_SERVER_GROUP) DIST_PX_GROUP_COUNT, COUNT(DISTINCT PX_SERVER_SET) DIST_PX_SET_COUNT, MAX(SQL_PLAN_HASH_VALUE) MAX_PLAN_HASH_VALUE, MAX(SQL_FULL_PLAN_HASH_VALUE) MAX_FULL_PLAN_HASH_VALUE, MAX(USERID) MAX_USERID, MAX(PROGRAM) MAX_PROGRAM, MAX(USERNAME) MAX_USERNAME, MAX(MODULE) MAX_MODULE, MAX(ACTION) MAX_ACTION, MAX(SERVICE_NAME) MAX_SERVICE_NAME, MAX(CLIENT_IDENTIFIER) MAX_CLIENT_ID, MAX(CLIENT_INFO) MAX_CLIENT_INFO, MAX(ERROR_NUMBER) MAX_ERROR_NUMBER, MAX(ERROR_FACILITY) MAX_ERROR_FACILITY, MAX(ERROR_MESSAGE) MAX_ERROR_MESSAGE, MAX(NVL2(PX_QCSID, HAS_OTHER_XML, NULL)) QC_HAS_OTHER_XML, MAX(HAS_BINDS_XML) QC_HAS_BINDS_XML, MAX(PL_OID) MAX_PL_OID, MAX(PL_PROGID) MAX_PL_PROGID, MAX(PL_ENTRY_OID) MAX_PL_ENTRY_OID, MAX(PL_ENTRY_PROGID) MAX_PL_ENTRY_PROGID, MAX(SQL_ID) MAX_SQL_ID, MAX(SQL_EXEC_START) MAX_SQL_EXEC_START, MAX(SQL_EXEC_ID) MAX_SQL_EXEC_ID, MAX(LAST_REFRE SH_TIME) MAX_LAST_REFRESH_TIME, MAX(STATUS) MAX_STATUS, SUM(REFRESH_COUNT) SUM_REFRESH_COUNT, MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, MAX(DBOP_NAME) MAX_DBOP_NAME, MAX(DBOP_EXEC_ID) MAX_DBOP_EXEC_ID, CASE WHEN :B13 = 0 THEN NULL ELSE MAX(SQL_TEXT) END SQLMON_TEXT, SUM(ELAPSED_TIME) SUM_ELAPSED_TIME, MAX(ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(QUEUING_TIME) MAX_QUEUING_TIME, SUM(CPU_TIME) SUM_CPU_TIME, SUM(USER_IO_WAIT_TIME) SUM_USER_IO_WAIT_TIME, SUM(APPLICATION_WAIT_TIME) SUM_APPLICATION_WAIT_TIME, SUM(CONCURRENCY_WAIT_TIME) SUM_CONCURRENCY_WAIT_TIME, SUM(CLUSTER_WAIT_TIME) SUM_CLUSTER_WAIT_TIME, SUM(PLSQL_EXEC_TIME) SUM_PLSQL_EXEC_TIME, SUM(JAVA_EXEC_TIME) SUM_JAVA_EXEC_TIME, SUM(OTHER_WAIT_TIME) SUM_OTHER_WAIT_TIME, SUM(FETCHES) SUM_FETCHES, SUM(BUFFER_GETS) SUM_BUFFER_GETS, SUM(READ_REQS) SUM_READ_REQS, SUM(READ_BYTES) SUM_READ_BYTES, SUM(WRITE_REQS) SUM_WRITE_REQS, SUM(WRITE_BYTES) SUM_WRITE_BYTES, NVL(SUM(READ_BYTES), 0) + NVL(SUM(WRITE_BYTES), 0) SU M_IO_BYTES, SUM(IO_INTER_BYTES) SUM_IO_INTER_BYTES, SUM(IO_UNC_BYTES) SUM_IO_UNC_BYTES, SUM(IO_ELIG_BYTES) SUM_IO_ELIG_BYTES, SUM(IO_RET_BYTES) SUM_IO_RET_BYTES, MAX(RM_INFO) AGG_RM_INFO, MAX(CON_NAME) MAX_CON_NAME, MAX(CON_ID) MAX_CON_ID FROM MONITOR_DATA) MD), ASH_DATA AS (SELECT AD0.INST_ID, AD0.SESSION_ID, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.ACTIVITY_BUCKET_NUM, AD0.PLAN_ACTIVITY_BUCKET_NUM, AD0.SQL, AD0.TOP_LEVEL_SQL_ID, AD0.DBOP_NAME, AD0.IS_MONITORED_SQL, AD0.IS_PX_SLAVE, AD0.BUCKET_ACTIVITY_START, AD0.ACTIVITY_START, AD0.BUCKET_ACTIVITY_END, AD0.ACTIVITY_END, AD0.ACTIVITY_COUNT, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.IMQ_COUNT, AD0.WAIT_COUNT, AD0.CPU_COUNT, AD0.OTHER_SQL_COUNT, AD0.PX_SERVER_SET, AD0.PX_DFO_ACTIVITY_COUNT, AD0.DFO_MOST_ACTIVE_IID, AD0.DFO_MOST_ACTIVE_SID, (CASE WHEN AD0.DFO_MOST_ACTIVE_IID = AD0.INST_ID AND AD0.DFO_MOST_ACTIVE_SID = AD0.SESSION_ID AND (((AD0.PX_DFO _ACTIVITY_COUNT / AD0.DFO_MOST_ACTIVE_COUNT) >= AD0.PX_DOP * 1.05) OR ((AD0.PX_DFO_ACTIVITY_COUNT / AD0.DFO_MOST_ACTIVE_COUNT) <= AD0.PX_DOP *0.95)) AND (AD0.DFO_DURATION * 100) > :B32 THEN AD0.ACTIVITY_COUNT ELSE NULL END) DFO_MOST_ACTIVE_COUNT, AD0.BUCKET_DISTINCT_SAMPLES, AD0.SQL_BUCKET_DISTINCT_SAMPLES, CASE WHEN AD0.PX_SERVER_GROUP IS NULL AND AD0.IS_PX_SLAVE = 1 THEN 1 ELSE AD0.PX_SERVER_GROUP END PX_SERVER_GROUP, AD0.PX_STEP_ID, AD0.PX_DFO_PAIR_TYPE, AD0.PX_DFO_PAIR_ID, AD0.PX_STEP_ARG, AD0.PX_DOP, CASE WHEN AD0.PX_DOP IS NOT NULL AND AD0.PX_DOP <> AD0.PX_MIN_DOP AND AD0.PX_MIN_DOP != 0 THEN PX_MIN_DOP ELSE NULL END PX_MIN_DOP FROM (SELECT /*+ use_hash(ash) leading(mo) */ ASH.INST_ID, ASH.IS_PX_SLAVE, ASH.SESSION_ID, ASH.PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.ACTIVITY_BUCKET_NUM, ASH.PLAN_ACTIVITY_BUCKET_NUM, ASH.SQL, ASH.TOP_LEVEL_SQL_ID, ASH.DBOP_NAME, ASH.IS_MONITORED_SQL, ASH.BUCKET_ACTIVITY_START, ASH.ACTIVITY_ST ART, ASH.BUCKET_ACTIVITY_END, ASH.ACTIVITY_END, ASH.ACTIVITY_COUNT, ASH.ACTIVITY_TYPE, ASH.OTHER_SQL_ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, MO.PX_SERVER_SET, ASH.PX_DFO_ACTIVITY_COUNT, TRUNC(ASH.MOST_ACTIVE_IN_DFO / 10000000000) DFO_MOST_ACTIVE_COUNT, MOD(TRUNC(ASH.MOST_ACTIVE_IN_DFO / 1000000), 10000) DFO_MOST_ACTIVE_IID, MOD(ASH.MOST_ACTIVE_IN_DFO, 1000000) DFO_MOST_ACTIVE_SID, ASH.DFO_DURATION, ASH.BUCKET_DISTINCT_SAMPLES, ASH.SQL_BUCKET_DISTINCT_SAMPLES, MO.PX_SERVER_GROUP, ASH.PX_STEP_ID, ASH.PX_DFO_PAIR_TYPE, ASH.PX_DFO_PAIR_ID, ASH.PX_STEP_ARG, ASH.PX_DOP, ASH.PX_MIN_DOP FROM (SELECT /*+ no_merge */ MD.INST_ID, MD.SID, MD.SESSION_SERIAL#, MD.PX_SERVER_SET, MD.PX_SERVER_GROUP FROM MONITOR_DATA MD WHERE MD.SID IS NOT NULL OR :B31 = 1) MO, (SELECT /*+ no_merge */ GVTF.INST_ID, GVTF.GLOBAL_SAMPLE_ID, GVTF.IS_PX_SLAVE, GVTF.SESSION_ID, GVTF.PLAN_LINE_ID, GVTF.PLSQL_OBJECT_ID, GVTF.PLSQL_S UBPROGRAM_ID, GVTF.ACTIVITY_BUCKET_NUM, GVTF.PLAN_ACTIVITY_BUCKET_NUM, GVTF.SQL, GVTF.TOP_LEVEL_SQL_ID, GVTF.DBOP_NAME, GVTF.IS_MONITORED_SQL, GVTF.BUCKET_ACTIVITY_START, GVTF.ACTIVITY_START, GVTF.BUCKET_ACTIVITY_END, GVTF.ACTIVITY_END, GVTF.ACTIVITY_COUNT, GVTF.ACTIVITY_TYPE, GVTF.OTHER_SQL_ACTIVITY_TYPE, GVTF.EVENT_NAME, GVTF.IMQ_COUNT, GVTF.WAIT_COUNT, GVTF.CPU_COUNT, GVTF.OTHER_SQL_COUNT, MAX(GVTF.PER_SERVER_DFO_COUNT * 10000000000 + GVTF.INST_ID * 1000000 + GVTF.SESSION_ID) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) MOST_ACTIVE_IN_DFO, SUM(GVTF.ACTIVITY_COUNT) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) PX_DFO_ACTIVITY_COUNT, GVTF.DFO_DURATION, GVTF.PX_STEP_ID, GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID, GVTF.PX_STEP_ARG, GVTF.PX_DOP, GVTF.PX_MIN_DOP, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GVTF.ACTIVITY_BUCKET_NUM) BUCKET_DISTINCT_SAMPLES, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GV TF.SQL, GVTF.ACTIVITY_BUCKET_NUM) SQL_BUCKET_DISTINCT_SAMPLES FROM TABLE(SYS.GV$(CURSOR( (SELECT USERENV('INSTANCE') INST_ID, ASH2.GLOBAL_SAMPLE_ID, CASE WHEN :B30 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END ACTIVITY_BUCKET_NUM, CASE WHEN :B29 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END PLAN_ACTIVITY_BUCKET_NUM, ASH2.SQL, ASH2.TOP_LEVEL_SQL_ID, ASH2.DBOP_NAME, ASH2.IS_MONITORED_SQL, ASH2.PLAN_LINE_ID, ASH2.PLSQL_OBJECT_ID, ASH2.PLSQL_SUBPROGRAM_ID, ASH2.ACTIVITY_TYPE, ASH2.OTHER_SQL_ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.IS_PX_SLAVE, ASH2.SESSION_ID, ASH2.PX_STEP_ID, ASH2.PX_DFO_PAIR_TYPE, ASH2.PX_DFO_PAIR_ID, ASH2.PX_STEP_ARG, CASE WHEN ASH2.PX_DFO_PAIR_ID IS NOT NULL THEN DECODE(ASH2.PX_DOP, 0, :B28 , ASH2.PX_DOP) ELSE NULL END PX_DOP, ASH2.PX_MIN_DOP, :B20 + NUMTODSINTERVAL(:B26 * (ASH2.BUCKET_NUM-1), 'SECOND') BUCKET_ACTIVITY_START, :B20 + NUMTODSINTERVAL( :B26 * ASH2.BUCKET_NUM - 1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.A CTIVITY_END, ASH2.ACTIVITY_COUNT, ASH2.IMQ_COUNT, ASH2.WAIT_COUNT, ASH2.CPU_COUNT, ASH2.OTHER_SQL_COUNT, SUM(ASH2.ACTIVITY_COUNT) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE, DECODE(ASH2.PX_DFO_PAIR_ID, NULL, NULL, ASH2.SESSION_ID)) PER_SERVER_DFO_COUNT, CEIL((MAX(ASH2.MAX_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE) - MIN(ASH2.MIN_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE)) * 3600 * 24) DFO_DURATION FROM (SELECT ASH1.BUCKET_NUM, ASH1.GLOBAL_SAMPLE_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.SESSION_ID, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG, MAX(ASH1.SQL) SQL, MAX(ASH1.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(ASH1.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(ASH1.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, MIN(SAMPLE_DATE) MIN_SAMPLE_DATE, MAX(SAMPLE_DATE) MAX_SAMPLE_DATE, MAX(ASH1.IS_PX_SLAVE) IS_PX_S LAVE, MAX(ASH1.PX_DOP) PX_DOP, MIN(ASH1.PX_DOP) PX_MIN_DOP, MIN(ASH1.SAMPLE_DATE) ACTIVITY_START, MAX(ASH1.SAMPLE_DATE) ACTIVITY_END, COUNT(ASH1.SQL) ACTIVITY_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME = 'in memory' THEN 1 ELSE NULL END) IMQ_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE != 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' AND ASH1.ACTIVITY_TYPE != 'Cpu' THEN 1 ELSE NULL END) WAIT_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME IS NULL THEN 1 ELSE NULL END) CPU_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' THEN 1 ELSE NULL END) OTHER_SQL_COUNT, MAX(ASH1.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(ASH1.DBOP_NAME) DBOP_NAME FROM ( SELECT (CASE WHEN :B27 > 1 THEN (TRUNC(DELTA_TIME_SECONDS/ :B26 ) + 1) ELSE 1 END) BUCKET_NUM, ASH00.SQL, ASH00.SAMPLE_DATE, NVL2(DUP.C2, TRUNC(DELTA_TIME_SECONDS/ (:B25 )) + 1, NULL) GLOBAL_SAMPLE_ID, NVL2(DUP.C2, NULL, ASH00.IS_MONITORED_SQL) IS_MONITORED_SQL, NVL2(DUP.C2, NULL, ASH00.IN_INMEMORY_QUERY) IN_INMEMORY_QUERY, NVL2(DUP.C2, NULL, ASH00.WAIT_CLASS) WAIT_CLASS, NVL2(DUP.C2, NULL, ASH00.ACTIVITY_TYPE) ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.OTHER_SQL_ACTIVITY_TYPE) OTHER_SQL_ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.EVENT_NAME) EVENT_NAME, NVL2(DUP.C2, NULL, ASH00.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, NVL2(DUP.C2, NULL, ASH00.DBOP_NAME) DBOP_NAME, NVL2(DUP.C2, NULL, ASH00.IS_PX_SLAVE) IS_PX_SLAVE, NVL2(DUP.C2, NULL, ASH00.SESSION_ID) SESSION_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_OBJECT_ID) PLSQL_OBJECT_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_SUBPROGRAM_ID) PLSQL_SUBPROGRAM_ID, NVL2(DUP.C2, NULL, ASH00.PLAN_LINE_ID) PLAN_LINE_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ID) PX_STEP_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ARG) PX_STEP_ARG, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, NVL2(DUP.C2, NULL, ASH00.PX_DOP) PX_DOP FROM (SELECT 1 C1, NULL C2 FROM V$DUAL UNION ALL SELECT 1 C1, 1 C2 FROM V$DUAL) DUP, (SELECT /*+ no_merge */ 1 C1, ASH000.SAMPLE_DATE, ASH000.IS_MONITORED_SQL, ((EXTRACT(SECOND FROM(DELTA_TIME)) + EXTRACT(MINUTE FROM(DELTA_TIME)) * 60 + EXTRACT(HOUR FROM(DELTA_TIME)) * 3600 + EXTRACT(DAY FROM(DELTA_TIME)) * 86400)) DELTA_TIME_SECONDS, ASH000.IN_INMEMORY_QUERY, ASH000.WAIT_CLASS, DECODE(ASH000.IS_MONITORED_SQL, 1, NVL(ASH000.WAIT_CLASS, 'Cpu'), DECODE(SQL_ID, NULL, 'Non SQL', 'Other SQL Execution')) ACTIVITY_TYPE, NVL(ASH000.WAIT_CLASS, 'Cpu') OTHER_SQL_ACTIVITY_TYPE, DECODE(:B24 , 1, CASE WHEN ASH000.IS_MONITORED_SQL = 1 THEN NVL(ASH000.EVENT, DECODE(ASH000.IN_INMEMORY_QUERY, 'Y', 'in memory', ASH000.EVENT)) WHEN ASH000.SQL_ID IS NOT NULL THEN 'sql_id: ' || ASH000.SQL_ID WHEN ASH000.CALL_NAME IS NOT NULL THEN 'call: ' || ASH000.CALL_NAME ELSE 'anonymous: '|| ASH000.EVENT END, NULL) EVENT_NAME, CASE WHEN ASH000.IS_MONITORED_SQL = 1 AND (NVL(ASH000.SQL_ID, :B9 ) = :B9 OR NVL(ASH000.DBOP_NAME, :B6 ) = :B6 ) THEN 'this' WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.SQL_ID IS NOT NULL AND ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID THEN ASH000.TOP_LEVEL_SQL_ID WHEN ASH000.SQL_ID IS NOT NULL THEN ASH000.SQL_ID ELSE NVL(CALL_NAME, 'anonymous') END SQL, CASE WHEN ASH000.IS_PX_SLAVE = 0 AND (ASH000.SQL_ID IS NULL OR ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID) THEN ASH000.TOP_LEVEL_SQL_ID END TOP_LEVEL_SQL_ID, ASH000.DBOP_NAME, ASH000.IS_PX_SLAVE, CASE WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.IS_MONITORED_SQL != 1 THEN 65536 ELSE ASH000.SESSION_ID END SESSION_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_OBJECT_ID, NULL) PLSQL_OBJECT_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_SUBPROGRAM_ID, NULL) PLSQL_SUBPROGRAM_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.SQL_PLAN_LINE_ID, NULL) PLAN_LINE_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ID, NULL) PX_STEP_ID, CASE WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.IS_MONITORED_SQL = 1 AND ASH000.PX_STEP_ID IN (1, 2, 3) THEN ASH000.PX_STEP_ARG ELSE NULL END PX_DFO_PAIR_ID, CASE WHEN ASH000.IS_PX_SLAVE = 0 OR ASH000.IS_MONITORED_SQL != 1 THEN NULL WHEN ASH000.PX_STEP_ID = 1 THEN 1 WHEN ASH000.PX_STEP_ID IN (2, 3) THEN 0 ELSE NULL END PX_DFO_PAIR_TYPE, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ARG, NULL) PX_STEP_ARG, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_DOP, NULL) PX_DOP FROM (SELECT ASH0.*, CASE WHEN ASH0.IS_TARGET_SQL = 1 OR (ASH0.IS_PX_SLAVE = 1 AND ((NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) IS NOT NULL AND NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) = :B9 ) OR (SQL_ID IS NULL AND :B23 = 'Y'))) THEN 1 ELSE 0 END IS_MONITORED_SQL FROM (SELECT (CASE WHEN (ASH.SQL_ID = :B9 AND ASH.SQL_EXEC_ID = :B7 AND ASH.SQL_EXEC_START = :B8 ) THEN 1 ELSE 0 END) IS_TARGET_SQL, ASH.SQL_ID, ASH.SQL_PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.TOP_LEVEL_SQL_ID, DECODE(ASH.SQL_ID, NULL, ASH.TOP_LEVEL _CALL_NAME, NULL) CALL_NAME, ASH.EVENT, ASH.IN_INMEMORY_QUERY, ASH.WAIT_CLASS, ASH.SQL_EXEC_ID, ASH.SQL_EXEC_START, ASH.DBOP_NAME, ASH.DBOP_EXEC_ID, ASH.SESSION_ID, ASH.SESSION_SERIAL#, CASE WHEN QC_INSTANCE_ID IS NOT NULL AND (ASH.SESSION_ID != ASH.QC_SESSION_ID OR ASH.SESSION_SERIAL# != ASH.QC_SESSION_SERIAL# OR USERENV('instance') != ASH.QC_INSTANCE_ID) THEN 1 ELSE 0 END IS_PX_SLAVE, SAMPLE_TIME - CAST(:B20 AS TIMESTAMP) DELTA_TIME, CAST(FROM_TZ(ASH.SAMPLE_TIME, DBTIMEZONE) AS DATE) SAMPLE_DATE, TRUNC(MOD(PX_FLAGS/65536, 32)) PX_STEP_ID, MOD(PX_FLAGS, 65536) PX_STEP_ARG, TRUNC(PX_FLAGS/2097152) PX_DOP FROM V$ALL_ACTIVE_SESSION_HISTORY ASH WHERE ((ASH.SESSION_ID = :B19 AND ASH.SESSION_SERIAL# = :B18 AND USERENV('INSTANCE') = :B17 ) OR (ASH.QC_SESSION_ID IS NOT NULL AND ASH.QC_SESSION_ID = :B19 AND ASH.QC_SESSION_SERIAL# = :B18 AND ASH.QC_INSTANCE_ID = :B17 )) AND SAMPLE_TIME BETWEEN :B16 AND :B15 ) ASH0 WHERE (ASH0.SAMPLE_DATE BETWEEN :B20 + 1/24/3600 AND :B22 - 1/ 24/3600 OR (ASH0.SQL_ID = :B9 AND ASH0.SQL_EXEC_START = :B8 AND ASH0.SQL_EXEC_ID = :B7 ) OR (ASH0.DBOP_NAME = :B6 AND ASH0.DBOP_EXEC_ID = :B5 )) AND (:B21 IS NULL OR ASH0.SQL_PLAN_LINE_ID = :B21 ) AND (ASH0.IS_PX_SLAVE = 0 OR ASH0.SQL_ID IS NOT NULL)) ASH000 ) ASH00 WHERE ASH00.C1 = DUP.C1) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B27 GROUP BY USERENV('INSTANCE'), ASH1.GLOBAL_SAMPLE_ID, ASH1.BUCKET_NUM, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG) ASH2)))) GVTF WHERE GVTF.INST_ID BETWEEN :B11 AND :B10 ) ASH WHERE ASH.GLOBAL_SAMPLE_ID IS NULL AND ASH.SESSION_ID = MO.SID(+) AND ASH.INST_ID = MO.INST_ID(+)) AD0), RESPONSE_TIME_DATA AS (SELECT ADH.BUCKET_NUM, ADH.SQL_ROWNUM, ADH.SQL, ADH.TOP_LEVEL_SQL_ID, ADH.DBOP_NAME, ADH.PX_ID, DECODE( ADH.PX_STEP_ID, NULL, NULL, 0, NULL, 1, 'PX Server(s) - Executing Pare nt DFO', 2, 'PX Server(s) - Executing Child DFO', 3, 'PX Server(s) - Sampling Child DFO', 4, 'PX Server(s) - Joining Group', 5, 'QC - Scheduling Child DFO', 6, 'QC - Scheduling Parent DFO', 7, 'QC - Initializing Objects', 8, 'QC - Flushing Objects', 9, 'QC - Allocating Slaves', 10, 'QC - Initializing Granules', 11, 'PX Server(s) - Parsing Cursor', 12, 'PX Server(s) - Executing Cursor', 13, 'PX Server(s) - Preparing Transaction', 14, 'PX Server(s) - Joining Transaction', 15, 'PX Server(s) - Load Commit', 16, 'PX Server(s) - Aborting Transaction', 17, 'QC - Executing Child DFO', 18, 'QC - Executing Parent DFO', 'PX Step - ' || PX_STEP_ID) PX_STEP_ID, ADH.PX_STEP_ARG, ADH.PX_DFO_PAIR_ID, ADH.PX_DOP, ADH.PX_MIN_DOP, ADH.DFO_MOST_ACTIVE_IID, ADH.DFO_MOST_ACTIVE_SID, ADH.DFO_MOST_ACTIVE_COUNT, ADH.ACTIVITY_START, ADH.ACTIVITY_END, ADH.ACTIVITY_TYPE, ADH.OTHER_SQL_ACTIVITY_TYPE, ADH.EVENT_NAME, ADH.PLAN_LINE_ID, ADH.PLSQL_OBJECT_ID, ADH.PLSQL_ SUBPROGRAM_ID, CASE WHEN PLSQL_ROWNUM = 1 AND ADH.PLSQL_OBJECT_ID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM ALL_PROCEDURES P WHERE P.OBJECT_ID = ADH.PLSQL_OBJECT_ID AND P.SUBPROGRAM_ID = ADH.PLSQL_SUBPROGRAM_ID), 'Unavailable') ELSE NULL END PLSQL_NAME, ADH.ACTIVITY_COUNT, ADH.BUCKET_ACTIVE_SECONDS, ADH.BUCKET_IDLE_SECONDS, (CASE WHEN ADH.IS_MONITORED_SQL = 0 THEN ADH.ACTIVE_SECONDS WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_CPU_HEIGHT >= :B34 THEN DECODE(ADH.ACTIVITY_TYPE, 'Cpu', (ADH.DFO_PAIR_ACTIVITY_HEIGHT / ADH.DFO_PAIR_CPU_HEIGHT) * ADH.DFO_PAIR_TOTAL_SECONDS, 0) WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * DECODE(ADH.ACTIVITY_TYPE, 'Cpu', ADH.DFO_PAIR_ACTIVITY_HEIGHT, (ADH.DFO_PAIR_ACTIVITY_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT))) / :B34 WHEN ADH.PX_DFO _PAIR_ID IS NOT NULL THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_ACTIVITY_HEIGHT) / ADH.PX_DOP ELSE ADH.ACTIVE_SECONDS END) RESP_TIME, (CASE WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * (ADH.DFO_PAIR_IDLE_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT)) / :B34 WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_IDLE_HEIGHT) / ADH.PX_DOP ELSE 0 END) DFO_PAIR_IDLE_RESP_TIME, ADH.DFO_PAIR_ACTIVITY_HEIGHT, ADH.DFO_PAIR_CPU_HEIGHT, ADH.DFO_PAIR_IDLE_HEIGHT, ADH.DFO_PAIR_TOTAL_HEIGHT, ADH.DFO_PAIR_CPU_ACTIVITY, ADH.DFO_PAIR_TOTAL_SECONDS FROM (SELECT ADH_1.*, (ROW_NUMBER() OVER(PARTITION BY ADH_1.PLSQL_OBJECT_ID, ADH_1.PLSQL_SUBPROGRAM_ID ORDER BY ADH_1.BUCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) PLSQL_ROWNUM, (ROW_NUMBER() OVER(PARTITION BY ADH_1.SQL ORDER BY A DH_1.BUCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) SQL_ROWNUM, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP - ADH_1.DFO_PAIR_TOTAL_HEIGHT ELSE 0 END) DFO_PAIR_IDLE_HEIGHT, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP ELSE ADH_1.DFO_PAIR_TOTAL_HEIGHT END) DFO_PAIR_TOTAL_HEIGHT_ADJ FROM (SELECT ADH_0.*, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_TOTAL_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_TOTAL_HEIGHT, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_CPU_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_CPU_HEIGHT, (CASE WHEN ADH_0.PX_DFO_PAIR_ID IS NOT NULL AND ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.ACTIVITY_COUNT * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECON DS ELSE 0 END) DFO_PAIR_ACTIVITY_HEIGHT FROM (SELECT AD3.*, (SUM(DECODE(AD3.PX_DFO_PAIR_ID, NULL, 0, AD3.ACTIVE_SECONDS)) OVER(PARTITION BY DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.BUCKET_NUM), DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.PX_STEP_ARG))) DFO_PAIR_TOTAL_SECONDS FROM (SELECT AD2.*, CASE WHEN AD2.IS_MONITORED_SQL = 0 THEN SQL_BUCKET_DISTINCT_SAMPLES * :B25 WHEN AD2.PX_ID IS NULL THEN AD2.ACTIVITY_COUNT * :B25 WHEN AD2.BUCKET_PARALLEL_MON_ACTIVITY > 0 THEN (AD2.ACTIVITY_COUNT * AD2.BUCKET_PARALLEL_MON_SECONDS) / AD2.BUCKET_PARALLEL_MON_ACTIVITY ELSE 0 END ACTIVE_SECONDS, CASE WHEN AD2.BUCKET_INTERVAL > BUCKET_ACTIVE_SECONDS THEN AD2.BUCKET_INTERVAL - BUCKET_ACTIVE_SECONDS ELSE 0 END BUCKET_IDLE_SECONDS FROM (SELECT AD1.*, (AD1.BUCKET_SERIAL_MON_ACTIVITY * :B25 ) BUCKET_SERIAL_MON_SECONDS, (AD1.BUCKET_TOTAL_MON_ACTIVITY - AD1.BUCKET_SERIAL_MON_ACTIVITY) BUCKET_PARALLEL_MON_ACTIVITY, (AD1.BUCKET_ACTIVE_SECONDS - (AD1.BUCKET_OTHER_ACTIVITY + AD1.BUCKET_SE RIAL_MON_ACTIVITY) * :B25 ) BUCKET_PARALLEL_MON_SECONDS, (AD1.BUCKET_OTHER_ACTIVITY * :B25 ) BUCKET_OTHER_SECONDS, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(AD1.ACTIVITY_COUNT) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_TOTAL_ACTIVITY, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(DECODE(AD1.ACTIVITY_TYPE, 'Cpu', AD1.ACTIVITY_COUNT, 0)) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_CPU_ACTIVITY FROM (SELECT AD01.*, NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 1, AD01.ACTIVITY_COUNT, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0) BUCKET_TOTAL_MON_ACTIVITY, (NVL(SUM(CASE WHEN AD01.IS_MONITORED_SQL = 1 AND AD01.PX_ID IS NULL THEN AD01.ACTIVITY_COUNT ELSE NULL END) OVER(PARTITION BY AD01.BUCKET_NUM), 0)) BUCKET_SERIAL_MON_ACTIVITY, (NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 0, AD01.SQL_BUCKET_DISTINCT_SAMPLES, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0)) BUCKET_OTHER_ACTIVITY, (NVL(AD01.BUCKET_DISTINCT_SAMPLES, 0) * :B25 ) BUCKET_ACTIVE_SECONDS, DECODE(AD01.BUCKET_NUM, :B27 , MOD(:B32 , :B26 ), :B26 ) BUCKET_INTERVAL FROM (SELECT AD0.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.PLAN_LINE_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, MAX(AD0.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(AD0.SQL) SQL, MAX(AD0.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(AD0.DBOP_NAME) DBOP_NAME, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_IID)) DFO_MOST_ACTIVE_IID, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_SID)) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT, MAX(PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(PX_DFO_PAIR_ID) PX_DFO_PAIR_I D, MAX(AD0.PX_DOP) PX_DOP, MIN(AD0.PX_MIN_DOP) PX_MIN_DOP, MAX(AD0.BUCKET_DISTINCT_SAMPLES) BUCKET_DISTINCT_SAMPLES, MAX(AD0.SQL_BUCKET_DISTINCT_SAMPLES) SQL_BUCKET_DISTINCT_SAMPLES FROM (SELECT AD00.*, (CASE WHEN AD00.IS_MONITORED_SQL = 1 AND (AD00.SESSION_ID != :B19 OR AD00.INST_ID != :B17 ) THEN AD00.PX_SERVER_GROUP END) PX_ID FROM ASH_DATA AD00 WHERE :B33 = 1 ) AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG ) AD01) AD1) AD2) AD3) ADH_0) ADH_1) ADH) SELECT /*+ no_monitor no_xml_query_rewrite opt_param('_gby_hash_aggregation_enabled', 'false') */ XMLELEMENT( "sql_monitor_report", XMLATTRIBUTES(:B59 AS "version", TO_CHAR(:B58 , :B12 ) AS "sysdate"), XMLELEMENT( "report_parameters", NULL, CASE WHEN :B57 IS NOT NULL THEN XMLFOREST( :B57 AS "dbop _name", :B56 AS "dbop_exec_id") ELSE XMLFOREST( :B55 AS "sql_id", :B54 AS "sql_exec_id") END, XMLFOREST( :B53 AS "session_id", :B52 AS "session_serial", TO_CHAR(:B51 , :B12 ) AS "sql_exec_start", :B27 AS "bucket_count", TO_CHAR(:B50 , :B12 ) AS "interval_start", TO_CHAR(:B22 , :B12 ) AS "interval_end", DECODE(:B49 , 'Y', :B48 , NULL) AS "auto_refresh", :B47 AS "base_path"), CASE WHEN :B23 = 'Y' AND :B46 IS NOT NULL AND NOT (:B4 = 1 AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) THEN XMLELEMENT( "parallel_filter", NULL, XMLFOREST( DECODE(:B4 , 1, 'yes', 'no') AS "qc", :B3 AS "server_group", :B2 AS "server_set", :B1 AS "server_number")) ELSE NULL END), CASE WHEN :B14 = 1 THEN XMLELEMENT( "derived_parameters_testing", NULL, XMLFOREST(:B11 AS "instance_id_low", :B10 AS "in stance_id_high", :B26 AS "bucket_interval_sec", :B32 AS "interval_second", :B27 AS "bucket_calc_count", :B45 AS "bucket_calc_max_count", :B13 AS "sel_sqltext")) ELSE NULL END, (SELECT XMLCONCAT( CASE WHEN :B46 IS NULL AND :B67 IS NULL THEN XMLELEMENT( "target", XMLATTRIBUTES(:B17 AS "instance_id", :B19 AS "session_id", :B18 AS "session_serial", NVL2(:B6 , NULL, :B9 ) AS "sql_id", NVL2(:B6 , NULL, TO_CHAR(:B8 , :B12 )) AS "sql_exec_start", NVL2(:B6 , NULL, :B7 ) AS "sql_exec_id", NVL2(:B6 , :B6 , NULL) AS "dbop_name", NVL2(:B6 , :B5 , NULL) AS "dbop_exec_id", NVL2(:B6 , TO_CHAR(:B8 , :B12 ), NULL) AS "dbop_exec_start", NVL2(:B6 , NULL, MAX_PLAN_HASH_VALUE) AS "sql_plan_hash", NVL2(:B6 , NULL, MAX_FULL_PLAN_HASH_VALUE) AS "sql_full_plan_hash", MAGG.DB_UNIQUE_NAME AS "db_uniqu e_name", MAGG.PLATFORM_NAME AS "db_platform_name", MAGG.HOST_NAME AS "report_host_name"), NVL2(SUM_SERVERS_REQUESTED, XMLELEMENT( "servers_requested", NULL, SUM_SERVERS_REQUESTED), NULL), NVL2(SUM_SERVERS_ALLOCATED, XMLELEMENT( "servers_allocated", NULL, SUM_SERVERS_ALLOCATED), NULL), NVL2(MAX_USERID, XMLELEMENT( "user_id", NULL, MAX_USERID), NULL), NVL2(MAX_USERNAME, XMLELEMENT( "user", NULL, MAX_USERNAME), NULL), NVL2(MAX_CON_ID, XMLELEMENT( "con_id", NULL, MAX_CON_ID), NULL), NVL2(MAX_CON_NAME, XMLELEMENT( "con_name", NULL, MAX_CON_NAME), NULL), NVL2(MAX_PROGRAM, XMLELEMENT( "program", NULL, MAX_PROGRAM), NULL), NVL2(MAX_MODULE, XMLELEMENT( "module", NULL, MAX_MODULE), NULL), NVL2(MAX_ACTION, XMLELEMENT( "action", NULL, MAX_ACTION), NULL), NVL2(MAX_SERVICE_NAME, XMLELEMENT( "service", NULL, MAX_SERVICE_NAME), NULL), NVL2(MAX_CLIENT_ID, XMLELEMENT( "client_id", NULL, MAX_CLIENT_ID), NULL), NVL2(MAX_CLIENT_INFO, XMLELEMENT( "client_info", NULL, MAX_CLIENT_INFO), NULL), NVL2(MAX_PL_ENTRY_OID, XMLELEMENT( "plsql_entry_object_id", NULL, MAX_PL_ENTRY_OID), NULL), NVL2(MAX_PL_ENTRY_PROGID, XMLELEMENT( "plsql_entry_subprogram_id", NULL, MAX_PL_ENTRY_PROGID), NULL), NVL2(MAX_PL_ENTRY_NAME, XMLELEMENT( "plsql_entry_name", NULL, MAX_PL_ENTRY_NAME), NULL), NVL2(MAX_PL_OID, XMLELEMENT( "plsql_object_id", NULL, MAX_PL_OID), NULL), NVL2(MAX_PL_PROGID, XMLELEMENT( "plsql_subprogram_id", NULL, MAX_PL_PROGID), NULL), NVL2(MAX_PL_NAME, XMLELEMENT( "plsql_name", NULL, MAX_PL_NAME), NULL), CASE WHEN (:B13 = 0 OR :B6 IS NOT NULL) THEN NULL ELSE XMLELEMENT( "sql_fulltext", XMLATTRIBUTES( NVL2(:B65 , 'Y', :B66 ) AS "is_full"), NVL2(:B65 , :B65 , SQLMON_TEXT)) END, XML ELEMENT( "status", NULL, MAX_STATUS), XMLELEMENT( "refresh_count", NULL, SUM_REFRESH_COUNT), XMLELEMENT( "first_refresh_time", NULL, TO_CHAR(MIN_FIRST_REFRESH_TIME, :B12 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B58 , :B12 )), XMLELEMENT( "duration", NULL, GREATEST(:B64 , LEAST(MAX_ELAPSED_TIME/1000000, 1), CEIL(MAX_QUEUING_TIME/1000000))), DECODE(MAX_RM_INFO, '00/00/0000 00:00:00', NULL, XMLTYPE(SUBSTR(MAX_RM_INFO, 20))), CASE WHEN (:B62 = 'Y') THEN XMLELEMENT( "adaptive_plan", XMLATTRIBUTES(:B63 AS "is_final"), :B62 ) ELSE NULL END ) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), DECODE(NVL(SUM_ELAPSED_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), SUM_ELAPSED_TIME)), DECODE(NVL(MAX_QUEUING_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "n ame"), MAX_QUEUING_TIME)), DECODE(NVL(SUM_CPU_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), SUM_CPU_TIME)), DECODE(NVL(SUM_USER_IO_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), SUM_USER_IO_WAIT_TIME)), DECODE(NVL(SUM_APPLICATION_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), SUM_APPLICATION_WAIT_TIME)), DECODE(NVL(SUM_CONCURRENCY_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), SUM_CONCURRENCY_WAIT_TIME)), DECODE(NVL(SUM_CLUSTER_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), SUM_CLUSTER_WAIT_TIME)), DECODE(NVL(SUM_PLSQL_EXEC_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), SUM_PLSQL _EXEC_TIME)), DECODE(NVL(SUM_JAVA_EXEC_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), SUM_JAVA_EXEC_TIME)), DECODE(NVL(SUM_OTHER_WAIT_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), SUM_OTHER_WAIT_TIME)), DECODE(NVL(SUM_FETCHES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), SUM_FETCHES)), DECODE(NVL(SUM_BUFFER_GETS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), SUM_BUFFER_GETS)), DECODE(NVL(SUM_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('r ead_reqs' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_READ_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), SUM_READ_BYTES)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_WRITE_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), SUM_WRITE_BYTES)), DECODE(NVL(SUM_IO_UNC_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), SUM_IO_UNC_BYTES)), DECODE(NVL(SUM_IO_ELIG_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), SUM_IO_ELIG_BYTES)), DECODE(NVL(SUM_IO_RET_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), SUM_IO_RET_BYTES)), CASE WHEN SUM_IO_INTER_BYTES IS NULL OR SUM_IO_BYTES = 0 OR SUM_IO_INT ER_BYTES = SUM_IO_BYTES OR NVL(SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(SUM_IO_BYTES / DECODE(SUM_IO_INTER_BYTES, 0, 1, SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(SUM_IO_ELIG_BYTES, 0) = 0 OR SUM_IO_BYTES = 0 OR SUM_IO_UNC_BYTES = SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST(SUM_IO_UNC_BYTES, SUM_IO_ELIG_BYTES) - SUM_IO_RET_BYTES) / SUM_IO_ELIG_BYTES, 2))) END), CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN SUM(ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME)) ELSE NULL END FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) ELSE NULL END, NVL2(MAX_ERROR_NUMBER, XMLELEMENT( "error", XMLATTRIBUTES(MAX_ERROR_NUMBER AS "number", MAX_ERROR_FACILITY AS "facility"), MAX_ERROR_MESSAGE), NULL), CASE WHEN :B61 = 1 AND MAGG.QC_HAS_BINDS_XML = 'Y' THEN (SELECT XMLTYPE(BINDS_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = :B17 AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END, CASE WHEN :B60 = 1 AND MAGG.QC_HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = MAGG.MAX_INST_ID AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) FROM MONITOR_AGG MAGG), CASE WHEN :B44 = 1 THEN (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 T HEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( :B41 AS "ash_missing_seconds", TO_CHAR(AT.ACTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AT.ACTIVITY_END, :B12 ) AS "last_sample_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", AT.ACTIVITY_COUNT AS "count", AT.IMQ_COUNT AS "imq_count", AT.WAIT_COUNT AS "wait_count", AT.CPU_COUNT AS "cpu_count", DECODE(AT.OTHER_SQL_COUNT, 0, NULL, AT.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), AT.ACTIVITY_TOTAL, AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) ELSE NULL END FROM (SELECT MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_ COUNT, SUM(AD1.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT CASE WHEN :B30 = 1 AND :B27 > 1 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "bucket_interval", :B27 AS "bucket_count ", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", ROUND(( :B22 - :B20 ) *3600*24) + 1 AS "duration"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(IMQ_COUNT) IMQ_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, SUM(CPU_COUNT) CPU_COUNT, SUM(OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NA ME) ACTIVITY_BUCKET FROM (SELECT AD0.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN AH.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", :B41 AS "ash_missing_seconds", TO_CHAR(AH.ACTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AH.ACTIVITY_END, :B12 ) AS "last _sample_time", ROUND((AH.ACTIVITY_END - AH.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", :B25 AS "sample_interval", :B26 AS "bucket_interval", :B27 AS "bucket_count", ROUND((:B22 - :B20 ) *3600*24) + 1 AS "bucket_duration", :B40 AS "cpu_cores", :B34 AS "total_cpu_cores", :B39 AS "hyperthread"), AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) ELSE NULL END FROM (SELECT MIN(AD2.ACTIVITY_START) ACTIVITY_START, MAX(AD2.ACTIVITY_END) ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET_XML) ORDER BY AD2.BUCKET_NUM) GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, S UM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, MAX(AD1.BUCKET_IDLE_SECONDS) BUCKET_IDLE_SECONDS, XMLAGG( XMLCONCAT( CASE WHEN AD1.DFO_PAIR_IDLE_RESP_TIME != 0 AND DFO_PAIR_ROWNUM = 1 THEN XMLELEMENT( "activity", XMLATTRIBUTES( 'Parallel Skew' AS "class", AD1.PX_STEP_ARG AS "line", AD1.PX_ID AS "px", ROUND(AD1.DFO_PAIR_IDLE_RESP_TIME, 2) AS "rt"), 0) ELSE NULL END, XMLELEMENT( "activity", XMLATTRIBUTES( NVL(AD1.OTHER_SQL, AD1.RPI) AS "sql", AD1.NON_SQL AS "non_sql", AD1.CLASS AS "class", AD1.OTHER_SQL_CLASS AS "other_sql_class", AD1.EVENT AS "event", AD1.PLAN_LINE_ID AS "line", NVL2(AD1.PLSQL_OBJECT_ID, AD1.PLSQL_OBJECT_ID||'.'|| AD1.PLSQL_SUBPROGRAM_ID, NULL) AS "plsql_id", AD1.PLSQL_NAME AS "plsql_name", CASE WHEN AD1.SQL_ROWNUM = 1 THEN AD1.TOP_LEVEL_SQL_ID END AS "top_sql_id", CASE WHEN AD1.DBOP_NAME IS NOT NULL THEN AD1. DBOP_NAME END AS "dbop_name", CASE WHEN AD1.DFO_MOST_ACTIVE_IID IS NOT NULL AND :B68 = 'Y' THEN AD1.DFO_MOST_ACTIVE_IID END AS "skew_iid", DECODE(AD1.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD1.DFO_MOST_ACTIVE_SID) AS "skew_sid", AD1.DFO_MOST_ACTIVE_COUNT AS "skew_count", DECODE(AD1.PX_DOP, :B28 , NULL, AD1.PX_DOP) AS "dop", DECODE(AD1.PX_DOP, AD1.PX_MIN_DOP, NULL, AD1.PX_MIN_DOP) AS "min_dop", AD1.PX_ID AS "px", AD1.PX_STEP_ID AS "step", AD1.PX_STEP_ARG AS "arg", DECODE(AD1.ACTIVITY_COUNT, AD1.RESP_TIME, NULL, ROUND(AD1.RESP_TIME, 2)) AS "rt"), AD1.ACTIVITY_COUNT)) ORDER BY AD1.PX_STEP_ID, AD1.PX_STEP_ARG, AD1.DFO_PAIR_ROWNUM) ACTIVITY_BUCKET_XML FROM (SELECT AD01.*, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execution' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.ACTIVITY_TYPE END CLASS, CASE WHEN (AD01.ACTIVITY_TYPE = 'Other SQL Execution' OR AD01.ACTIVITY_TYPE = 'Non SQL') THEN AD01.OTHER_SQL_ACTIVITY_TYPE END OTHER_SQL_CLASS, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execution' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.EVENT_NAME END EVENT, CASE WHEN AD01.SQL IN ('this', 'anonymous') THEN NULL ELSE AD01.SQL END RPI, DECODE(AD01.ACTIVITY_TYPE, 'Other SQL Execution', SUBSTR(AD01.EVENT_NAME, 9), NULL) OTHER_SQL, DECODE(AD01.ACTIVITY_TYPE, 'Non SQL', AD01.EVENT_NAME, NULL) NON_SQL, ROW_NUMBER() OVER(PARTITION BY AD01.BUCKET_NUM, AD01.PX_DFO_PAIR_ID ORDER BY AD01.ACTIVITY_TYPE, AD01.EVENT_NAME, AD01.PLAN_LINE_ID) DFO_PAIR_ROWNUM FROM RESPONSE_TIME_DATA AD01) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B23 = 'Y' THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B17 AS "qc_instance_id", MAX_PX_QCSID AS "qc_session_id", MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MAX_PX_DOP AS "dop", MAX_PX_DOP_INSTANCES AS "max_dop_instances", DI ST_INST_COUNT AS "inst_count", DIST_PX_GROUP_COUNT AS "server_group_count", DIST_PX_SET_COUNT AS "server_set_count"), CASE WHEN :B70 = 1 THEN PX_SESSIONS ELSE NULL END, CASE WHEN :B68 = 'Y' THEN DECODE(:B69 , 1, PX_INSTANCES, NULL) ELSE NULL END) FROM (SELECT MAX_PX_QCSID, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.IMQ_COUNT) AS "max_imq_count", MAX(PX_SESSION.CPU_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_SESSION.MAX_IO_REQS) AS "max_io_reqs", MAX(PX_SESSION.MAX_IO_BYTES) AS "max_io_bytes", MAX(PX_SESSION.MAX_BUFFER_GETS) AS "max_buffer_gets", MAX(PX_SESSION.MAX_ELAPSED_TIME) AS "max_elapsed_time"), XMLAGG(PX_SESSION.PX_SESSION_XML ORDER BY PX_SERVER_GROUP NULLS FIRST, PX_SERVER_SET, PX_SERVER#)) FROM (SELECT PX_SERVER_GROUP, PX_SERVER_SET, PX_SERVER#, MAX(PI.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(PI.MAX_IO_REQS) MAX_IO_REQS, MAX(PI.MAX_IO_BYTES) MAX_IO_BYTES, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "process_name", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), M AX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL 2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX (IO_INTER_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES) / DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST( NVL(MAX(IO_UNC_BYTES), 0), NVL(MAX(IO_ELIG_BYTES), 0)) - MAX(IO_RET_BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN SUM(PI.ACTIVITY_COUNT) > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "first_sample_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "last_sample_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", S UM(PI.ACTIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.OTHER_SQL_COUNT)AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME)) ELSE NULL END, CASE WHEN :B60 = 1 AND PI.HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$ALL_SQL_MONITOR MON WHERE MON.INST_ID = PI.INST_ID AND MON.KEY = PI.KEY AND MON.SID = PI.SID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) PX_SESSION_XML FROM (SELECT MO.HAS_OTHER_XML, MO.KEY, MO.INST_ID, DECODE(MO.PROCESS_NAME, 'ora', 'PX Coordinator', MO.PROCESS_NAME) PROCESS_NAME, MO.SID, MO.SES SION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, MAX(MO.IO_REQS) MAX_IO_REQS, MAX(MO.IO_BYTES) MAX_IO_BYTES, MAX(MO.BUFFER_GETS) MAX_BUFFER_GETS, MAX(MO.ELAPSED_TIME) MAX_ELAPSED_TIME, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.ACTIVITY_COUNT)) ACTIVITY_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.IMQ_COUNT)) IMQ_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.CPU_COUNT)) CPU_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.OTHER_SQL_COUNT)) OTHER_SQL_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.QUEUING_TIME, 0, NULL, MO.QUEUING_TIME)) QUEUING_TIME, MAX(DECODE(MO.CPU_TIME, 0, NULL, CPU_TIME)) CPU_TIME, MAX(DECODE(MO.FETCHES, 0, NULL, FETCHES)) FETCHES, MAX(DECODE(M O.BUFFER_GETS, 0, NULL, MO.BUFFER_GETS)) BUFFER_GETS, MAX(DECODE(MO.IO_INTER_BYTES, 0, NULL, MO.IO_INTER_BYTES)) IO_INTER_BYTES, MAX(DECODE(MO.READ_REQS, 0, NULL, MO.READ_REQS)) READ_REQS, MAX(DECODE(MO.READ_BYTES, 0, NULL, MO.READ_BYTES)) READ_BYTES, MAX(DECODE(MO.WRITE_REQS, 0, NULL, MO.WRITE_REQS)) WRITE_REQS, MAX(DECODE(MO.WRITE_BYTES, 0, NULL, MO.WRITE_BYTES)) WRITE_BYTES, MAX(DECODE(MO.IO_BYTES, 0, NULL, MO.IO_BYTES)) IO_BYTES, MAX(DECODE(MO.IO_UNC_BYTES, 0, NULL, MO.IO_UNC_BYTES)) IO_UNC_BYTES, MAX(DECODE(MO.IO_ELIG_BYTES, 0, NULL, MO.IO_ELIG_BYTES)) IO_ELIG_BYTES, MAX(DECODE(MO.IO_RET_BYTES, 0, NULL, MO.IO_RET_BYTES)) IO_RET_BYTES, MAX(DECODE(MO.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TIME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WA IT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, (SELECT ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, SUM(ASH1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH1.IMQ_COUNT) IMQ_COUNT, SUM(ASH1.WAIT_COUNT) WAIT_COUNT, SUM(ASH1.CPU_COUNT) CPU_COUNT, SUM(ASH1.OTHER_SQL_COUNT)OTHER_SQL_COUNT, MIN(ASH1.ACTIVITY_START) ACTIVITY_START, MAX(ASH1.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH1 GROUP BY ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B70 = 1 OR :B69 = 1) GROUP BY MO.INST_ID, MO.KEY, MO.HAS_OTHER_XML, MO.PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, M O.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) PI WHERE (:B70 = 1) GROUP BY PI.INST_ID, PI.KEY, PI.HAS_OTHER_XML, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_SERVER_SET, PI.PX_SERVER#) PX_SESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.IMQ_COUNT) AS "max_imq_count", MAX(PX_INSTANCE.CPU_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_REQS) AS "max_io_reqs", MAX(PX_INSTANCE.IO_BYTES) AS "max_io_bytes"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_REQS) IO_RE QS, MAX(PI.IO_BYTES) IO_BYTES, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_COUNT, XMLELEMENT( "instance", XMLATTRIBUTES( INST_ID AS "inst_id"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT ( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL) , NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_b ytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES)/ DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (GREATEST( NVL(MAX(IO_UNC_BYTES), 0), NVL(MAX(IO_ELIG_BYTES), 0)) - MAX(IO_RET_ BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN :B33 = 1 AND SUM(PI.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME)) ELSE NULL END) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, AS H.EVENT_NAME, ASH.ACTIVITY_COUNT, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.QUEUING_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.USER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.IO_INTER_BYTES, MO.IO_BYTES, MO.READ_REQS, MO.READ_BYTES, MO.WRITE_REQS, MO.WRITE_BYTES, MO.IO_REQS, MO.IO_UNC_BYTES, MO.IO_ELIG_BYTES, MO.IO_RET_BYTES FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.QUEUING_TIME) QUEUING_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.IO_INTER_BYTES) IO_INTER_BYTES, SUM(MO0.IO_BYTES) IO_BYTES, SUM(MO0.READ_REQS) READ_REQS, SUM(MO0.READ_BYTES) READ_BYTES, SUM(MO0.WRITE_REQS) WRITE_REQS, SUM(MO0.WRITE_BYTES) WRITE_BYTES, SUM(MO0.IO_REQS) IO_REQS, SUM(MO0.IO_UNC_BYTES) IO_U NC_BYTES, SUM(MO0.IO_ELIG_BYTES) IO_ELIG_BYTES, SUM(MO0.IO_RET_BYTES) IO_RET_BYTES, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.IMQ_COUNT) IMQ_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, SUM(ASH0.CPU_COUNT) CPU_COUNT, SUM(ASH0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH0 GROUP BY ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) ASH, MONITOR_AGG WHERE MO.INST_ID = ASH.INST_ID(+) AND MONITOR_AGG.DIST_INST_COUNT > 0 ) PI GROUP BY PI.INST_ID)PX_INSTANCE) P X_INSTANCES FROM MONITOR_AGG)) ELSE NULL END, XPLAN_XML, CASE WHEN :B43 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(PLI.MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(PLI.OVERALL_MAX_IO_REQS) AS "max_io_reqs", MAX(PLI.OVERALL_MAX_IO_BYTES) AS "max_io_bytes", MAX(PLI.MAX_LINE_IMQ_COUNT) AS "max_imq_count", MAX(PLI.MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(PLI.MAX_LINE_WAIT_COUNT) AS "max_wait_count", MAX(PLI.MAX_LINE_OTHER_SQL_COUNT) AS "max_other_sql_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PLI.PLAN_LINE_ID AS "id", PLI.PARENT_ID AS "parent_id", PLI.OPERATION AS "name", PLI.OPTIONS AS "options", PLI.DEPTH AS "depth", PLI.POSITION AS "position", PLI.INACTIVE AS "skp", PLI.PX_TYPE AS "px_type"), NVL2(PLI.OBJECT_NAME, XMLELEMENT( "object", XMLATTRI BUTES(PLI.OBJECT_TYPE AS "type"), XMLFOREST(PLI.OBJECT_OWNER AS "owner"), XMLFOREST(PLI.OBJECT_NAME AS "name")), NULL), XMLFOREST(PLI.PARTITION_START AS "partition_start", PLI.PARTITION_STOP AS "partition_stop"), CASE WHEN PLI.CARDINALITY IS NULL AND PLI.BYTES IS NULL AND PLI.COST IS NULL AND PLI.TEMP_SPACE IS NULL AND PLI.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PLI.CARDINALITY, XMLFOREST(PLI.CARDINALITY AS "cardinality"), NULL), NVL2(PLI.BYTES, XMLFOREST(PLI.BYTES AS "bytes"), NULL), NVL2(PLI.COST, XMLFOREST(PLI.COST AS "cost"), NULL), NVL2(PLI.CPU_COST, XMLFOREST(PLI.CPU_COST AS "cpu_cost"), NULL), NVL2(PLI.IO_COST, XMLFOREST(PLI.IO_COST AS "io_cost"), NULL), NVL2(PLI.TEMP_SPACE, XMLFOREST(PLI.TEMP_SPACE AS "temp"), NULL), NVL2(PLI.TIME, XMLFOREST(PLI.TIME AS "time"), NULL)) END, XMLELEMENT( "stats ", XMLATTRIBUTES('plan_monitor' AS "type"), NVL2(PLI.FIRST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR(FIRST_MOVE_TIME, :B12 )), NULL), CASE WHEN PLI.FIRST_MOVE_TIME != PLI.FIRST_CHANGE_TIME THEN XMLELEMENT( "stat", XMLATTRIBUTES('first_row' AS "name"), TO_CHAR(FIRST_CHANGE_TIME, :B12 )) ELSE NULL END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR(LAST_MOVE_TIME, :B12 )), NULL), CASE WHEN (PLI.FIRST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND((LAST_MOVE_TIME - FIRST_MOVE_TIME) * 3600 * 24)+1) END, CASE WHEN (PLI.OVERALL_LAST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND((PLI.OVERALL_LAST_MOVE_TIME - PLI.LAST_MOVE_TIME) * 3600 * 24)) END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (FIRST_MOVE_TIME - :B8 ) * 3600*24)), NULL), NVL2(PLI.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIBUTES('percent_complete' AS "name"), PLI.PERCENT_COMPLETE), NULL), NVL2(PLI.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), PLI.TIME_REMAINING), NULL), CASE WHEN PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PLI.STARTS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_starts' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_STARTS/1000000), 10000), NULL) AS "iid", MOD(MAX_STARTS, 1000000) AS "sid"), TRUNC(PLI.MAX_STARTS/10000000000)) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXEC UTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('dop' AS "name"), PLI.DOP) ELSE NULL END, CASE WHEN NEED_ROWS IS NOT NULL AND PLI.FIRST_MOVE_TIME IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), PLI.OUTPUT_ROWS) ELSE NULL END, CASE WHEN PLI.NEED_ROWS IS NOT NULL AND PLI.DOP > 0 AND PLI.MAX_OUTPUT_ROWS IS NOT NULL AND (PLI.FIRST_MOVE_TIME IS NOT NULL) THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_card' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_OUTPUT_ROWS/1000000), 10000), NULL) AS "iid", MOD(MAX_OUTPUT_ROWS, 1000000) AS "sid"), TRUNC(PLI.MAX_OUTPUT_ROWS/10000000000)) ELSE NULL END, CASE WHEN PLI.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PLI.MEM) ELSE NULL END, CASE WHEN PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PLI.MAX_MEM) ELSE NULL END, CASE WH EN PLI.DOP > 0 AND PLI.MIN_MAX_MEM IS NOT NULL AND PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'min_max_mem' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MIN_MAX_MEM/1000000), 10000), NULL) AS "iid", MOD(MIN_MAX_MEM, 1000000) AS "sid"), TRUNC(PLI.MIN_MAX_MEM/10000000000)) ELSE NULL END, CASE WHEN PLI.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PLI.TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PLI.MAX_TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('spill_count' AS "name"), PLI.SPILL_COUNT) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_MAX_TEMP IS NOT NULL AND PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_max_temp' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_MAX_TEMP/1000000), 10000), NULL) AS "iid", MOD(MAX_MAX_TEMP, 1000000) AS "sid"), TRUNC(PLI.MAX_MAX_TEMP/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), PLI.READ_REQS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_REQS IS NOT NULL AND PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_reqs' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_READ_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), PLI.READ_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_BYTES IS NOT NULL AND PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_bytes' AS "name", DECODE(: B68 , 'Y', MOD(TRUNC(PLI.MAX_READ_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), PLI.WRITE_REQS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_REQS IS NOT NULL AND PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_write_reqs' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), PLI.WRITE_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_BYTES IS NOT NULL AND PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "st at", XMLATTRIBUTES( 'max_write_bytes' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('io_inter_bytes' AS "name"), PLI.IO_INTER_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_io_inter_bytes' AS "name", DECODE(:B68 , 'Y', MOD(TRUNC(MAX_IO_INTER_BYTES/1000000), 10000), NULL) AS "iid", MOD(MAX_IO_INTER_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_IO_INTER_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(PLI.IO_BYTES / DECODE(PLI.IO_INTER_BYTES, 0, 1, PLI.IO_INTER_BYTES), 2)) ELSE NULL END), NVL2(STAT_GID, XMLELEMENT( "rwsstats", XMLATTRIBUTES( PLI.STAT_GID AS "group_id"), DECODE(GID_ROWNUM, 1, (SELECT XMLELEMENT( "metadata", NULL, XMLAGG( XMLELEMENT( "stat", XMLATTRIBUTES( ROWNUM AS "id", NAME AS "name", DESCRIPTION AS "desc", TYPE AS "type", DECODE(FLAGS, 0, NULL, FLAGS) AS "flags"), NULL) ORDER BY ID)) FROM V$SQL_MONITOR_STATNAME WHERE GROUP_ID = PLI.STAT_GID), NULL), NVL2(STAT1_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(1 AS "id"), STAT1_VALUE), NULL), NVL2(STAT2_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(2 AS "id"), STAT2_VALUE), NULL), NVL2(STAT3_VALUE, XMLELEMENT( "sta t", XMLATTRIBUTES(3 AS "id"), STAT3_VALUE), NULL), NVL2(STAT4_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(4 AS "id"), STAT4_VALUE), NULL), NVL2(STAT5_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(5 AS "id"), STAT5_VALUE), NULL), NVL2(STAT6_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(6 AS "id"), STAT6_VALUE), NULL), NVL2(STAT7_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(7 AS "id"), STAT7_VALUE), NULL), NVL2(STAT8_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(8 AS "id"), STAT8_VALUE), NULL), NVL2(STAT9_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(9 AS "id"), STAT9_VALUE), NULL), NVL2(STAT10_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(10 AS "id"), STAT10_VALUE), NULL)), NULL), CASE WHEN PLI.LINE_ACTIVITY_COUNT > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(PLI.LINE_ACTIVITY_START , :B12 ) AS "start_time", TO_CHAR(PLI.LINE_ACTIVITY_END, :B12 ) AS "end_time", ROUND((PLI.LINE_ACTIVITY_END - PLI.LINE_ACTIVITY_START) * 3600*24) + 1 AS "duration", PLI.LINE_ACTIVITY_COUNT AS "count", PLI.LINE_IMQ_COUNT AS "imq_count", PLI.LINE_WAIT_COUNT AS "wait_count", PLI.LINE_CPU_COUNT AS "cpu_count", PLI.LINE_OTHER_SQL_COUNT AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), PLI.ACTIVITY_TOTAL, PLI.PLAN_ACTIVITY_HISTO) ELSE NULL END) ORDER BY PLI.PLAN_LINE_ID) ) FROM (SELECT AT.MAX_LINE_ACTIVITY_COUNT, PM.OVERALL_MAX_IO_REQS, PM.OVERALL_MAX_IO_BYTES, AT.MAX_LINE_IMQ_COUNT, AT.MAX_LINE_CPU_COUNT, AT.MAX_LINE_WAIT_COUNT, AT.LINE_OTHER_SQL_COUNT, AT.MAX_LINE_OTHER_SQL_COUNT, PM.PLAN_LINE_ID, PM.PARENT_ID, PM.OPERATION, PM.OPTIONS, PM.DEPTH, PM.POSITION, PM.INACTIVE, CASE WHEN PM.OPERATION = 'PX COORDINATOR' AND :B23 = 'Y' AND (PM.PX_SERVER_S ET IS NOT NULL OR AH.PX_SERVER_SET IS NOT NULL) THEN 'QC' WHEN PM.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(PM.PX_SERVER_SET) WHEN AH.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(AH.PX_SERVER_SET) WHEN (:B23 = 'N' OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END PX_TYPE, PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START, PM.LAST_CHANGE_TIME, PM.OVERALL_LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END, AT.OVERALL_LINE_ACTIVITY_END, LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) FIRST_MOVE_TIME, GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) LAST_MOVE_TIME, GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) OVERALL_LAST_MOVE_TIME, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN 1 ELSE 0 END HAS_EXECUTED, PM.OBJECT_NAME, PM.OBJECT_TYPE, P M.OBJECT_OWNER, PM.PARTITION_START, PM.PARTITION_STOP, PM.CARDINALITY, PM.BYTES, PM.COST, PM.TEMP_SPACE, PM.TIME, PM.CPU_COST, PM.IO_COST, LO.PERCENT_COMPLETE, LO.TIME_REMAINING, PM.STARTS, PM.DOP, PM.MAX_STARTS, PM.OUTPUT_ROWS, PM.NEED_ROWS, PM.MAX_OUTPUT_ROWS, PM.MEM, PM.MAX_MEM, PM.MIN_MAX_MEM, PM.TEMP, PM.MAX_TEMP, PM.SPILL_COUNT, PM.MAX_MAX_TEMP, PM.READ_REQS, PM.MAX_READ_REQS, PM.READ_BYTES, PM.MAX_READ_BYTES, PM.WRITE_REQS, PM.MAX_WRITE_REQS, PM.WRITE_BYTES, PM.MAX_WRITE_BYTES, PM.IO_INTER_BYTES, PM.IO_BYTES, PM.MAX_IO_INTER_BYTES, AT.LINE_ACTIVITY_COUNT, AT.LINE_IMQ_COUNT, AT.LINE_WAIT_COUNT, AT.LINE_CPU_COUNT, AT.ACTIVITY_TOTAL, AH.PLAN_ACTIVITY_HISTO, PM.STAT_GID, PM.GID_ROWNUM, PM.STAT1_VALUE, PM.STAT2_VALUE, PM.STAT3_VALUE, PM.STAT4_VALUE, PM.STAT5_VALUE, PM.STAT6_VALUE, PM.STAT7_VALUE, PM.STAT8_VALUE, PM.STAT9_VALUE, PM.STAT10_VALUE FROM (SELECT AT0.*, MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_A CTIVITY_COUNT) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_IMQ_COUNT) OVER() MAX_LINE_IMQ_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT, MAX(LINE_OTHER_SQL_COUNT) OVER() MAX_LINE_OTHER_SQL_COUNT FROM (SELECT AD1.PLAN_LINE_ID, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) LINE_ACTIVITY_COUNT, SUM(AD1.IMQ_COUNT) LINE_IMQ_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.OTHER_SQL_COUNT) LINE_OTHER_SQL_COUNT, SUM(AD1.CPU_COUNT) LINE_CPU_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, M AX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MAX(AD0.DFO_MOST_ACTIVE_IID) DFO_MOST_ACTIVE_IID, MAX(AD0.DFO_MOST_ACTIVE_SID) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT FROM ASH_DATA AD0 WHERE AD0.PLAN_LINE_ID IS NOT NULL GROUP BY AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID) AT0) AT, (SELECT AD2.PLAN_LINE_ID, MIN(AD2.PX_SERVER_SET) PX_SERVER_SET, MIN(AD2.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD2.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD2.IMQ_COUNT) IMQ_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, SUM(AD2.CPU_COUNT) CPU_COUNT, SUM(AD2.OTHER_SQL_COUNT) OTHER_SQL_COUNT, CASE WHEN :B29 = 1 AND SUM(AD2.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "bucket_interval", :B27 AS "bucket_count", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR(:B22 , :B12 ) AS "end_time", ROUND((:B22 -:B20 ) *3600*24) + 1 AS "duration"), XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END PLAN_ACTIVITY_HISTO FROM (SELECT AD1.PLAN_LINE_ID, AD1.BUCKET_NUM, MIN(AD1.PX_SERVER_SET) PX_SERVER_SET, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(NVL2(AD1.ACTIVITY_TYPE, XMLELEMENT ( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT), NULL) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(NVL2(AD0.ACTIVITY_START, AD0.PX_SERVER_SET, NULL)) PX_SERVER_SET, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID, AD1.BUCKET_NUM) AD2 GROUP BY AD2.PLAN_LINE_ID) AH, (SELECT LO2.SQL_PLAN_LINE_ID PLAN_LINE_ ID, DECODE(SUM(LO2.TOTALWORK), 0, NULL, ROUND(SUM(LO2.SOFAR)*100/SUM(LO2.TOTALWORK))) PERCENT_COMPLETE, MAX(LO2.TIME_REMAINING) TIME_REMAINING FROM MONITOR_DATA MO, (SELECT * FROM TABLE( GV$(CURSOR( SELECT USERENV('instance') INST_ID, SQL_PLAN_LINE_ID, TOTALWORK, SOFAR, TIME_REMAINING, SID FROM V$SESSION_LONGOPS L WHERE (:B49 = 'Y' OR :B71 = 'DONE (ERROR)') AND L.SQL_ID = :B9 AND L.SQL_EXEC_START = :B8 AND L.SQL_EXEC_ID = :B7 )))) LO2 WHERE LO2.INST_ID = MO.INST_ID AND LO2.SID = MO.SID GROUP BY LO2.SQL_PLAN_LINE_ID ) LO, (SELECT PM0.*, CASE WHEN PM0.STARTS IS NULL OR PM0.STARTS = 0 OR PM0.OUTPUT_ROWS IS NULL THEN NULL ELSE 1 END NEED_ROWS, ROW_NUMBER() OVER(PARTITION BY PM0.STAT_GID ORDER BY PM0.PLAN_LINE_ID ) GID_ROWNUM, MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME, MAX(MAX_IO_REQS) OVER() OVERALL_MAX_IO_REQS, MAX(MAX_IO_BYTES) OVER() OVERALL_MAX_IO_BYTES FROM (SELECT /*+ leading(mo) use_hash(plm) */ PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATIO N, PLM.PLAN_OPTIONS OPTIONS, MAX(PLM.PLAN_PARENT_ID) PARENT_ID, MAX(PLM.PLAN_DEPTH) DEPTH, MAX(PLM.PLAN_POSITION) POSITION, MAX(PLM.PLAN_OPERATION_INACTIVE) INACTIVE, MAX(PLM.PLAN_OBJECT_OWNER) OBJECT_OWNER, MAX(PLM.PLAN_OBJECT_NAME) OBJECT_NAME, MAX(PLM.PLAN_OBJECT_TYPE) OBJECT_TYPE, MAX(PLM.PLAN_COST) COST, MAX(PLM.PLAN_CARDINALITY) CARDINALITY, MAX(PLM.PLAN_BYTES) BYTES, MAX(PLM.PLAN_CPU_COST) CPU_COST, MAX(PLM.PLAN_IO_COST) IO_COST, MAX(PLM.PLAN_TEMP_SPACE) TEMP_SPACE, MAX(PLM.PLAN_TIME) TIME, MAX(PLM.PLAN_PARTITION_START) PARTITION_START, MAX(PLM.PLAN_PARTITION_STOP) PARTITION_STOP, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(PLM.LAST_CHANGE_TIME) MIN_LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, COUNT(CASE WHEN PLM.PAR IS NOT NULL AND PLM.STARTS IS NOT NULL AND PLM.STARTS > 0 AND PLM.PLAN_LINE_ID != 0 AND PLM.PLAN_OPERATION != 'PX COORDINATOR' THEN 1 ELSE NULL END) DOP, SUM(PLM.STARTS) STARTS, MAX(NVL2(PAR, PLM.STARTS * 10000000000 + PLM.ES, NULL)) MAX_STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, MAX(NVL2(PAR, PLM.OUTPUT_ROWS * 10000000000 +PLM.ES, NULL)) MAX_OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, MIN(NVL2(PAR, PLM.WORKAREA_MAX_MEM * 10000000000 + PLM.ES, NULL)) MIN_MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MAX_TEMP, MAX(NVL2(PAR, PLM.WORKAREA_MAX_TEMPSEG * 10000000000 + PLM.ES, NULL)) MAX_MAX_TEMP, COUNT(PLM.WORKAREA_MAX_TEMPSEG) SPILL_COUNT, SUM(PLM.PHYSICAL_READ_REQUESTS) READ_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_READ_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_READ_REQS, SUM(PLM.PHYSICAL_READ_BYTES) READ_BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_READ_BYTES * 10000000000 + PLM.ES, NULL)) MAX_READ_BYTES, SUM(PLM.PHYSICAL_WRITE_REQUESTS) WRITE_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_WRITE_REQS, SUM(PLM.PHYSICAL_WRITE_ BYTES) WRITE_BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_BYTES * 10000000000 + PLM.ES, NULL)) MAX_WRITE_BYTES, NVL(SUM(PLM.PHYSICAL_READ_BYTES), 0) + NVL(SUM(PLM.PHYSICAL_WRITE_BYTES), 0) IO_BYTES, SUM(NVL(PLM.PHYSICAL_READ_REQUESTS, 0) + NVL(PLM.PHYSICAL_WRITE_REQUESTS, 0)) MAX_IO_REQS, SUM(NVL(PLM.PHYSICAL_READ_BYTES, 0) + NVL(PLM.PHYSICAL_WRITE_BYTES, 0)) MAX_IO_BYTES, SUM(PLM.IO_INTERCONNECT_BYTES) IO_INTER_BYTES, MAX(NVL2(PAR, PLM.IO_INTERCONNECT_BYTES * 10000000000 + PLM.ES, NULL)) MAX_IO_INTER_BYTES, MAX(OTHERSTAT_GROUP_ID) STAT_GID, NVL(DECODE(MAX(OTHERSTAT_1_TYPE), 3, MAX(OTHERSTAT_1_VALUE), 4, MAX(OTHERSTAT_1_VALUE), 6, MIN(OTHERSTAT_1_VALUE), 7, MIN(OTHERSTAT_1_VALUE), NULL), SUM(DECODE(OTHERSTAT_1_TYPE, 1, OTHERSTAT_1_VALUE, 2, OTHERSTAT_1_VALUE, NULL))) STAT1_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_2_TYPE), 3, MAX(OTHERSTAT_2_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_2_VALUE), 7, MIN(RMAX1 + OTHERSTAT_2_VALUE), NU LL), 6, MIN(OTHERSTAT_2_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_2_TYPE, 1, OTHERSTAT_2_VALUE, 2, OTHERSTAT_2_VALUE, NULL))) STAT2_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_3_TYPE), 3, MAX(OTHERSTAT_3_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_3_VALUE), 7, MIN(RMAX1 + OTHERSTAT_3_VALUE), NULL), 6, MIN(OTHERSTAT_3_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_3_TYPE, 1, OTHERSTAT_3_VALUE, 2, OTHERSTAT_3_VALUE, NULL))) STAT3_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_4_TYPE), 3, MAX(OTHERSTAT_4_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_4_VALUE), 7, MIN(RMAX1 + OTHERSTAT_4_VALUE), NULL), 6, MIN(OTHERSTAT_4_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_4_TYPE, 1, OTHERSTAT_4_VALUE, 2, OTHERSTAT_4_VALUE, NULL))) STAT4_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_5_TYPE), 3, MAX(OTHERSTAT_5_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_5_VALUE), 7, MIN(RMAX1 + OTHERSTAT_5_VALUE), NULL), 6, MIN(OTHERSTAT_5_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_5_TYPE, 1, OTHERSTAT_5_VALUE, 2, OTHERSTAT_5_VALUE, NULL))) STAT5_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_6_TYPE), 3, MAX(OTHERSTAT_6_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_6_VALUE), 7, MIN(RMAX1 + OTHERSTAT_6_VALUE), NULL), 6, MIN(OTHERSTAT_6_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_6_TYPE, 1, OTHERSTAT_6_VALUE, 2, OTHERSTAT_6_VALUE, NULL))) STAT6_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_7_TYPE), 3, MAX(OTHERSTAT_7_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_7_VALUE), 7, MIN(RMAX1 + OTHERSTAT_7_VALUE), NULL), 6, MIN(OTHERSTAT_7_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_7_TYPE, 1, OTHERSTAT_7_VALUE, 2, OTHERSTAT_7_VALUE, NULL))) STAT7_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_8_TYPE), 3, MAX(OTHERSTAT_8_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MA X(RMAX1 + OTHERSTAT_8_VALUE), 7, MIN(RMAX1 + OTHERSTAT_8_VALUE), NULL), 6, MIN(OTHERSTAT_8_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_8_TYPE, 1, OTHERSTAT_8_VALUE, 2, OTHERSTAT_8_VALUE, NULL))) STAT8_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_9_TYPE), 3, MAX(OTHERSTAT_9_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_9_VALUE), 7, MIN(RMAX1 + OTHERSTAT_9_VALUE), NULL), 6, MIN(OTHERSTAT_9_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_9_TYPE, 1, OTHERSTAT_9_VALUE, 2, OTHERSTAT_9_VALUE, NULL))) STAT9_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_10_TYPE), 3, MAX(OTHERSTAT_10_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_10_VALUE), 7, MIN(RMAX1 + OTHERSTAT_10_VALUE), NULL), 6, MIN(OTHERSTAT_10_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_10_TYPE, 1, OTHERSTAT_10_VALUE, 2, OTHERSTAT_10_VALUE, NULL))) STAT10_VALUE FROM (SELECT A.*, OTHERSTAT_1_VALUE * 10000000000000000 RMAX1, A.I NST_ID * 1000000 + A.SID ES, DECODE(A.PROCESS_NAME, 'ora', NULL, 1) PAR FROM (SELECT * FROM TABLE(GV$(CURSOR( SELECT USERENV('instance') INST_ID, A0.* FROM V$ALL_SQL_PLAN_MONITOR A0 WHERE A0.SQL_ID = :B9 AND A0.SQL_EXEC_START = :B8 AND A0.SQL_EXEC_ID = :B7 AND USERENV('instance') BETWEEN :B11 AND :B10 ))) ) A ) PLM, MONITOR_DATA MO WHERE PLM.INST_ID = MO.INST_ID AND PLM.INST_ID BETWEEN :B11 AND :B10 AND PLM.KEY = MO.KEY GROUP BY PLM.PLAN_LINE_ID, PLM.PLAN_OPERATION, PLM.PLAN_OPTIONS) PM0) PM WHERE AH.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND AT.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND (:B21 IS NULL OR PM.PLAN_LINE_ID = :B21 )) PLI) ELSE NULL END, CASE WHEN :B42 = 1 THEN DBMS_SQLTUNE.BUILD_STASH_XML( SESSION_ID=>:B19 , SESSION_SERIAL=>:B18 , SESSION_INST_ID=>:B17 , PX_MODE=>'yes', START_TIME=>:B20 , END_TIME=>:B22 , MISSING_SECONDS=> :B41 , INSTANCE_LOW_FILTER=>:B11 , INSTANCE_HIGH_FILTER=>:B10 , BUCKET_MAX_C OUNT=>NULL, BUCKET_INTERVAL=>:B26 , REPORT_LEVEL=>'typical', CPU_CORES=>:B40 , IS_HYPER=>:B39 ) ELSE NULL END), (CASE WHEN :B38 = 1 THEN (SELECT XMLELEMENT( "skewed_sessions", XMLATTRIBUTES( DECODE(MIN(INST_ID), NULL, :B17 , MIN(INST_ID)) AS "min_iid", DECODE(MAX(INST_ID), NULL, :B17 , MAX(INST_ID)) AS "max_iid"), XMLAGG( XMLELEMENT( "s", XMLATTRIBUTES( INST_ID AS "i"), SESSION_ID) ORDER BY INST_ID, SESSION_ID)) FROM (SELECT SESS.* FROM (SELECT DECODE(AD.DFO_MOST_ACTIVE_IID, :B17 , NULL, AD.DFO_MOST_ACTIVE_IID) INST_ID, AD.DFO_MOST_ACTIVE_SID SESSION_ID FROM ASH_DATA AD WHERE AD.DFO_MOST_ACTIVE_COUNT IS NOT NULL GROUP BY AD.DFO_MOST_ACTIVE_IID, AD.DFO_MOST_ACTIVE_SID ORDER BY MAX(AD.DFO_MOST_ACTIVE_COUNT) DESC) SESS WHERE ROWNUM <= 100)) ELSE NULL END), (CASE WHEN :B37 = 1 THEN (SELECT XMLELEMENT( "report_repository_summary", CASE WHEN MA.MAX_DBOP_NAME IS NOT NULL THEN XMLELEMENT( "dbop", XMLATTRIBUTES('F' AS "detail", MA.MAX_DBOP_NAME AS "dbop_name", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "dbop_exec_start", MA.MAX_DBOP_EXEC_ID AS "dbop_exec_id"), XMLFOREST( MA.MAX_STATUS AS "status", TO_CHAR(NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME), :B12 )AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 )AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID AS "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACTION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_info", MA.MAX_PROGRAM AS "program", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances"), CASE WHEN MA.MAX_ERROR_NUMBER IS NULL THEN NULL ELSE XMLELEMENT( "error", XMLATTRIBUTES(MA.MAX_ERROR_NUMBER AS "number", MA.MAX_ERROR_FACILITY AS "facility"), MA.MAX_ERROR_MESSAGE) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME))*86400), LEAST(MA.SUM_ELAPSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000 ))), DECODE(MA.SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MA.SUM_USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT ( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_WAIT_TIME)), DECODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MA.SUM_READ_REQS)), DECOD E(MA.SUM_READ_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYTES OR MA.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_BYTES = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATES T(0, ROUND(100 * (GREATEST(MA.SUM_IO_UNC_BYTES, MA.SUM_IO_ELIG_BYTES) - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) ELSE XMLELEMENT( "sql", XMLATTRIBUTES( MA.MAX_SQL_ID AS "sql_id", TO_CHAR(MA.MAX_SQL_EXEC_START, :B12 ) AS "sql_exec_start", MA.MAX_SQL_EXEC_ID AS "sql_exec_id"), XMLFOREST(MA.MAX_STATUS AS "status", SUBSTR(MA.SQLMON_TEXT, 1, 100) AS "sql_text", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 ) AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID AS "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACT ION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_info", MA.MAX_PROGRAM AS "program", MA.MAX_PLAN_HASH_VALUE AS "plan_hash", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances", MA.SUM_SERVERS_REQUESTED AS "px_servers_requested", MA.SUM_SERVERS_ALLOCATED AS "px_servers_allocated"), XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- MA.MAX_SQL_EXEC_START) * 86400), LEAST(MA. SUM_ELAPSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000))), DECODE(MA.SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MA.SUM_USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_ TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_WAIT_TIME)), DECODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUT ES('read_reqs' AS "name"), MA.SUM_READ_REQS)), DECODE(MA.SUM_READ_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), DECODE(MA.SUM_IO_UNC_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MA.SUM_IO_UNC_BYTES)), DECODE(MA.SUM_IO_ELIG_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MA.SUM_IO_ELIG_BYTES)), DECODE(MA.SUM_IO_RET_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MA.SUM_IO_RET_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYT ES OR MA.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_BYTES = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100 * (GREATEST(MA.SUM_IO_UNC_BYTES, MA.SUM_IO_ELIG_BYTES) - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) END) FROM MONITOR_AGG MA) ELSE NULL END) FROM (SELECT CASE WHEN V1.XPLAN_XML IS NULL OR V1.XPLAN_XML.EXISTSNODE('/error') > 0 THEN NULL ELSE V1.XPLAN_XML END XPLAN_XML FROM (SELECT CASE WHEN :B36 = 1 THEN DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$all_sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE') ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW
0zy73fwdbj3xm	delete from SessionInfo where id=:1 and OPTLOCK=:2
14gjtb4v0g018	BEGIN insis_claim.srv_billing_uf_bo.BLC_Item_Paid_Status( :pi_Context, :po_RetPrm, :po_Err ); END;
16k4aqa86c4bh	SELECT EVENT_ID, FUNCTION_NAME, PARAM_NAME, PARAM_TYPE, PARAM_FORMAT, PARAM_VALUE FROM SRV_EVENT_PARAMS WHERE EVENT_ID = :B2 AND FUNCTION_NAME = :B1
18mqgjctmn7aj	select * from (select name, to_char(next_time, 'YYYY/MM/DD HH24:MI:SS') as restorable_time, recid, sequence# as seq_num from sys.v_$archived_log al JOIN sys.v_$database_incarnation di ON di.RESETLOGS_ID = al.RESETLOGS_ID and di.STATUS = 'CURRENT' where al.name is NOT NULL and al.standby_dest = 'NO' AND al.archived = 'YES' AND al.thread# = 1 AND al.sequence# >= :1 AND al.recid > :2 ORDER BY al.sequence#) where rownum < :3
1ak02x9xb11mx	SELECT ORDER_ID, ORDER_DATE, PROCESS_DATE, PRICE_DATE, STATUS, REGISTRATION_DATE, PROCESS_RESULT, STAGE, PRICE_EXISTENCE FROM UL_DAILY_ORDERS WHERE STATUS = :B1
1kz16yhs993h2	insert into sys.scheduler$_event_log (log_id, log_date, type#, name, owner, operation, status, user_name, client_id, guid, dbid, additional_info, destination, credential, class_id, flags) values (:1, SYSTIMESTAMP, :2, :3, :4, :5, :6, :7, :8, :9, NULL, :10, :11, :12, :13, :14)
27tyz74dyg625	SELECT BlcPayments.PAYMENT_ID, BlcPayments.PAYMENT_CLASS, BlcPayments.USAGE_ID, BlcPayments.VOUCHER_DATE, BlcPayments.VOUCHER_PREFIX, BlcPayments.VOUCHER_NUMBER, BlcPayments.VOUCHER_SUFFIX, BlcPayments.CURRENCY, BlcPayments.AMOUNT, BlcPayments.RATE, BlcPayments.RATE_TYPE, BlcPayments.RATE_DATE, BlcPayments.PARTY, BlcPayments.PARTY_BILL_TO_SITE, BlcPayments.PARTY_NAME, BlcPayments.PARTY_ADDRESS, BlcPayments.REFERENCE, BlcPayments.BANK_CODE, BlcPayments.BANK_ACCOUNT_CODE, BlcPayments.PAYMENT_ADDRESS, BlcPayments.MATCHING_CLASS, BlcPayments.VALUE_DATE, BlcPayments.MATCHING_PARAMETERS, BlcPayments.BILLING_REFERENCE, BlcPayments.BILL_REFERENCE_DETA ILS, BlcPayments.CLAIM_ID, BlcPayments.PAYMENT_DATE, BlcPayments.PAYMENT_PREFIX, BlcPayments.PAYMENT_NUMBER, BlcPayments.PAYMENT_SUFFIX, BlcPayments.DOC_ID, BlcPayments.ORG_ID, BlcPayments.STATUS, BlcPayments.SENT_ON, BlcPayments.CONFIRMED_ON, BlcPayments.EXCLUDED_ON, BlcPayments.REJECTED_ON, BlcPayments.CLEARED_ON, BlcPayments.CLEAR_RATE, BlcPayments.CLEAR_RATE_TYPE, BlcPayments.CLEAR_RATE_DATE, BlcPayments.UNCLEARED_ON, BlcPayments.REVERSED_ON, BlcPayments.ACTIVITY_ID, BlcPayments.NOTES, BlcPayments.CREATED_ON, BlcPayments.CREATED_BY, BlcPayments.UPDATED_ON, BlcPayments.UPDATED_BY, BlcPayments.ATTRIB_0, BlcPayments.ATTRIB_1, BlcPayments.ATTRIB_2, BlcPayments.ATTRIB_3, BlcPayments.ATTRIB_4, BlcPayments.ATTRIB_5, BlcPayments.ATTRIB_6, BlcPayments.ATTRIB_7, BlcPayments.ATTRIB_8, BlcPayments.ATTRIB_9, BlcPayments.PAY_INSTR_ID, (SELECT (bank_account_name || ' ' || card_name) FROM blc_pay_instr_master WHERE instrument_id = BlcPayments.PAY_INSTR_ID) AS PAY_INSTR_DESCR, BlcPayments.VERSION, BlcBaccUsages.USAGE_NAME, BlcBaccUsages.USAGE_CURRENCY, BlcBaccUsages.USAGE_ID AS USAGE_ID1, BlcBaccUsages.BANK_ACC_ID, (SELECT BlcBankAccounts.CURRENCY FROM BLC_BANK_ACCOUNTS BlcBankAccounts WHERE BlcBankAccounts.BANK_ACC_ID = BlcBaccUsages.BANK_ACC_ID) AS BANK_ACCOUNT_CURRENCY, (SELECT BlcOrgNames.ORG_NAME FROM BLC_ORGS BlcOrgNames WHERE BlcOrgNames.ORG_ID = BlcPayments.ORG_ID) AS ORG_NAME, (null) AS UNAPPLIED_AMOUNT, (SELECT PPeople.NAME FROM P_PEOPLE PPeople WHERE PPeople.MAN_ID = BlcPayments.Party) AS PARTY_NAME, (SELECT CASE WHEN (1 = (SELECT 1 FROM DUAL WHERE REGEXP_LIKE (BlcPayments.PARTY_NAME, '^\d+(\.\d+)?$', ''))) THEN (SELECT PPeople.NAME FROM P_PEOPLE PPeople WHERE PPeople.MAN_ID = BlcPayments.PARTY_NAME) ELSE BlcPayments.PARTY_NAME END FROM DUAL) AS PAYING_PARTY_NAME, (SELECT PAddress.ADDRESS FROM P_ADDRESS PAddress WHERE PAddress.ADDRESS_ID = BlcPayments.PARTY_BILL_TO_SITE) AS PARTY_BILL_TO_SITE_NAME, (SELECT CASE WHEN (1 = (SELECT 1 FROM DUAL WHERE REGEXP_LIKE(BlcPayments.PARTY_ADDRESS, '^\d+(\.\d+)?$', ''))) THEN (SELECT PAddress.ADDRESS FROM P_ADDRESS PAddress WHERE PAddress.ADDRESS_ID = BlcPayments.PARTY_ADDRESS) ELSE BlcPayments.PARTY_ADDRESS END FROM DUAL) AS PARTY_ADDRESS_NAME, (SELECT date_value FROM blc_values WHERE setting = 'OperDate' AND org_id = blcpayments.legal_entity FETCH FIRST ROW ONLY) AS OPER_DATE, BlcPayments.LEGAL_ENTITY, BlcPayments.REVERSE_REASON, (SELECT BlcLookups.LOOKUP_CODE FROM BLC_LOOKUPS BlcLookups WHERE B lcLookups.LOOKUP_ID = BlcPayments.MATCHING_CLASS) AS MATCHING_CLASS_CODE, (BlcPayments.AMOUNT * BlcPayments.RATE) AS FUNC_AMOUNT, (SELECT BlcDocuments.DOC_PREFIX || ' ' || BlcDocuments.DOC_NUMBER || ' ' || BlcDocuments.DOC_SUFFIX FROM BLC_DOCUMENTS BlcDocuments WHERE BlcDocuments.DOC_ID = TO_NUMBER(BlcPayments.BILLING_REFERENCE) AND (SELECT LOOKUP_CODE FROM BLC_LOOKUPS WHERE LOOKUP_ID = BlcPayments.MATCHING_CLASS) IN ('ON_TRX_A', 'ON_TRX_B', 'ON_TRX_L')) AS DOCUMENT, (BlcPayments.MATCHING_CLASS) AS LOOKUP_ID, (SELECT LOOKUP_CODE FROM BLC_LOOKUPS WHERE LOOKUP_ID = BlcPayments.MATCHING_CLASS) AS LOOKUP_CODE, (SELECT meaning FROM blc_lookups_vl WHERE lookup_code = blcpayments.payment_class AND lookup_set = 'PAYMENT_CLASS' AND org_id = 0 FETCH FIRST ROW ONLY) AS PAYMENT_CLASS_MEANING, (SELECT meaning FROM blc_lookups_vl WHERE lookup_code = blcpayments.status AND ( ( (blcpayments.payment_class LIKE 'O%') AND (lookup_set = 'OUTGOING_PAY_STATUSES')) OR ( (blcpayments.payment_class LIKE 'I%') AND (lookup_set = 'INCOME_PAY_STATUSES'))) AND org_id = 0 FETCH FIRST ROW ONLY) AS STATUS_MEANING, BlcPayments.APPLIED_STATUS, BlcPayments.ACTUAL_OPEN_BALANCE, BlcPayments.OPEN_BALANCE, BlcPayments.ACTIVITY_ID AS ACTIVITY_ID1, BlcBaccUsages.DESCRIPTION AS USER_DESCRIPTION, (SELECT trim(bd.doc_prefix || ' ' || bd.doc_number || ' ' || bd.doc_suffix) FROM blc_documents bd WHERE bd.doc_id = BlcPayments.doc_id) AS RECEIPT_BATCH, BlcPayments.PRIORITY, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.PRIORITY) AS PRIORITY_MEANING, BlcPayments.CHARGE_BEARER, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.CHARGE_BEARER) AS CHARGE_BEARER_MEANING, BlcPayments.ROUTE_ID, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.ROUTE_ID) AS ROUTE_MEANING, BlcPayments.ATTRIB_10, BlcPayments.ATTRIB_11, BlcPayments.ATTRIB_12, BlcPayments.ATTRIB_13, BlcPayments.ATTRIB_14, BlcPayments.ATTRIB_15, BlcPayments.ATTRIB_16, BlcPayments.ATTRIB_17, BlcPayments.ATTRIB_18, BlcPayments.ATTRIB_19, BlcPayments.N_ATTRIB_0, BlcPayments.N_ATTRIB_1, BlcPayments.N_ATTRIB_2, BlcPayments.N_ATTRIB_3, BlcPayments.N_ATTRIB_4, BlcPayments.N_ATTRIB_5, BlcPayments.N_ATTRIB_6, BlcPayments.N_ATTRIB_7, BlcPayments.N_ATTRIB_8, BlcPayments.N_ATTRIB_9, BlcPayments.D_ATTRIB_0, BlcPayments.D_ATTRIB_1, BlcPayments.D_ATTRIB_2, BlcPayments.D_ATTRIB_3, BlcPayments.D_ATTRIB_4, BlcPayments.D_ATTRIB_5, BlcPayments.D_ATTRIB_6, BlcPayments.D_ATTRIB_7, BlcPayments.D_ATTRIB_8, BlcPayments.D_ATTRIB_9, BlcPayments.W_ATTRIB_0, BlcPayments.W_ATTRIB_1 FROM BLC_PAYMENTS BlcPayments, BLC_BACC_USAGES BlcBaccUsages WHERE (((BlcPayments.USAGE_ID = BlcBaccUsages.USAGE_ID) AND (:bindUsageId IS NULL OR BlcPayments.USAGE_ID = :bindUsageId) AND (:bindVoucherDate IS NULL OR BlcPayments.VOUCHER_DATE = :bindVoucherDate) AND (:bindCurrency IS NULL OR BlcPayments.CURRENCY = :bindCurrency) AND (:bindRate IS NULL OR BlcPayments.RATE = :bindRate) AND (:bindRateType IS NULL OR BlcPayments.RATE_TYPE = :bindRateType) AND (:bindRateDate IS NULL OR BlcPayments.RATE_DATE = :bindRateDate) AND (:bindBankAccount IS NULL OR BlcPayments.BANK_ACCOUNT_CODE = :bindBankAccount) AND (:bindOrgId IS NULL OR BlcPayments.ORG_ID = :bindOrgId) AND (:bindPaymentsList IS NULL OR (INSTR(', ' || :bindPaymentsList || ', ', ', ' || BlcPayments.PAYMENT_ID || ', ' ) > 0)) AND (:bindPaymentClass IS NULL OR (BlcPayments.PAYMENT_CLASS IS NULL OR BlcPayments.PAYMENT_CLASS LIKE :bindPaymentClass || '%')))) AND ( ( ( (UPPER(BlcPayments.BILL_REFERENCE_DETAILS) LIKE UPPER(:vc_temp_1) ) AND (UPPER(BlcPayments.STATUS) LIKE UPPER(:vc_temp_2) ) ) ) ) ORDER BY BlcPayments.PAYMENT_DATE DESC
2brqw8cqaj6tn	BEGIN insis_people_v10.srv_people_bo.create_people_version( :pi_Context, :po_RetPrm, :po_Err ); END;
32ujvt7upjgy7	SELECT /*+ DYNAMIC_SAMPLING(st, 5) */ SYSCFG.ID, NVL(ST.DESCRIPTION, SYSCFG.NAME ) FROM SYSCFG_CLAIM_EVENTS SYSCFG, TABLE( RB_CS_NOM.C_SYSCFGTRANSLATIONSTAB(:B1 , :B2 )) ST WHERE TO_CHAR(SYSCFG.ID) = ST.ID(+)
3ajsr75apqgd8	BEGIN insis_gen_cfg_v10.rb_srv.RuleRequest(:1 , :2 , :3 , :4 ) ; END;
3csu4cbug7927	BEGIN rb_cs_common.Country( :pi_Context, :po_RetPrm, :po_Err ); END;
3q3rpf9r1hx0x	DECLARE result INT; BEGIN result := 0; IF cust_util_pkg.Prevalid_Status_Payment_Poller(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 ) THEN result := 1; END IF; :9 := result; END;
3tk3z1ayxmyrt	WITH A AS ( SELECT STATEMENT_ORDER_NO, COUNT(1) OVER(PARTITION BY STATEMENT_ORDER_NO) AS PRM_KW_CNT FROM CPRS_RULE_STATEMENTS WHERE STATEMENT_TYPE IN ( :B3 , :B2 ) AND RULE_CPR_ID = :B1 ) SELECT G.STATEMENT_TEXT, G.STATEMENT_TYPE, G.STATEMENT_ORDER_NO, G.RESERVED_WORD, A.PRM_KW_CNT FROM CPRS_RULE_STATEMENTS G, A WHERE G.RULE_CPR_ID = :B1 AND G.STATEMENT_ORDER_NO = A.STATEMENT_ORDER_NO(+) AND G.STATEMENT_TYPE IN ( :B5 , :B4 , :B6 ) ORDER BY G.STATEMENT_ORDER_NO, DECODE( G.STATEMENT_TYPE, :B6 , 1, :B5 , 2, :B4 , 3 )
3un3uka335wp4	BEGIN rb_cs_common.ParamValue( :pi_Context, :po_RetPrm, :po_Err ); END;
3v6nv8u6wbq8d	SELECT BlcPayments.PAYMENT_ID, BlcPayments.PAYMENT_CLASS, BlcPayments.USAGE_ID, BlcPayments.VOUCHER_DATE, BlcPayments.VOUCHER_PREFIX, BlcPayments.VOUCHER_NUMBER, BlcPayments.VOUCHER_SUFFIX, BlcPayments.CURRENCY, BlcPayments.AMOUNT, BlcPayments.RATE, BlcPayments.RATE_TYPE, BlcPayments.RATE_DATE, BlcPayments.PARTY, BlcPayments.PARTY_BILL_TO_SITE, BlcPayments.PARTY_NAME, BlcPayments.PARTY_ADDRESS, BlcPayments.REFERENCE, BlcPayments.BANK_CODE, BlcPayments.BANK_ACCOUNT_CODE, BlcPayments.PAYMENT_ADDRESS, BlcPayments.MATCHING_CLASS, BlcPayments.VALUE_DATE, BlcPayments.MATCHING_PARAMETERS, BlcPayments.BILLING_REFERENCE, BlcPayments.BILL_REFERENCE_DETA ILS, BlcPayments.CLAIM_ID, BlcPayments.PAYMENT_DATE, BlcPayments.PAYMENT_PREFIX, BlcPayments.PAYMENT_NUMBER, BlcPayments.PAYMENT_SUFFIX, BlcPayments.DOC_ID, BlcPayments.ORG_ID, BlcPayments.STATUS, BlcPayments.SENT_ON, BlcPayments.CONFIRMED_ON, BlcPayments.EXCLUDED_ON, BlcPayments.REJECTED_ON, BlcPayments.CLEARED_ON, BlcPayments.CLEAR_RATE, BlcPayments.CLEAR_RATE_TYPE, BlcPayments.CLEAR_RATE_DATE, BlcPayments.UNCLEARED_ON, BlcPayments.REVERSED_ON, BlcPayments.ACTIVITY_ID, BlcPayments.NOTES, BlcPayments.CREATED_ON, BlcPayments.CREATED_BY, BlcPayments.UPDATED_ON, BlcPayments.UPDATED_BY, BlcPayments.ATTRIB_0, BlcPayments.ATTRIB_1, BlcPayments.ATTRIB_2, BlcPayments.ATTRIB_3, BlcPayments.ATTRIB_4, BlcPayments.ATTRIB_5, BlcPayments.ATTRIB_6, BlcPayments.ATTRIB_7, BlcPayments.ATTRIB_8, BlcPayments.ATTRIB_9, BlcPayments.PAY_INSTR_ID, (SELECT (bank_account_name || ' ' || card_name) FROM blc_pay_instr_master WHERE instrument_id = BlcPayments.PAY_INSTR_ID) AS PAY_INSTR_DESCR, BlcPayments.VERSION, BlcBaccUsages.USAGE_NAME, BlcBaccUsages.USAGE_CURRENCY, BlcBaccUsages.USAGE_ID AS USAGE_ID1, BlcBaccUsages.BANK_ACC_ID, (SELECT BlcBankAccounts.CURRENCY FROM BLC_BANK_ACCOUNTS BlcBankAccounts WHERE BlcBankAccounts.BANK_ACC_ID = BlcBaccUsages.BANK_ACC_ID) AS BANK_ACCOUNT_CURRENCY, (SELECT BlcOrgNames.ORG_NAME FROM BLC_ORGS BlcOrgNames WHERE BlcOrgNames.ORG_ID = BlcPayments.ORG_ID) AS ORG_NAME, (null) AS UNAPPLIED_AMOUNT, (SELECT PPeople.NAME FROM P_PEOPLE PPeople WHERE PPeople.MAN_ID = BlcPayments.Party) AS PARTY_NAME, (SELECT CASE WHEN (1 = (SELECT 1 FROM DUAL WHERE REGEXP_LIKE (BlcPayments.PARTY_NAME, '^\d+(\.\d+)?$', ''))) THEN (SELECT PPeople.NAME FROM P_PEOPLE PPeople WHERE PPeople.MAN_ID = BlcPayments.PARTY_NAME) ELSE BlcPayments.PARTY_NAME END FROM DUAL) AS PAYING_PARTY_NAME, (SELECT PAddress.ADDRESS FROM P_ADDRESS PAddress WHERE PAddress.ADDRESS_ID = BlcPayments.PARTY_BILL_TO_SITE) AS PARTY_BILL_TO_SITE_NAME, (SELECT CASE WHEN (1 = (SELECT 1 FROM DUAL WHERE REGEXP_LIKE(BlcPayments.PARTY_ADDRESS, '^\d+(\.\d+)?$', ''))) THEN (SELECT PAddress.ADDRESS FROM P_ADDRESS PAddress WHERE PAddress.ADDRESS_ID = BlcPayments.PARTY_ADDRESS) ELSE BlcPayments.PARTY_ADDRESS END FROM DUAL) AS PARTY_ADDRESS_NAME, (SELECT date_value FROM blc_values WHERE setting = 'OperDate' AND org_id = blcpayments.legal_entity FETCH FIRST ROW ONLY) AS OPER_DATE, BlcPayments.LEGAL_ENTITY, BlcPayments.REVERSE_REASON, (SELECT BlcLookups.LOOKUP_CODE FROM BLC_LOOKUPS BlcLookups WHERE B lcLookups.LOOKUP_ID = BlcPayments.MATCHING_CLASS) AS MATCHING_CLASS_CODE, (BlcPayments.AMOUNT * BlcPayments.RATE) AS FUNC_AMOUNT, (SELECT BlcDocuments.DOC_PREFIX || ' ' || BlcDocuments.DOC_NUMBER || ' ' || BlcDocuments.DOC_SUFFIX FROM BLC_DOCUMENTS BlcDocuments WHERE BlcDocuments.DOC_ID = TO_NUMBER(BlcPayments.BILLING_REFERENCE) AND (SELECT LOOKUP_CODE FROM BLC_LOOKUPS WHERE LOOKUP_ID = BlcPayments.MATCHING_CLASS) IN ('ON_TRX_A', 'ON_TRX_B', 'ON_TRX_L')) AS DOCUMENT, (BlcPayments.MATCHING_CLASS) AS LOOKUP_ID, (SELECT LOOKUP_CODE FROM BLC_LOOKUPS WHERE LOOKUP_ID = BlcPayments.MATCHING_CLASS) AS LOOKUP_CODE, (SELECT meaning FROM blc_lookups_vl WHERE lookup_code = blcpayments.payment_class AND lookup_set = 'PAYMENT_CLASS' AND org_id = 0 FETCH FIRST ROW ONLY) AS PAYMENT_CLASS_MEANING, (SELECT meaning FROM blc_lookups_vl WHERE lookup_code = blcpayments.status AND ( ( (blcpayments.payment_class LIKE 'O%') AND (lookup_set = 'OUTGOING_PAY_STATUSES')) OR ( (blcpayments.payment_class LIKE 'I%') AND (lookup_set = 'INCOME_PAY_STATUSES'))) AND org_id = 0 FETCH FIRST ROW ONLY) AS STATUS_MEANING, BlcPayments.APPLIED_STATUS, BlcPayments.ACTUAL_OPEN_BALANCE, BlcPayments.OPEN_BALANCE, BlcPayments.ACTIVITY_ID AS ACTIVITY_ID1, BlcBaccUsages.DESCRIPTION AS USER_DESCRIPTION, (SELECT trim(bd.doc_prefix || ' ' || bd.doc_number || ' ' || bd.doc_suffix) FROM blc_documents bd WHERE bd.doc_id = BlcPayments.doc_id) AS RECEIPT_BATCH, BlcPayments.PRIORITY, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.PRIORITY) AS PRIORITY_MEANING, BlcPayments.CHARGE_BEARER, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.CHARGE_BEARER) AS CHARGE_BEARER_MEANING, BlcPayments.ROUTE_ID, (SELECT BlcLookups.MEANING FROM BLC_LOOKUPS_VL BlcLookups WHERE BlcLookups.LOOKUP_ID = BlcPayments.ROUTE_ID) AS ROUTE_MEANING, BlcPayments.ATTRIB_10, BlcPayments.ATTRIB_11, BlcPayments.ATTRIB_12, BlcPayments.ATTRIB_13, BlcPayments.ATTRIB_14, BlcPayments.ATTRIB_15, BlcPayments.ATTRIB_16, BlcPayments.ATTRIB_17, BlcPayments.ATTRIB_18, BlcPayments.ATTRIB_19, BlcPayments.N_ATTRIB_0, BlcPayments.N_ATTRIB_1, BlcPayments.N_ATTRIB_2, BlcPayments.N_ATTRIB_3, BlcPayments.N_ATTRIB_4, BlcPayments.N_ATTRIB_5, BlcPayments.N_ATTRIB_6, BlcPayments.N_ATTRIB_7, BlcPayments.N_ATTRIB_8, BlcPayments.N_ATTRIB_9, BlcPayments.D_ATTRIB_0, BlcPayments.D_ATTRIB_1, BlcPayments.D_ATTRIB_2, BlcPayments.D_ATTRIB_3, BlcPayments.D_ATTRIB_4, BlcPayments.D_ATTRIB_5, BlcPayments.D_ATTRIB_6, BlcPayments.D_ATTRIB_7, BlcPayments.D_ATTRIB_8, BlcPayments.D_ATTRIB_9, BlcPayments.W_ATTRIB_0, BlcPayments.W_ATTRIB_1 FROM BLC_PAYMENTS BlcPayments, BLC_BACC_USAGES BlcBaccUsages WHERE (((BlcPayments.USAGE_ID = BlcBaccUsages.USAGE_ID) AND (:bindUsageId IS NULL OR BlcPayments.USAGE_ID = :bindUsageId) AND (:bindVoucherDate IS NULL OR BlcPayments.VOUCHER_DATE = :bindVoucherDate) AND (:bindCurrency IS NULL OR BlcPayments.CURRENCY = :bindCurrency) AND (:bindRate IS NULL OR BlcPayments.RATE = :bindRate) AND (:bindRateType IS NULL OR BlcPayments.RATE_TYPE = :bindRateType) AND (:bindRateDate IS NULL OR BlcPayments.RATE_DATE = :bindRateDate) AND (:bindBankAccount IS NULL OR BlcPayments.BANK_ACCOUNT_CODE = :bindBankAccount) AND (:bindOrgId IS NULL OR BlcPayments.ORG_ID = :bindOrgId) AND (:bindPaymentsList IS NULL OR (INSTR(', ' || :bindPaymentsList || ', ', ', ' || BlcPayments.PAYMENT_ID || ', ' ) > 0)) AND (:bindPaymentClass IS NULL OR (BlcPayments.PAYMENT_CLASS IS NULL OR BlcPayments.PAYMENT_CLASS LIKE :bindPaymentClass || '%')))) AND ( ( ( (UPPER(BlcPayments.BILL_REFERENCE_DETAILS) LIKE UPPER(:vc_temp_1) ) ) ) )
40m3cxjxzu2qr	BEGIN SRV_BILL_BO.Get_billing_event_Prem( :pi_Context, :po_RetPrm, :po_Err ); END;
41v4b16phu33t	BEGIN srv_events_common.getKWValue( :pi_Context, :po_RetPrm, :po_Err ); END;
447h7nm94v4ac	BEGIN insis_cust.srv_cust_ds_qq.launch_quickquote( :pi_Context, :po_RetPrm, :po_Err ); END;
4bw2ddp4vv110	SELECT OUTCONOUTER.ATTRCODE AS ATTRCODE , OUTCONOUTER.ATTRTYPE AS ATTRYPE FROM TABLE(:B1 ) OUTCONOUTER WHERE OUTCONOUTER.ATTRCODE NOT IN ( SELECT OUTCONINNER.ATTRCODE AS ATTRCODE FROM TABLE(:B1 ) OUTCONINNER INNER JOIN TABLE(:B2 ) QQVARS ON OUTCONINNER.ATTRCODE LIKE QQVARS.NAME AND QQVARS.CONFIGURATION_TYPE = NVL(:B3 , QQVARS.CONFIGURATION_TYPE) )
4m7m0t6fjcs5x	update seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1
4pzd7jrb7zma9	BEGIN srv_policy_ds.GetCover( :pi_Context, :po_RetPrm, :po_Err ); END;
4tqv879z0n758	BEGIN INSIS_SYS_V10.SRV_EVENTS.sysEvent(:1 , :2 , :3 , :4 ); END;
4z7t8ndjgvt9m	SELECT MIN(BD.PERIOD_FROM), MIN(BD.PERIOD_TO) FROM INSIS_GEN_BLC_V10.BLC_TRX_ACC BT INNER JOIN BLC_DOCUMENTS BD ON BT.DOC_ID = BD.DOC_ID WHERE BT.AGREEMENT = :B3 AND BT.I_ITEM_TYPE = :B2 AND :B1 BETWEEN BD.PERIOD_FROM AND BD.PERIOD_TO
5ruswtsgwwyq1	SELECT LAYER_CPR_ID FROM CPR_LAYER WHERE COUNTRY_ID = :B3 AND ( COMPANY_ID = 'ALL' OR COMPANY_ID = :B2 ) AND CUSTOMER_GRP IS NULL AND AFFINITY_GRP IS NULL AND SALES_CHANNEL IS NULL AND CAMPAIGN_ID IS NULL AND :B1 BETWEEN VALID_FROM AND NVL(VALID_TO, :B1 )
5t0w7b7rwmddq	UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = :1 WHERE INSTANCE_NAME = :2
5xvk4h5ujy20k	BEGIN insis_ul_v10.srv_invest_bo.CreateInvAccountByPolicy( :pi_Context, :po_RetPrm, :po_Err ); END;
5xvpmr5xubh5t	BEGIN rbi_data.SetUserProfile( :pi_Context, :po_RetPrm, :po_Err ); END;
68sm2sdz9h00t	BEGIN srv_billing_ps_bo.DirectTransferPaymentPlanToBLC( :pi_Context, :po_RetPrm, :po_Err ); END;
69sv4apbmu1u6	SELECT SRV_EVENT_SEQ.nextval NEXTNUMBER FROM dual
6qhb6w68a0gvx	WITH X1 AS (SELECT TABLE_NAME, ID, COLUMN_NAME, LANGUAGE, DESCRIPTION, CASE LANGUAGE WHEN :B4 THEN :B6 ELSE :B5 END AS RANK FROM SYSCFG_TRANSLATIONS WHERE LANGUAGE IN (:B4 , :B3 ) AND TABLE_NAME = :B2 AND COLUMN_NAME = :B1 ) , X AS (SELECT TABLE_NAME, ID, COLUMN_NAME, MIN(RANK) AS RANK FROM X1 GROUP BY TABLE_NAME, ID, COLUMN_NAME) SELECT X1.TABLE_NAME, X1.ID, X1.COLUMN_NAME, X1.LANGUAGE, X1.DESCRIPTION FROM X, X1 WHERE X.ID = X1.ID AND X.TABLE_NAME=X1.TABLE_NAME AND X.COLUMN_NAME=X1.COLUMN_NAME AND X.RANK = X1.RANK
6wfsppjwhm53t	BEGIN insis_gen_v10.srv_policy_ds.GetRuleDateByPolicy( :pi_Context, :po_RetPrm, :po_Err ); END;
78qsqqh4q1fdb	WITH A AS ( SELECT S.RISK_TYPE, SUM( LENGTH(S.RISK_TYPE) + :B4 ) OVER( ORDER BY S.RISK_TYPE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SM FROM GEN_RISK_SELECT S WHERE S.GRC_ID = :B3 AND S.ANNEX_ID = :B2 AND S.APPLY_RISK = :B1 HAVING COUNT(S.GRC_ID) > :B1 GROUP BY S.RISK_TYPE ) SELECT LISTAGG(RISK_TYPE, ', ') WITHIN GROUP (ORDER BY RISK_TYPE) FROM A WHERE SM <= :B5
79vqhq25p3038	SELECT /*+ DYNAMIC_SAMPLING(st, 5) */ B.QUEST_ID, NVL( ST.DESCRIPTION, C.NAME ), C.ANSWER_TYPE, C.ANSWER_LEN_FIXED, C.ANSWER_LEN_MAX, C.DEFINED_ANSWERS, C.DEFAULT_ANSWER, C.MANDATORY, C.TO_LOAD, NULL, C.QUEST_ORDER, C.FORMAT_MASK, NULL FROM CPR_QUESTIONS B, CPRS_QUEST_QUESTIONS C, TABLE (RB_CS_NOM.C_SYSCFGTRANSLATIONSTAB(:B1 , :B2 )) ST WHERE C.QUEST_CPR_ID = B.QUEST_CPR_ID AND TO_CHAR(C.QUESTION_CPRS_ID) = ST.ID(+)
7fsg7bp85w7kt	SELECT /*+ DYNAMIC_SAMPLING(CovRisk, 5) */ RB_CLAIM_COVERAGE_TYPE ( :B1 , :B2 , COVRISK.RISK_TYPE, CLAIMCOV.EVENT_TYPE, :B10 , OBJSUBT.OBJECT_KIND, OBJSUBT.OBJECT_SUB_TYPE, OBJSUPPL.OBJECT_SUPPL_TYPE, :B12 , CLAIMCOV.LOCATION_ID, CLAIMCOV.CLAIM_RESOLVE_TYPE, CLAIMCOV.MEASURE_TYPE, CLAIMCOV.CPRS_COVERAGE_ID, CLAIMCOV.CLAIM_CPR_ID, COVRISK.DEATH_RISK, RISKGROUP.RISK_GROUP ) FROM TABLE(RBI_NOM_COMMON.GETRISKS( :B1 , NULL, :B2 , :B3 )) COVRISK, CPR_RISKS RISKVERSION, CFG_NL_COVERS CNC, CFG_NL_OBJECTS CNO, CPRS_CLAIM_COVERAGE CLAIMCOV, CPR_OBJECTS OBJTYPE, CPRS_OBJECT_SUBT OBJSUBT, CPRS_OBJECT_SUPPL OBJSUPPL, CFG_COVER_RISK_GROUPS RISKGROUPLINK, CPR_RISK_GROUPS RISKGROUP WHERE CLAIMCOV.RISK_CPR_ID = RISKVERSION.RISK_CPR_ID AND COVRISK.RISK_TYPE = RISKVERSION.RISK_TYPE AND CNO.OBJECT_LINK_ID = CNC.OBJECT_LINK_ID AND COVRISK.COVER_LI NK_ID = CNC.COVER_LINK_ID AND ((CLAIMCOV.OBJECT_TYPE_CPR_ID IS NULL AND OBJTYPE.OBJECT_TYPE_CPR_ID = CNO.OBJECT_TYPE_CPR_ID ) OR ( CLAIMCOV.OBJECT_TYPE_CPR_ID = OBJTYPE.OBJECT_TYPE_CPR_ID)) AND (CLAIMCOV.OBJECT_SUBTYPE_CPRS_ID IS NULL OR CLAIMCOV.OBJECT_SUBTYPE_CPRS_ID = OBJSUBT.OBJECT_SUBTYPE_CPRS_ID ) AND (CLAIMCOV.OBJECT_SUPPL_ID IS NULL OR CLAIMCOV.OBJECT_SUPPL_ID = OBJSUPPL.OBJECT_SUPPL_ID) AND OBJTYPE.OBJECT_TYPE_CPR_ID = OBJSUPPL.OBJECT_TYPE_CPR_ID(+) AND OBJTYPE.OBJECT_TYPE_CPR_ID = OBJSUBT.OBJECT_TYPE_CPR_ID(+) AND CLAIMCOV.EVENT_TYPE = NVL(:B11 , CLAIMCOV.EVENT_TYPE) AND OBJTYPE.OBJECT_TYPE = :B10 AND (CLAIMCOV.OBJECT_SUBTYPE_CPRS_ID IS NULL OR OBJSUBT.OBJECT_SUB_TYPE = NVL(:B9 , OBJSUBT.OBJECT_SUB_TYPE)) AND (CLAIMCOV.OBJECT_SUBTYPE_CPRS_ID IS NULL OR OBJSUBT.OBJECT_KIND = NVL(:B8 , OBJSUBT.OBJECT_KIND)) AND (CLAIMCOV.LOCATION_ID IS NULL OR CLAIMCOV.LOCATION_ID = NVL(:B7 , CLAIMCOV.LOCATION_ID)) AND (CLAIMCOV.GEOG_AREA_ID IS NULL OR CLAIMCOV.GEOG_AREA_ID IN (SELECT GEOG_AR EA_ID FROM SYSCFG_GEOG_AREA_CONTENT WHERE COUNTRY_ID = NVL(:B12 , COUNTRY_ID ))) AND COVRISK.RISK_GROUP_LINK_ID = RISKGROUPLINK.RISK_GROUP_LINK_ID(+) AND RISKGROUPLINK.RISK_GROUP_CPR_ID = RISKGROUP.RISK_GROUP_CPR_ID(+) AND COALESCE( :B6 , RISKGROUP.RISK_GROUP, :B5 ) = NVL( RISKGROUP.RISK_GROUP, :B5 ) AND :B4 BETWEEN RISKVERSION.VALID_FROM AND RISKVERSION.VALID_TO
7hbk609csy3pc	SELECT INSTANCE FROM ACTIVE WHERE SERVER = :1 AND DOMAINNAME='bip' AND CLUSTERNAME='bi_cluster' FOR UPDATE NOWAIT
7hdck51db5x8y	SELECT * FROM (SELECT ClaimRequest.CLAIM_ID, Claim.CLAIM_REGID AS CLAIM_NO, Claim.INSR_TYPE, --Claim.INSR_CLASS, Claim.EVENT_TYPE, Claim.POLICY_ID, ClaimRequest.REQUEST_ID, ClaimRequest.REQUEST_DATE, ClaimRequest.CLAIMANT_TYPE, ClaimRequest.REQUEST_TYPE, ClaimRequest.SOLVE_WAY, Claim.CLAIM_ID AS CLAIM_CLAIM_ID, --ClaimRequest.OSNOWANIE, --ClaimRequest.SUMA, ClaimRequest.CURRENCY, ClaimRequest.CLAIM_STATE, ClaimRequest.DUE_DATE, ClaimRequest.CLAIM_TYPE, ClaimRequest.CLAIMANT_ID, ClaimRequest.REGISTRATION_DATE, ClaimRequest.USERNAME, ClaimRequest.PAYMENT_TYPE payment_type, ClaimRequest.CLAIM_STATE_AUX, ClaimRequest.ACCUMULATED_LR, ClaimRequest.PROVIDER_ID, ClaimRequestNames.CLAIMANT_ NAME AS CLAIMANT_ID_DESCR, ClaimRequestNames.CLAIMANT_MAN_ID AS MAN_ID, HsClaimState.NAME AS CLAIM_STATE_DESCR, HsClaimState.ID AS ID1, HsClaimStateAux.NAME AS CLAIM_STATE_AUX_DESCR, HsClaimStateAux.ID AS ID2, ClaimRequest.RECOVERY, ClaimRequest.SUBROGATION, ClaimRequestNames.provider_name AS PROVIDER_DESCR, ClaimRequest.reopen_request_id, ClaimRequest.reopen_reason, ClaimRequest.convert_date, ClaimRequest.attr1, ClaimRequest.attr2, ClaimRequest.attr3, ClaimRequest.attr4, ClaimRequest.attr5, ClaimRequest.amount AS SUMA, ClaimRequest.CLAIM_REQ_SEQ, ClaimRequest.OPERATION_DATE, ClaimRequest.ATTRC1, ClaimRequest.ATTRC2, ClaimRequest.ATTRC3, ClaimRequest.ATTRC4, ClaimRequest.ATTRC5, ClaimRequest.ATTRN1, ClaimRequest.ATTRN2, ClaimRequest.ATTRN3, ClaimRequest.ATTRN4, ClaimRequest.ATTRN5, ClaimRequest.ATTRN6, ClaimRequest.ATTRN7, ClaimRequest.ATTRN8, ClaimRequest.ATTRN9, ClaimRequest.ATTRN10, ClaimRequest.ATTRD1, ClaimRequest.ATTRD2, ClaimRequest.ATTRD3, ClaimRequest.ATTRD4, ClaimRequest.ATTRD5, ClaimRequest.UPDATED_BY, ClaimRequest.UPDATED_ON FROM claim_request ClaimRequest, claim Claim, hs_claim_type HsClaimType, h_claimant_benef ClwfClaimClaimant, hs_claim_state HsClaimState, hs_claim_state_aux HsClaimStateAux, claim_request_names ClaimRequestNames WHERE ClaimRequest.claim_id = Claim.claim_id AND ClaimRequest.CLAIM_TYPE = HsClaimType.ID(+) AND ClaimRequest.CLAIMANT_TYPE = ClwfClaimClaimant.ID(+) AND ClaimRequest.CLAIM_STATE = HsClaimState.ID AND ClaimRequest.CLAIM_STATE_AUX = HsClaimStateAux.ID AND ClaimRequest.CLAIM_ID = ClaimRequestNames.claim_id AND ClaimRequest.REQUEST_ID = ClaimRequestNames.request_id AND (:bind_claim_id IS NULL OR Cl aimRequest.claim_id = :bind_claim_id) AND (:bind_request_id IS NULL OR ClaimRequest.request_id LIKE :bind_request_id) AND (:bind_policy_id is null or Claim.POLICY_ID like :bind_policy_id)) QRSLT ORDER BY request_id desc
7kz2vfa0gkk0y	SELECT P_COVER_TYPE ( INSURED_OBJ_ID, COVER_TYPE, INSR_TYPE, CLAIM_OBJECT_TYPE, POLICY_ID, ANNEX_ID, INSURED_VALUE, CURRENCY, CURRENCY_RATE, TARIFF_PERCENT, PREMIUM, ADD_PREMIUM, OBJECT_NUM, FULL_PREMIUM, DISCOUNT, NET_PREMIUM, INSR_BEGIN, INSR_END, RISK_STATE, DURATION, DUR_DIMENSION, REGISTRATION_DATE, MANUAL_PREM_DIMENSION, AUX_ANNEX_ID, COVER_ORDER, USERNAME, DATE_COVERED, ANNUAL_PREMIUM, ANNIVERSARY, RI_AMOUNT_SUBJECT, RATE_FULL_PREMIUM, RATE_ANNUAL_PREMIUM, PACKAGE_ID, IV_TYPE, RISK_CLASS, GRC_SRV1, GRC_SRV2, GRC_SRV3, LOCATION_VALUE, LOCVAL_DIMENSION, SELECTED_COVER, DESIGNATION, WAIVER, COVER_PAYMENT_SOURCE, GRC_ID, GRC_ORG_ID, GRC_REF_ID, SLICE_TYPE, COVER_LOB, UPDATED_BY, UPDATED_ON, GROUP_ID, SUBGROUP_ID, COVER_DESCRIPTION, TARIFF_RULE, ATTRC1, ATTRC2, ATTRC3, ATTRC4, ATTRC5, ATTRC6, ATTRC7, ATTRC8, ATTRC9, ATTRC10, ATTRN1, ATTRN2, ATTRN3, ATTRN4, ATTRN5, ATTRN6, ATTRN7, ATTRN8, ATTRN9, ATTRN10, ATTRD1, ATTRD2, ATTRD3, ATTRD4, ATTRD5 ) FROM GEN_RISK_COVERED WHERE POLICY_ID = :B6 AND ANNEX_ID = NVL( :B5 , ANNEX_ID ) AND INSURED_OBJ_ID = NVL( :B4 , INSURED_OBJ_ID ) AND COVER_TYPE = NVL( :B3 , COVER_TYPE ) AND GRC_ID = NVL( :B2 , GRC_ID ) AND NVL( :B1 , INSR_BEGIN ) BETWEEN INSR_BEGIN AND GREATEST(TRUNC( INSR_END ), INSR_BEGIN )
82akdnpv49zq9	SELECT ClaimObjects.ANNEX_ID, ClaimObjects.ATTR1, ClaimObjects.ATTR2, ClaimObjects.ATTR3, ClaimObjects.ATTR4, ClaimObjects.ATTR5, ClaimObjects.CLAIM_ID, ClaimObjects.CLAIM_OBJ_SEQ, ClaimObjects.CLAIM_STATE, ClaimObjects.CLAIM_STATE_AUX, ClaimObjects.COVER_TYPE, ClaimObjects.EX_GRATIA, ClaimObjects.FIND_DATE, ClaimObjects.INITIAL_CSC_AMNT, ClaimObjects.INITIAL_CSC_CURRENCY, ClaimObjects.INITIAL_CSC_DIM, ClaimObjects.INITIAL_RESERV_AMNT, ClaimObjects.INITIAL_RESERV_CURRENCY, ClaimObjects.INJURED_GUILTY_TYPE, ClaimObjects.INJURED_OBJECT_ID, ClaimObjects.INJURED_OBJECT_TYPE, ClaimObjects.INJURY_CAUSED_BY, ClaimObjects.INSR_TYPE, ClaimObjects.INSURED_OBJ_ ID, ClaimObjects.ITEM_DESCRIPTION, ClaimObjects.ITEM_PURCHASE_DATE, ClaimObjects.ITEM_TYPE, ClaimObjects.LIABILITY, ClaimObjects.LOSS_TYPE, ClaimObjects.MAN_ID, ClaimObjects.OBJECT_ID, ClaimObjects.OBJECT_TYPE, ClaimObjects.PLACE_ID, ClaimObjects.POLICY_ID, ClaimObjects.REGISTRATION_DATE, ClaimObjects.REQUEST_ID, ClaimObjects.RESOLVE_TYPE, ClaimObjects.RISK_TYPE, ClaimObjects.SKIP_CALCULATION, ClaimObjects.SKIP_EVALUATION, ClaimObjects.SUPPL_OBJECT_ID, ClaimObjects.SYSTEM_NOTES, ClaimObjects.USERNAME, (SELECT ClaimObjectsNames.injured_object_name FROM claim_objects_names ClaimObjectsNames WHERE ClaimObjects.claim_obj_seq = ClaimObjectsNames.claim_obj_seq) AS INJURED_OBJECT_DESCR, (SELECT HClaimLocationView.NAME FROM h_claim_location_view HClaimLocationView WHERE ClaimObjects.place_id = HClaimLocation View.id) AS LOCATION_DESC, (SELECT HsClaimState.NAME FROM hs_claim_state HsClaimState WHERE ClaimObjects.CLAIM_STATE = HsClaimState.ID) AS CLAIM_STATE_DESCR, (SELECT HsClaimStateAux.NAME FROM hs_claim_state_aux HsClaimStateAux WHERE ClaimObjects.CLAIM_STATE_AUX = HsClaimStateAux.ID) AS CLAIM_STATE_AUX_DESCR, (SELECT HEventList.name FROM h_event_list HEventList WHERE HEventList.id = ClaimObjects.injury_caused_by) AS EVENT_NAME, ClaimObjectsNames.INSURED_OBJ_ID_TYPE, ClaimObjectsNames.CLAIM_OBJ_SEQ AS CLAIM_OBJ_SEQ1, ClaimObjects.ATTRC1, ClaimObjects.ATTRC2, ClaimObjects.ATTRC3, ClaimObjects.ATTRC4, ClaimObjects.ATTRC5, ClaimObjects.ATTRD1, ClaimObjects.ATTRD2, ClaimObjects.ATTRD3, ClaimObjects.ATTRD4, ClaimObjects.ATTRD5, ClaimObjects.ATTRN1, ClaimObjects.ATTRN2, ClaimObjects.ATTRN3, ClaimObjects.ATTRN4, Cla imObjects.ATTRN6, ClaimObjects.ATTRN7, ClaimObjects.ATTRN8, ClaimObjects.ATTRN9, ClaimObjects.ATTRN10, ClaimObjects.GRC_ID, ClaimObjects.DESIGNATION, ClaimObjects.WAIVER, ClaimObjects.LOB, ClaimObjects.DEATH, ClaimObjects.INV_OPER_STATE, ClaimObjects.PARENT_INSR_TYPE, ClaimObjects.RISK_GROUP, ClaimObjects.COVER_DSCR FROM CLAIM_OBJECTS ClaimObjects, CLAIM_OBJECTS_NAMES ClaimObjectsNames WHERE (ClaimObjects.CLAIM_OBJ_SEQ = ClaimObjectsNames.CLAIM_OBJ_SEQ(+)) AND ( ( (ClaimObjects.CLAIM_ID = :bind_claim_id ) AND ( ( ClaimObjects.CLAIM_OBJ_SEQ = :bind_claim_obj_seq ) OR ( :bind_claim_obj_seq IS NULL ) ) AND (ClaimObjects.REQUEST_ID = :bind_request_id ) ) )
8ccsdnu3qqyrr	SELECT MenuEntryUserView.MENU_ENTRY, MenuEntryUserView.USERNAME, MenuEntryUserView.ORDER_NO, MenuEntryUserView.NAME, WebMenuView.action, WebMenuView.focus_view_id, (SELECT service_menu_process.gMenuEntry_tfId(MenuEntryUserView.MENU_ENTRY) FROM dual) AS content_source_id , (SELECT component_type FROM h_web_content_source WHERE id = service_menu_process.gMenuEntry_tfId(MenuEntryUserView.MENU_ENTRY) ) AS component_type FROM MENU_ENTRY_USER_VIEW MenuEntryUserView, WEB_MENU_VIEW WebMenuView WHERE UPPER(MenuEntryUserView.USERNAME) = UPPER(:bind_user_name) AND WebMenuView.menu_entry (+) = MenuEntryUserView.menu_entry ORDER BY ORDER_NO
8pjwnhc1c62pz	BEGIN :1 := insis_sys_v10.aq_utils.send_ping(:2 ); END;
9c7qbm1svt7dy	SELECT NVL(GREATEST (SUM (pppf.amount), 0), 0) FROM blc_policy_payment_plan_fract pppf WHERE pppf.policy_pplan_id IN ( SELECT ppp.policy_pplan_id FROM blc_policy_payment_plan ppp INNER JOIN blc_policy_payment_plan ppp_paid ON ppp.policy_pplan_id = ppp_paid.pplan_link_id WHERE ppp.policy_id = :policy_id AND ppp_paid.amnt_type = 'PAID' AND TRUNC(ppp.due_date) >= (SELECT TRUNC(MAX(ga.insr_begin)) FROM gen_annex ga WHERE ga.policy_id = :policy_id AND annex_type = 17 AND annex_state NOT IN (-30, 0, 11, 12, 30, 38, 99)) AND (select object_state from insured_object where insured_obj_id = :INSURED_OBJ_ID) IN (0, 11, 12) ) AND pppf.cover_type = :cover_type AND pppf.gl_fract = :fract_type
9hfut3qvrzcac	SELECT * FROM TABLE(RBI_NOM_COMMON.GETCOVERS( :B1 , :B2 , :B3 , :B4 ))
9pmfvsnkhxt4a	BEGIN SRV_BILLING_BO.BLC_Item_Paid_Status( :pi_Context, :po_RetPrm, :po_Err ); END;
9uwqyhhd8mrp6	SELECT * FROM (SELECT ClaimObjects.CLAIM_OBJ_SEQ, ClaimObjects.CLAIM_ID, ClaimObjects.REQUEST_ID, ClaimObjects.POLICY_ID, ClaimObjects.INSR_TYPE, ClaimObjects.COVER_TYPE, ClaimObjects.ANNEX_ID, ClaimObjects.RISK_TYPE, ClaimObjects.OBJECT_ID, ClaimObjects.OBJECT_TYPE, ClaimObjects.LOSS_TYPE, ClaimObjects.INJURED_GUILTY_TYPE, ClaimObjects.INJURED_OBJECT_TYPE, ClaimObjects.INJURED_OBJECT_ID, ClaimObjects.CLAIM_STATE, ClaimObjects.INITIAL_RESERV_AMNT, ClaimObjects.INITIAL_RESERV_CURRENCY, --ClaimObjects.REFERENCE_ID, ClaimObjects.MAN_ID, ClaimObjects.FIND_DATE, ClaimObjects.REGISTRATION_DATE, ClaimObjects.USERNAME, Clai mObjects.CLAIM_STATE_AUX, HsObjectType.ID AS ID1, HsObjectType.NAME AS OBJECT_TYPE_DESCR, ClaimObjectsNames.injured_object_name AS INJURED_OBJECT_DESCR, ClaimObjectsNames.object_name AS INSURED_DESCR, (SELECT InjuredObjType.object_type_description FROM claim_injured_object_type_view InjuredObjType WHERE ClaimObjects.injured_object_type = InjuredObjType.object_type AND ROWNUM = 1) AS INJURED_OBJECT_TYPE_DESCR, HsClaimState.NAME AS CLAIM_STATE_DESCR, HsClaimState.ID AS ID3, HsClaimStateAux.NAME AS CLAIM_STATE_AUX_DESCR, HsClaimStateAux.ID AS ID2, ClaimObjects.initial_csc_amnt, ClaimObjects.initial_csc_currency, ClaimObjects.initial_csc_dim, ClaimObjects.item_description, ClaimObjects.item_purchase_date, ClaimObjects.item_type, ClaimObjects.place_id, ClaimObjects.resolve_type, ClaimObjects.suppl_object_id, ClaimObjects.system_notes, claim.event_type, claim.claim_id as claim_id1, ClaimObjects.attr1, ClaimObjects.attr2, ClaimObjects.attr3, ClaimObjects.attr4, ClaimObjects.attr5, (SELECT policy_no FROM policy WHERE policy_id = ClaimObjects.policy_id) AS policy_no, (SELECT waiver FROM gen_risk_covered WHERE policy_id = ClaimObjects.policy_id AND cover_type = ClaimObjects.cover_type AND rownum = 1) AS waiver, ClaimObjects.INSURED_OBJ_ID AS INSURED_ID, ClaimObjects.attrc1, ClaimObjects.attrc2, ClaimObjects.attrc3, ClaimObjects.attrc4, ClaimObjects.attrc5, ClaimObjects.attrd1, ClaimObjects.attrd2, ClaimObjects.attrd3, ClaimObjects.attrd4, ClaimObjects.attrd5, ClaimObjects.attrn1, ClaimObjects.attrn2, ClaimObjects.attrn3, ClaimObjects.attrn4, ClaimObjects.attrn6, ClaimObjects.attrn7, ClaimObjects.attrn8, ClaimObjects.attrn9, ClaimObjects.attrn10, ClaimObjects.injury_caused_by, ClaimObjects.GRC_ID, ClaimObjects.DESIGNATION, ClaimObjects.LOB, ClaimObjects.DEATH, ClaimObjects.INV_OPER_STATE, ClaimObjects.PARENT_INSR_TYPE, ClaimObjects.UPDATED_BY, ClaimObjects.UPDATED_ON, ClaimObjects.COVER_DSCR, ClaimObjects.RISK_GROUP, (SELECT DISTINCT risk_group_name FROM rb_cover_risk rb WHERE rb.cover_type = ClaimObjects.cover_type and rb.object_type = ClaimObjects.object_type and rb.risk_group = ClaimObjects.risk_group) as RISK_GROUP_NAME FROM CLAIM_OBJECTS ClaimObjects, HS_OBJECT_TYPE HsObjectType, HS_CLAIM_STATE HsClaimState, HS_CLAIM_STATE_AUX HsClaimStateAux, claim, CLAIM_OBJECTS_NAMES ClaimObjectsNam es WHERE ClaimObjects.INJURED_OBJECT_TYPE = HsObjectType.ID AND ClaimObjects.CLAIM_STATE = HsClaimState.ID AND ClaimObjects.CLAIM_STATE_AUX = HsClaimStateAux.ID AND claim.claim_id = ClaimObjects.claim_id AND ClaimObjects.claim_obj_seq = ClaimObjectsNames.claim_obj_seq AND NVL(:bind_insr_type, -1) = NVL(:bind_insr_type, -1)) QRSLT WHERE ( ( ( ( INSR_TYPE = :bind_insr_type ) OR ( :bind_insr_type IS NULL ) ) ) ) AND CLAIM_ID = :Bind_ClaimId AND REQUEST_ID = :Bind_RequestId
9vhmnmg5j5hhg	SELECT EVENT_ID, FUNCTION_NAME, ORDER_NO, PROCESSING_COMPONENT, PROCESSING_MODE, TIMEOUT, DEST_APP FROM SRV_EVENT_FUNCTION WHERE EVENT_ID = :B1 AND PROCESSING_COMPONENT IS NOT NULL ORDER BY ORDER_NO ASC
aahq4qbx8633v	SELECT WebMenuView.ENTRY_LEVEL, WebMenuView.MENU_ENTRY, WebMenuView.NAME, WebMenuView.ORDER_1, WebMenuView.ORDER_2, WebMenuView.ORDER_3, WebMenuView.ORDER_4, WebMenuView.ORDER_5, WebMenuView.ORDER_6, WebMenuView.ORDER_7, WebMenuView.PARENT_ID, WebMenuView.ACTION, WebMenuView.FOCUS_VIEW_ID, WebMenuView.CONTENT_SOURCE_ID, WebMenuView.COMPONENT_TYPE FROM WEB_MENU_VIEW WebMenuView CONNECT BY PRIOR MENU_ENTRY = PARENT_ID START WITH ENTRY_LEVEL = 1 ORDER BY ORDER_1, ORDER_2, ORDER_3, ORDER_4, ORDER_5, ORDER_6, ORDER_7
ad8r3431t8v01	BEGIN srv_policy_bo.Insert_Insured_Object( :pi_Context, :po_RetPrm, :po_Err ); END;
admh1skuwtbnk	select processins0_.InstanceId as InstanceId1_29_0_, processins0_.lastModificationDate as lastModificationDa2_29_0_, processins0_.lastReadDate as lastReadDate3_29_0_, processins0_.processId as processId4_29_0_, processins0_.processInstanceByteArray as processInstanceByt5_29_0_, processins0_.startDate as startDate6_29_0_, processins0_.state as state7_29_0_, processins0_.OPTLOCK as OPTLOCK8_29_0_ from ProcessInstanceInfo processins0_ where processins0_.InstanceId=:1
ag2u4uhg6kkn1	SELECT q.id, q.quest_id, q.quest_answer, q.quest_order, q.man_id, h.name AS quest_name, quest_common.Get_Answer_Descr( q.id ) AS quest_answer_descr, DECODE( cq.mandatory, 'Y', '*', NULL ) AS is_mand FROM quest_questions q, cfg_quest_questions cq, h_quest_questions h WHERE h.id = q.quest_id AND cq.id = q.quest_id and q.man_id = :bind_man_id
atwuyuvqkf27w	SELECT /*+ OPT_PARAM('_fix_control' '16391176:1') */ GROUP_TYPE, BUCKET_START, BUCKET_END, TM_GROUP_TYPE, TM_BUCKET_START, TM_BUCKET_END, SUM(TM_CPU_FIRST_BUCKET_VALUE) TM_CPU_FIRST_BUCKET_VALUE, SUM(TM_CPU_MIDDLE_BUCKETS_VALUE) TM_CPU_MIDDLE_BUCKETS_VALUE, SUM(TM_CPU_LAST_BUCKET_VALUE) TM_CPU_LAST_BUCKET_VALUE, SUM(TM_DB_FIRST_BUCKET_VALUE) TM_DB_FIRST_BUCKET_VALUE, SUM(TM_DB_MIDDLE_BUCKETS_VALUE) TM_DB_MIDDLE_BUCKETS_VALUE, SUM(TM_DB_LAST_BUCKET_VALUE) TM_DB_LAST_BUCKET_VALUE, SUM(RREQS_FIRST_BUCKET_VALUE) RREQS_FIRST_BUCKET_VALUE, SUM(RREQS_MIDDLE_BUCKETS_VALUE) RREQS_MIDDLE_BUCKETS_VALUE, SUM(RREQS_LAST_BUCKET_VALUE) RREQS_LAST_BUCKET_VALUE, SUM(WREQS_FIRST_BUCKET_VALUE) WREQS_FIRST_BUCKET_VALUE, SUM(WREQS_MIDDLE_BUCKETS_VALUE) WREQS_MIDDLE_BUCKETS_VALUE, SUM(WREQS_LAST_BUCKET_VALUE) WREQS_LAST_BUCKET_VALUE, SUM(RBYTES_FIRST_BUCKET _VALUE) RBYTES_FIRST_BUCKET_VALUE, SUM(RBYTES_MIDDLE_BUCKETS_VALUE) RBYTES_MIDDLE_BUCKETS_VALUE, SUM(RBYTES_LAST_BUCKET_VALUE) RBYTES_LAST_BUCKET_VALUE, SUM(WBYTES_FIRST_BUCKET_VALUE) WBYTES_FIRST_BUCKET_VALUE, SUM(WBYTES_MIDDLE_BUCKETS_VALUE) WBYTES_MIDDLE_BUCKETS_VALUE, SUM(WBYTES_LAST_BUCKET_VALUE) WBYTES_LAST_BUCKET_VALUE, SUM(IBYTES_FIRST_BUCKET_VALUE) IBYTES_FIRST_BUCKET_VALUE, SUM(IBYTES_MIDDLE_BUCKETS_VALUE) IBYTES_MIDDLE_BUCKETS_VALUE, SUM(IBYTES_LAST_BUCKET_VALUE) IBYTES_LAST_BUCKET_VALUE, SUM(CRBYTES_FIRST_BUCKET_VALUE) CRBYTES_FIRST_BUCKET_VALUE, SUM(CRBYTES_MIDDLE_BUCKETS_VALUE) CRBYTES_MIDDLE_BUCKETS_VALUE, SUM(CRBYTES_LAST_BUCKET_VALUE) CRBYTES_LAST_BUCKET_VALUE, SUM(PGA_ALLOC_FIRST_BUCKET_VALUE) PGA_ALLOC_FIRST_BUCKET_VALUE, SUM(PGA_ALLOC_MIDDLE_BUCKETS_VALUE) PGA_ALLOC_MIDDLE_BUCKETS_VALUE, SUM(PGA_ALLOC_LAST_BUCKET_VALUE) PGA_ALLOC_LAST_BUCKET_VALUE, SUM(TMP_ALLOC_FIRST_BUCKET_VALUE) TMP_ALLOC_FIRST_BUCKET_VALUE, SUM(TMP_ALLOC_MIDDLE_BUCKETS_VALUE) TMP _ALLOC_MIDDLE_BUCKETS_VALUE, SUM(TMP_ALLOC_LAST_BUCKET_VALUE) TMP_ALLOC_LAST_BUCKET_VALUE FROM ( SELECT GROUP_TYPE, BUCKET_START, TM_BUCKET_START, TM_BUCKET_END, BUCKET_END, TM_GROUP_TYPE, SUM(GVTF.TM_CPU_FIRST_BUCKET_VALUE) TM_CPU_FIRST_BUCKET_VALUE, SUM(GVTF.TM_CPU_MIDDLE_BUCKETS_VALUE) TM_CPU_MIDDLE_BUCKETS_VALUE, SUM(GVTF.TM_CPU_LAST_BUCKET_VALUE) TM_CPU_LAST_BUCKET_VALUE, SUM(GVTF.TM_DB_FIRST_BUCKET_VALUE) TM_DB_FIRST_BUCKET_VALUE, SUM(GVTF.TM_DB_MIDDLE_BUCKETS_VALUE) TM_DB_MIDDLE_BUCKETS_VALUE, SUM(GVTF.TM_DB_LAST_BUCKET_VALUE) TM_DB_LAST_BUCKET_VALUE, SUM(GVTF.RREQS_FIRST_BUCKET_VALUE) RREQS_FIRST_BUCKET_VALUE, SUM(GVTF.RREQS_MIDDLE_BUCKETS_VALUE) RREQS_MIDDLE_BUCKETS_VALUE, SUM(GVTF.RREQS_LAST_BUCKET_VALUE) RREQS_LAST_BUCKET_VALUE, SUM(GVTF.WREQS_FIRST_BUCKET_VALUE) WREQS_FIRST_BUCKET_VALUE, SUM(GVTF.WREQS_MIDDLE_BUCKETS_VALUE) WREQS_MIDDLE_BUCKETS_VALUE, SUM(GVTF.WREQS_LAST_BUCKET_VALUE) WREQS_LAST_BUCKET_VALUE, SUM(GVTF.RBYTES_FIRST_BUCKET_VALUE) RBYTES_FIR ST_BUCKET_VALUE, SUM(GVTF.RBYTES_MIDDLE_BUCKETS_VALUE) RBYTES_MIDDLE_BUCKETS_VALUE, SUM(GVTF.RBYTES_LAST_BUCKET_VALUE) RBYTES_LAST_BUCKET_VALUE, SUM(GVTF.WBYTES_FIRST_BUCKET_VALUE) WBYTES_FIRST_BUCKET_VALUE, SUM(GVTF.WBYTES_MIDDLE_BUCKETS_VALUE) WBYTES_MIDDLE_BUCKETS_VALUE, SUM(GVTF.WBYTES_LAST_BUCKET_VALUE) WBYTES_LAST_BUCKET_VALUE, SUM(GVTF.IBYTES_FIRST_BUCKET_VALUE) IBYTES_FIRST_BUCKET_VALUE, SUM(GVTF.IBYTES_MIDDLE_BUCKETS_VALUE) IBYTES_MIDDLE_BUCKETS_VALUE, SUM(GVTF.IBYTES_LAST_BUCKET_VALUE) IBYTES_LAST_BUCKET_VALUE, SUM(GVTF.CRBYTES_FIRST_BUCKET_VALUE) CRBYTES_FIRST_BUCKET_VALUE, SUM(GVTF.CRBYTES_MIDDLE_BUCKETS_VALUE) CRBYTES_MIDDLE_BUCKETS_VALUE, SUM(GVTF.CRBYTES_LAST_BUCKET_VALUE) CRBYTES_LAST_BUCKET_VALUE, SUM(GVTF.PGA_ALLOC_FIRST_BUCKET_VALUE) PGA_ALLOC_FIRST_BUCKET_VALUE, SUM(GVTF.PGA_ALLOC_MIDDLE_BUCKETS_VALUE) PGA_ALLOC_MIDDLE_BUCKETS_VALUE, SUM(GVTF.PGA_ALLOC_LAST_BUCKET_VALUE) PGA_ALLOC_LAST_BUCKET_VALUE, SUM(GVTF.TMP_ALLOC_FIRST_BUCKET_VALUE) TMP_ALLOC_FIR ST_BUCKET_VALUE, SUM(GVTF.TMP_ALLOC_MIDDLE_BUCKETS_VALUE) TMP_ALLOC_MIDDLE_BUCKETS_VALUE, SUM(GVTF.TMP_ALLOC_LAST_BUCKET_VALUE) TMP_ALLOC_LAST_BUCKET_VALUE FROM TABLE(SYS.GV$(CURSOR( SELECT INST_ID, GROUP_TYPE, BUCKET_START, TM_BUCKET_START, TM_BUCKET_END, BUCKET_END, TM_GROUP_TYPE, SUM(TM_DELTA_CPU_TIME * TM_FIRST_BUCKET_RATIO) TM_CPU_FIRST_BUCKET_VALUE, SUM(TM_DELTA_CPU_TIME * TM_MIDDLE_BUCKETS_RATIO) TM_CPU_MIDDLE_BUCKETS_VALUE, SUM(TM_DELTA_CPU_TIME * TM_LAST_BUCKET_RATIO) TM_CPU_LAST_BUCKET_VALUE, SUM(TM_DELTA_DB_TIME * TM_FIRST_BUCKET_RATIO) TM_DB_FIRST_BUCKET_VALUE, SUM(TM_DELTA_DB_TIME * TM_MIDDLE_BUCKETS_RATIO) TM_DB_MIDDLE_BUCKETS_VALUE, SUM(TM_DELTA_DB_TIME * TM_LAST_BUCKET_RATIO) TM_DB_LAST_BUCKET_VALUE, SUM(DELTA_READ_REQUESTS * FIRST_BUCKET_RATIO) RREQS_FIRST_BUCKET_VALUE, SUM(DELTA_READ_REQUESTS * MIDDLE_BUCKETS_RATIO) RREQS_MIDDLE_BUCKETS_VALUE, SUM(DELTA_READ_REQUESTS * LAST_BUCKET_RATIO) RREQS_LAST_BUCKET_VALUE, SUM(DELTA_WRITE_REQUESTS * FIRST_BUCKE T_RATIO) WREQS_FIRST_BUCKET_VALUE, SUM(DELTA_WRITE_REQUESTS * MIDDLE_BUCKETS_RATIO) WREQS_MIDDLE_BUCKETS_VALUE, SUM(DELTA_WRITE_REQUESTS * LAST_BUCKET_RATIO) WREQS_LAST_BUCKET_VALUE, SUM(DELTA_READ_BYTES * FIRST_BUCKET_RATIO) RBYTES_FIRST_BUCKET_VALUE, SUM(DELTA_READ_BYTES * MIDDLE_BUCKETS_RATIO) RBYTES_MIDDLE_BUCKETS_VALUE, SUM(DELTA_READ_BYTES * LAST_BUCKET_RATIO) RBYTES_LAST_BUCKET_VALUE, SUM(DELTA_WRITE_BYTES * FIRST_BUCKET_RATIO) WBYTES_FIRST_BUCKET_VALUE, SUM(DELTA_WRITE_BYTES * MIDDLE_BUCKETS_RATIO) WBYTES_MIDDLE_BUCKETS_VALUE, SUM(DELTA_WRITE_BYTES * LAST_BUCKET_RATIO) WBYTES_LAST_BUCKET_VALUE, SUM(DELTA_INTERCONNECT_BYTES * FIRST_BUCKET_RATIO) IBYTES_FIRST_BUCKET_VALUE, SUM(DELTA_INTERCONNECT_BYTES * MIDDLE_BUCKETS_RATIO) IBYTES_MIDDLE_BUCKETS_VALUE, SUM(DELTA_INTERCONNECT_BYTES * LAST_BUCKET_RATIO) IBYTES_LAST_BUCKET_VALUE, SUM(DELTA_READ_MEM_BYTES * FIRST_BUCKET_RATIO) CRBYTES_FIRST_BUCKET_VALUE, SUM(DELTA_READ_MEM_BYTES * MIDDLE_BUCKETS_RATIO) CRBYTES_MIDDLE_BU CKETS_VALUE, SUM(DELTA_READ_MEM_BYTES * LAST_BUCKET_RATIO) CRBYTES_LAST_BUCKET_VALUE, SUM(PGA_ALLOCATED * LENGTH_SEG * FIRST_BUCKET_RATIO) PGA_ALLOC_FIRST_BUCKET_VALUE, SUM(PGA_ALLOCATED * LENGTH_SEG * MIDDLE_BUCKETS_RATIO) PGA_ALLOC_MIDDLE_BUCKETS_VALUE, SUM(PGA_ALLOCATED * LENGTH_SEG * LAST_BUCKET_RATIO) PGA_ALLOC_LAST_BUCKET_VALUE, SUM(TEMP_SPACE_ALLOCATED * LENGTH_SEG * FIRST_BUCKET_RATIO) TMP_ALLOC_FIRST_BUCKET_VALUE, SUM(TEMP_SPACE_ALLOCATED * LENGTH_SEG * MIDDLE_BUCKETS_RATIO) TMP_ALLOC_MIDDLE_BUCKETS_VALUE, SUM(TEMP_SPACE_ALLOCATED * LENGTH_SEG * LAST_BUCKET_RATIO) TMP_ALLOC_LAST_BUCKET_VALUE FROM ( SELECT USERENV('INSTANCE') INST_ID, ROW_ID, SID, SAMPLE_TIME, CASE WHEN BUCKET_END IS NULL OR BUCKET_START IS NULL THEN NULL WHEN BUCKET_START = TM_BUCKET_START AND BUCKET_START = BUCKET_END THEN 'CO' WHEN BUCKET_START = TM_BUCKET_START THEN 'CM' WHEN BUCKET_START = BUCKET_END THEN 'SO' ELSE 'SM' END GROUP_TYPE, CASE WHEN TM_BUCKET_START IS NULL OR BUCKET_END IS NULL OR (TM_BUCKET_START = BUCKET_START) THEN NULL WHEN TM_BUCKET_START = BUCKET_END THEN 'O' ELSE 'M' END TM_GROUP_TYPE, START_SEG, BUCKET_START, BUCKET_START_OFFSET, TM_START_SEG, CASE WHEN TM_BUCKET_START IS NULL OR (TM_BUCKET_START = BUCKET_START) THEN NULL ELSE TM_BUCKET_START END TM_BUCKET_START, CASE WHEN TM_BUCKET_START IS NULL OR (TM_BUCKET_START = BUCKET_START) THEN NULL ELSE BUCKET_END END TM_BUCKET_END, TM_BUCKET_START_OFFSET, LENGTH_SEG, TM_LENGTH_SEG, END_SEG, BUCKET_END, BUCKET_END_OFFSET, CASE WHEN BUCKET_START != BUCKET_END THEN (:B9 - BUCKET_START_OFFSET) / LENGTH_SEG ELSE 1 END FIRST_BUCKET_RATIO, CASE WHEN BUCKET_END >= BUCKET_START + 2 THEN :B9 / LENGTH_SEG ELSE NULL END MIDDLE_BUCKETS_RATIO, CASE WHEN BUCKET_START != BUCKET_END THEN BUCKET_END_OFFSET / LENGTH_SEG ELSE NULL END LAST_BUCKET_RATIO, CASE WHEN TM_BUCKET_START != BUCKET_END THEN (:B9 - TM_BUCKET_START_OFFSET) / TM_LENGTH_SEG ELSE 1 END TM_FIRST_BUCKET_RATIO, CASE WHEN BUCKET_END >= TM_BUC KET_START + 2 THEN :B9 / TM_LENGTH_SEG ELSE NULL END TM_MIDDLE_BUCKETS_RATIO, CASE WHEN TM_BUCKET_START != BUCKET_END THEN BUCKET_END_OFFSET / TM_LENGTH_SEG ELSE NULL END TM_LAST_BUCKET_RATIO, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS DELTA_READ_REQUESTS, DELTA_WRITE_IO_REQUESTS DELTA_WRITE_REQUESTS, DELTA_READ_IO_BYTES DELTA_READ_BYTES, DELTA_WRITE_IO_BYTES DELTA_WRITE_BYTES, DELTA_INTERCONNECT_IO_BYTES DELTA_INTERCONNECT_BYTES, DELTA_READ_MEM_BYTES DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ROW_ID, SID, SAMPLE_TIME, START_SEG, TRUNC(START_SEG / :B9 ) BUCKET_START, MOD(START_SEG, :B9 ) BUCKET_START_OFFSET, TM_START_SEG, TRUNC(TM_START_SEG/:B9 ) TM_BUCKET_START, MOD(TM_START_SEG, :B9 ) TM_BUCKET_START_OFFSET, TRUNC(END_SEG/:B9 ) BUCKET_END, MOD(END_SEG, :B9 ) BUCKET_END_OFFSET, END_SEG, LENGTH_SEG, TM_LENGTH_SEG, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS, DELTA_WRITE_IO_REQUESTS, DELTA_READ_ IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES, DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ROW_ID, SID, SAMPLE_TIME, TIME_INTERVAL, CASE WHEN START_SEG < 0 THEN 0 ELSE START_SEG END START_SEG, CASE WHEN TM_START_SEG < 0 THEN 0 ELSE TM_START_SEG END TM_START_SEG, CASE WHEN END_SEG > TIME_INTERVAL THEN TIME_INTERVAL ELSE END_SEG END END_SEG, (LENGTH_SEG - (CASE WHEN START_SEG < 0 THEN -START_SEG ELSE 0 END + CASE WHEN END_SEG > TIME_INTERVAL THEN END_SEG - TIME_INTERVAL ELSE 0 END)) LENGTH_SEG, (TM_LENGTH_SEG - (CASE WHEN TM_START_SEG < 0 THEN -TM_START_SEG ELSE 0 END + CASE WHEN END_SEG > TIME_INTERVAL THEN END_SEG - TIME_INTERVAL ELSE 0 END)) TM_LENGTH_SEG, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS, DELTA_WRITE_IO_REQUESTS, DELTA_READ_IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES, DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ROW_ID, SID, SAMPL E_TIME, TIME_INTERVAL, LENGTH_SEG, TM_LENGTH_SEG, END_SEG - TM_LENGTH_SEG TM_START_SEG, END_SEG - LENGTH_SEG START_SEG, END_SEG, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS, DELTA_WRITE_IO_REQUESTS, DELTA_READ_IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES, DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ROW_ID, SID, SAMPLE_TIME, EXTRACT(SECOND FROM REL_SAMPLE_TIME_INT) + EXTRACT(MINUTE FROM REL_SAMPLE_TIME_INT)*60 + EXTRACT(HOUR FROM REL_SAMPLE_TIME_INT)*3600 + EXTRACT(DAY FROM REL_SAMPLE_TIME_INT)*3600*24 END_SEG, TIME_INTERVAL, DELTA_TIME/1000000 LENGTH_SEG, TM_DELTA_TIME/1000000 TM_LENGTH_SEG, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_READ_IO_REQUESTS, DELTA_WRITE_IO_REQUESTS, DELTA_READ_IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES, DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ROWNUM ROW_ID, SESSION_ID SID, CAST(FROM_TZ(SAMPLE_TIME, DBTIMEZONE) AS DATE) S AMPLE_DATE, SAMPLE_TIME, (SAMPLE_TIME - CAST(:B8 AS TIMESTAMP)) REL_SAMPLE_TIME_INT, TRUNC((:B7 - :B8 ) * 3600 * 24) TIME_INTERVAL, :B8 AS BND_START_INTERVAL , :B7 AS BND_END_INTERVAL , TM_DELTA_TIME, TM_DELTA_CPU_TIME, TM_DELTA_DB_TIME, DELTA_TIME, DELTA_READ_IO_REQUESTS, DELTA_WRITE_IO_REQUESTS, DELTA_READ_IO_BYTES, DELTA_WRITE_IO_BYTES, DELTA_INTERCONNECT_IO_BYTES, DELTA_READ_MEM_BYTES, PGA_ALLOCATED, TEMP_SPACE_ALLOCATED FROM (SELECT ASH0.* FROM V$ACTIVE_SESSION_HISTORY ASH0 WHERE SAMPLE_TIME BETWEEN :B6 AND :B5 AND ( (SESSION_ID = :B2 AND SESSION_SERIAL# = :B1 AND USERENV('INSTANCE') = :B3 ) OR (:B4 = 'yes' AND QC_INSTANCE_ID = :B3 AND QC_SESSION_ID = :B2 AND QC_SESSION_SERIAL# = :B1 ))) ) ASH1 WHERE SAMPLE_DATE >= BND_START_INTERVAL AND SAMPLE_DATE <= BND_END_INTERVAL + 120/3600/24 ) ) WHERE START_SEG <= TIME_INTERVAL ) WHERE (LENGTH_SEG IS NOT NULL AND LENGTH_SEG > 0) OR (TM_LENGTH_SEG IS NOT NULL AND TM_LENGTH_SEG > 0)) ) GROUP BY INST_ID, GROUP _TYPE, BUCKET_START, BUCKET_END, TM_GROUP_TYPE, TM_BUCKET_START, TM_BUCKET_END))) GVTF WHERE INST_ID >= :B11 AND INST_ID <= :B10 GROUP BY GROUP_TYPE, BUCKET_START, BUCKET_END, TM_GROUP_TYPE, TM_BUCKET_START, TM_BUCKET_END ) GROUP BY GROUPING SETS ( (GROUP_TYPE, BUCKET_START, BUCKET_END), (TM_GROUP_TYPE, TM_BUCKET_START, TM_BUCKET_END) )
au634v7kfdy4f	BEGIN IF 1=1 THEN :l_result := 'TRUE'; ELSE :l_result := 'FALSE'; END IF; END;
axjfrq5n4qd9v	BEGIN srv_system.Get_Context( :pi_Context, :po_RetPrm, :po_Err ); END;
ay8q3jzbthvuh	INSERT INTO IAU_COMMON COLUMNS ( IAU_ID, IAU_COMPONENTTYPE, IAU_MAJORVERSION, IAU_MINORVERSION, IAU_INSTANCEID, IAU_HOSTID, IAU_HOSTNWADDR, IAU_MODULEID, IAU_PROCESSID, IAU_ORACLEHOME, IAU_HOMEINSTANCE, IAU_ECID, IAU_RID, IAU_CONTEXTFIELDS, IAU_SESSIONID, IAU_TARGETCOMPONENTTYPE, IAU_APPLICATIONNAME, IAU_EVENTTYPE, IAU_EVENTCATEGORY, IAU_EVENTSTATUS, IAU_TSTZORIGINATING, IAU_THREADID, IAU_COMPONENTNAME, IAU_INITIATOR, IAU_MESSAGETEXT, IAU_FAILURECODE, IAU_REMOTEIP, IAU_TARGET, IAU_RESOURCE, IAU_ROLES, IAU_DOMAINNAME, IAU_COMPONENTDATA, IAU_AUDITUSER, IAU_TENANTID, IAU_TRANSACTIONID, IAU_USERTENANTID, IAU_SERVERNAME ) VALUES ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 )
b0bf7ct0tbb5m	insert into insis_cust.audit_log( al_log_id, al_fa_name, al_log_date, al_result) VALUES ( insis_cust.audit_log_pk_seq.nextval, 'insis_audit_trail', TO_TIMESTAMP('2023-09-15 14:39:43', 'YYYY-MM-DD HH24:MI:SS'), 'log file created')
b89tb01phnr7s	WITH C_MASTER AS (SELECT B.PRODUCT_LINK_ID AS PRODUCT_LINK_ID, LEVEL AS LVL FROM CFG_NL_PRODUCT B WHERE EXISTS ( SELECT 1 FROM CFG_NL_PRODUCT WHERE PRODUCT_CODE=:B1 AND STATUS IN ( :B6 , :B5 ) ) OR :B4 IN ( :B3 , :B2 ) START WITH B.PRODUCT_CODE = :B1 CONNECT BY PRIOR B.PARENT_PRODUCT_LINK_ID = B.PRODUCT_LINK_ID ORDER BY LEVEL ASC) , TB AS ( SELECT CFGOBJ.OBJECT_LINK_ID, CFGOBJ.PRODUCT_LINK_ID, CFGOBJ.OBJECT_TYPE_CPR_ID, CFGOBJ.VALID_FROM, CFGOBJ.VALID_TO, CFGOBJ.VERSION_IN, CFGOBJ.VERSION_OUT, CFGOBJ.OBJECT_NUM_FLG, CFGOBJ.MANDATORY, CFGOBJ.RECOMMENDED, M.LVL FROM C_MASTER M, CFG_NL_OBJECTS CFGOBJ, CPR_OBJECTS CPROBJ WHERE CFGOBJ.PRODUCT_LINK_ID = M.PRODUCT_LINK_ID AND CFGOBJ.OBJECT_TYPE_CPR_ID = CPROBJ.OBJECT_TYPE_CPR_ID AND CPROBJ.OBJECT_TYPE = NVL ( :B8 , CPROBJ.OBJECT_TYPE ) AND ( (NOT EXISTS (SELECT 1 FROM CFG_NL_OBJECT_LAYERS L WHERE L.CFG_ID = CFGOBJ.OBJECT_LINK_ID AND ROWNUM = 1) OR EXISTS (SELECT 1 FROM CFG_NL_OBJECT_LAYERS L WHERE L.CFG_ID = CFGOBJ.OBJECT_LINK_ID AND INSTR(:B7 , TO_CHAR(L.LAYER_ID)) > 0 AND L.ALLOWED = :B9 AND ROWNUM = 1)) OR :B7 IS NULL ) AND ( EXISTS (SELECT 1 FROM TABLE( :B10 ) PROD_VER WHERE PROD_VER.VERSION = CFGOBJ.VERSION_IN AND CFGOBJ.PRODUCT_LINK_ID = PROD_VER.PRODUCT_LINK_ID ) OR :B4 IN ( :B3 , :B2 )) ) SELECT A.OBJECT_LINK_ID, A.PRODUCT_LINK_ID, A.OBJECT_TYPE_CPR_ID, A.VALID_FROM, A.VALID_TO, A.VERSION_IN, A.VERSION_OUT, A.OBJECT_NUM_FLG, A.MANDATORY, A.RECOMMENDED FROM TB A WHERE (( :B4 IN (:B3 , :B11 ) AND NOT EXISTS (SELECT 1 FROM TB B WHERE B.OBJECT_LINK_ID <> A.OBJECT_LINK_ID AND B.PRODUCT_LINK_ID <> A.PRODUCT_LINK_ID AND B.OBJECT_TYPE_CPR_ID = A.OBJECT_TYPE_CPR_ID AND B.LVL < A.LVL) ) OR :B4 IN ( :B9 , :B2 ))
bf1xvxfuhwccu	SELECT ClaimObjectsIndemnity.CONVERT_DATE, ClaimObjectsIndemnity.CALCULATE_DATE, ClaimObjectsIndemnity.INDEM_TYPE, HsIndemnityType.NAME AS INDEM_TYPE_NAME, ClaimObjectsIndemnity.CALC_SUM, ClaimObjectsIndemnity.CURRENCY, ClaimObjectsIndemnity.PAY_SUM, ClaimObjectsIndemnity.CORRECT_REASON, ClaimObjectsIndemnity.INSPECT_NUM, ClaimObjectsIndemnity.OVER_LIMIT, ClaimObjectsIndemnity.REGISTRATION_DATE, ClaimObjectsIndemnity.USERNAME, ClaimObjectsIndemnity.CORRECT_PERC, ClaimObjectsIndemnity.CLAIM_INDEM_SEQ, ClaimObjectsIndemnity.CLAIM_OBJ_SEQ, ClaimObjectsIndemnity.CLAIM_ID, ClaimObjectsIndemnity.REQUEST_ID, ClaimObjectsIndemnity.POLICY_ID, ClaimObjectsIndemnity.INSR_TYPE, ClaimObjectsIndemnity.COVER_TYPE, ClaimObjectsIndemnity.ANNEX_ID, ClaimObjectsIndemnity.RISK_TYPE, HsIndemnityType.ID AS H_INDEM_TYPE, clm_process.GetCoverIVSlice(ClaimObjectsIndemnity.POLICY_ID, ClaimObjectsIndemnity.INSURED_OBJ_ID, ClaimObjectsIndemnity.COVER_TYPE, Claim.EVENT_DATE) AS INSURED_VALUE, ( SELECT grc.currency FROM TABLE(srv_policy_data.T_COVER_TABLE) grc, TABLE(srv_policy_data.T_ANNEX_TABLE) ga WHERE ga.policy_id(+) = grc.policy_id AND ga.annex_id(+) = grc.annex_id AND ( grc.annex_id = 0 OR ( grc.annex_id <> 0 AND ga.annex_state >= 0 )) AND grc.insured_obj _id = ClaimObjects.insured_obj_id AND grc.cover_type = ClaimObjects.cover_type AND rownum = 1 ) AS INSURED_VALUE_CURRENCY, ClaimObjects.OBJECT_ID, ClaimObjectsIndemnity.CONVERT_DATE_TYPE, ClaimObjectsIndemnity.INSURED_OBJ_ID AS INSURED_ID, ClaimObjectsIndemnity.attr1, ClaimObjectsIndemnity.attr2, ClaimObjectsIndemnity.attr3, ClaimObjectsIndemnity.attr4, ClaimObjectsIndemnity.attr5, ClaimObjectsIndemnity.UPDATED_BY, ClaimObjectsIndemnity.UPDATED_ON, Claim.EVENT_DATE FROM CLAIM_OBJECTS_INDEMNITY ClaimObjectsIndemnity, HS_INDEMNITY_TYPE HsIndemnityType, CLAIM_OBJECTS ClaimObjects, CLAIM Claim WHERE (ClaimObjectsIndemnity.INDEM_TYPE = HsIndemnityType.ID(+)) AND ClaimObjects.claim_obj_seq = ClaimObjectsIndemnity.claim_obj_seq AND ClaimObjectsIndemnity.claim_id = :bind_claim_id AND ClaimObjectsIndemnity.request_id = :bind_request_id AND ClaimObjectsIndemnity.claim_obj_seq = :bind_claim_obj_seq AND ClaimObjectsIndemnity.claim_id = Claim.claim_id AND ( (:bind_inspect_num is not NULL AND ClaimObjectsIndemnity.inspect_num = :bind_inspect_num ) OR ( :bind_inspect_num is NULL AND ClaimObjectsIndemnity.inspect_num = ( SELECT MAX(inspect_num) FROM claim_inspections WHERE claim_id = :bind_claim_id AND request_id = :bind_request_id AND claim_obj_seq = :bind_claim_obj_seq ) ) )
bfdqpzu2mk8jy	WITH C_MASTER AS (SELECT B.PRODUCT_LINK_ID, LEVEL AS LVL FROM CFG_NL_PRODUCT B WHERE EXISTS ( SELECT 1 FROM CFG_NL_PRODUCT WHERE PRODUCT_CODE=:B1 AND STATUS IN ( :B3 , :B2 ) ) START WITH B.PRODUCT_CODE = :B1 CONNECT BY PRIOR B.PARENT_PRODUCT_LINK_ID = B.PRODUCT_LINK_ID ORDER BY LEVEL ASC) , TB AS (SELECT PP.PRODUCT_NAME_ID, PP.PRODUCT_LINK_ID, PP.PARAM_CPR_ID, PP.VALID_FROM, PP.VALID_TO, PP.VERSION_IN, PP.VERSION_OUT, M.LVL FROM C_MASTER M, CFG_NL_PRODUCT_PARAMS PP WHERE PP.PRODUCT_LINK_ID = M.PRODUCT_LINK_ID AND ((NOT EXISTS (SELECT 1 FROM CFG_NL_PRODUCT_PARAM_LAYERS L WHERE L.CFG_ID = PP.PRODUCT_NAME_ID AND ROWNUM = 1) OR EXISTS (SELECT 1 FROM CFG_NL_PRODUCT_PARAM_LAYERS L WHERE L.CFG_ID = PP.PRODUCT_NAME_ID AND INSTR(:B4 , TO_CHAR(L.LAYER_ID)) > 0 AND L.ALLOWED = :B5 AND ROWNUM = 1)) OR :B4 IS NULL ) AND EXISTS (SELECT /*+ DYNAMIC_SAMPLING (prod_ver, 5) */ 1 FROM TABLE( :B6 ) PROD_VER WHERE PROD_VER.VERSION = PP.VERSION_IN AND PP.PRODUCT_LINK_ID = PROD_VER.PRODUCT_LINK_ID)) SELECT DISTINCT A.PRODUCT_NAME_ID, A.PRODUCT_LINK_ID, A.PARAM_CPR_ID, A.VALID_FROM, A.VALID_TO, A.VERSION_IN, A.VERSION_OUT FROM TB A WHERE NOT EXISTS (SELECT 1 FROM TB B WHERE B.LVL < A.LVL AND B.PARAM_CPR_ID = A.PARAM_CPR_ID)
bh84mx8uugk2t	SELECT * FROM TABLE(RBI_NOM_COMMON.GETOBJECTS( :B1 , :B2 , :B3 ))
bzccmyk64zt8n	SELECT /*+ FIRST_ROWS(10) */ count(DN.entryid) from JPS_DN DN WHERE DN.PARENTDN = :1 and exists ( select 1 from CT_10 A where ( A.JPS_DN_ENTRYID = DN.entryid ) and ( A.attrval IS NOT NULL ))
c4hm92pdyf9dh	SELECT SUM(TARGET_AMOUNT) FROM BLC_APPLICATIONS WHERE TARGET_TRX = :B2 AND (STATUS <> 'D' OR (STATUS = 'D' AND RUN_ID = :B1 ))
cjj5a8k0qzr2q	SELECT BP.PAYMENT_ID, BA.APPLICATION_ID FROM BLC_PAYMENTS BP, BLC_APPLICATIONS BA WHERE BP.PAYMENT_ID = :B1 AND BP.PAYMENT_ID = BA.SOURCE_PAYMENT FOR UPDATE WAIT 10
ct4x6n43nykun	BEGIN insis_gen_ri_v10.srv_ri_bo.process_ri_check_policy_CoverInst( :pi_Context, :po_RetPrm, :po_Err ); END;
d4zns8492dchu	BEGIN INSIS_BLC_GLOBAL_CUST.srv_cust_bo.ModifyPayDate( :pi_Context, :po_RetPrm, :po_Err ); END;
d8uxmtq3qwjqu	SELECT NAMESPACE, ATTRIBUTE FROM (SELECT :B2 NAMESPACE, CT.ATTRIBUTE FROM TABLE(:B1 ) CT WHERE CT.NAMESPACE = :B2 AND NOT EXISTS (SELECT 1 FROM SRV_SYSTEM_CONTEXT S WHERE CT.ATTRIBUTE = S.ATTRIBUTE AND S.NAMESPACE = :B2 ))
dcuj2dhgmpv34	SELECT BT.CURRENCY, BT.AMOUNT, BT.FC_AMOUNT, BLC_APPL_UTIL_PKG.TRX_OUTSTANDING_BALANCE(BT.TRANSACTION_ID, BT.AMOUNT, BT.PAID_STATUS, BT.OPEN_BALANCE), NVL(BT.ACTUAL_OPEN_BALANCE, BT.AMOUNT) FROM BLC_TRANSACTIONS BT WHERE BT.TRANSACTION_ID = :B1
f7gznycvm74x7	BEGIN srv_policy_bo.ApplySelection( :pi_Context, :po_RetPrm, :po_Err ); END;
fjvmxv2f46065	DECLARE p1 VARCHAR2(20); p2 VARCHAR2(100); p3 VARCHAR2(40); p4 VARCHAR2(1000); p5 VARCHAR2(20); p6 VARCHAR2(3); p7 VARCHAR2(1000); p8 VARCHAR2(1000); p9 VARCHAR2(1000); p10 VARCHAR2(1000); p11 VARCHAR2(1000); BEGIN ---------------------------------------------------- -- Input parameters setup -- ---------------------------------------------------- p1 := :1 ; p2 := :2 ; p3 := :3 ; p4 := :4 ; p5 := :5 ; p6 := :6 ; p7 := :7 ; p8 := :8 ; p9 := :9 ; p10 := :10 ; p11 := :11 ; ---------------------------------------------------- -- Call Function -- ---------------------------------------------------- insis_context.prepare_session(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11); --------------------------------------- ------------- -- Output parameters setup -- ---------------------------------------------------- END;
ft8u1vzy0n92z	call INSIS_SCHEDULER.SCHEDULER_ENGINE.EXECUTE_JOB_PROGRAM ( :0 )
g3f9anp86j2cw	BEGIN insis_context.Prepare_Session(:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 ); END;
gf4yn91705cfx	/*dt:ownQuery*/WITH getsize AS ( SELECT tablespace_name, max(autoextensible) autoextensible, SUM(bytes) tsize FROM dba_data_files GROUP BY tablespace_name), getfree AS ( SELECT a.tablespace_name, contents, SUM(blocks * block_size) tfree FROM dba_free_space a, dba_tablespaces c WHERE a.tablespace_name = c.tablespace_name GROUP BY a.tablespace_name, contents) SELECT a.tablespace_name, b.contents, a.tsize, b.tfree, a.tsize - b.tfree AS tused, 100 * ((a.tsize - b.tfree) / a.tsize) AS tused_ratio, a.autoextensible AS autoextend FROM getsize a, getfree b WHERE a.tablespace_name = b.tablespace_name UNION SELECT tablespace_name, 'TEMPORARY', SUM(tablespace_s ize) AS tsize, SUM(free_space) AS tfree, SUM(tablespace_size) - SUM(free_space) AS tused, 100 * ((SUM(tablespace_size) - SUM(free_space)) / SUM(tablespace_size)) AS tused_ratio, 'NO' FROM dba_temp_free_space GROUP BY tablespace_name
ggdtf51ghany4	SELECT /*+ DYNAMIC_SAMPLING(f, 5) */ F.PARAM_VALUE FROM TABLE( RBI_NOM_COVERAGE.GETPRODUCTPARAMS( :B1 ) ) D, CPRS_PARAM_VALUE F, CPR_PARAMS G WHERE D.PARAM_CPR_ID = F.PARAM_VALUE_CPR_ID AND F.PARAM_ID = G.PARAM_CPR_ID AND G.PARAM_NAME = :B2
ggg8hk3gqst7w	update ProcessInstanceInfo set lastModificationDate=:1 , lastReadDate=:2 , processId=:3 , startDate=:4 , state=:5 , OPTLOCK=:6 , processInstanceByteArray=:7 where InstanceId=:8 and OPTLOCK=:9
gvswmctbh6nnw	BEGIN insis_cust.srv_cust_ds_qq.get_input_parameters_quickquote( :pi_Context, :po_RetPrm, :po_Err ); END;

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Key Instance Activity Stats
• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Key Instance Activity Stats

• Ordered by statistic name

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats

• Ordered by statistic name