RE: Question for PeopleSoft DBAs out there
- From: "Goulet, Dick" <richard.goulet@xxxxxxxxxxxxx>
- To: <Chris.Taylor@xxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 21 Jan 2008 10:16:32 -0500
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@xxxxxxxxxxxxx
45 Bartlett St. / Marlborough, MA 01752
Together: the Collaborative Business Experience
______________________________________________________________
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Taylor, Chris David
Sent: Wednesday, January 09, 2008 11:58 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx
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.
Other related posts:
