[blasley]

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?

[Shells]

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!

[Shells]

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).

[blasley]

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.

[Shells]

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'

[blasley]

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.

[Shells]

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.

[blasley]

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?

[chrisplough]

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:

http://www.otmfaq.com/forums/f27/otm...rovements-515/ (OTM 5.5: VPD Query Performance - Improvements)

Thanks,
Chris