It's only an estimated plan. An Actual plan, that is a plan from the an actual execution is better since it provides more information. However, it seems that the table PS_JP_PST1_TAO4 would benefit from an index on the column PROCESS_INSTANCE. And if this value is intended to be unique, it should be a unique index. (Else not.)
Process taking double the time post Table Archieval
We are using PeopleSoft Finance Application where we have Million rows of data on some of the Core Table's.
As we are facing Performance issues, we are planning to archive the data by keeping only 2 years of data and moving all the remaining years to the archive table.
Here we did some testing by archiving 3 core finance tables on the TESTING instance.
Here the 1st table had 124242809 row Data before archive and 64691967 after archive, 2nd table had 6220948 row data before archive and 2808580 after archive and the 3rd table had 93246 row data before archive and 30908 after archive.
After archiving the data to the Archive Table, we ran the index rebuild and Update Stats on the Original Table.
Post that we ran one of the regular PeopleSoft Process and that process is taking double the time. Usually before archiving it takes 20 minutes to complete and now post archive data it takes 40 to 1 Hour.
Tried the below steps as well and the problem exists. Not sure whether we are missing out any additional steps. Kindly suggest some tips for improving the performance post Table Archive.
- Created an staging table same as live table.
- Inserted the records from live table to Staging table.
- Drop the Live Table.
- Then rename the Staging table to as same as live table name.
- Created the Index same as live table
- rebuild indexes for 3 tables and update the stats for the 3 tables.
To be able to help you we would need to see the queries, the table definitions and the query plan. Please keep in mind that you cannot assume that people here know PeopleSoft. If you want help based on knowledge about PeopleSoft, you are better off asking in a forum devoted to PeopleSoft.
Sort by: Most helpful
Please find the attached execution plan, and queries which is taking more time for particular table which we mentioned that archived.214317-queryplanandqueries.pdf
I don't want to be a whiner, but putting the XML for q query plan in a PDF is somewhat unpractical. It would be simpler to upload each plan as an XML file, or post them at www.pastetheplan.com.
There two queries which is taking more time i have took the one plan in XML 214854-execution-plan.xml
in this community we can able to attach only one file which is either .log or jpeg or XML
Sign in to comment