Announcement

**Shells** · November 14, 2006, 16:29

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** · November 14, 2006, 17:32

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** · November 14, 2006, 21:44

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** · November 15, 2006, 15:45

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** · November 20, 2006, 19:58

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** · November 20, 2006, 21:58

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** · November 29, 2006, 18:00

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** · September 18, 2007, 08:53

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:

http://www.otmfaq.com/forums/f27/otm...rovements-515/

Thanks,
Chris

**Andy Zero** · October 31, 2010, 11:23

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?

**acuartero** · November 1, 2010, 13:28

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

**jradey** · November 1, 2010, 16:48

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

**acuartero** · November 1, 2010, 17:58

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

**Andy Zero** · November 15, 2010, 05:37

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

Announcement

VPD profile has negative effect on query performance

VPD profile has negative effect on query performance

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment