-
Hi
Please use code tags to post the plan i am afraid without that the plan is unreadable
regards
Hrishy
-
i can't update here properly if i copy that file from notepad and paste here it aligns to left.I have edited previous thread manully but still it doesn't shows any major change,it move only one space
-
Hi
You need to learn how to use the followng tags and enclose your message in those tags
click on the # symbol and then insert the plan with the query in those tags
regards
Hrishy
-
Code:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc | Cost |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 399 | 23142| | 5337 |
| 1 | SORT ORDER BY | | 399 | 23142| | 5337 |
|*2 | HASH JOIN SEMI | | 399 | 23142| | 5326 |
|*3 | HASH JOIN | | 399 | 21546| | 2570 |
|*4 | HASH JOIN | | 7084 | 290K| | 1810 |
|*5 | TABLE ACCESS FULL | PERSON | 7084 | 172K| | 703 |
| 6 | VIEW | VW_SQ_1 | 87401 | 1450K| | 1092 |
| 7 | SORT GROUP BY | | 87401 | 682K| 5360K | 1092 |
|*8 | INDEX FAST FULL SCAN | REGIMEN_PRES_PK | 184K| 1438K| | 413 |
|*9 | TABLE ACCESS FULL | REGIMEN_PRES | 61385 | 719K| | 745 |
|10 | VIEW | VW_NSO_2 | 209K| 817K| | 2753 |
|* 11 | HASH JOIN | | 209K| 4495K| 1080K | 2753 |
|* 12 | HASH JOIN | | 36761 | 646K| | 2190 |
|* 13 | TABLE ACCESS FULL | JOB_QUEUE | 36761 | 358K| | 1466 |
|* 14 | TABLE ACCESS FULL | PERSON | 40479 | 316K| | 703 |
| 15 | INDEX FAST FULL SCAN | REGIMEN_PRES_QX28 | 552K| 2158K| | 373 |
--------------------------------------------------------------------------------------------------------------------------------------
SELECT rp.person_nr,
rp.regimen_sequence,
rp.regimen_type_code
FROM REGIMEN_PRES rp,
PERSON p
WHERE regimen_type_code = 'CD'
AND prescription_status_code= 'FNSH'
AND rp.person_nr = p.person_nr
and p.email not like '%test%'
AND regimen_sequence = (SELECT MAX(regimen_sequence)
FROM REGIMEN_PRES r
WHERE r.person_nr = p.person_nr
AND regimen_type_code = 'CD')
AND p.person_nr IN (SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'))
ORDER BY p.person_nr;
Query copied from previous thread
thnx Hrishy
Last edited by ams-jamali; 08-31-2007 at 05:59 AM.
Reason: increase details
-
Hi
I wish you would have learned how to post and ask a question
Code:
SELECT rp.person_nr,
rp.regimen_sequence,
rp.regimen_type_code
FROM REGIMEN_PRES rp,
PERSON p
WHERE regimen_type_code = 'CD'
AND prescription_status_code= 'FNSH'
AND rp.person_nr = p.person_nr
and p.email not like '%test%'
AND regimen_sequence = (SELECT MAX(regimen_sequence)
FROM REGIMEN_PRES r
WHERE r.person_nr = p.person_nr
AND regimen_type_code = 'CD')
AND p.person_nr IN (SELECT a.person_nr
FROM JOB_QUEUE b,
REGIMEN_PRES rp,
PERSON a
WHERE a.person_nr = b.person_nr
AND a.person_nr = rp.person_nr
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'))
ORDER BY p.person_nr;
Plan is
Code:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc | Cost |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 399 | 23142| | 5337 |
| 1 | SORT ORDER BY | | 399 | 23142| | 5337 |
|*2 | HASH JOIN SEMI | | 399 | 23142| | 5326 |
|*3 | HASH JOIN | | 399 | 21546| | 2570 |
|*4 | HASH JOIN | | 7084 | 290K| | 1810 |
|*5 | TABLE ACCESS FULL | PERSON | 7084 | 172K| | 703 |
| 6 | VIEW | VW_SQ_1 | 87401 | 1450K| | 1092 |
| 7 | SORT GROUP BY | | 87401 | 682K| 5360K | 1092 |
|*8 | INDEX FAST FULL SCAN | REGIMEN_PRES_PK | 184K| 1438K| | 413 |
|*9 | TABLE ACCESS FULL | REGIMEN_PRES | 61385 | 719K| | 745 |
|10 | VIEW | VW_NSO_2 | 209K| 817K| | 2753 |
|* 11 | HASH JOIN | | 209K| 4495K| 1080K | 2753 |
|* 12 | HASH JOIN | | 36761 | 646K| | 2190 |
|* 13 | TABLE ACCESS FULL | JOB_QUEUE | 36761 | 358K| | 1466 |
|* 14 | TABLE ACCESS FULL | PERSON | 40479 | 316K| | 703 |
| 15 | INDEX FAST FULL SCAN | REGIMEN_PRES_QX28 | 552K| 2158K| | 373 |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PERSON_NR"="VW_NSO_2"."$nso_col_1")
3 - access("RP"."PERSON_NR"="P"."PERSON_NR" AND "RP"."REGIMEN_SEQUENCE"="VW_SQ_1"."VW_COL_1")
4 - access("VW_SQ_1"."PERSON_NR"="P"."PERSON_NR")
5 - filter("P"."EMAIL" NOT LIKE '%test%')
8 - filter("R"."REGIMEN_TYPE_CODE"='CD')
9 - filter("RP"."REGIMEN_TYPE_CODE"='CD' AND "RP"."PRESCRIPTION_STATUS_CODE"='FNSH')
11 - access("A"."PERSON_NR"="RP"."PERSON_NR")
12 - access("A"."PERSON_NR"="B"."PERSON_NR")
13 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')
14 - filter("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG')
-
yes Hrishy,Thanks
I think PAVB lost his patient and went home
-
Hi
Can you create indexes on
a)PERSON (person_nr ,status_code)
b)JOB_QUEUE(job_queue_type_nr,status_code)
run dbms_stats to analyze the indexes and tables and post the query and the plan using code tags please
regards
Hrishy
-
I have indexes on these two columns in person and in job_queue table.
-
Hi
Nope i dont see composite indexes anywhere on these two tables
You need to have those composite indexes
PERSON (person_nr ,status_code)
JOB_QUEUE(job_queue_type_nr,status_code)
run dbms_stats and run the explain plan again
regards
Hrishy
-
Let me add a couple of suggestions.
1- Your main query is doing a Full Table Scan on PERSON table because of the p.email not like '%test%' predicate.
Since your are filtering out p.email like '%test%' I'm wondering if you can just delete these offending rows from PERSON table and get rid of your not like predicate.
2- For each row on your main query you are executing both of your inline views -namely subqueries. That means the individual performance of each one of your subqueries has a big effect in the overall performance.
I would focus in fine tuning each one of the subqueries -individually and then, fine tune the main query.
Just my two cents.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|