Automation of sql tuning method and system using statistic sql pattern analysis

A structured query language (SQL) tuning automation system through a statistical SQL pattern analysis, the SQL tuning automation system comprising:

• an operating server configured to operate a database;
• and
• a monitoring server configured to monitor performance by extracting transaction information including SQLs, a response time, and a processing result from a network stream transmitted between a client and the database of the operating server, find a cause of performance degradation, and propose improvement measures,
• wherein the monitoring server includes:
• a sniffing module configured to capture packets transmitted and received between the client and the operating server;
• a proxy module configured to relay a connection between the client and the operating server and collect packets;
• a packet analysis module configured to analyze the packets captured by the sniffing module and the proxy module and extract transaction information;
• a transaction analysis module configured to analyze user information, a connection data base management system (DBMS) server, an SQL, and a table, a column, a function, a data amount, a conditional clause, a join table, a processing result, and a processing time which are used in the SQL from the extracted transaction information and store SQL attribute information in a repository;
• an optimizing module configured to classify a fast SQL group and a slow SQL group on the basis of an average processing time stored in the repository, analyzes a characteristic of the SQL for the same table by referring to a synonym dictionary and a previous optimization history, and propose improvement measures by reflecting a characteristic of a fast SQL to that of a slow SQL;
• and
• a verification module configured to compare and verify the improvement measures with a source SQL in an actual database,
• wherein the improvement measures are proposed by monitoring performance of a target database and analyzing a cause of performance degradation.