What is SQL performance tuning?<\/strong><\/p>\nSQL performance tuning describes a process \u2013 on the client side \u2013 that will generate an SQL query to return the correct answer in the least amount of time, using the minimum amount of resources at the server end.\n What is database performance tuning?<\/strong><\/p>\nDBMS performance tuning describes a process \u2013 on the server side \u2013 that will properly configure the DBMS environment to respond to clients\u2019 requests in the fastest way possible, while making optimum use of existing resources.\n What is the focus of most performance tuning activities, and why does that focus exist?<\/strong><\/p>\nMost performance-tuning activities focus on minimizing the number of I\/O operations, because the I\/O operations are much slower than reading data from the data cache.\n What are database statistics, and why are they important?<\/p>\nThe term\u00a0database statistics<\/i>\u00a0refers to a number of measurements gathered by the DBMS to describe a snapshot of the database objects\u2019 characteristics. The DBMS gathers statistics about objects such as tables, indexes, and available resources -- such as number of processors used, processor speed, temporary space available, and so on. Such statistics are used to make critical decisions about improving the query processing efficiency.\n How are database statistics obtained?<\/strong><\/p>\nDatabase statistics can be gathered manually by the DBA or automatically by the DBMS. For example, many DBMS vendors support the SQL\u2019s ANALYZE command to gather statistics. In addition, many vendors have their own routines to gather statistics. For example, IBM\u2019s DB2 uses the RUNSTATS procedure, while Microsoft\u2019s SQL Server uses the UPDATE STATISTICS procedure and provides the Auto-Update and Auto-Create Statistics options in its initialization parameters.\n What database statistics measurements are typical of tables, indexes, and resources?<\/strong><\/p>\nFor tables, typical measurements include the number of rows, the number of disk blocks used, row length, the number of columns in each row, the number of distinct values in each column, the maximum value in each column, the minimum value in each column, what columns have indexes, and so on.\n\nFor indexes, typical measurements include the number and name of columns in the index key, the number of key values in the index, the number of distinct key values in the index key, histogram of key values in an index, etc.\n\nFor resources, typical measurements include the logical and physical disk block size, the location and size of data files, the number of extends per data file, and so on.\n How is the processing of SQL DDL statements (such as CREATE TABLE) different from the processing required by DML statements?<\/strong><\/p>\nA DDL statement actually updates the data dictionary tables or system catalog, while a DML statement (SELECT, INSERT, UPDATE and DELETE) mostly manipulates end user data.\n What are the three phases that the DBMS processes queries in and what is accomplished in each phase?<\/strong><\/p>\nThe three phases are:\n If indexes are so important, why not index every column in every table?<\/strong><\/p>\nIndexing every column in every table will tax the DBMS too much in terms of index-maintenance processing, especially if the table has many attributes, many rows, and\/or requires many inserts, updates, and\/or deletes.\n\nOne measure to determine the need for an index is the data\u00a0sparsity<\/i>\u00a0of the column you want to index. Data sparsity refers to the number of different values a column could possibly have. For example, a STU_SEX column in a STUDENT table can have only two possible values, \u201cM\u201d or \u201cF\u201d; therefore this column is said to have low sparsity. In contrast, the STU_DOB column that stores the student date of birth can have many different date values; therefore, this column is said to have high sparsity. Knowing the sparsity helps you decide whether or not the use of an index is appropriate. For example, when you perform a search in a column with low sparsity, you are very likely to read a high percentage of the table rows anyway; therefore index processing may be unnecessary work.\n What is the difference between a rule-based optimizer and a cost-based optimizer?<\/strong><\/p>\nA rule-based optimizer uses a set of preset rules and points to determine the best approach to execute a query. The rules assign a \u201ccost\u201d to each SQL operation; the costs are then added to yield the cost of the execution plan.\n\nA cost-based optimizer uses sophisticated algorithms based on the statistics about the objects being accessed to determine the best approach to execute a query. In this case, the optimizer process adds up the processing cost, the I\/O costs and the resource costs (RAM and temporary space) to come up with the total cost of a given execution plan.\n What are optimizer hints and how are they used?<\/strong><\/p>\nHints are special instructions for the optimizer that are embedded inside the SQL command text. Although the optimizer generally performs very well under most circumstances, there are some circumstances in which the optimizer may not choose the best execution plan. Remember, the optimizer makes decisions based on the existing statistics. If the statistics are old, the optimizer may not do a good job in selecting the best execution plan. Even with the current statistics, the optimizer choice may not be the most efficient one. There are some occasions when the end-user would like to change the optimizer mode for the current SQL statement. In order to accomplish this task, you have to use hints.\n What are some general guidelines for creating and using indexes?<\/strong><\/p>\nCreate indexes for each single attribute used in a WHERE, HAVING, ORDER BY, or GROUP BY clause<\/i>. If you create indexes in all single attributes\u00a0used in search conditions<\/i>, the DBMS will access the table using an index scan, instead of a full table scan. For example, if you have an index for P_PRICE, the condition P_PRICE > 10.00 can be solved by accessing the index, instead of sequentially scanning all table rows and evaluating P_PRICE for each row. Indexes are also used in join expressions, such as in CUSTOMER.CUS_CODE = INVOICE.CUS_CODE.\n\nDo not use indexes in small tables or tables with low sparsity<\/i>. Remember, small tables and low sparsity tables are not the same thing. A search condition in a table with low sparsity may return a high percentage of table rows anyway, making the index operation too costly and making the full table scan a viable option. Using the same logic, do not create indexes for tables with few rows and few attributes\u2014unless you must ensure the existence of unique values in a column<\/i>.\n\nDeclare primary and foreign keys so the optimizer can use the indexes in join operations<\/i>. All natural joins and old-style joins will benefit if you declare primary keys and foreign keys because the optimizer will use the available indexes at join time. (The declaration of a PK or FK will automatically create an index for the declared column. Also, for the same reason, it is better to write joins using the SQL JOIN syntax. (See Chapter 8, \u201cAdvanced SQL.\u201d)\n\nDeclare indexes in join columns other than PK\/FK<\/i>. If you do join operations on columns other than the primary and foreign key, you may be better off declaring indexes in such columns.\n What factors should you keep in mind if you intend to write conditional expressions in SQL code?<\/strong><\/p>\nUse simple columns or literals as operands in a conditional expression\u2014avoid the use of conditional expressions with functions whenever possible<\/i>. Comparing the contents of a single column to a literal is faster than comparing to expressions.\n\nNumeric field comparisons are faster than character, date, and NULL comparisons<\/i>. In search conditions, comparing a numeric attribute to a numeric literal is faster than comparing a character attribute to a character literal. In general, numeric comparisons (integer, decimal) are handled faster by the CPU than character and date comparisons. Because indexes do not store references to null values, NULL conditions involve additional processing and therefore tend to be the slowest of all conditional operands.\n\nEquality comparisons are faster than inequality comparisons<\/i>. As a general rule, equality comparisons are processed faster than inequality comparisons. For example, P_PRICE = 10.00 is processed faster because the DBMS can do a direct search using the index in the column. If there are no exact matches, the condition is evaluated as false. However, if you use an inequality symbol (>, >=, <, <=) the DBMS must perform additional processing to complete the request. This is because there would almost always be more \u201cgreater than\u201d or \u201cless than\u201d values and perhaps only a few exactly \u201cequal\u201d values in the index. The slowest (with the exception of NULL) of all comparison operators is LIKE with wildcard symbols, such as in V_CONTACT LIKE \u201c%glo%\u201d. Also, using the \u201cnot equal\u201d symbol (<>) yields slower searches, especially if the sparsity of the data is high; that is, if there are many more different values than there are equal values.\n\nWhenever possible, transform conditional expressions to use literals<\/i>. For example, if your condition is P_PRICE -10 = 7, change it to read P_PRICE = 17. Also, if you have a composite condition such as:\n\nP_QOH < P_MIN AND P_MIN = P_REORDER AND P_QOH = 10\n\nchange it to read:\n\nP_QOH = 10 AND P_MIN = P_REORDER AND P_MIN > 10\n\nWhen using multiple conditional expressions, write the equality conditions first<\/i>. (Note that we did this in the previous example.) Remember, equality conditions are faster to process than inequality conditions. Although most RDBMSs will automatically do this for you, paying attention to this detail lightens the load for the query optimizer. (The optimizer won\u2019t have to do what you have already done.)\n\nIf you use multiple AND conditions, write the condition most likely to be false first<\/i>. If you use this technique, the DBMS will stop evaluating the rest of the conditions as soon as it finds a conditional expression that is evaluated to be false. Remember, for multiple AND conditions to be found true, all conditions must be evaluated as true. If one of the conditions evaluates to false, everything else is evaluated as false. Therefore, if you use this technique, the DBMS won\u2019t waste time unnecessarily evaluating additional conditions. Naturally, the use of this technique implies an implicit knowledge of the sparsity of the data set.\n\nWhenever possible, try to avoid the use of the NOT logical operator<\/i>. It is best to transform a SQL expression containing a NOT logical operator into an equivalent expression. For example:\n\nNOT (P_PRICE > 10.00) can be written as P_PRICE <= 10.00.\n\nAlso, NOT (EMP_SEX = 'M') can be written as EMP_SEX = 'F'.\n What recommendations would you make for managing the data files in a DBMS with many tables and indexes?<\/strong><\/p>\nFirst, create independent data files for the system, indexes and user data table spaces. Put the data files on separate disks or RAID volumes. This ensures that index operations will not conflict with end-user data or data dictionary table access operations.\n\nSecond, put high-usage end-user tables in their own table spaces. By doing this, the database minimizes conflicts with other tables and maximizes storage utilization.\n\nThird, evaluate the creation of indexes based on the access patterns. Identify common search criteria and isolate the most frequently used columns in search conditions. Create indexes on high usage columns with high sparsity.\n\nFourth, evaluate the usage of aggregate queries in your database. Identify columns used in aggregate functions and determine if the creation of indexes on such columns will improve response time.\n\nFinally, identify columns used in ORDER BY statements and make sure there are indexes on such columns.\n\n \t