-
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
-
Hi
You should run update stats on all tables and indexes
You should also post the plan using dbms_xplan which includes everything otherwise you wont get good respnoses from folks here
regards
Hrishy
-
here is the X_plan after i done stats on affected tables and indexes.
Plan
--------------------------------------------------------------------------
SELECT STATEMENT
SORT ORDER BY
HASH JOIN SEMI
HASH JOIN
HASH JOIN
TABLE ACCESS FULL PERSON
VIEW VW_SQ_1
SORT GROUP BY
INDEX FAST FULL SCAN REGIMEN_PRES_PK
TABLE ACCESS FULL REGIMEN_PRES
VIEW VW_NSO_2
HASH JOIN
HASH JOIN
TABLE ACCESS FULL PERSON
TABLE ACCESS FULL JOB_QUEUE
INDEX FAST FULL SCAN REGIMEN_PRES_QX28
16 rows selected.
Same thing it shows nothing changed.
-
Hi
Please post the predicate information with the complete explain plan using code tags its very very difficult if you don't do so.
regards
Hrishy
-
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")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
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')
Note: cpu costing is off
Hrishy is this enough,if anythingelse i left pls specify.
thnx
Last edited by ams-jamali; 08-31-2007 at 05:09 AM.
Reason: increase details
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
|