Re: Question for PeopleSoft DBAs out there

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 21 Jan 2008 14:19:50 -0600
Message-ID: <ad3aa4c90801211219k7277258alf93712c4ea733129@mail.gmail.com>


You could try using OEM to identify a good plan and store that and activate it. That is one of the real nice tools that 10g has for dealing with canned code you cant modify.

On Jan 21, 2008 9:16 AM, Goulet, Dick <richard.goulet_at_capgemini.com> wrote:

> Chris,
>
>
>
> Sorry for a late response, but I don't read the list that
> often for several reasons. But, yes I have seen problems like that with
> PeopleSoft. The best solution I ever came up with was to put the database
> in the cost mode of operation and maqking sure stats were uptodate.
> Otherwise your only alternative is to complain to PeopleSoft which isn't
> going to get a response anywhere in the foreseeable future as they do not
> test their code with volumes of data.
>
>
>
> ______________________________________________________________
> Dick Goulet / *Capgemini*
> North America P&C / East Business Unit
> Senior Oracle DBA / Hosting
> Office: 508.573.1978 / Mobile: 508.742.5795 / www.capgemini.com
> Fax: 508.229.2019 / Email: *richard.goulet_at_capgemini.com*
> 45 Bartlett St. / Marlborough, MA 01752
>
> *Together: the Collaborative Business Experience *
> ______________________________________________________________
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Taylor, Chris David
> *Sent:* Wednesday, January 09, 2008 11:58 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Question for PeopleSoft DBAs out there
>
>
>
> We've a NVS RPTBOOK that keeps going out to lunch on PS_LEDGER with a
> Cartesian Join. We're using PeopleSoft Financials 9 and I've tried to tweak
> the performance any way I can and cannot get this query to come back.
>
>
>
> The NVS RptBooks use templates where you specify the criteria so the
> underlying query can't be rewritten. We're running CHOOSE with Oracle
> 10.2.0.3.
>
>
>
> Anyone come across anything like this before?
>
>
>
> Here's the query:
>
>
>
> SELECT l1.tree_node_num, l2.tree_node_num, SUM (a.posted_total_amt)
>
> FROM PS_LEDGER a, PSTREESELECT10 l1, PSTREESELECT10 l2, PSTREESELECT05
> l
>
> WHERE a.ledger = 'ACTUALS'
>
> AND a.fiscal_year = 2006
>
> AND a.accounting_period BETWEEN 1 AND 12
>
> AND l1.selector_num = 14422
>
> AND a.deptid >= l1.range_from_10
>
> AND a.deptid <= l1.range_to_10
>
> AND ( l1.tree_node_num BETWEEN 1996093750 AND 1997802733
>
> OR l1.tree_node_num BETWEEN 1998046875 AND 1999023436
>
> OR l1.tree_node_num BETWEEN 1999267577 AND 1999389646
>
> OR l1.tree_node_num BETWEEN 1999450682 AND 1999511717
>
> )
>
> AND l2.selector_num = 13780
>
> AND a.ACCOUNT >= l2.range_from_10
>
> AND a.ACCOUNT <= l2.range_to_10
>
> AND ( l2.tree_node_num BETWEEN 1687500000 AND 1734374999
>
> OR l2.tree_node_num BETWEEN 1750000000 AND 1874999999
>
> )
>
> AND l.selector_num = 13782
>
> AND a.business_unit = l.range_from_05
>
> AND l.tree_node_num BETWEEN 1750000000 AND 2000000000
>
> AND a.currency_cd = 'USD'
>
> AND a.statistics_code = ' '
>
> GROUP BY l1.tree_node_num, l2.tree_node_num
>
>
>
>
>
>
>
> *Chris Taylor*
>
> *Sr. Oracle DBA*
>
> Ingram Barge Company
>
> Nashville, TN 37205
>
> Office: 615-517-3355
>
> Cell: 615-354-4799
>
> Email: chris.taylor_at_ingrambarge.com
>
>
> This message contains information that may be privileged or confidential
> and is the property of the Capgemini Group. It is intended only for the
> person to whom it is addressed. If you are not the intended recipient, you
> are not authorized to read, print, retain, copy, disseminate, distribute, or
> use this message or any part thereof. If you receive this message in error,
> please notify the sender immediately and delete all copies of this message.
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 21 2008 - 14:19:50 CST

Original text of this message