Oracle 11.2.0.3 timediff function
From: MJ Chicago <emjay.mody_at_gmail.com>
Date: Sun, 14 Feb 2016 12:25:27 -0600
Message-ID: <CAOD+=a0jwjPk_K5LTz5BSSi67RjNUXKCXxBA-XrynE7FmkkcXA_at_mail.gmail.com>
Hello Oracle-L
Is anyone familiar if Oracle has a timediff function to calculate difference in time (seconds/milliseconds/etc) in 11.2.0.3? I have an app query where a table is joined onto itself to do this manually. Overall performance has been very choppy. The table has under 400,000 rows in it with up-to-date stats. I am testing a rewrite and tuning outside a package. End goal is this would end up in a cursor.
Date: Sun, 14 Feb 2016 12:25:27 -0600
Message-ID: <CAOD+=a0jwjPk_K5LTz5BSSi67RjNUXKCXxBA-XrynE7FmkkcXA_at_mail.gmail.com>
Hello Oracle-L
Is anyone familiar if Oracle has a timediff function to calculate difference in time (seconds/milliseconds/etc) in 11.2.0.3? I have an app query where a table is joined onto itself to do this manually. Overall performance has been very choppy. The table has under 400,000 rows in it with up-to-date stats. I am testing a rewrite and tuning outside a package. End goal is this would end up in a cursor.
Ran ADDM, increased SGA. Ran SQL Advisor, added recommended indexes. Ran SQL Tuning Advisor, added recommended function-based index.
Explain plan shows index range scan under CBO. I have yet to run the same with /*+ RULE */ hint.
I am asking two questions here:
a) compute timediff without having to join table onto itself
b) any additional recommendation to generally improve query performance
Any suggestions or ideas are greatly appreciated.
Thank you in advance
MJ
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 14 2016 - 19:25:27 CET