UTC.GET_LOCAL_DATE SQL Performance

[prasad]

We are using utc.get_local_date function very frequently. Is there a better way to get the local date? This function call is taking a long time execute.

For example.

to_char(ORD.LATE_PICKUP_DATE,'DD-MON-YYYY') = '14-MAR-2008' (ORD is order release)

This condition in the where clause scans through all the rows in the order release ID.
How can I avoid the complete table scan?

Thanks,
Prasad.

[cunninghamg]

Re: UTC.GET_LOCAL_DATE SQL Performance

prasad

Its not the utc.get_local_date that is causing the full table scan - its the query.

If you look for a particular date in the Order_release table, it will scan all entries unless you add an index on the field being searched.

Adding further filtering criteria would help reduce the number of records to scan (if available).

Cheers
Gary

[chrisplough]

Re: UTC.GET_LOCAL_DATE SQL Performance

I definitely agree with Gary on this one. All queries in the system should be qualified with additional criteria (especially date criteria) in order to avoid full table scans. Many times the screens are modified, so that a date criteria is automatically includes, such as only returning results within the last 60 or 90 days.

--Chris

[prasad]

Re: UTC.GET_LOCAL_DATE SQL Performance

Gary & Chris.

Thank You for you hint. By narrowing the query, the performance increased significantly.

regards,
Prasad.

[[email protected]]

Re: UTC.GET_LOCAL_DATE SQL Performance

Hi Chris,

We recently migrated our Devl environment from 4.5 to 5.5 CU#4 RU#1.

We could find when we run the the sql statement on migrated 5.5 Database
select UTC.GET_LOCAL_DATE (SYSDATE, NULL) from dual; throws an exception "ORA-20000: Undefined time zone for location".

However this same used to run on 4.5 Database. Now we have a bunch of reports which uses this function and all those reports are now throwing this exception. We do have raised a SR with Oracle on this, however still research is going on.

I will keep you update on this on receipt of a workaround.

Regards,
Suresh

[chrisplough]

Re: UTC.GET_LOCAL_DATE SQL Performance

Suresh,

Thank you. I haven't run into this issue myself, but will shop it around some of my contacts to see if I can help find a solution. Otherwise, I appreciate you keeping us updated.

--Chris

[cunninghamg]

Re: UTC.GET_LOCAL_DATE SQL Performance

Hi

So far as I recall the utc.get_local_date function is used to determine the timezone of the Location being passed in.
In you case - no location is being passed in - hence the error.

Did you expect this function to use your local PC timezone setting?
If so - I don't see this is possible.

Gary

[sprabhakar]

Re: UTC.GET_LOCAL_DATE SQL Performance

The reason you are getting is error is that Oracle have modified the UTC package.
Prior to 55, the piece of code was coded as

if ( v_time_zone_gid is null) then
return local_date;
end if;

where local_date returned the system date from the database server. Whereas in 55 the above piece of code has been modified as

if ( v_time_zone_gid is null) then
raise_application_error(-20000, 'Undefined time zone for location'|| v_location_gid);
end if;

Thus making it mandatory to pass a location gid to get the local date from the server. A simple workaround to get this working without modifiying any of your reports would be to add a location with LOCATION_GID value as 'NULL' and the TIME_ZONE_GID with a value of your local timezone.

Alternatively, you can also use the below query to get local date in GMT.

select vpd.gmt_sysdate from dual

Please update if this works or I will suggest a few other options.

Regards,
Prabhakar

[MURTHYKO]

Re: UTC.GET_LOCAL_DATE SQL Performance

Hi Prabhakar,

We are also facing the same issue. I created one location in OTM wth id as NULL and timezone as the local timezone that I required. But I am stll getting the same error. If I am wrong, pls explan what exactly you mean by create a LOCATION_GID with 'NULL' and TIME_ZONE as local time zone.

[MURTHYKO]

Re: UTC.GET_LOCAL_DATE SQL Performance

Could you also pls suggest some more ulternative for this?

[MURTHYKO]

Re: UTC.GET_LOCAL_DATE SQL Performance

Thanks Prabhakar, it worked with NULL location. But i would like to know the alternatives by keeping the future inmind.

Ex: If we have bulk plans which pick orders based on query that is using UTC.get_local_date function, and runs on different warehouses, we can create NULL location for each warehouse in OTM as its unique. So to have timezone of the warehouse where we are planning the orders, what can be the best solution?

[DarkCloud67]

Re: UTC.GET_LOCAL_DATE SQL Performance

I am seeing the same perfomance hit in OTM 6.1.6 on Oracle 11g. If I run a query with a search with conditions on the late pickup date using utc.get_local_date and order_type it is taking about 3 seconds to run.

Same query without the utc.get_local_date takes 0.172 seconds

SELECT *
FROM order_release o
WHERE utc.get_local_date(o.late_pickup_date, o.source_location_gid) > '01-SEP-12'
AND o.order_release_type_gid IN ('A','B','C');
--
SELECT *
FROM order_release o
WHERE o.late_pickup_date > '01-SEP-12'
AND o.order_release_type_gid IN ('A','B','C')

[nipunlakhotia]

Re: UTC.GET_LOCAL_DATE SQL Performance

SQL alternative to UTC function to convert GMT into local time zone


SELECT TO_CHAR (FROM_TZ (CAST (the_date AS TIMESTAMP), 'GMT') AT TIME ZONE timz, 'DD-MON-YYYY HH24:MI:SS') "DATE", TIMZ
FROM
(SELECT ord.early_pickup_date AS the_date, loc.time_zone_gid timz FROM order_release ord, location loc where ord.source_location_gid=loc.location_gid and ord.order_release_gid ='domian.abc' );



Regards
Nipun