explain shows enormous values for temp_space, bytes and rows ............
Date: 27 Mar 2003 16:34:26 -0800
Message-ID: <e2a16bc8.0303271634.2b251984_at_posting.google.com>
I have a query which is taking way too much temp space and filling up more than 30 GB of temp space. I know am doing lot of sorts and hash joins. I did explain plan on the query, and it shows enormous values viz 40 TB of rows, 4,000 TB of bytes, 2,000 TB of temp_space.
I analyzed all the tables and indexes using compute statistics. I do give that these are large tables, but I feel that I missed out something very obvious, cos I have never seen these unearthly numbers.
Has anyone seen this type of problem? The query is below.
Appreciate your help.
Sravan
explain plan for
SELECT t0.patient_account_no,
t0.unique_id, t2.code, t2.code
FROM encounter_568524992 t0,
person t1,
person_system_map t2,
person_system_map t3,
person t4,
practitioner t5,
enc_pract_568524992 t6,
enc_sitem_568524992 t7
WHERE t5.lookup_cd (+) = 6
AND t3.person_id=t4.unique_id (+) AND t5.person_id(+)=t4.unique_id AND t6.practitioner_id(+)=t5.common_id AND t7.order_practitioner_id(+)=t6.unique_id AND t2.person_id(+)=t1.unique_id AND t0.person_id=t1.unique_id (+)
/
SQL> _at_?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT --------------------------| Rows |
| Id | Operation | Name
Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT |
| 43T|
4758T| | 11G|
| 1 | MERGE JOIN OUTER |
| 43T|
4758T| | 11G|
| 2 | SORT JOIN |
| 8923G|
925T| 2045T| 11G|
|* 3 | HASH JOIN OUTER |
| 8923G|
925T| 6120G| 1350M|
| 4 | NESTED LOOPS OUTER |
| 58G|
5464G| | 1241M|
|* 5 | HASH JOIN OUTER |
| 58G|
4590G| 4863G| 159M|
| 6 | MERGE JOIN CARTESIAN |
| 58G|
4207G| | 76M|
|* 7 | HASH JOIN OUTER |
| 147K|
9M| 5936K| 4775 |
|* 8 | HASH JOIN OUTER |
| 141K|
4274K| 4968K| 3854 |
| 9 | TABLE ACCESS FULL | ENCOUNTER_568524992
| 141K|
3309K| | 1933 |
| 10 | TABLE ACCESS FULL | PERSON
| 382K|
2616K| | 1733 |
| 11 | TABLE ACCESS FULL | PERSON_SYSTEM_MAP
| 398K|
14M| | 519 |
| 12 | BUFFER SORT |
| 398K|
2722K| | 76M|
| 13 | TABLE ACCESS FULL | PERSON_SYSTEM_MAP
| 398K|
2722K| | 519 |
| 14 | TABLE ACCESS FULL | PERSON
| 382K|
2616K| | 1733 |
| 15 | TABLE ACCESS BY INDEX ROWID | PRACTITIONER
| 1 |
16 | | 1241M|
| 16 | BITMAP CONVERSION TO ROWIDS |
| |
| | |
| 17 | BITMAP AND |
| |
| | |
| 18 | BITMAP CONVERSION FROM ROWIDS|
| |
| | |
|* 19 | INDEX RANGE SCAN | PRACTITIONER_4X
| 3 |
| | |
| 20 | BITMAP CONVERSION FROM ROWIDS|
| |
| | | |* 21 | INDEX RANGE SCAN | PRACTITIONER_3X
| 3 |
| | 11 |
| 22 | TABLE ACCESS FULL | ENC_PRACT_568524992
| 598K|
8176K| | 607 | |* 23 | SORT JOIN |
| 3351K|
22M| 102M| 9214 |
| 24 | INDEX FAST FULL SCAN | ENC_SITEM_6X_568524992
| 3351K|
22M| | 1167 |
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
3 - access("T6"."PRACTITIONER_ID"(+)="T5"."COMMON_ID")
5 - access("T3"."PERSON_ID"="T4"."UNIQUE_ID"(+))
7 - access("T2"."PERSON_ID"(+)="T1"."UNIQUE_ID")
8 - access("T0"."PERSON_ID"="T1"."UNIQUE_ID"(+))
19 - access("T5"."PERSON_ID"(+)="T4"."UNIQUE_ID")
21 - access("T5"."LOOKUP_CD"(+)=6)
23 - access("T7"."ORDER_PRACTITIONER_ID"(+)="T6"."UNIQUE_ID")
filter("T7"."ORDER_PRACTITIONER_ID"(+)="T6"."UNIQUE_ID")
Note: cpu costing is off
44 rows selected.
Received on Fri Mar 28 2003 - 01:34:26 CET