SQL> SQL> set linesize 1000 SQL> set pagesize 1000 SQL> set timing on SQL> set trimout on SQL> set trimspool on SQL> alter session set statistics_level=all; Session altered. Elapsed: 00:00:00.01 SQL> @brkr.sql SUM(CASH_VALUE) --------------- 1307269944 Elapsed: 00:17:23.01 SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID gak3hj6rtuug3, child number 0 ------------------------------------- WITH replist AS (SELECT DISTINCT rep_set.FILTER_TOKEN FROM (SELECT DISTINCT wdsd.data_setting_id, wdsd.FILTER_TOKEN --- RR2 FROM WEB_DATA_SETTING_DETAIL wdsd, (SELECT inst_query_id, NVL(y.data_setting_id,X.data_setting_id) data_setting_id FROM WEB_INSTANTIABLE_QUERY y, (SELECT DATA_SETTING_ID FROM WEB_DATA_SETTING WHERE BROKER_ID = :"SYS_B_0" AND DEFAULT_YN = :"SYS_B_1" ) X WHERE inst_query_id = :"SYS_B_2" ) wiq WHERE wiq.data_setting_id = wdsd.data_setting_id) Plan hash value: 733012279 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 4003 (100)| | 1 |00:16:58.53 | 815K| 120K| | | | | 1 | SORT AGGREGATE | | 1 | 1 | 131 | | | 1 |00:16:58.53 | 815K| 120K| | | | | 2 | NESTED LOOPS | | 1 | | | | | 135K|00:16:58.36 | 815K| 120K| | | | | 3 | NESTED LOOPS | | 1 | 52 | 6812 | 125 (1)| 00:00:02 | 135K|00:06:41.97 | 680K| 48278 | | | | | 4 | VIEW | VW_NSO_1 | 1 | 12 | 1224 | 6 (0)| 00:00:01 | 25 |00:00:00.01 | 9 | 3 | | | | | 5 | SORT UNIQUE | | 1 | 12 | 504 | | | 25 |00:00:00.01 | 9 | 3 | 2048 | 2048 | 2048 (0)| | 6 | NESTED LOOPS | | 1 | 12 | 504 | 6 (0)| 00:00:01 | 25 |00:00:00.01 | 9 | 3 | | | | | 7 | MERGE JOIN CARTESIAN | | 1 | 1 | 23 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 2 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID| WEB_DATA_SETTING | 1 | 1 | 12 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 | | | | |* 9 | INDEX RANGE SCAN | WEB_DATASETNG_I_BROKER_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1 | | | | | 10 | BUFFER SORT | | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 | 73728 | 73728 | | |* 11 | INDEX RANGE SCAN | WEB_INST_QRY_N_DSID | 1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1 | | | | |* 12 | INDEX RANGE SCAN | WEBDATASETDTL_PK_TEAM | 1 | 12 | 228 | 2 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | 1 | | | | |* 13 | INDEX RANGE SCAN | ACCOUNT_SUMM_I_ATT_MAR_ACT_ID | 25 | 9 | | 3 (0)| 00:00:01 | 135K|00:06:41.86 | 680K| 48275 | | | | |* 14 | HASH JOIN | | 135K| 1 | 31 | 8 (13)| 00:00:01 | 135K|00:06:38.66 | 679K| 47470 | 1236K| 1236K| 603K (0)| |* 15 | INDEX RANGE SCAN | WTT_PK | 135K| 3 | 57 | 4 (0)| 00:00:01 | 406K|00:06:11.59 | 543K| 47470 | | | | |* 16 | INDEX RANGE SCAN | WBXT_PK_BROKER_TEAM_ID1 | 135K| 13 | 156 | 3 (0)| 00:00:01 | 135K|00:00:00.54 | 135K| 0 | | | | | 17 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_SUMMARY | 135K| 4 | 116 | 13 (0)| 00:00:01 | 135K|00:10:16.23 | 135K| 71802 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$20FF7734 4 - SEL$976A52FC / VW_NSO_1@SEL$20FF7734 5 - SEL$976A52FC 8 - SEL$976A52FC / WEB_DATA_SETTING@SEL$4 9 - SEL$976A52FC / WEB_DATA_SETTING@SEL$4 11 - SEL$976A52FC / Y@SEL$3 12 - SEL$976A52FC / WDSD@SEL$2 13 - SEL$20FF7734 / AM@SEL$7 14 - SEL$FC1F63E8 15 - SEL$FC1F63E8 / BO_TEAM_X_ACCOUNT@SEL$6 16 - SEL$FC1F63E8 / WBXT@SEL$5 17 - SEL$20FF7734 / AM@SEL$7 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.1.0.7') DB_VERSION('11.1.0.7') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$FC1F63E8") MERGE(@"SEL$ABDE6DFF") OUTLINE_LEAF(@"SEL$976A52FC") OUTLINE_LEAF(@"SEL$20FF7734") UNNEST(@"SEL$87FA6C3C") OUTLINE(@"SEL$10") OUTLINE(@"SEL$ABDE6DFF") MERGE(@"SEL$6") OUTLINE(@"SEL$87FA6C3C") MERGE(@"SEL$88122447") OUTLINE(@"SEL$E2C0F2F4") MERGE(@"SEL$7") OUTLINE(@"SEL$5") OUTLINE(@"SEL$6") OUTLINE(@"SEL$8") OUTLINE(@"SEL$88122447") MERGE(@"SEL$641071AC") OUTLINE(@"SEL$9") OUTLINE(@"SEL$7") OUTLINE(@"SEL$1") OUTLINE(@"SEL$641071AC") MERGE(@"SEL$07BDC5B4") OUTLINE(@"SEL$2") OUTLINE(@"SEL$07BDC5B4") MERGE(@"SEL$4") OUTLINE(@"SEL$3") OUTLINE(@"SEL$4") NO_ACCESS(@"SEL$20FF7734" "VW_NSO_1"@"SEL$20FF7734") INDEX(@"SEL$20FF7734" "AM"@"SEL$7" ("ACCOUNT_SUMMARY"."ATTR2" "ACCOUNT_SUMMARY"."MARKET_VALUE" "ACCOUNT_SUMMARY"."ACT_ID")) LEADING(@"SEL$20FF7734" "VW_NSO_1"@"SEL$20FF7734" "AM"@"SEL$7") USE_NL(@"SEL$20FF7734" "AM"@"SEL$7") NLJ_BATCHING(@"SEL$20FF7734" "AM"@"SEL$7") PUSH_SUBQ(@"SEL$FC1F63E8") INDEX_RS_ASC(@"SEL$976A52FC" "WEB_DATA_SETTING"@"SEL$4" ("WEB_DATA_SETTING"."BROKER_ID")) INDEX(@"SEL$976A52FC" "Y"@"SEL$3" ("WEB_INSTANTIABLE_QUERY"."INST_QUERY_ID" "WEB_INSTANTIABLE_QUERY"."DATA_SETTING_ID")) INDEX(@"SEL$976A52FC" "WDSD"@"SEL$2" ("WEB_DATA_SETTING_DETAIL"."DATA_SETTING_ID" "WEB_DATA_SETTING_DETAIL"."FILTER_TOKEN" "WEB_DATA_SETTING_DETAIL"."RLS_ID")) LEADING(@"SEL$976A52FC" "WEB_DATA_SETTING"@"SEL$4" "Y"@"SEL$3" "WDSD"@"SEL$2") USE_MERGE_CARTESIAN(@"SEL$976A52FC" "Y"@"SEL$3") USE_NL(@"SEL$976A52FC" "WDSD"@"SEL$2") INDEX(@"SEL$FC1F63E8" "BO_TEAM_X_ACCOUNT"@"SEL$6" ("BO_TEAM_X_ACCOUNT"."ACCOUNT_ID" "BO_TEAM_X_ACCOUNT"."TEAM_ID")) INDEX(@"SEL$FC1F63E8" "WBXT"@"SEL$5" ("WEB_BROKER_X_TEAM"."BROKER_ID" "WEB_BROKER_X_TEAM"."TEAM_ID")) LEADING(@"SEL$FC1F63E8" "BO_TEAM_X_ACCOUNT"@"SEL$6" "WBXT"@"SEL$5") USE_HASH(@"SEL$FC1F63E8" "WBXT"@"SEL$5") PX_JOIN_FILTER(@"SEL$FC1F63E8" "WBXT"@"SEL$5") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :SYS_B_0 (NUMBER): 48270 2 - :SYS_B_1 (VARCHAR2(30), CSID=873): 'Y' 3 - :SYS_B_2 (NUMBER): 1385776 Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter("DEFAULT_YN"=:SYS_B_1) 9 - access("BROKER_ID"=:SYS_B_0) 11 - access("INST_QUERY_ID"=:SYS_B_2) 12 - access("WDSD"."DATA_SETTING_ID"=NVL("Y"."DATA_SETTING_ID","DATA_SETTING_ID")) 13 - access("ATTR2"="FILTER_TOKEN") filter( IS NOT NULL) 14 - access("TEAM_ID"="WBXT"."TEAM_ID") 15 - access("ACCOUNT_ID"=:B1) 16 - access("WBXT"."BROKER_ID"=:SYS_B_3) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) SUM("CASH_VALUE")[22] 2 - "CASH_VALUE"[NUMBER,22] 3 - "SYS_ALIAS_1".ROWID[ROWID,10] 4 - "FILTER_TOKEN"[VARCHAR2,200] 5 - (#keys=1) "WDSD"."FILTER_TOKEN"[VARCHAR2,200] 6 - "WDSD"."FILTER_TOKEN"[VARCHAR2,200] 7 - "DATA_SETTING_ID"[NUMBER,22], "Y"."DATA_SETTING_ID"[NUMBER,22] 8 - "DATA_SETTING_ID"[NUMBER,22] 9 - "WEB_DATA_SETTING".ROWID[ROWID,10] 10 - (#keys=0) "Y"."DATA_SETTING_ID"[NUMBER,22] 11 - "Y"."DATA_SETTING_ID"[NUMBER,22] 12 - "WDSD"."FILTER_TOKEN"[VARCHAR2,200] 13 - "SYS_ALIAS_1".ROWID[ROWID,10] 14 - (#keys=1) 15 - "TEAM_ID"[VARCHAR2,200] 16 - "WBXT"."TEAM_ID"[VARCHAR2,200] 17 - "CASH_VALUE"[NUMBER,22] 157 rows selected. Elapsed: 00:00:00.31 SQL>