Query Rewrite - PowerPoint PPT Presentation

1 / 30
About This Presentation
Title:

Query Rewrite

Description:

Medtronic is the world leader in medical technology providing ... Closing in on the culprit. Options including benefits and limitations. Chosen solution ... – PowerPoint PPT presentation

Number of Views:73
Avg rating:3.0/5.0
Slides: 31
Provided by: neyi
Category:

less

Transcript and Presenter's Notes

Title: Query Rewrite


1
Query Rewrite
Query Rewrite Ingrid Ney and Monica
Penshorn October 2006
2
  • Medtronic is the world leader in medical
    technology providing lifelong solutions for
    people with chronic disease. We offer products,
    therapies and services that enhance or extend the
    lives of millions of people. Each year, 5 million
    patients benefit from Medtronic's technology,
    used to treat conditions such as diabetes, heart
    disease, neurological disorders, and vascular
    illnesses.

3
Agenda
  • Data Warehouse performance problem
  • Closing in on the culprit
  • Options including benefits and limitations
  • Chosen solution
  • Details for implementing

4
  • And we also decided to help ourselves by
    developing our own IO monitoring tool

5
Input/Output Capacity
  • I/O Reading and writing data from the hardware
    disk drives
  • The Enterprise Data Warehouse was approaching
    the limit of Input/Output (I/O) capacity
  • Excessive I/O utilization was causing read and
    write bottlenecks throughout the system
  • We needed to reduce the I/O produced by the system

6
Problem Areas Identified
  • By interrogating the SQL area (vsql_plan and
    vsql_area) we found that one table was being
    used excessively
  • mast_order_detail_base
  • 20 of the total database I/O
  • The queries against this table were
    overwhelmingly retrieving order records with a
    status of Open

7
Reduce size of physical object
  • Goal Reduce the size of the physical table
    which should improve the performance of queries
    against it.
  • TABLE TABLE TABLE
  • 38GB 256MB
  • How

8
Option 1
  • Create a copy of the original table that only
    contains the Open orders
  • An additional load process would insert data for
    Open orders into the new table
  • End-User queries would need to be re-directed to
    access the new table
  • Limitations
  • A significant effort was required to modify
    reports and queries

9
Logical View
10
Option 2 Indexes
  • Create an index on order status and quantity
  • Let Oracle optimize the queries to use the
    indexes
  • Limitations
  • Oracle optimizer was not choosing the indexes
    because it favors hash join which uses full table
    scans. End-User reports and queries would need
    to be modified with HINTS to use the new indexes
  • Indexes did not reduce the IO and execution time
    as much as query rewrite

11
Logical View
12
Materialized View
  • A cross between a view and a table. A
    materialized view gives the traditional logical
    view its own physicality. The rows of the views
    query are stored in an object that occupies space
  • The materialized view can be refreshed from the
    base tables completely (full) or incrementally
    (fast) on a scheduled basis
  • Users can access a materialized view directly,
    but a better option is to leverage the query
    rewrite feature which will transparently point
    the query to the materialized view when the
    optimizer deems that a faster access path to the
    data.
  • Typical Uses Data Warehousing aggregates and
    summaries for better performance, eliminate cost
    of expensive joins, and to replicate data

13
Option 3 Desired Choice!
  • Create a materialized view that is refreshed
    from the original table with open orders. The
    materialized view is loaded using the Informatica
    load tool instead of Oracles built-in refresh
    process.
  • Let Oracle optimize the queries to select from
    the new materialized view using its Query Rewrite
    feature
  • Reasons for choosing
  • End-User reports and queries wouldnt need to be
    modified
  • Same load times for both Informatica and
    Materialized view
  • The team supporting as familiar with the
    Informatica process instead of Oracle
    Materialized View refresh

14
Logical View
15
Query Rewrite
  • Matching SQL Text
  • Full Text Match
  • Partial Text Match
  • Aggregate Computability
  • Selection Compatibility
  • Join Compatibility
  • Data Sufficiency
  • Grouping Compatibility
  • SQL Text matching and aggregate computability do
    not require
  • Primary key, foreign key or dimensions

16
The new table
CREATE TABLE MAST_OPEN_ORDERS ( ORDER_ID
VARCHAR2(40 CHAR) NOT NULL, SOURCE
VARCHAR2(5 BYTE) NOT NULL,
ORDER_NUMBER VARCHAR2(25 CHAR)
NOT NULL, ORDER_TYPE VARCHAR2(25
CHAR) NOT NULL, ORDER_LINE_NUM
NUMBER(12) NOT NULL, ORDER_SUFFIX
VARCHAR2(3 CHAR), COMPANY_CODE
VARCHAR2(25 CHAR) NOT NULL, TIME_KEY
NUMBER(9) NOT NULL,
MAST_PROD_KEY NUMBER(9) NOT
NULL, MAST_KIT_PROD_KEY NUMBER(9)
NOT NULL, PARTITION BY LIST (SOURCE) (
PARTITION MAST_OPEN_ORDERS_SRC1 VALUES ('CP',
'US', 'MFG1', 'AN', 'FE', 'SD', 'CA') ) CREATE
UNIQUE INDEX MAST_OPEN_ORDERS_PK ON
MAST_OPEN_ORDERS (ORDER_ID, SOURCE) LOCAL (
PARTITION MAST_OPEN_ORDERS_SRC1) ALTER TABLE
MAST_OPEN_ORDERS ADD ( CONSTRAINT
MAST_OPEN_ORDERS_PK PRIMARY KEY (ORDER_ID,
SOURCE) USING INDEX LOCAL)
17
The materialized view
CREATE MATERIALIZED VIEW MAST_OPEN_ORDERS ON
PREBUILT TABLE NEVER REFRESH ENABLE QUERY
REWRITE AS SELECT FROM MAST_ORDER_DETAIL_BASE W
HERE (ORDER_STATUS IN ('OP', 'BO') OR
QUANTITY_OPEN gt 0 OR QUANTITY_BACKORDERED gt 0)
18
Permissions Required
Database Grants GRANT global query rewrite TO
ltend usersgt GRANT query rewrite TO lttable
ownergt GRANT create materialized view TO lttable
ownergt Parameter Settings These changes
should also be applied in the pfile/spfile
ALTER sessionsystem SET query_rewrite_enabl
ed TRUE ALTER sessionsystem SET
query_rewrite_integrity STALE_TOLERATED
19
Data Load Process
  • One Time
  • Create the new table
  • Create the materialized view
  • Nightly
  • Alter materialized view X disable query rewrite
    -- to make sure that the batch job doesnt
    rewrite to the materialized view
  • Load the table
  • Alter materialized view X enable query rewrite
    --Must be done with a package if batch user does
    not own the table

20
Issues along the way
  • Truncating the table disables query rewrite
  • Disable query rewrite so the batch job sql is not
    rewritten to use the materialized view as the
    source
  • Enabling query rewrite can ONLY be done by the
    view owner or through a package

21
What should be tested?
  • Capture queries that are accessing the original
    table
  • Verify that the original queries are now
    accessing the smaller materialized view

22
Testing
  • Create the rewrite_table to verify your query can
    be re-written
  • ORACLE_HOME/rdbms/admin/utlxrw.sql
  • Configure database for autotrace
  • ORACLE_HOME/sqlplus/admin/plustrce.sql
  • Enable query rewrite in your session
  • alter session set query_rewrite_enabledtrue
  • alter session set query_rewrite_integritySTALE_T
    OLERATED

23
Query
  • SELECT order_number,
  • req_ship_date,
  • ship_to,
  • ship_to_name,
  • quantity_ordered,
  • FROM mast_order_detail,
  • mast_customer,
  • mast_product,
  • mast_geography
  • WHERE (mast_order_detail.mast_prod_key
    mast_product.mast_prod_key)
  • AND (mast_order_detail.geo_key
    mast_geography.geo_key)
  • AND (mast_order_detail.cust_key
    mast_customer.cust_key) AND (mast_geography.countr
    y IN ('USA')
  • AND mast_order_detail.order_status IN
    ('BO')
  • AND mast_product.business_unit_desc IN
    ('CORONARY VASCULAR')
  • )

24
Test 1
  • delete from rewrite_table
  • declare
  • query varchar2(1024)
  • 'SELECT / qREWRITE(mast_open_orders) /
  • sc_main.mast_order_detail.order_number,
  • sc_main.mast_order_detail.req_ship_date,
  • sc_main.mast_order_detail.quantity_ordered,
  • sc_main.mast_order_detail.order_type
  • FROM sc_main.mast_order_detail
  • WHERE sc_main.mast_order_detail.order_status IN
    (''BO'')
  • '
  • BEGIN
  • dbms_mview.explain_rewrite(query,'MAST_OPEN_ORDERS
    ')
  • END
  • /
  • select message from rewrite_table order by
    sequence
  • MESSAGE

25
Test 2
  • Set autotrace traceonly
  • Execute the query
  • Verify the explain plan is using the materialized
    view
  • --------------------------------------------------
    -------
  • 0 SELECT STATEMENT OptimizerCHOOSE
    (Cost6835 Card20132 Byte s2395708)
  • 1 0 HASH JOIN (Cost6835 Card20132
    Bytes2395708)
  • 2 1 HASH JOIN (Cost4573 Card20132
    Bytes1610560)
  • 3 2 TABLE ACCESS (FULL) OF
    'MAST_BASE_PRODUCT' (Cost4239 Card3957
    Bytes71226)
  • 4 2 HASH JOIN (Cost330 Card44018
    Bytes2729116)
  • 5 4 TABLE ACCESS (FULL) OF
    'MAST_GEOGRAPHY' (Cost4 Card 50
    Bytes850)
  • 6 4 TABLE ACCESS (FULL) OF
    'MAST_OPEN_ORDERS' (Cost325 Card96220Bytes432
    9900)
  • 7 1 TABLE ACCESS (FULL) OF
    'MAST_CUSTOMER' (Cost1325 Card1 660550
    Bytes64761450)

26
Review of our options
  • New Table
  • Materialized view with Query Rewrite
  • Create a new index

27
Success
  • Before
  • 7 minutes
  • 1.4 million I/O's
  • After implementing query rewrite
  • 32 seconds
  • 70,000 I/O's
  • Total System IO
  • 20 -gt 12

28
Decreased Reads
29
Decreased Query Time
30
  • Its been a pleasure sharing our presentation
    with you.
  • Ingrid and Monica
Write a Comment
User Comments (0)
About PowerShow.com