Home » RDBMS Server » Performance Tuning » slow query (10.2.3.0)
| slow query [message #291496] |
Fri, 04 January 2008 07:09  |
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  |
 |
Barbara Boehmer
Messages: 9104
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;
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 23:16:02 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
