Home » RDBMS Server » Performance Tuning » SQL Query performance Tuning (RDBMS : 11.2.0.4.0)
SQL Query performance Tuning [message #668656] Thu, 08 March 2018 00:38 Go to next message
saketsingare
Messages: 1
Registered: March 2018
Junior Member
SELECT /* ORDERED (hxc tbb_det tbb_day) */
      TO_CHAR (tbb_day.start_time, 'DD-MON-RRRR') Start_Date,
       LPAD (TO_CHAR (tbb_day.start_time, 'RRRRMMDD'), 8, '0') slno,
       tbb_day.start_time,
       tbb_det.resource_id,
       TRUNC (hxc.stop_time) stop_time,
       tbb_det.resource_type,
       tbb_det.measure tot_hrs,
         (  (  TO_CHAR (
                  TRUNC (SYSDATE) + (tbb_det.stop_time - tbb_det.start_time),
                  'HH24')
             * 60)
          + TO_CHAR (
               TRUNC (SYSDATE) + (tbb_det.stop_time - tbb_det.start_time),
               'MI'))
       / 60
          TIME,
       tbb_det.unit_of_measure uom,
       tbb_det.time_building_block_id,
       tbb_det.object_version_number parent_building_block_ovn
  FROM hxc_time_building_blocks tbb_day,
       hxc_time_building_blocks tbb_det,
       hxc_timecard_summary hxc,
       HXC_TIME_ATTRIBUTE_USAGES HTAU,
       HXC_TIME_ATTRIBUTES HTA,
       PAY_ELEMENT_TYPES_F PETF
 WHERE     tbb_det.scope = 'DETAIL'
       AND tbb_day.scope = 'DAY'
       AND tbb_det.date_to = hr_general.end_of_time
       AND tbb_det.parent_building_block_ovn = tbb_day.object_version_number
       AND tbb_det.parent_building_block_id = tbb_day.time_building_block_id
       AND tbb_day.parent_building_block_ovn = hxc.timecard_ovn
       AND tbb_day.parent_building_block_id = hxc.timecard_id
       AND TRUNC (tbb_day.start_time) >= ( :P_START_DATE)
       AND TRUNC (tbb_day.stop_time) <= ( :P_END_DATE)
       AND TBB_DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
       AND TBB_DET.object_version_number = HTAU.TIME_BUILDING_BLOCK_OVN
       AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
       AND HTA.ATTRIBUTE_CATEGORY = 'ELEMENT - ' || PETF.ELEMENT_TYPE_ID
       AND PETF.ELEMENT_NAME = 'Total Payroll Hours'


[EDITED by LF: formatted code, applied [code] tags]

[Updated on: Thu, 08 March 2018 01:23] by Moderator

Report message to a moderator

Re: SQL Query performance Tuning [message #668657 is a reply to message #668656] Thu, 08 March 2018 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: SQL Query performance Tuning [message #668658 is a reply to message #668657] Thu, 08 March 2018 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This:

SELECT /* ORDERED (hxc tbb_det tbb_day) */

looks as if you meant to use an Oracle hint. Well, you do not. This is just a comment. A hint requires the "+" sign, such as

SELECT /*+ ORDERED (hxc tbb_det tbb_day) */


Anyway, posting a query we know nothing about, not a single word of explanation ... how do you expect anyone to assist?
Re: SQL Query performance Tuning [message #668661 is a reply to message #668656] Thu, 08 March 2018 02:41 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi
Please run the query with one parameter (Time Card id) and see how many rows and how much time it is taking.
Re: SQL Query performance Tuning [message #668662 is a reply to message #668661] Thu, 08 March 2018 03:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
One obvious thing to do is get rid of the truncs in the where clause. This:
AND TRUNC (tbb_day.start_time) >= ( :P_START_DATE)
AND TRUNC (tbb_day.stop_time) <= ( :P_END_DATE)
can be written as:
AND tbb_day.start_time >= :P_START_DATE
AND tbb_day.stop_time < :P_END_DATE + 1

Then if there are indexes on start_time and/or stop_time oracle can use them and even if there isn't you're still skipping a load of unnecessary function calls.
Past that you need to provide mores details as the others already mentioned.
Re: SQL Query performance Tuning [message #668663 is a reply to message #668661] Thu, 08 March 2018 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
mist598 wrote on Thu, 08 March 2018 08:41
Hi
Please run the query with one parameter (Time Card id) and see how many rows and how much time it is taking.
It's not obvious what you mean by that, since the posted query doesn't have a card id parameter, and it's certainly not obvious what you think it'll prove.
Re: SQL Query performance Tuning [message #668664 is a reply to message #668663] Thu, 08 March 2018 03:13 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
This lpad:
LPAD (TO_CHAR (tbb_day.start_time, 'RRRRMMDD'), 8, '0')
is completely pointless. That to_char always returns 8 digits, even if the year is before 1000 A.D.
SQL> select to_char(to_date('1', 'YYYY'), 'RRRRMMDD') from dual;
 
TO_CHAR(TO_DATE('1','YYYY'),'R
------------------------------
00010301
Re: SQL Query performance Tuning [message #668667 is a reply to message #668656] Thu, 08 March 2018 06:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is a bit odd,
Quote:
AND tbb_det.date_to = hr_general.end_of_time
unless I'm going blind, there is no table hr_general mentioned previously.
Re: SQL Query performance Tuning [message #668688 is a reply to message #668667] Fri, 09 March 2018 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Given the name I suspect it's a function or global variable in a package that returns a hard-coded date.
Re: SQL Query performance Tuning [message #668761 is a reply to message #668688] Tue, 13 March 2018 09:21 Go to previous message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi,

Please share the explain plan for the query. Please use below steps for generating explain plan:


//Generate query explain plan

EXPLAIN PLAN FOR
<select query>


//query for formatted explain plan output
SELECT  *
FROM    TABLE(DBMS_XPLAN.DISPLAY);

Previous Topic: Partitioning in table
Next Topic: Reorganizing index datafiles with tables in partition (merged)
Goto Forum:
  


Current Time: Thu Mar 28 03:11:35 CDT 2024