-
query tuning
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;
Here is the output of plan table:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5059 Card=516 Bytes=29928)
1 0 SORT (ORDER BY) (Cost=5059 Card=516 Bytes=29928)
2 1 HASH JOIN (SEMI) (Cost=5046 Card=516 Bytes=29928)
3 2 HASH JOIN (Cost=2413 Card=516 Bytes=27864)
4 3 HASH JOIN (Cost=1684 Card=7084 Bytes=297528)
5 4 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=7084 Bytes=177100)
6 4 VIEW OF 'VW_SQ_1' (Cost=969 Card=63454 Bytes=1078718)
7 6 SORT (GROUP BY) (Cost=969 Card=63454 Bytes=507632)
8 7 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_PK' (UNIQUE) (Cost=367 Card=176165 Bytes=1409320)
9 3 TABLE ACCESS (FULL) OF 'REGIMEN_PRES' (Cost=715 Card=58722 Bytes=704664)
10 2 VIEW OF 'VW_NSO_2' (Cost=2628 Card=342716 Bytes=1370864)
11 10 HASH JOIN (Cost=2628 Card=342716 Bytes=7539752)
12 11 HASH JOIN (Cost=2094 Card=42816 Bytes=770688)
13 12 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=40479 Bytes=323832)
14 12 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1369 Card=42817 Bytes=428170)
15 11 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (NON-UNIQUE) (Cost=348 Card=528495 Bytes=2113980)
I have index on person,job_queue & regimen_pres tables on that particular column also.Why it is accessing Full table scan.
please help.,
-
Please, next time enclose your code into CODE tags like...
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;
Questions:
1- Do you have up-to-date statistics on affected tables and indexes?
2- Could you publish your indexes DDL?
Comments:
Your code has everything it needs to perform badly,
a) you have two in-line views -which will execute once per each base row, one of them in an IN clause.
b) you have one of the ugliest predicates you can think about "not like %*%"
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.
-
You have indexes on those columns, that's fine. But how much data is being retrieved using those indexed columns. Usually if the data returned is more than 10% of total number of records it's better to go for a FTS, this 10% value change depending upon the situation. Hope this explains.
-
You can remove table rp in the subquery. Seems to me it's not used in where clause.
-
Originally Posted by dba1
You can remove table rp in the subquery. Seems to me it's not used in where clause.
You are wrong.
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.
-
1. I don't have up-to-date statistics on affected tables and indexes.I took that long back(may be 2-3 months).
2.
Table : JOB_QUEUE
INDEX NAME UNIQUE COLUMN
********* ****** ******
JOB_QUEUE_PK UNIQUE JOB_QUEUE_NR
JOB_QUEUE_QX11 NONUNIQUE JOB_QUEUE_TYPE_NR
JOB_QUEUE_QX12 NONUNIQUE STATUS_CODE
JOB_QUEUE_QX13 NONUNIQUE PERSON_NR
Table : PERSON
INDEX NAME UNIQUE COLUMN
********* ****** ******
PERSON_PK UNIQUE PERSON_NR
PERSON_AK_1 UNIQUE USERNAME
PERSON_IDX1 NONUNIQUE EMAIL
PERSON_QX101 NONUNIQUE STATUS_CODE
Table : REGIMEN_PRES
INDEX NAME UNIQUE COLUMN
********* ****** *******
REGIMEN_PRES_PK UNIQUE REGIMEN_TYPE_CODE
REGIMEN_PRES_PK UNIQUE PERSON_NR
REGIMEN_PRES_PK UNIQUE REGIMEN_SEQUENCE
REGIMEN_PRES_QX28 NONUNIQUE PERSON_NR
In regimen_pres table i have two index refer to same person_nr column.Here i have posted only the required column alone.
Last edited by ams-jamali; 08-30-2007 at 05:57 AM.
Reason: increase details
-
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')
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
|