Indexes

From PTAGISWiki

PTAGIS3 Indexes

A Trouble Report

In the early spring of 2008, users reported that some web based queries seemed to take to long to run. Around the same time Dave Marvin reported that the csv.pl process (used to generate the one tag history report) was taking too long.

A query that exhibited the former behavior was identified. In analyzing the Query Execution Plan of the query on the production server, it was noticed that a cartesian product join was being used in the query processing. An examination of the Query Execution Plan for the same query in the development server environment did not use the cartesian product join.

Further investigation showed that there existed indices on the obs_site table in the development environment that did not exist in the production environment.

This problem is documented as issue 314 in the Request Tracker (RT) system.

Remedial Actions

A rule was established in the PTAGIS section of [Big Brother] to assure that database rules, procedures and indices are in-tact. As an aside, the checks to assure that rules and procedures are in place were established in 1995 and are run each time data loading processes are invoked.

The processes that are invoked by the [Big Brother] system are:

/home/pittag/bin/DBA/DBA_IndexMonitor.pl LOG
/home/pittag/bin/DBA/DBA_RuleMonitor.pl LOG