Announcement

Collapse
No announcement yet.

UTC.GET_LOCAL_DATE SQL Performance

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • UTC.GET_LOCAL_DATE SQL Performance

    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.
    Thanks,
    Prasad.

  • #2
    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
    Regards
    Gary Cunningham
    www.cbmc.co.uk
    www.mavenwire.com

    Comment


    • #3
      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

      Comment


      • #4
        Re: UTC.GET_LOCAL_DATE SQL Performance

        Gary & Chris.

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

        regards,
        Prasad.
        Thanks,
        Prasad.

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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
              Regards
              Gary Cunningham
              www.cbmc.co.uk
              www.mavenwire.com

              Comment


              • #8
                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
                MavenWire

                www.MavenWire.com

                Comment


                • #9
                  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.
                  Cheers,
                  Murthy

                  Comment


                  • #10
                    Re: UTC.GET_LOCAL_DATE SQL Performance

                    Could you also pls suggest some more ulternative for this?
                    Cheers,
                    Murthy

                    Comment


                    • #11
                      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?
                      Cheers,
                      Murthy

                      Comment


                      • #12
                        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')

                        Comment


                        • #13
                          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
                          Nipun Lakhotia
                          Manager, EY

                          Comment

                          Working...
                          X