query tuning

Printable View

Show 40 post(s) from this thread on one page

09-04-2007, 07:54 AM
ams-jamali

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)..,
09-04-2007, 07:56 AM
hrishy

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
09-04-2007, 08:05 AM
ams-jamali

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.
09-04-2007, 08:11 AM
hrishy

Hi

Can you please post the plan for the entire query using xplan

regards
Hrishy
09-04-2007, 10:02 AM
PAVB

Posting current version of the query wouldn't hurt either.
09-05-2007, 12:56 AM
ams-jamali

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.,
09-05-2007, 01:15 AM
hrishy

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
09-05-2007, 01:21 AM
ams-jamali

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.
09-05-2007, 01:27 AM
hrishy

Hi

The predicate information is still missing from the plan.

request the support team to remove test account from prod

regards
Hrishy
09-05-2007, 01:51 AM
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
09-05-2007, 02:15 AM
ams-jamali

Code:

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 - access("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("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG') 14 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')

sorry for the delay.,
09-05-2007, 02:29 AM
abhaysk

Quote:

Originally Posted by hrishy

Hi
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

regards
Hrishy

Hrishy,

A distinct is needed as was in my post, else he will get redundant data.

Rgds
Abhay
09-05-2007, 06:23 AM
ams-jamali

hrishy..,
I tried that query.., it takes looong time.,
I can use previous query itself.,

hrishy cool down pls.,
09-05-2007, 07:14 AM
PAVB

Quote:

Originally Posted by hrishy

At this rate i will loose my patience and might switch to a SYBASE forum

LMAO... I feel your pain Hrishy, this particular thread is totally out of control, pure insanity.

Show 40 post(s) from this thread on one page