A customer had a query which performed well on one database, but was not optimal on another. In this case the plans for the queries differed between the two databases.
In this case the client required to copy the statistics on the tables in the first database to the second to see if the problem was down to statistics. How do you do it though ?
Firstly there is no tool which can import and export statistics, unlike dbms_stats in oracle which allows you to import and export table statistics. However there is a way around this in Netezza. Of course, the logical method is to recreate statistics in the second database, and check distribution to ensure that they are distributed in the same manner. Normally this would be down to the distribution on one of the tables in the query resulting in a changed plan.
Using the script nzdumpschema, Netezza will extract the table definitions and statistics on a database to allow you to run this against another database. Part of the process this script generates includes a number of statements which insert data into the _t_class, _t_attribute and _t_statistic tables.
The script does firstly drop and re-create the database so DON’T RUN IT without editing the script first. Remove the drop and create database statements if you want the database name to be the same, else, if your database is called PROD, then the database created will be called PROD_SHADOW. So, either remove or rename the database name in the create statement to ensure you have the required setup in the script.
If your second database already has the tables and data in it, then you should remove all the create table statements, and execute it against the database which has performance problems, to update the statistics in that database to appear the same as the production database.
Running the script having modified the database name will create a new database which is empty but contains all the statistics in the original database. This will allow you to ensure execution is the same as the originial database.
Remember to run the nzdumpschema on the second database and perform the same changes incase you want to re-import the statistics back into the database after testing.
