Information Needed By Customer Support

If you have worked through the query performance evaluation and your problem is not resolved, call customer support. Before calling, follow these two procedures:

• Isolate and analyze the suspect query
• Create a test case

Isolate and Analyze the Problem Query

To determine whether the problem is due to the user interface, the query itself, or a software bug, follow these steps:

1. Isolate a poorly performing query from your user interface using the trace flag set printqry, which prints queries before they are optimized and executed. Identify the query that seems to hang.

   For details on setting printqry, see the System Administrator Guide.

   Execute the query in a terminal monitor or from within the VDBA SQL Scratchpad window, and determine if performance is the same. If performance is only a problem when the query is executed from the user interface, you have identified an application problem. If performance is the same, continue.

2. In a terminal monitor, issue the following statements to display the QEP without running the query:

   set qep;
   set optimizeonly;

   Now, execute your query and save the output to a file for examination. After running the query, exit the terminal monitor session or turn query execution back on using:

   set nooptimizeonly;

   For details on these set statements, see the System Administrator Guide.

3. Review the Design Issues section and evaluate the QEP for your query. For example, you can look for:
   • Large table scans that can be avoided
   • An additional index that is needed
   • Cartesian products with large tables
   • Function joins
   • If you are not able to identify your problem and suspect a software bug, submit your query and a test case to customer support.

Create a Test Case

To create a test case, follow these steps:

1. Verify that you are using the most recent release of Ingres available for your platform.
2. Collect the information customer support needs to duplicate your problem.

   Customer support needs the following information in ASCII files that you can send by e-mail, UUCP, or on a tape:

   • The exact query that causes the error to occur
   • The QEP generated by the problem query
   • Dump optimizer statistics for all the tables in the query (use the Direct Output to Server File option in the Display Statistics dialog in VDBA, or the statdump command with the -o flag)
   • The help table tablename information for all the tables that the query references (or equivalent information obtained from within VDBA)
   • The help index indexname information for all secondary indexes of tables in the query (or equivalent information obtained from within VDBA)
   • The help permit on table tablename information for grants on all the tables in the query (or equivalent information obtained from within VDBA)
   • A query of the system catalogs for information about each table. Look at iirelation and select relpages, reltups, relmain, and relprim, where the relid is equal to each table and index in the query.

The create scripts and data for all the tables, indexes, and grants that the query references. When generating the scripts, you must specify the Create Printable Data Files option. For information on generating these scripts, see the chapter "Loading and Unloading Databases."