query tuning

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

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.