| 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?  |