Order by clause slows my query

[Ramg]

I have a query,which runs in 2 sec without order by..when used with order by it takes 29 sec. It is 9.2.0/dedicated server using pga_aggregate_target.

My question is

1) How to verify/confirm it is using temp or just sort_area_size(part of pga_aggregate_target)?

2) In case if it is using only sort_area, what else might have causes for this slowness.

Appreciate any help.

[svk]

how much data is the query returning ? How many columns does the "oder by" clause has ?

Also, when you say "it completes in 2 sec without order by", how are you testing it ? SQL*Plus ? Or, some other front end. Are you allowing the query to really complete ?

[Sharma]

Try this, this will give you blocks used in temp. tablespace.

SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr;

[Ramg]

I am using Sql*plus with "set timing on" to measure time. Number of rows 110K.

Here is the Query if anyone has patience to go thro':

SELECT * FROM(
SELECT ROWNUM rn,clienttab.*
FROM
(SELECT
ORDER_CNTCT_ID AS ORDER_CONTACT_ID,
CONTACT_PH_NO||CONTACT_PH_EXT_NO AS CONTACT_NO,
ORDER_NUM AS "WO/MO Number",ORDER_TYP_CD AS TYPE,
AI_NO AS "AI Number",FLD_LOC_NO AS DISTRICT_NO,
PLANNER_NAME AS PLANNER,
ORDR_EXTRCT_DATE AS EXTRACTION_DATE ,
CONTACT_NAME AS CUSTOMER,
JOB_ADDR AS PROJECT_ADDRESS,
WAM_Pk_Common.WAM_F_Get_Code_Desc('WAMUR_ORDER_CNTCT','ORDER_STAT_CD',ORDER_STAT_CD) AS
"WO/MO Status",
WAM_Pk_Common.WAM_F_Get_Code_Desc('WAMUR_ORDER_CNTCT','ORDR_CNTCT_STAT_CD',
ORDR_CNTCT_STAT_CD) AS STATUS,
ORDR_CNTCT_STAT_CD AS ACTUAL_STATUS,
DECODE(ORDR_CNTCT_STAT_CD,'NEW','FOLLOWUP',ORDR_CNTCT_STAT_CD) AS NEW_STATUS,
FLD_CNTCT_PH_NO AS FIELD_CNTCT_NUM,
LAST_UPDT_TMSTMP AS TIMESTAMP,
LAST_UPDT_USER_NUM AS USERNUM,
EXPIRY_INTERVAL_VAL AS EXPIRY_INTERVAL,
WAM_Pk_Common.Time_Diff((LAST_UPDT_TMSTMP+ NUMTODSINTERVAL(EXPIRY_INTERVAL_VAL,'MINUTE')), SYSTIMESTAMP) AS TIME_VAL
FROM WAMUR_ORDER_CNTCT
WHERE
ORDR_CNTCT_STAT_CD IN ('NEW','FOLLOWUP','NEWCM')
AND
PLNR_NAM_EXCP_FLAG <> 'Y'
ORDER BY CONTACT_PH_NO||CONTACT_PH_EXT_NO, FLD_CNTCT_PH_NO,
NEW_STATUS, ORDR_CNTCT_STAT_CD, ORDR_EXTRCT_DATE, ORDER_CNTCT_ID
) clienttab
WHERE ROWNUM<= 10)
WHERE rn >= 1;

[Ramg]

That's the reason I didn't post the code initially..People scared of lengthy code.Naturally! can't blame anyone!!

[slimdave]

Run a level 8 10046 trace and see what you are waiting on. You might be seeingg long waits on reading/writing to a temporary tablespace.