Peoplesoft/Application Engine slow down after creating Index in required Records
QUESTION: I created Indexes in some Records through Application Designer. But the result was not as expected, the program took longer time to complete. Could you please help me find the root cause and solution for this.
ANSWER: Hi, We need more information as performance depends on multiple scenarios like volume of data/where conditions/logic e.t.c
I am assuming that you already completed basic validation steps but please validate below and let me know if you were able to find the solution
Make sure indexes are altered & verify indexes created from Application designer are available in database
See if there are any SQL locks
Enable AE trace for the Application engine(Use Trace parameters -TRACE 135 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 31) and see which step is taking long. If it is SQL step, run this step in the database and see how long it is taking.
Generate explain plan and work with your DBA's to tune the SQL further as needed.
validate other joins in that particular step
Identify if you are running the process with the required data.
Change the logic to use temp tables if you are processing more volume
If volume is more, then drop the indexes, insert into table and then recreate the indexes
---------- FOLLOW-UP ----------
QUESTION: 1.Can you please explain on why do we need to alter Index ?
2.How to verify indexes in Database,by querying DBA_INDEX ?
Will I be able to access DBA_INDEX by using the following query in SQL developer ?
Select * from DBA_INDEX;
I mean build. Once created new index in the Application desinger (Tools > Data Administration > Indexes),You need to build the indexes. If you are using SQL server, then use sp_help <TABLENAME>
This should display indexe created from Application desinger
Indexes created from AE stores in PSINDEXDEFN and once build from Application Designer, stores in database record