Skip to content

Instantly share code, notes, and snippets.

@christopherdtaylor1994

christopherdtaylor1994/gist:3873133

Created October 11, 2012 15:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save christopherdtaylor1994/3873133 to your computer and use it in GitHub Desktop.
Save christopherdtaylor1994/3873133 to your computer and use it in GitHub Desktop.
Download ZIP
New SQL with INDEX hint
Raw
gistfile1.sql
SELECT /*+ gather_plan_statistics QB_NAME(OUTER_QUERY) INDEX(MAPCS, MAPY_CALC_SVC_PERF1) */
MAPCS.ID,
MA.ORG_ID_PROVIDER,
MAPCS.MON_ACCT_PAYER_CALC_SUMMARY_ID,
MAPCS.CE_SERVICE_ID
FROM
concuity.MON_ACCOUNT MA,
concuity.MON_ACCOUNT_PAYER MAPY,
concuity.MON_ACCOUNT_PAYER_CALC_LATEST MAPC,
concuity.MON_ACCOUNT_PAYER_CALC_SERVICE MAPCS
WHERE
MA.ID = MAPY.MON_ACCOUNT_ID
AND MA.ORG_ID_PROVIDER = :p_org_id
AND MAPY.ID = MAPC.MON_ACCOUNT_PAYER_ID
AND MAPC.ID = MAPCS.MON_ACCT_PAYER_CALC_SUMMARY_ID
AND EXISTS
(SELECT /*+ QB_NAME(INNER_QUERY) */
'x'
FROM
concuity.WQ_ORG_ACCOUNT WQOA
WHERE
WQOA.MON_ACCOUNT_ID = MA.ID AND WQOA.ORG_ID = :p_org_id)
/
197188 rows selected.
Elapsed: 00:00:48.06
Plan hash value: 2162032930
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| MON_ACCOUNT_PAYER_CALC_SERVICE | 1 | 2 | 54 | | 4 (0)| 00:00:01 | | | 197K|00:00:33.22 | 518K| 13 |
| 2 | NESTED LOOPS | | 1 | 294K| 24M| | 709K (1)| 02:21:49 | | | 357K|00:27:39.71 | 373K| 0 |
|* 3 | HASH JOIN RIGHT SEMI | | 1 | 159K| 9200K| 6080K| 70081 (1)| 00:14:01 | | | 160K|00:00:25.38 | 51106 | 0 |
| 4 | PARTITION LIST SINGLE | | 1 | 239K| 3271K| | 445 (1)| 00:00:06 | KEY | KEY | 222K|00:00:00.22 | 1877 | 0 |
|* 5 | TABLE ACCESS FULL | WQ_ORG_ACCOUNT | 1 | 239K| 3271K| | 445 (1)| 00:00:06 | KEY | KEY | 222K|00:00:00.01 | 1877 | 0 |
|* 6 | HASH JOIN | | 1 | 159K| 7017K| | 68907 (1)| 00:13:47 | | | 160K|00:00:33.54 | 49229 | 0 |
| 7 | PARTITION LIST SINGLE | | 1 | 79837 | 1091K| | 912 (1)| 00:00:11 | KEY | KEY | 80157 |00:00:00.24 | 4167 | 0 |
|* 8 | TABLE ACCESS FULL | MON_ACCOUNT | 1 | 79837 | 1091K| | 912 (1)| 00:00:11 | KEY | KEY | 80157 |00:00:00.24 | 4167 | 0 |
|* 9 | HASH JOIN | | 1 | 1263K| 37M| 34M| 67989 (1)| 00:13:36 | | | 1275K|00:00:29.72 | 45062 | 0 |
| 10 | VIEW | index$_join$_003 | 1 | 1263K| 20M| | 44178 (1)| 00:08:51 | | | 1275K|00:00:09.61 | 33129 | 0 |
|* 11 | HASH JOIN | | 1 | | | | | | | | 1275K|00:00:08.34 | 33129 | 0 |
| 12 | INDEX FAST FULL SCAN| MAPCS_LATEST_MAPY_CLAIM_ID | 1 | 1263K| 20M| | 7483 (1)| 00:01:30 | | | 1275K|00:00:00.01 | 6150 | 0 |
| 13 | INDEX FAST FULL SCAN| MAPCS_LATEST_PK | 1 | 1263K| 20M| | 33032 (1)| 00:06:37 | | | 1275K|00:00:01.28 | 26979 | 0 |
| 14 | VIEW | index$_join$_002 | 1 | 1917K| 25M| | 19695 (1)| 00:03:57 | | | 1935K|00:00:18.81 | 11933 | 0 |
|* 15 | HASH JOIN | | 1 | | | | | | | | 1935K|00:00:16.88 | 11933 | 0 |
| 16 | INDEX FAST FULL SCAN| MON_ACCT_PAYER_FK_MON_ACCT | 1 | 1917K| 25M| | 9055 (1)| 00:01:49 | | | 1935K|00:00:00.01 | 7428 | 0 |
| 17 | INDEX FAST FULL SCAN| MON_ACCT_PAYER_PK | 1 | 1917K| 25M| | 5350 (1)| 00:01:05 | | | 1935K|00:00:00.01 | 4505 | 0 |
|* 18 | INDEX RANGE SCAN | MAPY_CALC_SVC_PERF1 | 160K| 2 | | | 2 (0)| 00:00:01 | | | 197K|00:00:04.39 | 321K| 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$D6887185 / MAPCS@OUTER_QUERY
5 - SEL$D6887185 / WQOA@INNER_QUERY
8 - SEL$D6887185 / MA@OUTER_QUERY
10 - SEL$802A7B11 / MAPC@OUTER_QUERY
11 - SEL$802A7B11
12 - SEL$802A7B11 / indexjoin$_alias$_001@SEL$802A7B11
13 - SEL$802A7B11 / indexjoin$_alias$_002@SEL$802A7B11
14 - SEL$173B53D5 / MAPY@OUTER_QUERY
15 - SEL$173B53D5
16 - SEL$173B53D5 / indexjoin$_alias$_001@SEL$173B53D5
17 - SEL$173B53D5 / indexjoin$_alias$_002@SEL$173B53D5
18 - SEL$D6887185 / MAPCS@OUTER_QUERY
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$173B53D5")
OUTLINE_LEAF(@"SEL$802A7B11")
OUTLINE_LEAF(@"SEL$D6887185")
UNNEST(@"INNER_QUERY")
OUTLINE(@"SEL$D6887185")
UNNEST(@"INNER_QUERY")
OUTLINE(@"OUTER_QUERY")
OUTLINE(@"INNER_QUERY")
INDEX_JOIN(@"SEL$D6887185" "MAPC"@"OUTER_QUERY" ("MON_ACCOUNT_PAYER_CALC_LATEST"."MON_ACCOUNT_PAYER_ID" "MON_ACCOUNT_PAYER_CALC_LATEST"."CLAIM_ID")
("MON_ACCOUNT_PAYER_CALC_LATEST"."ID"))
INDEX_JOIN(@"SEL$D6887185" "MAPY"@"OUTER_QUERY" ("MON_ACCOUNT_PAYER"."MON_ACCOUNT_ID") ("MON_ACCOUNT_PAYER"."ID"))
FULL(@"SEL$D6887185" "MA"@"OUTER_QUERY")
FULL(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
INDEX(@"SEL$D6887185" "MAPCS"@"OUTER_QUERY" ("MON_ACCOUNT_PAYER_CALC_SERVICE"."MON_ACCT_PAYER_CALC_SUMMARY_ID"))
LEADING(@"SEL$D6887185" "MAPC"@"OUTER_QUERY" "MAPY"@"OUTER_QUERY" "MA"@"OUTER_QUERY" "WQOA"@"INNER_QUERY" "MAPCS"@"OUTER_QUERY")
USE_HASH(@"SEL$D6887185" "MAPY"@"OUTER_QUERY")
USE_HASH(@"SEL$D6887185" "MA"@"OUTER_QUERY")
USE_HASH(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
USE_NL(@"SEL$D6887185" "MAPCS"@"OUTER_QUERY")
SWAP_JOIN_INPUTS(@"SEL$D6887185" "MA"@"OUTER_QUERY")
SWAP_JOIN_INPUTS(@"SEL$D6887185" "WQOA"@"INNER_QUERY")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 966965592
2 - (NUMBER, Primary=1)
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("WQOA"."MON_ACCOUNT_ID"="MA"."ID")
5 - filter("WQOA"."ORG_ID"=:P_ORG_ID)
6 - access("MA"."ID"="MAPY"."MON_ACCOUNT_ID")
8 - filter("MA"."ORG_ID_PROVIDER"=:P_ORG_ID)
9 - access("MAPY"."ID"="MAPC"."MON_ACCOUNT_PAYER_ID")
11 - access(ROWID=ROWID)
15 - access(ROWID=ROWID)
18 - access("MAPC"."ID"="MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "MAPCS"."ID"[NUMBER,22], "MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID"[NUMBER,22], "MAPCS"."CE_SERVICE_ID"[NUMBER,22]
2 - "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPCS".ROWID[ROWID,10], "MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID"[NUMBER,22]
3 - (#keys=1) "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPC"."ID"[NUMBER,22]
4 - "WQOA"."MON_ACCOUNT_ID"[NUMBER,22]
5 - "WQOA"."MON_ACCOUNT_ID"[NUMBER,22]
6 - (#keys=1) "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22], "MAPC"."ID"[NUMBER,22]
7 - "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22]
8 - "MA"."ID"[NUMBER,22], "MA"."ORG_ID_PROVIDER"[NUMBER,22]
9 - (#keys=1) "MAPC"."ID"[NUMBER,22], "MAPY"."MON_ACCOUNT_ID"[NUMBER,22]
10 - "MAPC"."ID"[NUMBER,22], "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22]
11 - (#keys=1) "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22], "MAPC"."ID"[NUMBER,22]
12 - ROWID[ROWID,10], "MAPC"."MON_ACCOUNT_PAYER_ID"[NUMBER,22]
13 - ROWID[ROWID,10], "MAPC"."ID"[NUMBER,22]
14 - "MAPY"."ID"[NUMBER,22], "MAPY"."MON_ACCOUNT_ID"[NUMBER,22]
15 - (#keys=1) "MAPY"."MON_ACCOUNT_ID"[NUMBER,22], "MAPY"."ID"[NUMBER,22]
16 - ROWID[ROWID,10], "MAPY"."MON_ACCOUNT_ID"[NUMBER,22]
17 - ROWID[ROWID,10], "MAPY"."ID"[NUMBER,22]
18 - "MAPCS".ROWID[ROWID,10], "MAPCS"."MON_ACCT_PAYER_CALC_SUMMARY_ID"[NUMBER,22]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment