Home » RDBMS Server » Performance Tuning » slow query (10.2.3.0)
slow query [message #291496] Fri, 04 January 2008 07:09 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
The following SQL is very slow, and i guss its because of the full table scan on the PATENT table ( see explain plan below). This table is queried by most user sessions.

SQL> SELECT /*+ NOREWRITE */ LAST_DAY(o111496.REF_REQ_RECEIVED_DATE) as C_3, CASE WHEN o111442.Z_CON
TRACTED_UNITS LIKE '%5NN%' THEN 'C' ELSE 'NCA' END as C_2, TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-
MON-YYYY') as C_1, SUBSTR(o111496.COMMISSIONER_CODE, 0, 3) as C_4, o111496.FIRST_ATTENDANCE as E1135
69, o111496.PROVIDER_CODE as E113587, o111496.PRACTICE_CODE_OF_REGISTERED_GP as E113655, o111496.Z_F
IN_YEAR as E113693, SUM(o111496.Z_PBR_COUNT) as E113695_SUM FROM PBR.MONTHLY_MONITORING_OUT o111442,
 PBR.PATENT o111496 WHERE ( (o111496.X_SEQNO = o111442.X_SEQNO)) AND ( ( UPPER(o111496.X_PCT_FL
AG) IN (PBR.APEX_PCT_USER_ACCESS(UPPER(USER))) OR UPPER(o111496.X_PCT_FLAG) LIKE PBR.APEX_PCT_USER_A
CCESS(UPPER(USER)) ) ) AND (o111496.APPOINTMENT_DATE >= TO_DATE('20071001000000', 'YYYYMMDDHH24MISS'
)) AND (o111496.APPOINTMENT_DATE <= TO_DATE('20071031000000', 'YYYYMMDDHH24MISS')) AND (o111496.ATTE
NDED_OR_DID_NOT_ATTEND IN ('5', '6') AND o111496.FIRST_ATTENDANCE = '1' AND o111496.SOURCE_OF_REFERR
AL IN ('03', '92')) AND (o111496.PRACTICE_CODE_OF_REGISTERED_GP IN ('N81005', 'N81006', 'N81009', 'N
81017', 'N81018', 'N81023', 'N81030', 'N81031', 'N81034', 'N81038', 'N81046', 'N81050', 'N81060', 'N
81063', 'N81079', 'N81080', 'N81081', 'N81082', 'N81091', 'N81092', 'N81093', 'N81094', 'N81095', 'N
81100', 'N81101', 'N81102', 'N81104', 'N81115', 'N81117', 'N81120', 'N81121', 'N81125', 'N81126', 'N
81607', 'N81614', 'N81624', 'N81626', 'N81655')) AND (( SUBSTR(o111496 .COMMISSIONER_CODE, 0, 3) ) I
N ('5NN')) GROUP BY LAST_DAY(o111496.REF_REQ_RECEIVED_DATE), CASE WHEN o111442.Z_CONTRACTED_UNITS LI
KE '%5NN%' THEN 'C' ELSE 'NCA' END, TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-MON-YYYY'), SUBSTR(o111
496.COMMISSIONER_CODE, 0, 3), o111496.FIRST_ATTENDANCE, o111496.PROVIDER_CODE, o111496.PRACTICE_CODE
_OF_REGISTERED_GP, o111496.Z_FIN_YEAR;


Execution Plan
----------------------------------------------------------

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

| Id  | Operation                    | Name                   | Rows  | Bytes |
Cost (%CPU)|

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

|   0 | SELECT STATEMENT             |                        |     1 |    95 |
57981   (2)|

|   1 |  HASH GROUP BY               |                        |     1 |    95 |
57981   (2)|

|   2 |   TABLE ACCESS BY INDEX ROWID| MONTHLY_MONITORING_OUT |     1 |    32 |
    3   (0)|

|   3 |    NESTED LOOPS              |                        |     1 |    95 |
57980   (2)|

|   4 |     TABLE ACCESS FULL        | PATENT            |     1 |    63 |
57977   (2)|

|   5 |     INDEX RANGE SCAN         | PBR_MMON_OUT_SEQNO     |     1 |       |
    2   (0)|

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


Note
-----
   - 'PLAN_TABLE' is old version



following indexes are on the table

1
 SQL> SELECT INDEX_NAME,COLUMN_NAME,COLUMN_POSITION

  2* FROM DBA_IND_COLUMNS WHERE TABLE_NAME='PATENT'

SQL> /

 

INDEX_NAME                     COLUMN_NAME                      

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

PBR_OP_SEQNO                   X_SEQNO                          

PBR_OP_APP_DATE                APPOINTMENT_DATE                 

PBR_OP_COM_CODE                COMMISSIONER_CODE                

PBR_OP_REGISTERED_GP           REGISTERED_GP  



any help will be appreciated


[Edited By DreamzZ ] [ Code tags embeded]

[Updated on: Fri, 04 January 2008 23:58] by Moderator

Report message to a moderator

Re: slow query [message #291676 is a reply to message #291496] Sat, 05 January 2008 17:17 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
How many rows are in the patent table after the filter conditions have been applied? If very few rows, then a full table scan may be the best execution plan.

Is there an index on monthly_monitoring_unit.x_seqno?

Are your statistics current?

Why are you using the norewrite hint?

You have a lot of unnecessary parentheses that could be eliminated.

Where you have something like (a in (b) or a like (b)) isn't that the same as (a like b)? Why not eliminate the extra first part?

You might benefit from function-based indexes on the columns that you apply upper and substr to, such as UPPER(o111496.X_PCT_FLAG)
and SUBSTR(o111496 .COMMISSIONER_CODE, 0, 3). That would require that query rewrite be enabled.

Are the values all constants or are they variable? If they are variable, then you should be using bind variables.

How do you expect anyone to read the unformatted mess that you posted? Below I have listed, first a formatted version of what you posted, then a partially cleaned up version. Next time, please format it prior to posting.


-- formatted original:
SELECT /*+ NOREWRITE */ LAST_DAY(o111496.REF_REQ_RECEIVED_DATE) as C_3, 
       CASE WHEN o111442.Z_CONTRACTED_UNITS LIKE '%5NN%' 
            THEN 'C' 
            ELSE 'NCA' 
       END                                                      as C_2, 
       TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-MON-YYYY')      as C_1, 
       SUBSTR(o111496.COMMISSIONER_CODE, 0, 3)                  as C_4, 
       o111496.FIRST_ATTENDANCE                                 as E113569, 
       o111496.PROVIDER_CODE                                    as E113587, 
       o111496.PRACTICE_CODE_OF_REGISTERED_GP                   as E113655, 
       o111496.Z_FIN_YEAR                                       as E113693, 
       SUM(o111496.Z_PBR_COUNT)                                 as E113695_SUM 
FROM   PBR.MONTHLY_MONITORING_OUT o111442,
       PBR.PATENT                 o111496 
WHERE  ( (o111496.X_SEQNO = o111442.X_SEQNO)) 
AND    ( ( UPPER(o111496.X_PCT_FLAG) IN (PBR.APEX_PCT_USER_ACCESS(UPPER(USER))) 
           OR UPPER(o111496.X_PCT_FLAG) LIKE PBR.APEX_PCT_USER_ACCESS(UPPER(USER)) ) ) 
AND    (o111496.APPOINTMENT_DATE >= TO_DATE('20071001000000', 'YYYYMMDDHH24MISS')) 
AND    (o111496.APPOINTMENT_DATE <= TO_DATE('20071031000000', 'YYYYMMDDHH24MISS')) 
AND    (o111496.ATTENDED_OR_DID_NOT_ATTEND IN ('5', '6') 
        AND o111496.FIRST_ATTENDANCE = '1' 
        AND o111496.SOURCE_OF_REFERRAL IN ('03', '92')) 
AND    (o111496.PRACTICE_CODE_OF_REGISTERED_GP IN 
       ('N81005', 'N81006', 'N81009', 'N81017', 'N81018', 'N81023', 'N81030', 'N81031', 'N81034', 
        'N81038', 'N81046', 'N81050', 'N81060', 'N81063', 'N81079', 'N81080', 'N81081', 'N81082', 
        'N81091', 'N81092', 'N81093', 'N81094', 'N81095', 'N81100', 'N81101', 'N81102', 'N81104', 
        'N81115', 'N81117', 'N81120', 'N81121', 'N81125', 'N81126', 'N81607', 'N81614', 'N81624', 
        'N81626', 'N81655')) 
AND    (( SUBSTR(o111496 .COMMISSIONER_CODE, 0, 3) ) IN ('5NN')) 
GROUP  BY LAST_DAY(o111496.REF_REQ_RECEIVED_DATE), 
          CASE WHEN o111442.Z_CONTRACTED_UNITS LIKE '%5NN%' 
               THEN 'C' 
               ELSE 'NCA' 
          END, 
          TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-MON-YYYY'), 
          SUBSTR(o111496.COMMISSIONER_CODE, 0, 3), 
          o111496.FIRST_ATTENDANCE, 
          o111496.PROVIDER_CODE, 
          o111496.PRACTICE_CODE_OF_REGISTERED_GP, 
          o111496.Z_FIN_YEAR;



-- partial cleanup:
SELECT LAST_DAY(o111496.REF_REQ_RECEIVED_DATE)                  as C_3, 
       CASE WHEN o111442.Z_CONTRACTED_UNITS LIKE '%5NN%' 
            THEN 'C' 
            ELSE 'NCA' 
       END                                                      as C_2, 
       TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-MON-YYYY')      as C_1, 
       SUBSTR(o111496.COMMISSIONER_CODE, 0, 3)                  as C_4, 
       o111496.FIRST_ATTENDANCE                                 as E113569, 
       o111496.PROVIDER_CODE                                    as E113587, 
       o111496.PRACTICE_CODE_OF_REGISTERED_GP                   as E113655, 
       o111496.Z_FIN_YEAR                                       as E113693, 
       SUM(o111496.Z_PBR_COUNT)                                 as E113695_SUM 
FROM   PBR.MONTHLY_MONITORING_OUT o111442,
       PBR.PATENT                 o111496 
WHERE  o111496.X_SEQNO = o111442.X_SEQNO  
AND    UPPER(o111496.X_PCT_FLAG) LIKE PBR.APEX_PCT_USER_ACCESS(UPPER(USER))  
AND    o111496.APPOINTMENT_DATE >= TO_DATE('20071001000000', 'YYYYMMDDHH24MISS')  
AND    o111496.APPOINTMENT_DATE <= TO_DATE('20071031000000', 'YYYYMMDDHH24MISS')  
AND    o111496.ATTENDED_OR_DID_NOT_ATTEND IN ('5', '6') 
AND    o111496.FIRST_ATTENDANCE = '1' 
AND    o111496.SOURCE_OF_REFERRAL IN ('03', '92')  
AND    o111496.PRACTICE_CODE_OF_REGISTERED_GP IN 
       ('N81005', 'N81006', 'N81009', 'N81017', 'N81018', 'N81023', 'N81030', 'N81031', 'N81034', 
        'N81038', 'N81046', 'N81050', 'N81060', 'N81063', 'N81079', 'N81080', 'N81081', 'N81082', 
        'N81091', 'N81092', 'N81093', 'N81094', 'N81095', 'N81100', 'N81101', 'N81102', 'N81104', 
        'N81115', 'N81117', 'N81120', 'N81121', 'N81125', 'N81126', 'N81607', 'N81614', 'N81624', 
        'N81626', 'N81655')  
AND    SUBSTR(o111496 .COMMISSIONER_CODE, 0, 3) = '5NN'    
GROUP  BY LAST_DAY(o111496.REF_REQ_RECEIVED_DATE), 
          CASE WHEN o111442.Z_CONTRACTED_UNITS LIKE '%5NN%' 
               THEN 'C' 
               ELSE 'NCA' 
          END, 
          TO_DATE('01-'||o111442.Z_MONTH_YEAR, 'DD-MON-YYYY'), 
          SUBSTR(o111496.COMMISSIONER_CODE, 0, 3), 
          o111496.FIRST_ATTENDANCE, 
          o111496.PROVIDER_CODE, 
          o111496.PRACTICE_CODE_OF_REGISTERED_GP, 
          o111496.Z_FIN_YEAR;



Previous Topic: sql query running for too long .....
Next Topic: SGA - Buffer cache size
Goto Forum:
  


Current Time: Fri Dec 09 11:37:16 CST 2016

Total time taken to generate the page: 0.09539 seconds