I'm focusing on learning how to read an explain plan and from all the documentation I've poured through, I'm amazed at how little information is given to explaining some of the basic things on the explain plan diagram, like how to interpret what each returned row value means. I understand some of this now but I'm trying to get more of an algorythmic way of reading row returns.
The plan below is an example of a bad query BTW that was recently tuned by an ARMY of "able minded" SQL Pros here on OTN. I'm using the bad plan because It'll better help me understand how this all works.
Just tell me if I'm right. Since these are E-ROWS the below are, of course, all Estimates.
Line 7 - Executed 758 times. Full Scans the table TEMP_NAVRECON_ENTITY and returned accesses 758 rows.
Line 8 - Executed 3257k times. Full Scans the table PROFIT_LOSS_LEDGER_ACTIVITY and returned accesses 3257k rows.
Line 6 - The hash join of the 2 tables above excecutes 1924k times and returned accesses 1924k rows.
Line 13 - Executed 1 times. Full Scans the table REAL_TIME_LEDGER_ACTIVITY and returned accesses 1 row?
Line 14 - Executed 891 times. Index Range Scans UK_STAR_PROFILE_GROUPS and returned accesses 891 rows.
Line 15 - Executed 1 time. Index Rowid Scans STAR_PROFILE_GROUPS and returned accesses 1 row?
Line 16 - Executed 758 times again. Full table scans TEMP_NAVRECON_ENTITY and returned accesses 758 rows.
Line 12 - Executed 891 times. Loops the below INDENTED processes 891 times. *** I really want to understand this one.**** ( is Line 12 executing the 891 steps of Line 14, 891 times ((So 891 * 891)) or is Line 14 being executed 891 by the directio0n of the loop on Line 12.
Line 11 - Executed 1 time. Executes everything indented below it once.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 172K(100)| | 1 |00:00:17.25 | 749K| 754K| 13741 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 |00:00:17.25 | 749K| 754K| 13741 | | | | |
| 2 | VIEW | | 1 | 1924K| | | 172K (2)| 00:34:33 | 2157K|00:00:17.14 | 749K| 754K| 13741 | | | | |
| 3 | SORT UNIQUE | | 1 | 1924K| 165M| 200M| 172K (2)| 00:34:33 | 2157K|00:00:16.90 | 749K| 754K| 13741 | 120M| 3732K| 97M (1)| 108K|
| 4 | UNION-ALL | | 1 | | | | | | 2157K|00:00:10.68 | 749K| 740K| 0 | | | | |
|* 5 | FILTER | | 1 | | | | | | 2157K|00:00:09.64 | 740K| 740K| 0 | | | | |
|* 6 | HASH JOIN | | 1 | 1924K| 165M| | 131K (3)| 00:26:15 | 2157K|00:00:09.35 | 740K| 740K| 0 | 1817K| 1817K| 1541K (0)| |
| 7 | TABLE ACCESS FULL | TEMP_NAVRECON_ENTITY | 1 | 758 | 7580 | | 2 (0)| 00:00:01 | 758 |00:00:00.01 | 4 | 0 | 0 | | | | |
|* 8 | TABLE ACCESS FULL | PROFIT_LOSS_LEDGER_ACTIVITY | 1 | 3257K| 248M| | 131K (3)| 00:26:14 | 3173K|00:00:08.21 | 740K| 740K| 0 | | | | |
| 9 | HASH GROUP BY | | 1 | 1 | 142 | | 1811 (2)| 00:00:22 | 0 |00:00:00.05 | 9249 | 0 | 0 | 791K| 791K| | |
|* 10 | HASH JOIN SEMI | | 1 | 1 | 142 | | 1809 (2)| 00:00:22 | 0 |00:00:00.05 | 9249 | 0 | 0 | 798K| 798K| 171K (0)| |
| 11 | NESTED LOOPS | | 1 | 1 | 132 | | 1806 (2)| 00:00:22 | 0 |00:00:00.05 | 9249 | 0 | 0 | | | | |
| 12 | NESTED LOOPS | | 1 | 891 | 132 | | 1806 (2)| 00:00:22 | 0 |00:00:00.05 | 9249 | 0 | 0 | | | | |
|* 13 | TABLE ACCESS FULL | REAL_TIME_LEDGER_ACTIVITY | 1 | 1 | 86 | | 1777 (2)| 00:00:22 | 0 |00:00:00.05 | 9249 | 0 | 0 | | | | |
|* 14 | INDEX RANGE SCAN | UK_STAR_PROFILE_GROUPS | 0 | 891 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID| STAR_PROFILE_GROUPS | 0 | 1 | 46 | | 29 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
| 16 | TABLE ACCESS FULL | TEMP_NAVRECON_ENTITY | 0 | 758 | 7580 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
If you need the query that goes along with this then:
SELECT
COUNT (*)
FROM (
SELECT /*+ gather_plan_statistics*/ m.portfolio_acct,
m.asset_id,
NULL,
m.acct_dt,
balance,
TRIM (long_desc),
TRIM (m.open_evt_id)
FROM usbank.profit_loss_ledger_activity m
WHERE m.portfolio_acct IN (SELECT entity_id
FROM usbank.temp_navrecon_entity)
AND m.acct_dt BETWEEN TO_DATE ( :p_start_date, 'YYYYMMDD')
AND TO_DATE ( :p_end_date, 'YYYYMMDD')
UNION
SELECT m.portfolio_acct,
m.asset_id,
NULL,
m.acct_dt,
balance,
TRIM (long_desc),
TRIM (m.open_evt_id)
FROM ( SELECT m.portfolio_acct,
m.asset_id,
m.acct_dt,
SUM (amount) balance,
TRIM (spg.section) long_desc,
m.open_evt_id
FROM usbank.real_time_ledger_activity m,
rulesdbo.star_profile_groups spg
WHERE TRIM (m.ledger_acct) = spg.line_account
AND m.acct_dt = TRUNC (SYSDATE)
AND spg.profile_name = 'USB_PNL'
AND spg.action = 'GROUP'
GROUP BY m.portfolio_acct,
m.asset_id,
spg.section,
m.open_evt_id,
m.acct_dt) m
WHERE m.portfolio_acct IN (SELECT entity_id
FROM usbank.temp_navrecon_entity));