Announcement

Collapse
No announcement yet.

VPD profile has negative effect on query performance

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

  • VPD profile has negative effect on query performance

    I mentioned this offhand in a post about a different topic, but I think this deserves its own thread.

    In the process of trying to optimize some saved queries and reports, we discovered that the VPD profile has a dramatic effect on query execution time. The queries in question access, in particular, the order_release and shipment tables and the view_shipment_order_release view. For one particular sample query, which returns zero rows, execution takes between 0.1 and 5 seconds from sqlplus and between 1 and 5 minutes after a VPD profile has been applied with a call to the vpd.set_user('TMS.ADMIN') function.

    I believe that this command is actually called with user DBA.ADMIN when we connect to the database via sqlplus, but the DBA.ADMIN VPD profile does not seem to be affecting the query performance.

    We have been going back and forth with Oracle support about this issue for some time; they have sent us scripts to test performance with additional indexes (nothing has worked so far).

    Here is a sample query (not a thing of beauty, I must say):
    SELECT
    glog_util.remove_domain(ORLS.ORDER_RELEASE_GID) ORDER_RELEASE,
    glog_util.remove_domain(orls.source_location_gid) OR_ORIGIN,
    oo.location_name OR_O_NAME,
    oo.country_code3_gid OR_O_CTRY,
    oo.postal_code OR_O_PC,
    oo.city OR_O_CITY,
    glog_util.remove_domain(orp.STATUS_VALUE_GID) OR_PLANNING,
    orls.late_pickup_date OR_LATEST_PICKUP,
    orls.total_weight OR_GROSS_WEIGHT,
    glog_util.remove_domain(shiplist.shipment_gid) SHIPMENT_ID,
    glog_util.remove_domain(sr.shipment_refnum_value) LOAD_PLAN,
    glog_util.remove_domain(sgd.ship_group_gid) SHIPMENT_GROUP_ID,
    glog_util.remove_domain(s.servprov_gid) CARRIER,
    s.start_time S_START_TIME,
    glog_util.remove_domain(s.source_location_gid) SHIP_ORIGIN,
    so.location_name SHIP_O_NAME,
    so.country_code3_gid SHIP_O_CTRY,
    so.postal_code SHIP_O_PC,
    so.city SHIP_O_CITY,
    s.total_weight S_WEIGHT
    FROM
    order_release orls,
    order_release_status ors,
    order_release_status orp,
    shipment_refnum sr,
    shipment s,
    location so,
    location oo,
    ship_group_d sgd,
    view_shipment_order_release shiplist
    where
    orls.order_release_gid = ors.order_release_gid
    and ors.status_type_gid ='TMS.LOAD_PLAN_OR'
    and ors.status_value_gid = 'TMS.NOT CONFIRMED'
    and orls.order_release_gid = orp.order_release_gid
    and orp.status_type_gid='TMS.PLANNING'
    and orls.source_location_gid = oo.location_gid
    and orls.order_release_gid = shiplist.order_release_gid
    and shiplist.shipment_gid = sr.shipment_gid
    and sr.shipment_refnum_qual_gid = 'TMS.LOAD_PLAN'
    and shiplist.shipment_gid = s.shipment_gid
    and s.shipment_gid = sgd.shipment_gid (+)
    and s.source_location_gid = so.location_gid
    and orls.late_pickup_date between to_date('19/05/2005','DD/MM/YYYY')
    and to_date('20/05/2005','DD/MM/YYYY')


    Does anyone have any suggestions?


  • #2
    I suggest removing that view from your query and getting your data a different way. The built in views have done nothing for us in the past except slow our queries down. They are good for one shipment at a time, but when using date ranges, from our experience cause nothing but problems. Oracle has reccomended to us in the past to avoid using the views in date related queries as well. Also, does the time you have include the time it takes to do a full count of the dataset? Depending on how you are counting the time using sql, it may or may not be a true time. (Not doubting, just a thought). As I noted in a previous post, using hints also helped us greating in reducing the time of the query with VPDs. We have over 200 vpds set up in one of our domains and it definitely adds some time to the query and can be rather frustrating in tweaking. I know I have spent countless hours tweaking some of mine as convoluded as they were! Good luck with yours!

    Comment


    • #3
      Just a few additional questions. How many rows are in the shipment table in total? How many are in the specific domain you are testing the query in? Do your shipments all contain one line item each or does it vary from 1 - 20? (we have up to 100 on some of ours) Will the report be domain specific or will it be installed in the public domain to be run by any domain that wants it? (of course I am assuming you use more than 1 domain in your environment and are testing in the TMS domain since that is in your query).
      Last edited by Shells; November 14, 2006, 19:00.

      Comment


      • #4
        Thanks for your suggestions.

        Some answers to your questions:

        How many rows are in the shipment table in total?
        171742

        How many are in the specific domain you are testing the query in?
        169177

        Do your shipments all contain one line item each or does it vary from 1 - 20?
        Varies a lot, mostly in range between 1 and 50, but some with even more (I assume you mean s_ship_unit_line counts; most or all s_ship_units have only one line each, I believe).

        Will the report be domain specific or will it be installed in the public domain to be run by any domain that wants it? The report is specific to the domain at present, but new domains will probably be created soon that will need this report.

        The timings that I did were probably crude--I just used sqlplus "set timing on" and ran the query and then executed vpd.set_user('TMS.ADMIN') and ran the query again.

        I do not see a better way of tying shipments to orders (I agree that the view is a resource pig). I guess it would be too difficult for GC3 to maintain a simple order-shipment crossref table, since the relationships can be so complex. Too bad. But the fact is, even with the view, the query returns pretty quickly as long as a (non-DBA.ADMIN) VPD profile is not applied.

        Basically. the saved query as shown represents the information that the users need (including datetime constraints), and apparently the VPD profile logic does not work well with the view (or even the inline view query, which I also tried).

        Do you know exactly how the VPD profiles change the behavior and/or contents of the query sql? Also, I am not much of a SQL guru. For the query shown above, what hint(s) would you suggest?

        Thanks again.

        Comment


        • #5
          If it were in our environment, we would be making the changes below to the query. We have around 439,511 rows in our shipment table and 319,023 in the particular domain I tested. Because of that, we also have a index on shipment.domain_name (Even as few domains as their are) and add the -- AND s.domain_name = 'TMS' -- to the end of the query. Due to almost all of your rows being in the domain you are running the report for, I dont know that this will help you much although it may in the future if you continue to expand other domains so you may want to try it with and without that line to make sure your situation doesn't slow it down. We also add the hint to the top even if the index shows in the explain plan because it seems to make the query run more efficiently. Finally, instead of the view we use the subquery below (if you need sell shipments instead of buy, just adjust). If you take the script out of the view run an explain plan, you will see the cost is high. One of the reasons is the joins they use to get the data.

          I dont know that this will make a whole lot of difference on the results you get in 1 minute in the UI. With the vpd profiles and a date range, you are rarely going to get anything above that once you reach 100k+ rows.

          Anyway - check it out and hopefully it will help a little. Every customer/system/db is different and being that you might use the system in a totally different way that we do,
          I can only hope that it will help. Well good luck and let me know if you have any additional questions.


          SELECT /*+ INDEX(SHIPMENT PK_SHIPMENT)*/
          glog_util.remove_domain(ORLS.ORDER_RELEASE_GID) ORDER_RELEASE,
          glog_util.remove_domain(orls.source_location_gid) OR_ORIGIN,
          oo.location_name OR_O_NAME,
          oo.country_code3_gid OR_O_CTRY,
          oo.postal_code OR_O_PC,
          oo.city OR_O_CITY,
          glog_util.remove_domain(orp.STATUS_VALUE_GID) OR_PLANNING,
          orls.late_pickup_date OR_LATEST_PICKUP,
          orls.total_weight OR_GROSS_WEIGHT,
          glog_util.remove_domain(shiplist.shipment_gid) SHIPMENT_ID,
          glog_util.remove_domain(sr.shipment_refnum_value) LOAD_PLAN,
          glog_util.remove_domain(sgd.ship_group_gid) SHIPMENT_GROUP_ID,
          glog_util.remove_domain(s.servprov_gid) CARRIER,
          s.start_time S_START_TIME,
          glog_util.remove_domain(s.source_location_gid) SHIP_ORIGIN,
          so.location_name SHIP_O_NAME,
          so.country_code3_gid SHIP_O_CTRY,
          so.postal_code SHIP_O_PC,
          so.city SHIP_O_CITY,
          s.total_weight S_WEIGHT
          FROM
          ORDER_RELEASE orls,
          ORDER_RELEASE_STATUS ors,
          ORDER_RELEASE_STATUS orp,
          SHIPMENT_REFNUM sr,
          SHIPMENT s,
          LOCATION so,
          LOCATION oo,
          SHIP_GROUP_D sgd,
          (SELECT DISTINCT shp.shipment_gid, ssul.order_release_gid
          FROM SHIPMENT shp,
          SHIPMENT_STOP_D d,
          S_SHIP_UNIT_LINE ssul
          WHERE shp.shipment_gid = d.shipment_gid
          AND d.s_ship_unit_gid = ssul.s_ship_unit_gid
          AND ssul.order_release_gid IS NOT NULL
          AND d.STOP_NUM = 1
          AND shp.PERSPECTIVE = 'B') shiplist
          WHERE orls.order_release_gid = ors.order_release_gid
          AND ors.status_type_gid ='TMS.LOAD_PLAN_OR'
          AND ors.status_value_gid = 'TMS.NOT CONFIRMED'
          AND orls.order_release_gid = orp.order_release_gid
          AND orp.status_type_gid='TMS.PLANNING'
          AND orls.source_location_gid = oo.location_gid
          AND orls.order_release_gid = shiplist.order_release_gid
          AND shiplist.shipment_gid = sr.shipment_gid
          AND sr.shipment_refnum_qual_gid = 'TMS.LOAD_PLAN'
          AND shiplist.shipment_gid = s.shipment_gid
          AND s.shipment_gid = sgd.shipment_gid (+)
          AND s.source_location_gid = so.location_gid
          AND orls.late_pickup_date BETWEEN TO_DATE('19/05/2005','DD/MM/YYYY')
          AND TO_DATE('20/05/2005','DD/MM/YYYY')
          AND s.domain_name = 'TMS'

          Comment


          • #6
            As a (possibly unnecessary) clarification, the query as I originally stated it returns zero rows on the production database. The issue therefore does not appear to be related to the quantity of the results returned.

            I believe that one of the test scripts that Oracle sent us included replacement of the view with an inline query against the shipment_stop table, but it did not appear to improve the execution time.

            From sqlplus without the vpd profile, the original query executes in about 10 seconds on our database. Just as a matter of curiosity, I would expect that the original query (without the domain_name in the where clause) would return zero rows on your database, unless you have a TMS domain with similar data. Would it be possible for you to run it on your database and see how long it takes to execute?

            Thanks for your help and suggestions.

            Comment


            • #7
              I do return rows (212 to be exact) both without because I changed everything you had named as TMS to be a domain we had in our system when I tested the query. It did run in about 30 seconds using the changes I gave you in the UI using the VPD. Without the VPD, in about 4. I wouldn't suggest running a query where no rows return in your db. You should at least use a date range where rows return as that is going to add to your timing.

              Comment


              • #8
                Well, I tried using the suggested hint /*+ INDEX(SHIPMENT PK_SHIPMENT)*/ , but the performance was actually worse with (5:48 versus 5:08 without the hint) or without (0:27 versus 0:22) the TMS.ADMIN VPD profile.

                By the way, I chose the date range that returned no rows just to reduce the screen clutter. Also, this query and others like it will typically have a fairly small return set (probably <20 as a rule), so I would not expect the actual data retrieval to be a large part of the performance time. But I speak from utter ignorance here--if this is not the case, please set me straight.

                A development guy from Oracle also recommended investigating VPD granularity. As I understand it (third-hand from our DBA), Oracle has a static and a dynamic policy attache to every table, and we could theoretically change the dynamic policy attached to individual tables based on the GC3 userid. I don't know if this is a correct interpretation or whether that really helps.

                Since we do still want to enforce interdomain security, I was wondering, if we are able to control the application of VPD on individual tables by the means described above (or any other), would it improve query performance if we left security in place for the main tables (shipment, order_release, ob_order_base, etc.) and turned it off for all the child tables (shipment_refnum, shipment_status, shipment_s_equipment_join, etc.)? What do you think? Is this possible, and would it do any good?

                Another option I was thinking of: Seems to me that the view, a compex "select distinct" query, could be the biggest drag on performance. We could perhaps remove the view (or its query) from the query, by getting the developers to configure GC3 via agents to reflect the desired shipment attributes (refnums and statuses) in the order status and refnum tables. Do you think that would help, or would adding more aliases of such tables to the query have an even worse effect on performance?

                Comment


                • #9
                  Re: VPD profile has negative effect on query performance

                  FYI - we hit some extreme VPD performance issues after a v5.0 to v5.5 migration, which Ian Lo was able to resolve through a mixture of SQL and DB tuning. Here's a link for more info:Thanks,
                  Chris

                  Comment


                  • #10
                    Re: VPD profile has negative effect on query performance

                    would it improve query performance if we left security in place for the main tables (shipment, order_release, ob_order_base, etc.) and turned it off for all the child tables (shipment_refnum, shipment_status, shipment_s_equipment_join, etc.)? What do you think? Is this possible, and would it do any good?

                    -------
                    Can u get some results by using above method?

                    Comment


                    • #11
                      Re: VPD profile has negative effect on query performance

                      Hi Andy,

                      I work with blasley and to answer your question, yes that is exactly what we did. In fact we removed the vpd policy from several child tables and this reduced the number of VPD predicates and the corresponding cost of the SQL significantly. Our analysis turned up the following candidate tables for removal (you may of course have other tables that are more of an issue).

                      IE_SHIPMENTSTATUS
                      INVOICE_REFNUM
                      OB_ORDER_BASE_STATUS
                      OB_REFNUM
                      ORDER_RELEASE_REFNUM
                      ORDER_RELEASE_STATUS
                      ORDER_RELEASE_STATUS_HISTORY
                      S_SHIP_UNIT_LINE
                      S_SHIP_UNIT
                      SHIP_GROUP_STATUS
                      SHIPMENT_REFNUM
                      SHIPMENT_STATUS
                      SHIPMENT_STOP_STATUS
                      SS_STATUS_HISTORY
                      STATUS_VALUE
                      If you are not familiar with removing a tables VPD profile let me know and I wil post more details. Also if your DBA is running AWR reports he should be able to pull the worst SQL and execution plans from the AWR reports (you can also get this from the dbms_xplan.display_awr table based function). If you have specific actions in the UI that are slow, you should be able to find the SQL by turning on SQL logging. However in order to get the correct explain plan you will need to execute the SQL with the VPD profile enabled and use the autotrace option. Here is an example showing the queries used in the UI drill downs from order release to shipment (a common performance problem).

                      exec vpd.set_user('GUEST.ADMIN');
                      set autotrace traceonly

                      -- Sell shipment drill down from order release
                      select sh.shipment_gid
                      from SHIPMENT sh
                      where (sh.shipment_gid in
                      (select sstop.shipment_gid
                      from ORDER_RELEASE o,
                      S_SHIP_UNIT ssu,
                      SHIPMENT_STOP_D sstopd,
                      SHIPMENT_STOP sstop,
                      S_SHIP_UNIT_LINE ssul
                      where (o.order_release_gid = :1)
                      and (ssu.s_ship_unit_gid = ssul.s_ship_unit_gid)
                      and (sstopd.s_ship_unit_gid = ssu.s_ship_unit_gid)
                      and (sstop.shipment_gid = sstopd.shipment_gid)
                      and (sstop.stop_num = sstopd.stop_num)
                      and (ssul.order_release_gid = o.order_release_gid)))
                      and (sh.perspective = :"SYS_B_0");


                      -- Buy shipment drill down from order release
                      select sh.shipment_gid
                      from SHIPMENT sh
                      where (sh.shipment_gid in
                      (select ssej.shipment_gid
                      from S_SHIP_UNIT ssuej,
                      S_EQUIPMENT_S_SHIP_UNIT_JOIN sessuj,
                      ORDER_RELEASE orej,
                      S_SHIP_UNIT_LINE ssulej,
                      SHIPMENT_S_EQUIPMENT_JOIN ssej
                      where (ssuej.s_ship_unit_gid = ssulej.s_ship_unit_gid)
                      and (sessuj.s_ship_unit_gid = ssuej.s_ship_unit_gid)
                      and (orej.order_release_gid = :1)
                      and (ssulej.order_release_gid = orej.order_release_gid)
                      and (ssej.s_equipment_gid = sessuj.s_equipment_gid)))
                      and (sh.perspective = :"SYS_B_0");
                      Additional tuning options include using Materialized views with criteria to limit the number of rows (typically insert date). However mviews tend to generate quite a bit of redo and can be a bit of a space management headache but can be quite helpful for non-operational reports (monthly invoce reports for example). Using them for saved queries in the UI is possible but you will need to constantly refresh them which leads to the redo overhead.

                      Thanks,
                      Alan
                      Last edited by acuartero; November 1, 2010, 14:31.

                      Comment


                      • #12
                        Re: VPD profile has negative effect on query performance

                        Just getting back to the query itself from a performance point of view the below sql will probably be faster.

                        SELECT /*+ INDEX(SHIPMENT PK_SHIPMENT)*/
                        glog_util.remove_domain(ORLS.ORDER_RELEASE_GID) ORDER_RELEASE,
                        glog_util.remove_domain(orls.source_location_gid) OR_ORIGIN,
                        oo.location_name OR_O_NAME,
                        oo.country_code3_gid OR_O_CTRY,
                        oo.postal_code OR_O_PC,
                        oo.city OR_O_CITY,
                        glog_util.remove_domain(orp.STATUS_VALUE_GID) OR_PLANNING,
                        orls.late_pickup_date OR_LATEST_PICKUP,
                        orls.total_weight OR_GROSS_WEIGHT,
                        glog_util.remove_domain(shiplist.shipment_gid) SHIPMENT_ID,
                        glog_util.remove_domain(sr.shipment_refnum_value) LOAD_PLAN,
                        glog_util.remove_domain(sgd.ship_group_gid) SHIPMENT_GROUP_ID,
                        glog_util.remove_domain(s.servprov_gid) CARRIER,
                        s.start_time S_START_TIME,
                        glog_util.remove_domain(s.source_location_gid) SHIP_ORIGIN,
                        so.location_name SHIP_O_NAME,
                        so.country_code3_gid SHIP_O_CTRY,
                        so.postal_code SHIP_O_PC,
                        so.city SHIP_O_CITY,
                        s.total_weight S_WEIGHT
                        FROM
                        ORDER_RELEASE orls,
                        ORDER_RELEASE_STATUS ors,
                        ORDER_RELEASE_STATUS orp,
                        SHIPMENT_REFNUM sr,
                        SHIPMENT s,
                        LOCATION so,
                        LOCATION oo,
                        SHIP_GROUP_D sgd,
                        (SELECT DISTINCT shp.shipment_gid, ssul.order_release_gid
                        FROM SHIPMENT shp,
                        SHIPMENT_STOP_D d,
                        S_SHIP_UNIT_LINE ssul,
                        order_release o
                        WHERE shp.shipment_gid = d.shipment_gid
                        AND d.s_ship_unit_gid = ssul.s_ship_unit_gid
                        AND ssul.order_release_gid=o.order_release_gid
                        AND o.late_pickup_date BETWEEN TO_DATE('19/05/2005','DD/MM/YYYY')
                        AND TO_DATE('20/05/2005','DD/MM/YYYY')
                        AND shp.domain_name = 'TMS') shiplist
                        WHERE orls.order_release_gid = ors.order_release_gid
                        AND ors.status_type_gid ='TMS.LOAD_PLAN_OR'
                        AND ors.status_value_gid = 'TMS.NOT CONFIRMED'
                        AND orls.order_release_gid = orp.order_release_gid
                        AND orp.status_type_gid='TMS.PLANNING'
                        AND orls.source_location_gid = oo.location_gid
                        AND orls.order_release_gid = shiplist.order_release_gid
                        AND shiplist.shipment_gid = sr.shipment_gid
                        AND sr.shipment_refnum_qual_gid = 'TMS.LOAD_PLAN'
                        AND shiplist.shipment_gid = s.shipment_gid
                        AND s.shipment_gid = sgd.shipment_gid (+)
                        AND s.source_location_gid = so.location_gid

                        Comment


                        • #13
                          Re: VPD profile has negative effect on query performance

                          John,

                          Good point, I think we actually did refactor that query as well (Bill's post was some time ago, before I started here anyway) and that should be the first method of attack in such cases. However with regards to Andy's issue it's unclear if the performance impact he is experiencing is due to application generated or user created queries. In the case of app generated queries not only can they be sub-optimal your options for tweaking them directly are limited. You can to some degree rewrite them as well (using query profiles on the DB side) but that can be quiet time consuming. We did get fairly good results from dropping VPD profiles without comprimising security.

                          Thanks,
                          Alan

                          Comment


                          • #14
                            Re: VPD profile has negative effect on query performance

                            I work with blasley and to answer your question, yes that is exactly what we did. In fact we removed the vpd policy from several child tables and this reduced the number of VPD predicates and the corresponding cost of the SQL significantly. Our analysis turned up the following candidate tables for removal (you may of course have other tables that are more of an issue).


                            Quote:
                            IE_SHIPMENTSTATUS
                            INVOICE_REFNUM
                            OB_ORDER_BASE_STATUS
                            OB_REFNUM
                            ORDER_RELEASE_REFNUM
                            ORDER_RELEASE_STATUS
                            ORDER_RELEASE_STATUS_HISTORY
                            S_SHIP_UNIT_LINE
                            S_SHIP_UNIT
                            SHIP_GROUP_STATUS
                            SHIPMENT_REFNUM
                            SHIPMENT_STATUS
                            SHIPMENT_STOP_STATUS
                            SS_STATUS_HISTORY
                            STATUS_VALUE
                            If you are not familiar with removing a tables VPD profile let me know and I wil post more details.

                            ----------------------------------

                            Thanks for ur patient. Nowadays ,we tried some ways to make our OTM more stability and faster.

                            For what u said above, I understand that it should change like this? (red part)
                            OBJECT_OWNER OBJECT_NAME POLICY_GROUP POLICY_NAME PF_OWNER PACKAGE FUNCTION SEL
                            GLOGOWNER ORDER_RELEASE_REFNUM DYNAMIC DDS_ORDER_RELEASE_REFNUM754 GLOGOWNER VPD DOMAIN_SELECT_RULE YES -> NO
                            GLOGOWNER ORDER_RELEASE_REFNUM DYNAMIC DXR_ORDER_RELEASE_REFNUM754 GLOGOWNER VPD EXTERNAL_PREDICATE_RULE_READ YES -> NO

                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SXD_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWREXT_PREDICATE_DELETE754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SXU_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWREXT_PREDICATE_UPDATE754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SXI_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWREXT_PREDICATE_INSERT754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SDU_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWRDOMAIN_UPDATE754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SIU_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWRINSERT_USER754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SXA_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWREXT_PREDICATE_IUD754 NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SXR_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWREXT_PREDICATE_READ754 YES->NO
                            GLOGOWNER ORDER_RELEASE_REFNUM STATIC SDS_ORDER_RELEASE_REFNUM754 GLOGOWNER VPDWRDOMAIN_SELECT754 YES->NO

                            Comment

                            Working...
                            X