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