Quiz time again.

Usually, I get mails asking for PeopleSoft Questionnaire.
I will try to make set of Questions and Answers as and when possible so that I can build a database.
I am posting the answers which I think might be right. If you think its not, please send the corrections with proper comments to harshagatt@gmail.com.
I will do the needful :)

1. PeopleSoft reserves ALL WinHelp context numbers (in Utilities, PeopleTools Options) up to which number?
a) 10,000,000
b) 12,000,000
c) 14,000,000
d) 16,000,000
e) 18,000,000

2. You just finished customization work that involved the creation of many new objects. You must now migrate these objects from your development database (source) to a testing database (target) to conduct more elaborate testing.
Referring to the above information, why would adding your objects to a Project streamline the migration to a new database?
a) Projects do not copy the actual objects, but only the Project Definition. You must still migrate the objects individually.
b) Projects automatically overwrite the same objects in the target database and, thus, reduce the need for consistency checks.
c) Migrating projects automatically produces a comparison report of the objects in the Project and those same objects in the target database.
d) Projects enable migration of individual objects only which ensures you do not overwrite objects in the target database unknowingly.
e) Projects provide the ability to migrate all objects at once or each object type individually.

3. PeopleSoft has its own naming convention for system and non-system (application) tables. In the list below, RECORDNAME refers to the name of any table or record.
Referring to the above information, which one of the following identifies the naming convention PeopleSoft uses for system tables(ppl tools table)?
a) PS_RECORDNAME
b) SYSRECORDNAME
c) PS$RECORDNAME
d) PSRECORDNAME
e) SYS_PSRECORDNAME

4. Your company obtained the newest Application Release of PeopleSoft, and you need to prepare for the upgrade. Upon reading the new Release Notes, you notice that changes were made to two COBOL modules (batch programs).
You need to decide how to compare the current versions of these COBOL programs with the versions delivered with the new PeopleSoft application release. Which would be the fastest method of comparing these different versions?
a) Find Object References.
b) Application Designer, Compare Batch Programs.
c) Application Designer, Upgrade View.
d) Use non-PeopleSoft comparison tools.
e) Application Designer, Tools, Upgrade, Compare & Report.
(since COBOL is a non PS delivered product)

5. Which one of the following tree types is NOT supported in the PeopleSoft tree manager?
a) Node-Oriented trees
b) Query trees
c) Detail trees
d) Summary trees
e) Combination trees (if only 1 is asked)
(types of trees: Standard Detail, Dynamic Detail, Node oriented, Summary )

6. Which of the following menu options allows you to modify the Last Process Instance Number used within the system?
a) Process Scheduler, Use, Job Definitions
b) Process Scheduler, Use, Process Servers
c) Process Scheduler, Use, System Settings, Process System (8.42)
d) Process Scheduler, Use, Administration
e) Process Scheduler, Use, Process Definitions

7. Which one of the following PeopleCode debugging tools automatically converts values of any data type other than object into string values for viewing during debugging?
a) MsgGetText (for message catalog, only message)
(msg-set, msg no, def text, [parameters])
b) MsgGet (for message catalog, msg with the message set no.)
(msg-set, msg no, def text)
c) MessageBox (style, title, msg-set, msg no, def str, parameters)
d) WinMessage (msg, style, title)
e) ExpandBindVar (str)

8. In which one of the following views would you see the fields, criteria, and other details associated with the current query?
a) Object view
b) Record view
c) Component view
d) Designer view
e) Query view

9. In which PeopleCode event does ALL data validation take place?(SaveEdit)
a) SavePostChange
b) FieldFormula
c) SavePreChange
d) FieldEdit
e) FieldChange

10. Which one of the following is NOT a valid PeopleSoft query type?
a) Message agent queries
b) Reporting queries
c) Role queries
d) Ad-hoc queries
e) View queries
(rpt qry, role qry, user qry, process qry, view qry, DB agent qry)

Answers
--------

1. a
2. e
3. d
4. d
5. b,e
6. c
   
7. d
8. d
   
9. d
10. a
    

Bypassing the Search page :)

As usual, Clients are always there to come up with strange requests. We as Consultants end up tweaking the code or sometimes doing what PeopleSoft also wont have advised us to do :-D
One of such requests was taken by my Client, the users were complaining that they were losing lot of time in searching for the tasks which are due in their bucket. So they wanted to bypass the search page and directly get the tasks (read results) once they click on the "My Task List" in PS CRM.
The users were requesting the Task List page to show the pending and open tasks for the last 2 weeks of a Sales User logged in to be shown. Initally, we were like this is not possible, blah blah and all.. but when the client was firm on this requirement, we were left with no choice but to work on this requirement.
To start off with; all requirements were captured . Functional design and technical design was done. The ral change came when the coding phase started. We were just thinking how will we do this :-)
We took a trace on the click of Search button and what we got :-( a trace file of around 45 Mb!!!!!!!!!!
tht was like some 2 to 3 textbook of 5,000 pages :-) I bet even my whole engineering material (softcopy) will not cross 45 Mb
Painstakingly, we started analysing the trace file . one day, over.. two days, over..
it took around 8 painstaking days to realise the whole flow of the RB_FILTER_FORM app package and related app packages for the Task List component.
But at the end of 8 days, we were just wondering what a brain would those PS designers would hve possessed.
In RB_FILTER_FORM, there is a search object created for that app package and it is configured to run for any component in PS. Once the search object is created, the component name should be fed into this search object.
Now, using this search object created, we can pass various search parameteres like =,>,<,<>,and,or, etc etc. This is done by using the property.value = ;
Also, the search fields can be specified in the same way.
the magic starts when u type "Search_object.Execute". This piece of code is similar to chossing all values in the search page of PeopleSoft and clicking on the Search button.
Try this concept on any of your components and see the power of PeopleSoft code. :-)

PeopleSoft Quiz!!!!!

After spending some months in PeopleSoft and attending quite a few number of interviews, I have complied some set of good questionarie.
This can be used by both the candidate and the panel also. Some of the question I have added based on the interviews attended by my friends also.
I am purposely not adding the answers to the questions listed below so that the suspense is retained and we dont tend to memorize the answers :-D

Try finding out the answers for the following Questions :
1. Which PeopleCode events can run in both two-tier and three-tier environments on either the client or application server?
2.How do you set up table-sharing in PeopleTools applications?
3. When running PS Query to output to Excel, how would you prevent the space between Excel columns being printed?
4.select max(effdt) from ps_car_alloc where emplid = &EMPLID and effdt <= %currentdate and end_dt is null; What problem exists in the above PeopleCode statement?

5.PS reserves ALL Message Sets (in Utilities, Message Catalog) up to which number?
6. Why is the record description is important when creating a new record?
7, PeopleSoft uses which tables to automate the process of creating large volumes of users on tables?
8.Your UNIX system administrator have just informed you that they are reorganizing some directory structures on the UNIX machine. They want you to determine if the PeopleSoft architecture will be affected. You quickly realize that your CAB and JAR files locations will be changed.
Referring to the above, how do you ensure the Web client will perform correctly once these directory structures have changed?
9.How would you run SQL Alter to alert you to discrepancies between your record definitions and underlying SQL tables, without actually performing changes to the object in the database?
10.How many security layers are involved in the PeopleSoft environment?
11.How are PeopleCode programs saved within the database?
12.What is PeopleSoft's recommended way to access panel groups and the business logic associated with them outside of PeopleSoft online panels?
13.Your application users are experiencing problems with one of the customizations your development team has recently worked on. The customization involved changes to a PeopleCode object within the database. You decide to use Application Reviewer to debug the PeopleCode. When you try to start the Application Reviewer you receive an error that the Reviewer cannot be started.
Referring to the above, why is the Application Reviewer unable to start in your normal three-tier client configuration?
14.A user requests a new field to be added to the data entry application used by the company. This new field will capture large amounts of text, up to 2000 characters long. As a developer, you must decide what type of field to use to store this data.
Referring to the above information, which one of the following field types would you create to store this amount of data?
15.If an object is removed from the database, it will NOT automatically be removed from the project(s) to which it was added. How do you determine if a project contains valid objects without checking every object manually?


Much more Questions will be added!!!! keep watching this space.

Oder Capture component

PeopleSoft CRM runs mainly on Application package code and it is reducing its dependency on the Function Library with each release.
HRMS, FSCM, EPM and other modules of PeopleSoft are yet to adopt the Application Package totally.

Application package is the heart of the PeopleSoft where in it will allow reusability, Object oriented programming, building complex architecture for representing component and also expose PeopleSoft business logic through CI adapters.

Order Capture module in CRM is one such example for displaying the power of the Application package. The whole component is captured into RO_CAPTURE and RO_CAPTURE_TYPES application package.

The logic is built so well that it closely integrates with other application packages. We all know that PeopleSoft doesn’t support multiple inheritances while building/creating the classes. To bring the business logic of the other application packages, PeopleSoft uses a combination of property, super class and get/set concepts.
Our team was facing a complex problem in Order Capture module, it goes something like this:
1. Pricing Engine currently runs on Header level keys; configure it run on combination of Header and Line level keys.
2. Currently, Price List cannot be associated to a Catalog code so build the customization for the same
3. We will not be using the PS delivered Catalog functionality, so on the line level details add a new field Catalog Code. On the field change event, run the pricing engine with the Catalog code as line level key.

The above one liners were looking simple to read and understand, but as we started to look into the code and architecture of the Order Capture component, we realized one thing. It doesn’t look as simple as the requirements are written in the document. :-(
But, we were determined to crack this complex puzzle. Yeah, the whole application package looked like one complex jigsaw puzzle with all pieces thrown around. It required complete understanding of these huge code and classes to get the complete picture.
So, we started understanding the requirements and breaking down this huge task into smaller ones.

Initially, we started understanding the Order Capture and Pricing engine (Price Rule + Price list + Arbitration Plan) linkage.
While the CSR (Customer Service Rep) takes the Order, PeopleSoft calculates the discounts and offers on Products using the Header level information (record.RO_HEADER). The Price Rule, Price List and Arbitration plan are built in the PeopleSoft which will identify the information on the Header level records.
The above scenario was existent in the earlier versions of PeopleSoft i.e before PeopleSoft CRM 9.0.

One of the major enhancements in the PS CRM 9.0 was that PeopleSoft is able to calculate discounts and offers now on line level also. This was what the client wanted in our case and it matched with their requirement.
Strangely, the gap analysis was done against PS CRM 8.9 by the functional team and they had listed this request by the client as one the major enhancement as it was changing the whole logic of the Pricing Engine.

Now, once we started work in this aspect we found that Pricing engine could be configured to recognize the keys not just on the Order Header level but also in the Line level.
But, we wanted to confirm this with the Functional team whether we are on the right track. We told them about our finding, they too were surprised and asked us whether we can give them a demo of our work till now.

We smelled some success, after so many hours of understanding, reading and searching. We readily agreed and we were excited also at the same time.
The summary of our customizations are as follows:
1. We built a Price Key for Catalog Code. This will be used to capture the Transactions data of the Order Capture component. (Homeà Setup CRMàEnterpriser PriceràBuild Price key by transaction)
2. Once this is done, check whether the newly built key is available as “Related object” in Price List, Price Rule, Arbitration plan. If this is done, then you are on the right track.
3. Now build your own Price Rule, Price List, and Arbitration Plan for testing purposes (Homeà Pricing Configurationà Price Rule, Price List and Arbitration plan).
4. Go to Homeà Pricing Configurationà Simulator. Simulator is equal to Order Capture logic and this is used by the Marketing Team to determine whether the Price Rules and Price lists created are working as intended.
5. Here, create a new Order and check whether the Pricing engine is running as intended. (Line level keys can be added or deleted by clicking on Key symbol in the line level details section)

Finally, the D-day arrived. We were all tensed, will we able to pull it off or not. But it turned out to be a smooth one with the functional team happy with our findings and told us that we are on the right track.

A complex puzzle solved with great team effort and some own thinking helped :-D

App

How to use application packages effectively

All of us would have done the customer data conversion or company data conversion while clients migrate from legacy systems to PeopleSoft CRM systems.
This piece of code which I developed is really efficient and makes the best use of application packages.
Steps to follow :
1. Create an application engine and store the file details into a temp table or state record depending on the technical design.
2. In the peoplecode section,
a) import the RB_CDM:objectcollection and Individual:contact objects.
b) Create a object collect of RB_CDM.
c) Once done, instantiate the object in add mode and set the properties.
d) Invoke the save method which would create a new record.

Once this is done, just run the app engine and see the magic.
The best part of this code is that the speed at which it runs in the process schedluer and you will be amazed.
I did a test run of inserting 100 rows into the PeopleSoft CRM system and it ran just under 2 minues. Roughly, 1.2 sec for insertng every customer.
Hope this approach helps for the other developers.
If you have any other code/examples , mail me on harshagatt@gmail.com. I will post the same on my blog.
Regards,
Harsha Gatt

PS: I got to know that example code is copyrighted material and not supposed to be posted.
Thanks to Chris Speer for letting me know about the same.

KEY CONCEPTS FOR PEOPLESOFT

KEY CONCEPTS FOR PEOPLESOFT NAVIGATION
Uniquely identifying a row of data.
PeopleSoft uniquely identifies a row through the use of key fields.

Difference between primary and alternate search keys.
Primary search keys uniquely identify a row of data. Alternate search
keys help determine a unique row of data but you need more information
to narrow a search.

Panel group.(Component)
A panel group is created when there are too many fields to fit on one panel.
The panels in the panel group are organized by subject or business process.
How to move between panels in a panel group.
-clicking on panel tabs
pressing the Next Panel in Group icon.
- using the F2 function key.

Valid value search.
Helps narrow a search and the lists of options to choose from is a manageable size.

Fastest way to return to your last accessed panel in PeopleSoft.
The fastest way to return to your last accessed panel is by using the Back button.

Effective Dating
Effective dating is an important PeopleSoft concept that allows you to:
-recreate any point in time.
-keep a chronological record of an employee’s history and the values
maintained through tables.

Effective Date Categories
There are three (3) effective date categories: Current, Future and History.
Future - Data rows have an effective date greater than today’s date.
Current - Close to but not greater than today’s date.
History - Rows have effective dates less than the effective date on the
current row.

Effective Date Sequencing
Actions that occur on the same date are entered in a certain order or sequence.

Effective Date Status
The status is either Active or Inactive.

Update/Display
The Update/Display Action 􀀻 allows you to do the following (if authorized) on an
effective-dated table:
-View current and future rows.
-Change future rows.
-Insert future rows or a new current row.

Update/Display All
The Update/Display All action 􀀻+ allows you to perform (if authorized) the functions
noted above and and view history rows.

Data Entry Modes
Data entry modes determine how you are working in a panel. For instance, there is a
mode for adding records and another mode for updating existing records.
☺ By selecting a data entry mode, a search dialog box displays.
In order to open a panel, a mode must be associated with it. If the panel you select does
not contain any effective dated rows, the system defaults to the Update/Display mode,
which allows you to work with existing records. If the panel you select contains effective
dated rows you need to select a specific mode to work in.
Data Entry Mode Action
Add-----> Inserts a new record to the database. Used when a
new employee is hired.
Update/Display------> Access existing records to view or add current or
future information.
Update/Display All------> Accesses existing records to view or add current,
future or historical information.
Correction------> Limited to certain personnel only. Users will know the impact correcting the history records.

Benefit of effective dating
-Allows a history of information to be maintained.
Need for effective date categories
- PeopleSoft classifies each row of data as current, future or history which allows for a
chronological ordering of information.

Importance of effective date status
- Effective data status allows you to determine whether effective-dated information
(employee address, job data, etc.) is active or inactive at any point in time.
Function of “Add,” data entry mode
- “Add” creates a new high level record (new position, new applicant, new job
requisition).

Difference between Update/Display and Update/Display All
-Update/Display All shows current, future and history rows.
-Update/Display shows current and future rows only.

What does it mean if there is a choice of data entry modes when you select a panel?
-If a choice of data entry modes is available on a panel you’ve selected, it means the
panel is effective-dated.

What does it mean when you see a scroll bar?
-The scroll bar indicates there is more than one row of data present.

Do scroll bars indicate an effective dated record?
-The presence of a scroll bar does not necessarily indicate an effective-dated record.

Tuning your PeopleSoft Applications and Environment for Maximum Performance

PERFORMANCE TUNING FOR PEOPLESOFT APPLICATIONS
1.Introduction:
It is a widely known fact that 80% of performance problems are a direct result of the to poor performance, such as server configuration, resource contention. Assuming you have tuned your servers and followed the guidelines for your database server, application server, and web server, most of your performance problems can be addressed by tuning the PeopleSoft Application.
This paper presents methodologies and techniques for optimizing the performance of PeopleSoft applications. The methodologies that are discussed are intended to provide useful tips that will help to better tune your PeopleSoft applications. These tips focus on tuning several different aspects within a PeopleSoft environment ranging from servers to indexes. You will find some of these tips provide you with a significant improvement in performance while others may not apply to your environment.

2. SERVER PERFORMANCE
In general, the approach to application tuning starts by examining the consumption of resources. The entire system needs to be monitored to analyze resource consumption on an individual component basis and as a whole.
The key to tuning servers in a PeopleSoft environment is to implement a methodology to accurately capture as much information as possible without utilizing critical resources needed to serve the end-users.
Traditional tools used to measure utilizations impact the system being measured and ultimately the end-user experience. Commands like the following provide snapshot data but not without an associated cost. These tools can consume a significant amount of resources so care should be taken when you execute them.
a) df size
b) iostat swapinfo
c) ipcs timex
d) netstat top
f) ps uptime
g) sar vmstat
h) swapinfo also glance & gpm
The goal of using these native commands is to identify, if and where, a bottleneck is in the server. Is the problem in the CPU, I/O or memory? These native tools provide indicators, but at the same time could skew the results because of the overhead associated with them. Typically, additional third party tools are needed to complete the analysis.
The last hurdle companies are facing in tuning the server is making timing decisions on when to upgrade the hardware itself. To do this, much more information needs to be collected and stored in order to understand if an historical spike in resource utilization was a one-time aberration or a regular occurrence building over time. The recommendation is to look at third party vendors for solutions that can collect key performance indicators while minimizing overhead on the system. The collected data can then be put in a repository for detailed historical analysis.

3. WEB SERVER PERFORMANCE
The release of PeopleSoft Pure Internet Architecture™ introduces new components to PeopleSoft architecture—the web server and application server. The application server is where most shops struggle with appropriate sizing. Web servers are used for handling the end-user srequests from a web browser to eliminate the administrative costs associated with loading software (fat clients) on individual desktops. The benefit is a significant savings on software deployment costs, maintenance, and upgrades. While the shift from fat clients to thin lessens the administrative burden, it increases the need to ensure the web servers are finely tuned since they
will service a large number of clients. The requirement for these web servers to achieve optimal performance is vital due to the mission critical-nature PeopleSoft plays in today’s enterprise.
Recommendations for ensuring good performance for your web servers:
• Ensure your load balancing strategy is sound
• Implement a solution to verify and highlight changes in traffic volumes
• Closely monitor the response times to verify that your strategy is optimizing your web servers
• Measure and review historical patterns on server resource utilization (see server section above).
• Increase the HEAP size to 200, 250, 300, or 380 MB for the web logic startup script.

4. TUXEDO PERFORMANCE MANAGEMENT
Tuxedo is additional middleware PeopleSoft utilizes to manage the following Internet application server services:
• Component Processor—Responsible for executing PeopleSoft Components—the core PeopleSoft application business logic
• Business Interlink Processor— Responsible for managing the interactions with third-party systems
• Application Messaging Processor—Manages messages in a PeopleSoft system
• User Interface Generator—Generates the user interface based on the Component or Query definition and generates the appropriate markup language (HTML, WML, or XML) and scripting language (JavaScript, WMLScript) based on the client accessing the application
• Security Manager—Authenticates end-users and manages their system access privileges
• Query Processor—Executes queries using the PeopleSoft Query tool
• Application Engine—Executes PeopleSoft Application Engine processes
• Process Scheduler—Executes reports and batch processes and registers the reports in the Portal’s Content Registry
• SQL Access Manager—Manages all interaction with the relational DBMS via SQL
This Tuxedo middle tier is another critical and influential component of performance. Similar to the web server, what is needed is a way to see into the “black box” to further understand some of the key performance metrics.
Some of the performance metrics you want to capture when analyzing tuxedo are:
• Transaction volumes by domain, server, and application
• Response time for each end-user request
• Tuxedo service generating a poor performing SQL statement
• Break down of Tuxedo time by Service time and Queue time
• Identify problem origin – is it in tuxedo or the database?
• Response time comparisons for multiple Tuxedo Server
Our experience has shown that too often companies throw hardware at a Tuxedo performance problem when a more effective solution can be as simple as adding another domain to the existing server(s). This is due to the fact that PeopleSoft and Tuxedo lack management solutions that provide historical views of performance.

5. APPLICATION PERFORMANCE
It is an accepted fact that 80% of application and database problems reside in the application code. But, there are other technical items to consider which could influence the applications performance. Here are some specific items to focus on when evaluating your database environment:
• Make sure the database is sized and configured correctly
• Make sure that the hardware and O/S environments are set up correctly
• Verify that patch levels are current
• Fix common SQL errors
• Review documentation of known problems with PeopleSoft supplied code
• Be sure to check available patches from PeopleSoft that might address the problem
• Review PeopleSoft suggested kernel parameters
• Set up the right number of processes
• Review the application server blocking for Long Running Queries
• Make sure you don’t undersize your version 8 application server
It is also recommended to continue to review these items on a periodic basis.


6. DATABASE PERFORMANCE
The performance of an application depends on many factors. We will start with the overall general approach to tuning SQL statements. We will then move to such areas as indexes, performance monitoring, queries, the Tempdb (Tempdb is often referred to as plain “TEMP”), and, finally, servers and memory allocation.
To understand the effect of tuning, we must compare ‘time in Oracle’ with ‘request wait time’. Request wait time is the time that a session is connected to Oracle, but not issuing SQL statements. In Oracle time shows the amount of time resolving a SQL statement once it has been submitted to Oracle for execution. If time in Oracle is not significantly smaller than the request wait time, then application tuning should be examined. Request wait time is almost always much greater than in Oracle time, especially for on line users, because of think time.
One exception to this is for a batch job that connects to Oracle and submits SQL statements, then processes the returned data. A greater ratio of request wait to Oracle could indicate a loop in the application outside of Oracle.
This should be identified and eliminated before continuing the performance analysis.
The next step focuses on tuning the SQL statements that use the most resources. To find the most resource consuming SQL statements, the scheduled collection approach can be used. The duration time is a commonly used criteria to locate the offensive SQL statements. Other useful criteria include the following wait states: I/O, row lock, table lock, shared pool, buffer, rollback segment, redo log buffer, internal lock, log switch and clear, background process, CPU, memory and I/O.For each offensive SQL statement, the execution plan and database statistics are analyzed. The following statistics are important: table and column selectivity, index clustering factor, and storage parameters. First, all the joins of the SQL are considered. For each join, the ordering of the tables is analyzed. It is of major importance to have the most selective filter condition for the driving table. Then, the type of the join is considered. If the join
Represents a Nested Loop, forcing it into a hash join can be advantageous under some conditions.
The analysis stage usually results in several modification proposals, which are applied and tested in sequence. Corrective actions include database object changes and SQL changes. The typical database object changes are:
index change, index rebuild and table reorganization. The typical SQL changes are: replacing subquery with a join, splitting a SQL into multiple SQLs, and inserting Oracle hints to direct the Optimizer to the right execution plan.

7. INDEXES
Tuning indexes is another important factor in improving performance in a PeopleSoft environment. Index maintenance is crucial to maintaining good database performance. Statistics about data distribution are maintained in each index. These statistics are used by the optimizer to decide which, if any, indexes to use. The statistics must also be maintained so that the optimizer can continue to make good decisions. Thus, procedures should be setup to update the statistics as often as is practical.
Keep in mind that objects that do not change, do not need to have their statistics created again. If the object has not changed, the stats will be the same. In this case, recreating the same statistics over again will waste resources.
Since PeopleSoft uses a lot of temp tables that are loaded and then deleted, but not dropped, it is helpful to create the statistics when those tables are full of data. If the statistics are created when the table is empty, the stats will reflect that fact. The Optimizer will not have correct information when it chooses an access path.
Periodically, indexes should be rebuilt to counter index fragmentation. An index creation script can be created via PeopleTools to drop and rebuild indexes. This procedure will eliminate index -wasted space on blocks that are created as a result of Oracle logical deletes. This is only necessary on tables that are changed often (inserts, updates or deletions).
Index scheme is also important to look at. The indexes in a standard PeopleSoft installation may not be the most efficient ones for all installations. Closely examine your data’s pattern, distribution, and modify the indexes accordingly. For example, the index on PS_VOUCHER (BUSINESS_UNIT, VOUCHER_ID) could be changed to (VOUCHER_ID, BUSINESS_UNIT) for an implementation with only a few business units. Use ISQLW Query Options (Show Query Plan and Show Stats I/O) to determine the effectiveness of new indexes. However, be
careful to thoroughly test the new index scheme to find all of its ramifications.

8. QUERIES
It is a good idea to examine queries to try and fix a problem that is affecting the application. Query analyzer can be used to see optimizer plans of slow SQL statements. Choose “Query/Display Plan” to see a graphical representation of a query plan. Alternatively, by issuing a “set showplan_text on” and running the statement you will get a textual representation of the plan, showing indexes used, the order in which the tables were used, etc.
When investigating queries, worktables created per second should also be addressed. If you see a large number of work tables being created per second (i.e. hundreds per second), this means that a large amount of sorting is occurring. This may not be a serious a problem, especially if it does not correspond with a large amount of I/O.
However, performance could be improved by tuning the queries and indexes involved in the sorts and, ideally, this will eliminate some sorting.
Recommendations for ensuring good performance for your Database servers:
- Avoid using Boolean operators >, <, >=, <=, is null, is not null
- Avoid using Not in,! =
- Avoid using Like '%pattern', not exists
- Avoid using Calculations on unindexed columns or (use union instead)
- Avoid using having (use a WHERE clause instead)
- Always use Enable aliases to prefix all columns
- Always use Place indexed columns higher in the WHERE clause
- Always use Use SQL Joins instead of using sub-queries
- Always use Make the table with the least number of rows the driving table by making it first in the FROM clause.
- Always Establish a tuning environment that reflects your production database
- Always Establish performance expectations before you begin
- Always Design and develop with performance in mind
- Create Indexes to support selective WHERE clauses and join conditions
- Use concatenated indexes where appropriate
- Consider indexing more than you think you should, to avoid table lookups
- Pick the best join method
- Nested loops joins are best for indexed joins of subsets
- Hash joins are usually the best choice for "big" joins
- Pick the best join order
- Pick the best "driving" table
- Eliminate rows as early as possible in the join order
- Use bind variables. Bind variables are key to application scalability
- Use Oracle hints where appropriate
- Compare performance between alternative syntax for your SQL statement
- Consider utilizing PL/SQL to overcome difficult SQL tuning issues
- Consider using third party tools to make the job of SQL tuning easier
- Use of Bind Variables:
The number of compiles can be reduced to one per multiple executions of the same SQL statement by constructing the statement with bind variables instead of literals.
- Application Engine - Reuse Flag
Application Engine programs use bind variables in their SQL statements, but these variables are PeopleSoft specific. When a statement is passed to the database, PeopleSoft Application Engine sends the statement with literal values. The only way to tell the Application Engine program to send the bind variables is by activating the ReUse flag in the Application Engine step containing the statement that needs to use the bind variable.

9. TEMPDB
To ensure that your application is performing at peak efficiency, it is important to look at the tempdb. The tempdb is used for sorting result sets, either because of an ‘order by’ clause in a query or to organize pre-result sets needed to execute a given query plan. If tempdb is being used extensively (evidenced by many work tables being created per second or heavy I/O to tempdb files), performance can be improved by tuning it.
First, consider moving the tempdb to its own set of disks. Do this with ‘alter database’ using the ‘modify file’ option to specify a new location for tempdb’s data file and log file. It may also be worthwhile to increase the SIZE option to a larger value, such as 100MB and increase the FILEGROWTH option to around 50MB.
Another option to consider is adding several data files to tempdb rather than having just one. This will help reduce contention on the tempdb. Do this by using ‘alter database’ using the ‘add file’ option. As with tempdb’s original data file, increase the SIZE option to a larger value as well as the FILEGROWTH option.

10. SERVERS AND MEMORY ALLOCATION
The use of an application server is strongly recommended for all on-line connections. The application server queues incoming requests and dramatically reduces process blocking in the database. This will not help batch processes, but it will greatly increase the number of on-line users.
Collecting CPU wait, memory wait, and I/O wait may show the application is having to wait on server resources. Typically, this indicates an undersized server or that other applications running on the server were hogging resources. Today, many IT organizations are looking at server consolidation to reduce the cost of ownership. Taking this approach puts you in a position to analyze performance over time as an aid to a server consolidation effort.
While it is possible to share a PeopleSoft database server with other applications – database or otherwise – it is always preferable to dedicate the entire server to the PeopleSoft installation. Any process running on the server may use resources that could be better utilized by the database engine or a PeopleSoft process running on the database server. Use of the database server as a file server can seriously degrade the database response time, thus it is important to dedicate the entire server to PeopleSoft processes. The single greatest determinant of database server performance is the amount of memory allocated to it. The memory configuration parameter is expressed in 2K blocks. Thus, if you wanted to allocate 100 MB you would set the memory to 51200.
Generally, the more memory allocated to a server the better it will perform. The goal is to add enough memory to increase performance, but not so much that it no longer helps. This
determination can be made through the NT Performance monitor. Monitor the Cache Hit Ratio and disk usage via the Performance monitor to determine if more memory should be allocated to the database engine. For the most part, it is better to have too much memory allocated than not enough with database servers. For Application servers, additional memory usually helps but having too much could have a negative effect since more memory correlates to longer search times when there is an operation frequently looking for an object in memory.

11. CONCLUSION:
The presented methods are intended as tips to help better tune your PeopleSoft applications. These tips are simply suggestions, as mentioned earlier, and they need to be used with caution as each tip may not apply directly to your situation. However, if used properly, the suggested tips can tune your PeopleSoft applications to perform at an optimal level.
There are many native tools available to monitor the various components that make up the PeopleSoft landscape. How effective they are in identifying the root cause of performance problems is still in question. The ultimate goal is to find a single solution to gain visibility end-to-end and in between.
It is a widely known fact that 80% of performance problems are a direct result of the application code. There are other factors that contribute to poor performance, such as server configuration, resource contention. Assuming you have tuned your servers and followed the guidelines for your database server, application server, and web server, most of your performance problems can be addressed by tuning the PeopleSoft Application code. Tuning the application can consist of tuning, PeopleCode, SQR code, SQL-intensive code, queries, nVision, and indexes.
Database Tuning:
Ineffective Indexing One of the most common performance problems in the PeopleSoft Application is ineffective indexing against key application tables. As we stated earlier, the PeopleSoft software is delivered with a generic code set that runs on several database platforms. In addition to the code set, the indexes that exist are not specific to any one environment. Because of this, you need to fine-tune your application by selectively finding poor performing applications and determining whether or not the cause is due to ineffective indexing. This can be achieved by tracing the SQL of poor performing pages, application engine programs, COBOL, or sqr programs and finding the long running queries. Once you find the problematic queries that take a significant amount of time to complete, you will need to analyze the indexes that are being used. Here is an example of how to fine-tune your indexes. The Journal Generator application, within the Financials software, could be a COBOL application (FSPGJGEN) that performs very many selects based on the run control id parameters. In running this process it is determined that it is taking approximately 2 hours to process only 50 Journals. The first thing to do is to turn on tracing for that specific process and re-running the process in your test environment. Be sure that you always do your tuning in your test environment. You do not want to blindly start adding indexes to your production environment without performing full regression testing. The results can be catastrophic. Once you have the trace file, you can examine it and look for the timings for the long running queries.
After examining the trace file we find the SQL statement that is causing the performance problem. Once you find the SQL statement, you can run it through your RDBMS query tool to determine which indexes are being used. If you are using SQL Server, you will issue the following command: SET SHOWPLAN_ALL { ON | OFF } If you are using Oracle you will utilize the explain plan. Once you execute this command, you can then run your select statement. This returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements, including the indexes that are being utilized. The next step is to look at the columns in the where clause of the SQL statement and determine if the indexes being used, if any, contain these columns. If they do not, you can simply create a new index with the missing columns. Once created re-run your query to re-examine the index usage. Simply repeat this process until you achieve the improved performance. In some cases, certain SQL statements will never even use an Index. This is what is called a full table scan. Full table scans are extremely taxing on the system and cause major performance degradation. If you determine that a SQL query is performing a full table scan, simply create an Index or Indexes with the columns that are contained within the where clause. Tuning and adding indexes is one of the most overlooked and very simple ways to improve performance. Just remember the following steps. - Trace - Examine the SQL - Analyze the SQL in your RDBMS tool - Determine Indexes being used - Create Indexes with Columns in Where clause - Re-Analyze the SQL and repeat until you get improved results Another tip for tuning indexes is to try re-ordering columns within the index. You can sometimes gain huge performance improvements, by simply changing the order of the columns when you create the index. This is a trial and error method that you will have to test. There is no hard and fast rule for which column should be placed in what order. Temporary Tables PeopleSoft utilizes temporary tables in many of its application programs, especially application engine programs. These application programs are constantly populated with data and deleted, over and over. Each time a temporary table is populated and deleted, it causes certain databases like Oracle to leave the High Water Mark and produces full table scans. For example, an application engine program can insert 200000 rows and then delete them. The next time that application runs, it only inserts 2000 rows, yet a read against that table performs poorly. Additionally, the indexes that exist on these temporary tables are heavily fragmented from all of the deletes. Temporary tables are a common cause of performance problems. In order to prevent fragmentation and improve performance on most used temporary tables, you should truncate these tables on a regular basis.
You can learn more about tuning your PeopleSoft applications and database from an e-book , titled "Tuning your PeopleSoft Applications and Environment for Maximum Performance" . Happy Tuning… :)