4285RE: PeopleSoft DBA Forum enabledbmonitoring question
- Aug 16, 2010I believe that SET CONTEXT INFO is the equivalent of the call to
DBMS_APPLICATION_INFO that PeopleSoft does on Oracle. This happens at the
top of each service call, and allows you to see which PeopleSoft Operator is
executing some code.
I do not know of any way to inject the additional call.
If I understand you correctly, you are trying to manage the amount of
resource that a query can consume. On Oracle you can only do this for an
specific user. By default, All PeopleSoft processes connect as the same
Oracle user. You need to find a way to make the PSQRYSRV processes connect
to a different user.
This is not easy on PeopleSoft on Oracle either. You have to create an
additional access profiles so that when the QRYSRV connects as a different
user it uses the alternative access profile, and lots of grants and synonyms
to the new database user account. The connect information is held in
psappsrv.cfg, and you need to provide an alternative config file for the
psqrysrv processes. That requires some editing of the Tuxedo config
template file psappsrv.ubx. I have tried this on PT8.48, and I didn't get a
satisfactory solution. The query servers still used the original config
file. Even if you can get it working, you end up with something that is
dificult to maintain.
You can set query timeouts so that the query terminates after a while, but
(on Oracle) this doesn't work well with queries that have larges sorts or
hash operations (caused by use of DISTINCT, UNION or ORDER BY) where the
database takes a long time to fetch the first row in the result set.
Best you can do is discourage users from running long running queries
on-line, restrict maximum number of PSQRYSRV process, and encourage users to
schedule queries on the process scheduler.
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>From: firstname.lastname@example.org [mailto:email@example.com]
>On Behalf Of m19peters
>Sent: Wednesday, August 11, 2010 10:07 PM
>Subject: PeopleSoft DBA Forum enabledbmonitoring question
>I'm unfortunate enough to have to work with PeopleSoft on SQL
>Server. I'm trying to figure out a way to use Resource
>Governor. For those that don't know what that is... it's a way
>to assign a connection within sql server to a "resource pool".
> This "pool" might only be allowed 10% of the cpu... or
>memory.. or whatever. It's a way to "govern" your resources
>on your sql box.
>I'd like to govern on if its coming from PS query for
>instance... In Oracle this would be very easy. As the program
>name is set in your client_info as well as your program column
>in v$session (you see psqrysrv.exe), along with the ps user
>id, server, etc. Well in sql server our equivilents are
>context_info and program_name. But for context_info all I see
>is <ps user or ouid>, <os user>, <app server>, and sometimes
><domain>... and program_name just says "PeopleSoft"...
>Does anyone know how this context info or app_name are set.
>Is there some configuration parameter that needs set or some
>code that needs changed or whatever... that would have sql
>server display the program name much like oracle does.
>When I trace the db I see "SET CONTEXT INFO <some binary>"
>displayed before each transaction. I just need some way to
>jump in before that and write "psqrysrv.exe" on the end of it
>if its coming from ps query.
>Sorry for the sql server question... but your the only group I
>could find for peoplesoft dba's.
>Also, just knowing what you know about peoplesoft... is there
>some other way you would think of doing this. I'm new to
>peoplesoft... I use to primarily work with SAP.
>PeopleSoft for the Oracle DBA is published by Apress - see
>http://www.psftdba.com The PeopleSoft DBA Forum is managed by
>http://www.go-faster.co.uk Yahoo! Groups Links
- << Previous post in topic Next post in topic >>