Week 6 Assignment HSIN

 

Using a word document with a cover sheet –

Don't use plagiarized sources. Get Your Custom Essay on
Week 6 Assignment HSIN
Just from $13/Page
Order Essay

Complete:

1- Chapter 14 Review Questions 1-11 located at the end of your Database Systems Chapter 11 book. Please use complete sentences and proper grammar. 

1- Chapter 15 Review Questions 1-25 located at the end of your Database Systems Chapter 13 book. Please use complete sentences and proper grammar.

Database Systems: Design,
Implementation, and

Management
Eighth Edition

Chapter 11
Database Performance Tuning and

Query Optimization

Database Systems, 8th Edition 2

  • Objectives
  • • In this chapter, you will learn:
    – Basic database performance-tuning concepts
    – How a DBMS processes SQL queries
    – About the importance of indexes in query processing
    – About the types of decisions the query optimizer has

    to make
    – Some common practices used to write efficient SQL

    code
    – How to formulate queries and tune the DBMS for

    optimal performance
    – Performance tuning in SQL Server 2005

    Database Systems, 8th Edition 3

  • 11.1 Database Performance-Tuning Concepts
  • • Goal of database performance is to execute
    queries as fast as possible

    • Database performance tuning
    – Set of activities and procedures designed to

    reduce response time of database system

    • All factors must operate at optimum level with
    minimal bottlenecks

    • Good database performance starts with
    good database design

    Database Systems, 8th Edition 4

    Database Systems, 8th Edition 5

  • Performance Tuning: Client and Server
  • • Client side
    – Generate SQL query that returns correct answer

    in least amount of time
    • Using minimum amount of resources at server

    – SQL performance tuning

    • Server side
    – DBMS environment configured to respond to

    clients’ requests as fast as possible
    • Optimum use of existing resources

    – DBMS performance tuning

    Database Systems, 8th Edition 6

  • DBMS Architecture
  • • All data in database are stored in data files
    • Data files

    – Automatically expand in predefined increments
    known as extends

    – Grouped in file groups or table spaces
    • Table space or file group:

    – Logical grouping of several data files that store
    data with similar characteristics

    Database Systems, 8th Edition 7

    Basic DBMS architecture

    Database Systems, 8th Edition 8

  • DBMS Architecture (continued)
  • • Data cache or buffer cache: shared, reserved
    memory area
    – Stores most recently accessed data blocks in RAM

    • SQL cache or procedure cache: stores most
    recently executed SQL statements
    – Also PL/SQL procedures, including triggers and

    functions

    • DBMS retrieves data from permanent storage and
    places it in RAM

    Database Systems, 8th Edition 9

    DBMS Architecture (continued)

    • Input/output request: low-level data access
    operation to/from computer devices, such as
    memory, hard disks, videos, and printers

    • Data cache is faster than data in data files
    – DBMS does not wait for hard disk to retrieve data

    • Majority of performance-tuning activities focus on
    minimizing I/O operations

    • Typical DBMS processes:
    – Listener, User, Scheduler, Lock manager, Optimizer

    Database Systems, 8th Edition 10

  • Database Statistics
  • • Measurements about database objects and available
    resources
    – Tables, Indexes, Number of processors used,

    Processor speed, Temporary space available
    • Make critical decisions about improving query

    processing efficiency
    • Can be gathered manually by DBA or automatically by

    DBMS
    – UPDATE STATISTICS table_name [index_name]
    – Auto-Update and Auto-Create Statistics option

    • 資料庫屬性 -> 自動更新統計資料
    • 資料庫屬性 -> 自動建立統計資料

    Database Systems, 8th Edition 11

    Database Systems, 8th Edition 12

    Ch08: dbcc show_statistics (customer, PK__CUSTOMER__24927208 )

    Ch08: dbcc show_statistics (customer, CUS_UI1)

    補充 SQL Server 2005

    Database Systems, 8th Edition 13

  • 11.2 Query Processing
  • • DBMS processes queries in three phases
    – Parsing

    • DBMS parses the query and chooses the most
    efficient access/

    execution plan

    – Execution
    • DBMS executes the query using chosen

    execution plan

    – Fetching
    • DBMS fetches the data and sends the result back

    to the client

    Database Systems, 8th Edition 14
    Query Processing

    Database Systems, 8th Edition 15

  • SQL Parsing Phase
  • • Break down query into smaller units
    • Transform original SQL query into slightly

    different version of original SQL code
    – Fully equivalent

    • Optimized query results are always the same as
    original query

    – More efficient
    • Optimized query will almost always execute faster

    than original query

    Database Systems, 8th Edition 16

  • SQL Parsing Phase (continued)
  • • Query optimizer analyzes SQL query and finds most

    efficient way to access data
    – Validated for syntax compliance

    – Validated against data dictionary
    • Tables, column names are correct
    • User has proper access rights

    – Analyzed and decomposed into more atomic components

    – Optimized through transforming into a fully equivalent but
    more efficient SQL query

    – Prepared for execution by determining the execution or
    access plan

    Database Systems, 8th Edition 17

    SQL Parsing Phase (continued)

    • Access plans are DBMS-specific
    – Translate client’s SQL query into series of

    complex I/O operations
    – Required to read the data from the physical data

    files and generate result set
    • DBMS checks if access plan already exists for

    query in SQL cache
    • DBMS reuses the access plan to save time
    • If not, optimizer evaluates various plans

    – Chosen plan placed in SQL cache

    Database Systems, 8th Edition 18

    Database Systems, 8th Edition 19

  • SQL Execution and Fetching Phase
  • • All I/O operations indicated in access plan are
    executed
    – Locks acquired
    – Data retrieved and placed in data cache
    – Transaction management commands processed

    • Rows of resulting query result set are returned to
    client

    • DBMS may use temporary table space to store
    temporary data
    – The server may send only the first 100 rows of 9000 rows

    Database Systems, 8th Edition 20

  • Query Processing Bottlenecks
  • • Delay introduced in the processing of an I/O
    operation that slows the system
    – CPU

    – RAM

    – Hard disk

    – Network

    – Application code

    Database Systems, 8th Edition 21

    SQL 敘述
    輸入完成
    後先不要
    執行查
    詢 , 請按
    下工具列
    的顯示估
    計執行計

    劃鈕

    Database Systems, 8th Edition 22

  • 11.3
  • Indexes and Query Optimization
  • • Indexes
    – Crucial in speeding up data access

    – Facilitate searching, sorting, and using
    aggregate functions as well as join operations

    – Ordered set of values that contains index key
    and pointers

    • More efficient to use index to access table than
    to scan all rows in table sequentially

    Database Systems, 8th Edition 23

    Indexes and Query Optimization
    • Data sparsity: number of different values a column

    could possibly have
    • Indexes implemented using: ( 課本 p. 453)

    – Hash indexes

    – B-tree indexes: most common index type. Used in tables in
    which column values repeat a small number of times. The
    leaves contain pointers to records It is self-balanced.

    Bitmap indexes

    : 0/1

    • DBMSs determine best type of index to use
    – Ex: CUST_LNAME with B-tree and REGION_CODE with

    Bitmap indexes

    Database Systems, 8th Edition 24B-tree and bitmap index representation

    25

    Index Representation for the
    CUSTOMER table

    SELECT CUS_NAME
    FROM CUSTOMER
    WHERE CUS_STATE=‘FL’
    Requires only 5 accesses to STATE_INDEX,
    5 accesses to CUSTOMER

    Database Systems, 8th Edition 26

  • 11.4 Optimizer Choices
  • • Rule-based optimizer
    – Preset rules and points

    – Rules assign a fixed cost to each operation

    • Cost-based optimizer
    – Algorithms based on statistics about objects

    being accessed

    – Adds up processing cost, I/O costs, resource
    costs to derive total cost

  • Example
  • Database Systems, 8th Edition 27

    SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME, V_STATE
    FROM PRODUCT P, VENDOR V
    WHERE P.V_CODE=V.V_CODE
    AND V.V_STATE=‘FL’;

    • With the following database statistics:
    – The PRODUCT table has 7000 rows
    – The VENDOR table has 300 rows
    – 10 vendors come from Florida
    – 1000 products come from vendors in Florida

    Database Systems, 8th Edition 28

    Example

    Database Systems, 8th Edition 29

    • Assume the PRODUCT table has the index
    PQOH_NDX in the P_QOH attribute

    SELECT MIN(P_QOH) FROM PRODUCT

    could be resolved by reading only the first entry in
    the PQOH_NDX index

    Database Systems, 8th Edition 30

  • Using Hints to Affect Optimizer Choices
  • • Optimizer might not choose best plan
    • Makes decisions based on existing statistics

    – Statistics may be old
    – Might choose less efficient decisions

    • Optimizer hints: special instructions for the
    optimizer embedded in the SQL command text

    Database Systems, 8th Edition 31

    Oracle 版本

    Database Systems, 8th Edition 32

    MS SQL Server 的語法請參考:

    http://msdn.microsoft.com/en-us/library/ms187713.aspx

    SQL Server Query Hints Example

    select o.customerid,companyname
    from orders as o inner MERGE join customers as c
    on o.customerid = c.customerid

    select o.customerid,companyname
    from orders as o inner HASH join customers as c
    on o.customerid = c.customerid

    select o.customerid,companyname
    from orders as o inner LOOP join customers as c
    on o.customerid = c.customerid

    select city, count(*)
    from customers
    group by city
    OPTION (HASH GROUP)

    • Database Systems: Design, Implementation, and Management Eighth Edition
    • Objectives
      11.1 Database Performance-Tuning Concepts

    • PowerPoint Presentation
    • Performance Tuning: Client and Server
      DBMS Architecture

    • Slide 7
    • DBMS Architecture (continued)

    • Slide 9
    • Database Statistics

    • Slide 11
    • Slide 12
    • 11.2 Query Processing

    • Slide 14
    • SQL Parsing Phase
      SQL Parsing Phase (continued)

    • Slide 17
    • Slide 18
    • SQL Execution and Fetching Phase
      Query Processing Bottlenecks

    • Slide 21
    • 11.3 Indexes and Query Optimization
      Indexes and Query Optimization

    • Slide 24
    • Slide 25
    • 11.4 Optimizer Choices
      Example

    • Slide 28
    • Slide 29
    • Using Hints to Affect Optimizer Choices

    • Slide 31
    • Slide 32

    Calculate the price of your order

    550 words
    We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
    Total price:
    $26
    The price is based on these factors:
    Academic level
    Number of pages
    Urgency
    Basic features
    • Free title page and bibliography
    • Unlimited revisions
    • Plagiarism-free guarantee
    • Money-back guarantee
    • 24/7 support
    On-demand options
    • Writer’s samples
    • Part-by-part delivery
    • Overnight delivery
    • Copies of used sources
    • Expert Proofreading
    Paper format
    • 275 words per page
    • 12 pt Arial/Times New Roman
    • Double line spacing
    • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

    Our guarantees

    Delivering a high-quality product at a reasonable price is not enough anymore.
    That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

    Money-back guarantee

    You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

    Read more

    Zero-plagiarism guarantee

    Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

    Read more

    Free-revision policy

    Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

    Read more

    Confidentiality Guarantee

    Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

    Read more

    Fair-cooperation guarantee

    By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

    Read more

    24/7 Support

    Our specialists are always online to help you! We are available 24/7 via live chat, WhatsApp, and phone to answer questions, correct mistakes, or just address your academic fears.

    See our T&Cs
    Live Chat+1(978) 822-0999EmailWhatsApp

    Order your essay today and save 30% with the discount code ESSAYHELP