query tuning - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 6 FirstFirst 12345 ... LastLast
Results 21 to 30 of 54

Thread: query tuning

  1. #21
    Join Date
    Jan 2007
    Posts
    231
    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.

  2. #22
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How about doing what hrishy suggested for subquery #2?
    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.

  3. #23
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    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.
    Looking for the greatest evil in the world? Look in the mirror.

  4. #24
    Join Date
    Jan 2007
    Posts
    231
    Code:
    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.

  5. #25
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ams-jamali
    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:

    Code:
    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.
    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.

  6. #26
    Join Date
    Jan 2001
    Posts
    2,828
    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

  7. #27
    Join Date
    Jan 2007
    Posts
    231
    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.

  8. #28
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

  9. #29
    Join Date
    Jan 2007
    Posts
    231
    here is the report,
    Code:
    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

  10. #30
    Join Date
    Jan 2001
    Posts
    2,828
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width