1 / 9

Database Performance Tuning Tutorial – DB Statistics

DB Statistics is one of the vital parameters that help us get an overview of database health. Today, we will look at how we can extract the statistics from various databases and use them.

optimizdbaca
Télécharger la présentation

Database Performance Tuning Tutorial – DB Statistics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Performance Tuning Tutorial DB Statistics DB Statistics is one of the vital parameters that help us get an overview of database health. Today, we will look at how we can extract the statistics from various databases and use them. 

  2. Statistics and Database Performance Tuning “ Does DB Statistics help in improving database performance?” The answer to that is it does help with improving the database performance but just not directly. When we are optimizing the database, we need to collect as much of its data as possible. This data has the capability to some critical questions we pose during the optimization process such as: • How many rows are present in a table? • How are the indexes created, and what are the relationships between the indexes in the DB?  • How many distinct values do a column hold, and more?  • Thus, ensuring that these statistics are being collected is imperative. 

  3. Composition of DB Statistics across Databases  Here let’s look at the contents of some DB Statistics.  1. Statistics for MS SQL Server: SQL Server creates and maintains a collection of statistics objects. Each of these objects is a histogram that provides information about column value distribution, distinct column values, etc. These objects can represent both single or multi-column information. However, for multi-column values, it also stores correlation information among columns. 

  4. 2. Stats stored in MySQL DB The MySQL Database stores all the table related statistics in the STATISTICS table. Similar to the SQL Server, the Statistics table holds information such as:  Cardinality – An estimated number of unique values within a given index. Collation – How the values in an index are stored. For example, are they in ascending or descending order? Sub Part – It holds the number of indexed characters in the case of partially indexed columns. For columns that are completely indexed, the value will be NULL. 

  5. 3. Oracle DB statistics Following are the types of statistics gathered: Table Statistics – We can extract the table metadata from these stats like the average row length, number of rows and blocks, etc.  Column Statistics – The values in each column are analyzed to build stats like the total number of distinct values in a given column, or the number of null values.  Index Statistics – We all know the significance of database indexes for fast query processing. Oracle DB stores stats such as the Index clustering factor, the number of index levels, and more. System Statistics – The system stats such as CPU utilization, I/O performance, and their utilization are gathered and stored as well. System statistics are important to understand the overall pressure on the database server and ensure that it the load doesn’t increase beyond its operating capacity.

  6. Fetching DBStatistics When it comes to statistics what is stored and when depends on the database itself. Each database also provides its own set of interfaces through queries or tools to fetch these statistics. In this section, let us look at how we can fetch statistics for various databases.

  7. 1. MySQL Database Global statuses are statistics about the database and the interactions with it. We can use the “ SHOW GLOBAL STATUS” statement to fetch various types of information. For example, if we were to fetch all the connection error statistics, we can use the following command:  SHOW GLOBAL STATUS LIKE ‘%Connection_errors%’

  8. 2. Oracle Database The Oracle database provides a package called “dbms_stats” which allows us to fetch and view the various stats it gathers.  We can write simple procedures to gather the stats we need. For example, here is a simple procedure to fetch all stats from a table: BEGIN dbms_stats.gather_table_stats(‘SH’,”TREASURY’); END; For the Oracle database, you start and stop the gathering workloads using appropriate procedures. Here’s an example on that:  dbms_stats.gather_system_stats(‘start’) dbms_stats.gather_system_stats(‘stop’)

  9. Conclusion Databases collect many different types of statistics from the time they are set up. From information about tables, the columns, values in each column, to even hardware usage on which the database server is running are captured. While some data is used regularly by the databases like the index  statistics by the query optimizer, other types of collected data are used by DBAs during database performance tuningoperations.  Looking for Experts In Database Performance Optimization Visit at https://optimizdba.com

More Related