One of the most important but yet forgotten concept during data conversions is 'database statistics'.
Object statistics give valuable information to the database optimizer to be able to generate the right execution plan for the SQL statements used in the conversions.
There are numerous ways that a given SQL statement can be executed.
For instance, you could reach nearest Starbucks or Subway from your home by taking a number of different roads/highways. Imagine, what would happen if you do not have the below critical information to get to your nearest Starbucks for your morning coffee.
Similarly, without the object level statistics, the database optimizer cannot decide how to execute the SQL statement in the most efficient manner. When we are dealing with conversions, we are dealing with millions or rows. So, the statistics become even more important.
In Peoplesoft, when we multi-thread a process, each thread works on its own instance of the base table. This instance of the table is temporary in nature meaning the data exists for the duration of the thread.
One of the first steps in the conversion is to gather statistics on the underlying objects like tables, and indexes once the initial INSERT statement is called to populate the STAGING tables.
This can be easily done in Peoplesoft by calling UPDATESTATS at the end of the INSERT statement. Another important point to remember here is to make sure to issue a COMMIT just after the INSERT and before the UPDATESTATS else, gather statistics would still run but would show 0 rows in dba_tables leading to incorrect execution path.
This simple step could save you countless hours in conversion and could save you a lot of hassles during the go-live. All the best for your go-live.
Have a question? Please leave a comment and I would be glad to answer.
Object statistics give valuable information to the database optimizer to be able to generate the right execution plan for the SQL statements used in the conversions.
There are numerous ways that a given SQL statement can be executed.
For instance, you could reach nearest Starbucks or Subway from your home by taking a number of different roads/highways. Imagine, what would happen if you do not have the below critical information to get to your nearest Starbucks for your morning coffee.
- Distance between your home and Starbucks
- Condition of the various road
- Traffic situation on these situations
Similarly, without the object level statistics, the database optimizer cannot decide how to execute the SQL statement in the most efficient manner. When we are dealing with conversions, we are dealing with millions or rows. So, the statistics become even more important.
In Peoplesoft, when we multi-thread a process, each thread works on its own instance of the base table. This instance of the table is temporary in nature meaning the data exists for the duration of the thread.
One of the first steps in the conversion is to gather statistics on the underlying objects like tables, and indexes once the initial INSERT statement is called to populate the STAGING tables.
This can be easily done in Peoplesoft by calling UPDATESTATS at the end of the INSERT statement. Another important point to remember here is to make sure to issue a COMMIT just after the INSERT and before the UPDATESTATS else, gather statistics would still run but would show 0 rows in dba_tables leading to incorrect execution path.
This simple step could save you countless hours in conversion and could save you a lot of hassles during the go-live. All the best for your go-live.
Have a question? Please leave a comment and I would be glad to answer.
No comments:
Post a Comment