Oracle Performance Tuning Tips- 2 Important Concepts

1. Oracle Performance Oracle Performance Tuning Tips: 2 Important Concepts Tuning Tips: 2 Important Concepts Database professionals encounter certain complicated issues when they begin performance tuning early on in their profession. These obstacles call for in-depth knowledge of the concepts to help them identify their causes. In this blog, we will be going through two essential concepts for a better understanding of Oracle performance tuning tips. These are Selectivity and Cardinality - two important concepts in SQL, each with applications. Understanding Selectivity & Cardinality Performance Query their unique before Tune You SQL First, let us talk about selectivity - what it means along with an example that accurately explains its function. Selectivity: It is a way to measure the uniqueness since it signifies the section of rows covered by an operation. Its value ranges from 0 to 1. Consider an example - suppose a statement fetched 10 rows, after which you placed a condition as a filter (such as “WHERE”) that returned one final result. In this case, your selectivity is 0.1 or 10% (1\10). Here’s another example - assuming a query returns a hundred rows, and still returns a hundred rows once you place a filter, your selectivity becomes 1 (100\100) or 100%. This value of selectivity is known as bad selectivity since the filter seems futile, resulting in the same number of records being fetched and subsequently, the need to performance tune SQL query to improve performance. V i s i t : w w w . t o s s k a . c o m

2. Oracle Performance Oracle Performance Tuning Tips: 2 Important Concepts Tuning Tips: 2 Important Concepts Some points to keep in mind regarding selectivity: ●If a column is extremely selective, i.e if it fetches far fewer duplicate rows, it is referred to as Good selectivity. ●On the other hand, selectivity is poor or Bad if the particular column is minimally selective, i.e if it fetches an extremely large number of rows or all of them. ●Perhaps the best example of bad selectivity would be the query SELECT * FROM <enter table name here> without a single filter. The selectivity in this instance is automatically 1 because the statement will fetch all the rows. ●You can turn bad selectivity into good through composite indexes. The use of multiple columns boosts index selectivity since it increases the uniqueness of the index. Cardinality: This term addresses the number of rows fetched by an operation. Selectivity and cardinality are related in the following manner: cardinality is equal to the value of selectivity multiplied by the number of input rows. So, suppose you have a query that returns two hundred records from the database and the conditions applied to V i s i t : w w w . t o s s k a . c o m

3. Oracle Performance Oracle Performance Tuning Tips: 2 Important Concepts Tuning Tips: 2 Important Concepts the statement after that, filtered the number of rows to fifty. The selectivity is, therefore, 0.25, whereas the cardinality becomes 50 (200x0.25). The Oracle optimizer is unable, at times, to determine the number of rows that will be fetched by a particular return due to certain reasons, such as incomplete table stats. It may keep Oracle from calculating the correct cost of a query plan, resulting in a subpar plan being picked instead. That’s when Oracle performance tuning tips and tools are needed to boost query performance. Therefore, mistakes in cardinality evaluation can lead to slow performing statements that need to be improved. Take a look at the following examples for clearer understanding - 1. Suppose a table named Employees contains ten records and you need to determine the biggest E_num in the table. The query for this is as follows - SELECT Max(E_num) from Employees; The selectivity and cardinality will be calculated as - selectivity = accessed rows/total rows = 10/10 = 1 (all the rows were accessed) cardinality = accessed rows = 10 V i s i t : w w w . t o s s k a . c o m

4. Oracle Performance Oracle Performance Tuning Tips: 2 Important Concepts Tuning Tips: 2 Important Concepts 2. Now, if we place a filter on the column labelled Sur_Name with the following query to help performance tune SQL query - SELECT Max(E_num) from Employees WHERE Sur_Name='JONES'; So, if the table contains two employees with ‘JONES’ as the last name, then selectivity = 2/10 = 0.2 (accessed rows = 20%) cardinality = 2 (2 rows were accessed) V i s i t : w w w . t o s s k a . c o m