query tuning

[ams-jamali]

Pavb U r in my way.

I too tried to fine tune the subquery,but nothing helped me(i.e, not major change).So i posted here to get some guru's idea.

[PAVB]

How about doing what hrishy suggested for subquery #2?

[Tuma]

Why don't you try to force the indexes or you can choose to accept the full scan...but can help it along with a PARALLEL HINT. In some cases it's quite good. Full table scans are not necessarily a bad thing. I have seen situations where indexes have performed worse.

[ams-jamali]

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'

      37115  rows processed

Elapsed: 00:00:34.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2435 Card=382725 Bytes=8802675)

   1    0   HASH JOIN (Cost=2435 Card=382725 Bytes=8802675)
   2    1     HASH JOIN (Cost=1981 Card=69850 Bytes=1257300)
   3    2       TABLE ACCESS (FULL) OF 'PERSON' (Cost=673 Card=33831 Bytes=338310)
   4    2       TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1266 Card=158302 Bytes=1266416)
   5    1     INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)





Statistics
----------------------------------------------------------
         35  recursive calls
          0  db block gets
      13929  consistent gets
          0  physical reads
          0  redo size
     424699  bytes sent via SQL*Net to client
      27709  bytes received via SQL*Net from client
       2476  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      37115  rows processed


37115 rows selected.

----------------------------------------------------------------------------------------------------------------------------------------

After creating composite index


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'
Elapsed: 00:00:02.68

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1479 Card=397716 Bytes=7556604)
   1    0   HASH JOIN (Cost=1479 Card=397716 Bytes=7556604)
   2    1     HASH JOIN (Cost=1028 Card=72586 Bytes=1016204)
   3    2       TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
   4    2       TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=164400 Bytes=986400)
   5    1     INDEX (FAST FULL SCAN) OF 'REGIMEN_PRESC_QX28' (NON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13918  consistent gets
          0  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

Hrishy thanks a lot,Plan doesn't changes.but execution time remarkable.Another subquery has no change.
please suggest to change NOT LIKE predicate.

[PAVB]

please suggest to change NOT LIKE predicate.

As far as I can see you have test data in your production database... bad mojo but, not the first time I see something like this.

You can either delete those test rows or filtering them out; since I do not know your environment I would filter them out after the query completes meaning, run your query as if all your data is valid and then filter it.

You can accomplish this by enveloping your query into an outer filter query, something like:

select pern,
       regs,
       regt
from
(
SELECT  rp.person_nr "pern",
        rp.regimen_sequence "regs",
        rp.regimen_type_code "regt",
        p.email "mail"
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%' 
...
...
...
)
where email not like '%test%' 
ORDER BY pern;

Note: Code was not tested, this is just a hint.

[hrishy]

Hi


Even though the plans are same the cardinalities are different.

So i am wondering now how do you collect stats .

Can you post exact statements which you use to collect stats

regards
Hrishy

[ams-jamali]

I gave only timing and trace output of that statement.

before creating comp.index i took timing with trace for that query(i ran that query twice same time returns,since it may vary due to buffer cache),then after creating index i ran that same query.I saw a big difference in timing.

[hrishy]

Hi

Can you run the query again and post the query and the plan again using code tags

regards
Hrishy

[ams-jamali]

here is the report,

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

Elapsed: 00:00:02.47

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1448 Card=225193 Bytes=5179439)
   1    0   HASH JOIN (Cost=1448 Card=225193 Bytes=5179439)
   2    1     HASH JOIN (Cost=1007 Card=41099 Bytes=739782)
   3    2       TABLE ACCESS (FULL) OF 'PERSON' (Cost=337 Card=34834 Bytes=278672)
   4    2       TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=654 Card=41100 Bytes=411000)
   5    1     INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (N
          ON-UNIQUE) (Cost=293 Card=496478 Bytes=2482390)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13918  consistent gets
      10271  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

So what u r expecting..?

To..,PAVB
There is a slight change in timing.(nearly 1sec).,thanks for your suggestion

[hrishy]

Hi

I want to see EXACTLY what statements do you use to gather your stats.


Your cardinalities still don't match to what you posted earlier by the way

regards
Hrishy