OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » sql tuning help (oracle 10g,1,xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql tuning help [message #435784]

Fri, 18 December 2009 00:46

krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore

Member

Hi,
Below is my query.It's taking more more time.Some one can help to tune below query.Pls find the attached explain plan for this query.

SELECT 'Reading Comprehension' TEST_NAME,T.TEST_END_DATE TEST_SESSION_DATE,
C.POOL_VERSION_ID, I.CREATED_ON POOL_CREATED_DT,
C.ITEM_ID, C.ITEM_RESPONSE_ID, S.STUDENT_ID_PK, C.RESPONSE_KEY, C.IS_CORRECT RESPONSE_IS_CORRECT,
T.SCORE SCALE_SCORE, C.RESPONSE_DURATION, P.ITEM_KEY,
T.TEST_SESSION_DETAIL_ID, SYSDATE CREATED_ON
-- BULK COLLECT INTO TV_PSYCHO_DET

CAT_ITEM_PARAMETER P, CAT_ITEM_USER_RESPONSE C, TEST_SESSION_DETAIL T,
TEST_SESSION S, ITEM_POOL_VERSION I, TEST_DETAIL D
,INSTITUTION E
WHERE TRUNC(T.TEST_END_DATE) BETWEEN TO_DATE('01-11-09','dd-mm-yy') AND TO_DATE('30-11-09','dd-mm-yy')
AND D.TEST_NAME = 'Reading Comprehension'
AND T.TEST_SESSION_STATUS_ID = 3
AND I.POOL_AVAILABILITY='Y'
AND P.PRETEST=0 AND C.RESTART_FLAG=0
AND T.TEST_DETAIL_ID = D.TEST_DETAIL_ID
AND S.TEST_SESSION_ID = T.TEST_SESSION_ID
AND C.TEST_SESSION_DETAIL_ID = T.TEST_SESSION_DETAIL_ID
AND S.INSTITUTION_ID=E.INSTITUTION_ID
AND SUBSTR(E.INSTITUTION_ID_DISPLAY,8,3) '000'
AND I.ITEM_ID = C.ITEM_ID
AND P.ITEM_ID = I.ITEM_ID;

Attachment: explan.txt
(Size: 11.60KB, Downloaded 1716 times)

Report message to a moderator

Re: sql tuning help [message #435785 is a reply to message #435784]

Fri, 18 December 2009 00:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Something seems to be missing (such as the FROM clause).

Report message to a moderator

Re: sql tuning help [message #435796 is a reply to message #435784]

Fri, 18 December 2009 01:18

krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore

Member

corrected pls check ,it was missed.

SELECT 'Reading Comprehension' TEST_NAME,T.TEST_END_DATE TEST_SESSION_DATE,
C.POOL_VERSION_ID, I.CREATED_ON POOL_CREATED_DT,
C.ITEM_ID, C.ITEM_RESPONSE_ID, S.STUDENT_ID_PK, C.RESPONSE_KEY, C.IS_CORRECT RESPONSE_IS_CORRECT,
T.SCORE SCALE_SCORE, C.RESPONSE_DURATION, P.ITEM_KEY,
T.TEST_SESSION_DETAIL_ID, SYSDATE CREATED_ON
-- BULK COLLECT INTO TV_PSYCHO_DET
FROM
CAT_ITEM_PARAMETER P, CAT_ITEM_USER_RESPONSE C, TEST_SESSION_DETAIL T,
TEST_SESSION S, ITEM_POOL_VERSION I, TEST_DETAIL D
,INSTITUTION E
WHERE TRUNC(T.TEST_END_DATE) BETWEEN TO_DATE('01-11-09','dd-mm-yy') AND TO_DATE('30-11-09','dd-mm-yy')
AND D.TEST_NAME = 'Reading Comprehension'
AND T.TEST_SESSION_STATUS_ID = 3
AND I.POOL_AVAILABILITY='Y'
AND P.PRETEST=0 AND C.RESTART_FLAG=0
AND T.TEST_DETAIL_ID = D.TEST_DETAIL_ID
AND S.TEST_SESSION_ID = T.TEST_SESSION_ID
AND C.TEST_SESSION_DETAIL_ID = T.TEST_SESSION_DETAIL_ID
AND S.INSTITUTION_ID=E.INSTITUTION_ID
AND SUBSTR(E.INSTITUTION_ID_DISPLAY,8,3) '000'
AND I.ITEM_ID = C.ITEM_ID
AND P.ITEM_ID = I.ITEM_ID;

Report message to a moderator

Re: sql tuning help [message #435799 is a reply to message #435796]

Fri, 18 December 2009 01:32

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read SQL statement tuning.
Please also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: sql tuning help [message #435801 is a reply to message #435784]

Fri, 18 December 2009 01:40

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

This line is incomplete:

AND SUBSTR(E.INSTITUTION_ID_DISPLAY,8,3) '000'

Is it really that difficult to copy and paste the complete statement?

Report message to a moderator

Re: sql tuning help [message #435803 is a reply to message #435796]

Fri, 18 December 2009 01:50

_jum
Messages: 577
Registered: February 2008

Senior Member

You can omit
TRUNC(T.TEST_END_DATE)
There is only a difference for one second a day:

WITH t AS
     (SELECT TO_DATE ('31-10-09 23:59:59','dd-mm-yy HH24:MI:SS') test_end_date, 'A' id FROM dual UNION ALL
      SELECT TO_DATE ('01-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'B'    FROM dual UNION ALL
      SELECT TO_DATE ('01-11-09 00:00:01','dd-mm-yy HH24:MI:SS')              , 'C'    FROM dual UNION ALL
      SELECT TO_DATE ('30-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'D'    FROM dual UNION ALL
      SELECT TO_DATE ('30-11-09 23:59:59','dd-mm-yy HH24:MI:SS')              , 'E'    FROM dual UNION ALL
      SELECT TO_DATE ('01-12-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'F'    FROM dual) 
SELECT test_end_date td , trunc(test_end_date) ttd, id
  FROM t
 WHERE TRUNC (t.test_end_date) BETWEEN TO_DATE ('01-11-09', 'dd-mm-yy') AND TO_DATE ('30-11-09', 'dd-mm-yy');

TD                  TTD           ID
------------------------------------
01.11.2009          01.11.2009    B
01.11.2009 00:00:01 01.11.2009    C
30.11.2009          30.11.2009    D
30.11.2009 23:59:59 30.11.2009    E

 
WITH t AS
     (SELECT TO_DATE ('31-10-09 23:59:59','dd-mm-yy HH24:MI:SS') test_end_date, 'A' id FROM dual UNION ALL
      SELECT TO_DATE ('01-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'B'    FROM dual UNION ALL
      SELECT TO_DATE ('01-11-09 00:00:01','dd-mm-yy HH24:MI:SS')              , 'C'    FROM dual UNION ALL
      SELECT TO_DATE ('30-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'D'    FROM dual UNION ALL
      SELECT TO_DATE ('30-11-09 23:59:59','dd-mm-yy HH24:MI:SS')              , 'E'    FROM dual UNION ALL
      SELECT TO_DATE ('01-12-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'F'    FROM dual)
SELECT test_end_date td , trunc(test_end_date) ttd, id
  FROM t
 WHERE        t.test_end_date  BETWEEN TO_DATE ('01-11-09', 'dd-mm-yy') AND TO_DATE ('30-11-09', 'dd-mm-yy');

TD	             TTD	  ID
-------------------------------------
01.11.2009	     01.11.2009   B
01.11.2009 00:00:01  01.11.2009	  C
30.11.2009	     30.11.2009	  D

Report message to a moderator

Re: sql tuning help [message #435816 is a reply to message #435803]

Fri, 18 December 2009 03:05

krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore

Member

If i omit trunc it won't give right result always.

Report message to a moderator

Re: sql tuning help [message #435824 is a reply to message #435803]

Fri, 18 December 2009 03:44

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

_jum wrote on Fri, 18 December 2009 07:50

You can omit
TRUNC(T.TEST_END_DATE)
There is only a difference for one second a day:

1 second ?!?
Try 23 hours 59 mins and 59 seconds.
With the trunc any time on the 30th is included. Without it only midnight is included. As you would have seen if you'd tried using some other times in your example

SQL> WITH t AS
  2       (SELECT TO_DATE ('31-10-09 23:59:59','dd-mm-yy HH24:MI:SS') test_end_date, 'A' id FROM dual UNION ALL
  3        SELECT TO_DATE ('01-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'B'    FROM dual UNION ALL
  4        SELECT TO_DATE ('01-11-09 00:00:01','dd-mm-yy HH24:MI:SS')              , 'C'    FROM dual UNION ALL
  5        SELECT TO_DATE ('30-11-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'D'    FROM dual UNION ALL
  6        SELECT TO_DATE ('30-11-09 23:59:59','dd-mm-yy HH24:MI:SS')              , 'E'    FROM dual UNION ALL
  7        SELECT TO_DATE ('01-12-09 00:00:00','dd-mm-yy HH24:MI:SS')              , 'F'    FROM dual UNION ALL
  8        SELECT TO_DATE ('30-11-09 05:00:00','dd-mm-yy HH24:MI:SS')              , 'G'    FROM dual)
  9  SELECT test_end_date td , trunc(test_end_date) ttd, id
 10    FROM t
 11   WHERE        t.test_end_date  BETWEEN TO_DATE ('01-11-09', 'dd-mm-yy') AND TO_DATE ('30-11-09', 'dd-mm-yy');

TD              TTD             I
--------------- --------------- -
20091101 000000 20091101 000000 B
20091101 000001 20091101 000000 C
20091130 000000 20091130 000000 D

The correct way to do it without trunc is this:

WHERE t.test_end_date  BETWEEN TO_DATE ('01-11-2009', 'dd-mm-yyyy') AND TO_DATE ('30-11-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss');

And you should always use 4 digit years.

EDIT: typo

[Updated on: Fri, 18 December 2009 03:45]

Report message to a moderator

Re: sql tuning help [message #435836 is a reply to message #435824]

Fri, 18 December 2009 05:39

_jum
Messages: 577
Registered: February 2008

Senior Member

Oh yes - you are right Shocked

- I'm glad it's finally weekend.

Report message to a moderator

Previous Topic:	Why Oracle 10g slows down Copmuter after few days
Next Topic:	How to configure Oracle on Two Servers in Hot Redundant mode

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 22:54:00 CST 2024