query tuning

**ams-jamali** · 08-29-2007 06:03 AM

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.,

**PAVB** · 08-29-2007 08:02 AM

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 %*%"

**malay_biswal** · 08-29-2007 08:29 AM

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.

**dba1** · 08-29-2007 02:17 PM

You can remove table rp in the subquery. Seems to me it's not used in where clause.

**PAVB** · 08-29-2007 02:42 PM

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.

**ams-jamali** · 08-30-2007 01:07 AM

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.

**ams-jamali** · 08-31-2007 05:12 AM

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

**hrishy** · 08-31-2007 05:16 AM

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

**ams-jamali** · 08-31-2007 05:43 AM

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

**hrishy** · 08-31-2007 05:51 AM

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')

Thread: query tuning

Thread Tools

Display

Hybrid View

query tuning

Bookmarks

Bookmarks

Posting Permissions