|
-
hrishy here is the new XPLAN
Code:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1098 Card=47579 Bytes=1332212)
1 0 HASH JOIN (Cost=1098 Card=47579 Bytes=1332212)
2 1 HASH JOIN (Cost=951 Card=6862 Bytes=157826)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (Cost=293 Card=3710 Bytes=37100)
5 4 INDEX (RANGE SCAN) OF 'PERSON_QX101' (NON-UNIQUE) (Cost=10 Card=3710)
6 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=86078 Bytes=1119014)
7 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=112 Card=516810 Bytes=2584050)
Thanks..,
abhay..i will check ur's (thanks for ur help)..,
-
Hi
Did you run the dbms_stats the way is suggested on all the tables involved ?
Can you post the entire query with the plan using code tags and let me know the timing now ?
regards
Hrishy
-
Hi,
I ran only that sub-query which i have posted earlier,here is the timing & trace output.
Code:
Elapsed: 00:00:02.47
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1098 Card=47579 Byte
s=1332212)
1 0 HASH JOIN (Cost=1098 Card=47579 Bytes=1332212)
2 1 HASH JOIN (Cost=951 Card=6862 Bytes=157826)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (Cost=293
Card=3710 Bytes=37100)
5 4 INDEX (RANGE SCAN) OF 'PERSON_QX101' (NON-UNIQUE)
(Cost=10 Card=3710)
6 2 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=8607
8 Bytes=1119014)
7 1 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (N
ON-UNIQUE) (Cost=112 Card=516810 Bytes=2584050)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11530 consistent gets
6787 physical reads
0 redo size
528694 bytes sent via SQL*Net to client
27713 bytes received via SQL*Net from client
2476 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37115 rows processed
abhay u have specified as a.person_nr where u have specified that alias.
-
Hi
Can you please post the plan for the entire query using xplan
regards
Hrishy
-
Posting current version of the query wouldn't hurt either.
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.
-
here is the plan
Code:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2004 Card=823 Bytes=52672)
1 0 SORT (ORDER BY) (Cost=2004 Card=823 Bytes=52672)
2 1 HASH JOIN (SEMI) (Cost=1993 Card=823 Bytes=52672)
3 2 HASH JOIN (Cost=1182 Card=823 Bytes=48557)
4 3 HASH JOIN (Cost=811 Card=6987 Bytes=300441)
5 4 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=6988 Bytes=174700)
6 4 VIEW OF 'VW_SQ_1' (Cost=463 Card=68538 Bytes=1233684)
7 6 SORT (GROUP BY) (Cost=463 Card=68538 Bytes=753918)
8 7 INDEX (RANGE SCAN) OF 'REGIMEN_PRES_PK' (UNIQUE) (Cost=463 Card=157490 Bytes=1732390)
9 3 TABLE ACCESS (FULL) OF 'REGIMEN_PRES' (Cost=343 Card=151810 Bytes=2428960)
10 2 VIEW OF 'VW_NSO_2' (Cost=809 Card=47579 Bytes=237895)
11 10 HASH JOIN (Cost=809 Card=47579 Bytes=1332212)
12 11 HASH JOIN (Cost=662 Card=6862 Bytes=157826)
13 12 INDEX (FAST FULL SCAN) OF 'PERSON_QX101' (NON-UNIQUE) (Cost=4 Card=3710 Bytes=37100)
14 12 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=86078 Bytes=1119014)
15 11 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=112 Card=516810 Bytes=2584050)
abhay.. your query hangs my testbox.,
-
Hi
Please post the query and the entire plan in one answer PLEASE.SO i dont have to refer back and forth for the plan and the query
PAVB asked you to remove where email not like '%test%' after cleaning the testing data from production.Did you try that in the query
You missed predicate information from the query
At this rate i will loose my patience and might switch to a SYBASE forum
Dont blindly cut paste Abhays query ponder over it for a while think and then try it in your box
regards
Hrishy
Last edited by hrishy; 09-05-2007 at 01:17 AM.
-
ok here is it
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;
Code:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2004 Card=823 Bytes=52672)
1 0 SORT (ORDER BY) (Cost=2004 Card=823 Bytes=52672)
2 1 HASH JOIN (SEMI) (Cost=1993 Card=823 Bytes=52672)
3 2 HASH JOIN (Cost=1182 Card=823 Bytes=48557)
4 3 HASH JOIN (Cost=811 Card=6987 Bytes=300441)
5 4 TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=6988 Bytes=174700)
6 4 VIEW OF 'VW_SQ_1' (Cost=463 Card=68538 Bytes=1233684)
7 6 SORT (GROUP BY) (Cost=463 Card=68538 Bytes=753918)
8 7 INDEX (RANGE SCAN) OF 'REGIMEN_PRES_PK' (UNIQUE) (Cost=463 Card=157490 Bytes=1732390)
9 3 TABLE ACCESS (FULL) OF 'REGIMEN_PRES' (Cost=343 Card=151810 Bytes=2428960)
10 2 VIEW OF 'VW_NSO_2' (Cost=809 Card=47579 Bytes=237895)
11 10 HASH JOIN (Cost=809 Card=47579 Bytes=1332212)
12 11 HASH JOIN (Cost=662 Card=6862 Bytes=157826)
13 12 INDEX (FAST FULL SCAN) OF 'PERSON_QX101' (NON-UNIQUE) (Cost=4 Card=3710 Bytes=37100)
14 12 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=86078 Bytes=1119014)
15 11 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=112 Card=516810 Bytes=2584050)
I can't remove that test account from prod they are created by suport team.
-
Hi
The predicate information is still missing from the plan.
request the support team to remove test account from prod
regards
Hrishy
-
Hi
You also need a index on
REGIMEN_PRES (person_nr,regimen_type_code,prescription_status_code)
And Abhay is right you have used some regimen_pres table twice
Try This as Abhay Suggested
Code:
SELECT
rp.person_nr,
FIRST_VALUE(rp.regimen_sequence)
OVER (PARTITION BY rp.person_nr, rp.regimen_type_code
order by rp.regimen_sequence desc ) regimen_sequence,
rp.regimen_type_code
FROM REGIMEN_PRES rp,
PERSON p
WHERE
rp.person_nr = p.person_nr
AND rp.regimen_type_code = 'CD'
AND rp.prescription_status_code= 'FNSH'
and p.email not like '%test%'
AND p.person_nr IN (SELECT b.person_nr
FROM JOB_QUEUE b
WHERE
AND b.job_queue_type_nr = 4
AND b.status_code = 'FNSH'
AND (a.status_code = 'ACTV' or
a.status_code = 'CHNG'))
ORDER BY rp.person_nr
Best of luck with this .
Sorry i lost my patience
regards
Hrishy
Last edited by hrishy; 09-05-2007 at 02:11 AM.
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
|
Reply With Quote