In 2002, Microsoft reported that the majority of errors and crashes in their software were caused by a minority of the bugs they detected, specifically about 20%. This phenomenon, known as the Pareto principle or the eighty-twenty rule, is also relevant to the analysis of database SQL behavior. It is assumed that only 20% of SQL statements should account for 80% of the resource consumption of the entire database system. Any application system in which less than 20% of SQL statements consume more than 80% of database resources is considered abnormal and should be reviewed and optimized. Using a SQL resource spectrum analysis technique, users have the ability to set a resource threshold percentage, typically around 80%, to pinpoint the top M% of SQL statements that are responsible for the majority of resource consumption. If the analysis reveals that only a few SQL statements, such as only 4%, are responsible for over 80% of the total resource consumption, it suggests that optimizing these specific SQL statements has the potential to significantly enhance the performance of the entire database. The presented example displays a collection of 90 SQL statements obtained from Oracle SGA, with each statement listed in a chart based on its resource usage, arranged in descending order starting from the highest consumption SQL on the left-hand side. The analysis reveals that approximately 14.44% of the SQL statements account for 80% of the total elapsed time, while 21.11% of the SQL statements account for 80% of the total CPU time. This indicates that the distribution of the SQL workload aligns closely with the 80/20 rule. Therefore, there may not be a pressing need for SQL tuning as it is unlikely to result in significant performance improvements. Another example highlights a significant deviation from the 80/20 rule in the distribution of the SQL workload. The analysis shows that around 4 SQL statements are accountable for 80% of the total elapsed time, while another 4 SQL statements are responsible for 80% of the total CPU time, and 2 SQL statements contribute to 80% of the total disk reads. This indicates that by optimizing just these 4 SQL statements, it is possible to achieve a substantial improvement in the overall performance of the database. You can utilize Excel to conduct a simulation of the 80/20 rule analysis described above, providing a comprehensive overview of the distribution of the SQL workload. This approach facilitates a rapid evaluation of the overall health of the database's SQL performance, as well as the associated costs and benefits of optimizing high workload SQL statements. Furthermore, the SQL resource spectrum analysis is integrated into our Tosska DB Ace for Oracle software. Tosska DB Ace Enterprise for Oracle - Tosska Technologies Limited DBAO Inspect SQL - YouTube
