| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Peoplesoft: Prolific Use of EXISTS and HASH hints
Yes, PeopleSoft uses WHERE EXISTS(), and a number of other strange
constructions, because it conforms to their internal platform agnostic
development standards/dogma.
Firstly, I would echo others (especailly Jonathan Lewis) who say that you need to give the cost-based optimiser better statistics to help it make a better choice on its own.
You don't want to be hinting to code, because even where you can change it, that is a customisation that you will have to worry about when you upgrade or apply a patch.
There are two Oracle parameters that need to be given realistic values. OPTIMIZER_INDEX_CACHING defaults to 0. But index blocks are cached too. This is perhaps a real use of the buffer cache hit ration. So if your average hit ratio is 90, set this parameter to 90 OPTIMIZER_INDEX_COST_ADJ defaults to 100. But sequential reads are generally quicker than scattered reads, the actual ratio will depend on the physical characteristics of your system, but a value of around 50 is not unreasonable. You can get the numbers from the following SQL: SELECT event, average_wait FROM v$system_event WHERE event LIKE 'db file s%read';
Changing these parameters will make index scans generally more attractive to the optimiser. It will reduce the amount of hash joining and sorting. With PeopleSoft Financials you will certainly have to provide additional indexes and you may need to adjust the order of columns in delivered indexes - all this must be done via the PeopleSoft development tools (Application Designer).
However, neither this nor hinting is not going to solve the where exists
problem
Although some of the Tools can support platforms specific code variations
they don't like doing that because of the development/test overhead. The
following statement is a typical example.
UPDATE PS_VOUCHER SET DSCNT_DUE_DT = NULL WHERE EXISTS ( SELECT 'X' FROM PS_NET_XREF_TAO B WHERE B.NET_PI = 63001 AND B.BUSINESS_UNIT = PS_VOUCHER.BUSINESS_UNIT AND B.VOUCHER_ID = PS_VOUCHER.VOUCHER_ID AND B.TXN_STATUS IN ('B', 'N')) The problem is that update (or delete) statement has no conditions in the where clauses other than the correlated sub-query. Because the query is correlated to the table being updated it is fired for every row found by the parent query. Which means that the parent query will scan the whole table (in this case PS_VOUCHER), and then for every row the sub-query will be executed.
UPDATE PS_VOUCHER SET DSCNT_DUE_DT = NULL WHERE (PS_VOUCHER.BUSINESS_UNIT , PS_VOUCHER.VOUCHER_ID) IN ( SELECT B.BUSINESS_UNIT , B.VOUCHER_ID FROM PS_NET_XREF_TAO B WHERE B.NET_PI = 63001 AND B.TXN_STATUS IN ('B', 'N') )
In this tuned example the correlations between parent query and sub-query have been converted to a multi-column in-list. The sub-query executes once only and before the parent query and feeds a set of valeus into the parent. The correlation columns are always key columns and so the database uses the unique key index to look up the data.
Where, as in this example, there is more than 1 correlation column; a multi-column in-list must be used. This is an Oracle specific construct. However, Application Engine is capable of supporting platform specific code variations. So, PeopleSoft could include this in the vanilla product while staying within the bounds of the PeopleSoft development standards.
I have been dealing with similar problems in various parts of the product since working on Financials 3. The problem appears and disappears in different parts of the product with each release. Different PeopleSoft customers experience problems in different places. On the system from which I took this example, only 4 statements with this construction are causing very significant performance problems. However, those 4 account for nearly 25% of the total batch execution time.
I am still investigating the effect of the UNNEST hint and the _unnest_subquery parameter, but as yet with no success.
"Ethan Post" <nospam_at_nowhere.com> wrote in message
news:R2jua.249049$Si4.198070_at_rwcrnsc51.ops.asp.att.net...
> Peoplesoft seems to use EXISTS all over the place in the app generated
SQL.
> I have found that in most cases the SQL can be tuned with dramatic effects
> simply by use a HASH_SJ hint or in other cases USE_HASH. The system has
> hash joins enabled, stats are up to date but they are not using
DBMS_STATS,
> they are using analyze with estimate. Since most of this SQL is generated
> by the app, what tips/tricks have you found to get Oracle to use hash
joins
> and why isn't Oracle figuring out that this is much faster? It seems this
> would be one of the plans Oracle would consider. I have not modified
> optimizer parameters but tests as the session level on a couple statements
> don't show any difference in the plan.
>
> Thanks!
>
> - Ethan Post
>
>
Received on Fri May 09 2003 - 04:38:03 CDT
 |
 |