Database Performance Tuning and Query Optimization

The flashcards below were created by user FelipeJung on FreezingBlue Flashcards.

  1. A set of activities and procedures designed to reduce the response time of the database system.
    Database performance tuning
  2. (T/F) The goal of database performance is to execute queries as fast as possible.
  3. (T/F) Good database performance starts with good database design.
  4. Generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.
    Client side
  5. Client side
    SQL performance tuning
  6. The DBMS environment must be properly configured to respond to clients’ requests in the fastest way possible, while making optimum use of existing resources.
    Server side
  7. Server side
    DBMS performance tuning
  8. All data in a database are stored in
    data files
  9. The data files can automatically expand in predefined increments
  10. Logical groupingof several data files that store data with similar characteristics
    table space or file group
  11. Shared, reserved memory area that stores the most recently accesseddata blocks in RAM
    data cache or buffer cache
  12. Shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.
    SQL cache, or procedure cache
  13. (T/F) To work with the data, the DBMS must retrieve the data from permanent storage (data files in which the data are stored) and place it in RAM (data cache)
  14. A low-level (read or write) data access operation to and from computer devices, such as memory, hard disks, video, and printers.
    input/output (I/O) request
  15. Represented by the processes and structures used to manage a database.
    DBMS architecture
  16. Listens for clients’ requests and handles the processing of the SQL requests to other DBMS processes.
  17. The DBMS creates a user process to manage each client session.
  18. Process organizes the concurrent execution of SQL requests
  19. This process manages all locks placed on database objects, including disk pages.
    Lock manager
  20. Analyzes SQL queries and finds the most efficient way to access the data.
    Query Optimizer
  21. Refers to a number of measurements about database objects, such as number of processors used, processor speed, and temporary space available.
    database statistics
  22. DBMS processes a query in three phases:
    • Parsing
    • Execution
    • Fetching
  23. The DBMS parses the SQL query and chooses the most efficient access/execution plan.
  24. The DBMS executes the SQL query using the chosen execution plan.
  25. The DBMS fetches the data and sends the result set back to the client.
  26. Result of parsing a SQL statement.
    access plan
  27. A delay introduced in the processing of an I/O operation that causes the overall system to slow down.
    query processing bottleneck
  28. Five components that typically cause bottlenecks:
    • CPU
    • RAM
    • Hard disk
    • Network
    • Application code
  29. Crucial in speeding up data access because they facilitate searching, sorting, and using aggregate functionsand even join operations.
  30. Number of different values a column could possibly have.
    Data sparsity
  31. Indexes implement using:
    • Hash index
    • B-tree index
    • Bitmap index
  32. Uses preset rules and points to determine the best approach to execute a query.
    Rule-based optimizer
  33. Uses sophisticated algorithms based on the statistics about the objects being accessed to determine the best approach to execute a query.
    Cost-based optimizer
  34. Special instructions for the optimizer that are embedded inside the SQL command text.
    Optimizer hints
  35. Measure of how likely an index will be used in query processing.
    Index selectivity
  36. DBMS performance tuning at the server end focuses on setting the parameters used for:
    • Data cache
    • SQL cache
    • Sort cache
    • Optimizer mode
  37. Use ______ to provide balance between performance and fault tolerance.
    RAID (redundant array of independent disks)
  38. Number of rows, number of disk blocks used, row length, number of columns in each row, number of distinct values in each column, maximum value in each column, minimum value in each column, and columns that have indexes.
  39. Number and name of columns in the index key, number of key values in the index,number of distinct key values in the index key, histogram of key values in an index, and number of disk pages used by the index.
  40. Logical and physical disk block size, location and size of data files, and number ofextends per data fileIf
    Environment Resources
  41. Optimized query results are always the same as the original query
    Fully equivalent
  42. Optimized query will almost always execute faster than the original query.
    More efficient
  43. Breaking down the query into smaller units and transforming the original SQL query into a slightly different version of the original SQL code.
    SQL parsing phase
  44. In this phase, all I/O operations indicated in the access plan are executed.
    SQL Execution Phase
  45. After the parsing and execution phases are completed, all rows that match the specified conditions are retrieved,sorted, grouped, and/or aggregated.
    SQL Fetching Phase
  46. More efficient than a full table scan because the index data are preordered and the amount of data is usually much smaller.
    index scan
  47. Based on an ordered list of hash values.
    Hash index
  48. Ordered data structure organized as an upside-down tree.
    B-tree index
  49. Uses a bit array (0s and 1s) to represent the existence of a value or condition.
    Bitmap index
  50. The central activity during the parsing phase in query processing. In this phase, the DBMS must choose what indexes to use, how to perform join operations, which table to use first, and so on.
    Query optimization
  51. Evaluated from the client, the goal is to illustrate some common practices used to write efficient SQL code.
    SQL performance tuning
  52. Normally placed within the WHERE or HAVING clauses of a SQL statement. Also known as conditional criteria, a conditional expression restricts the output of a query to only the rows matching the conditional criteria.
    conditional expression
  53. DBMS performance tuning at the server end focuses on setting the parameters used for:
    • Data cache
    • SQL cache
    • Sort cache
    • Optimizer mode
  54. This is used to store the data dictionary tables.
    System table space
  55. This is used to store end-user data.
    User data table space
  56. This is used to store indexes
    Index table space
  57. This is used as a temporary storage area for merge, sort, or set aggregate operations.
    Temporary table space
  58. This is used for transaction-recovery purposes.
    Rollback segment table space
  59. A table that stores the end-user data and the index data in consecutive locations on permanent storage.
    index organized table or (clustered index table)
Card Set:
Database Performance Tuning and Query Optimization
2016-01-13 13:27:47

Chapter 11
Show Answers: