Home » RDBMS Server » Performance Tuning » sql tuning help (oracle 10g,1,xp)
sql tuning help [message #435784] Fri, 18 December 2009 00:46 Go to next message
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 171 times)
Re: sql tuning help [message #435785 is a reply to message #435784] Fri, 18 December 2009 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something seems to be missing (such as the FROM clause).
Re: sql tuning help [message #435796 is a reply to message #435784] Fri, 18 December 2009 01:18 Go to previous messageGo to next message
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;
Re: sql tuning help [message #435799 is a reply to message #435796] Fri, 18 December 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sql tuning help [message #435801 is a reply to message #435784] Fri, 18 December 2009 01:40 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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?
Re: sql tuning help [message #435803 is a reply to message #435796] Fri, 18 December 2009 01:50 Go to previous messageGo to next message
_jum
Messages: 508
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

Re: sql tuning help [message #435816 is a reply to message #435803] Fri, 18 December 2009 03:05 Go to previous messageGo to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
If i omit trunc it won't give right result always.
Re: sql tuning help [message #435824 is a reply to message #435803] Fri, 18 December 2009 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
Oh yes - you are right Shocked - I'm glad it's finally weekend.
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:
  


Current Time: Sun Sep 25 00:55:21 CDT 2016

Total time taken to generate the page: 0.04196 seconds