1 / 55

Oracle MATERIALZIED VIEWS A Performance Tuning Gem

Oracle MATERIALZIED VIEWS A Performance Tuning Gem. Agenda. A Game Changer What is a Materialized View Purposes of Materialized Views Enhancing SQL Performance Query Rewrite Real World Example Advanced Query Rewrite Concepts Q/A. A Game Changer.

malha
Download Presentation

Oracle MATERIALZIED VIEWS A Performance Tuning Gem

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle MATERIALZIED VIEWSA Performance Tuning Gem

  2. Agenda • A Game Changer • What is a Materialized View • Purposes of Materialized Views • Enhancing SQL Performance • Query Rewrite • Real World Example • Advanced Query Rewrite Concepts • Q/A

  3. A Game Changer • Provide SQL Tuning Services for OLTP and DSS (Data Warehouse) • Most Tuning Methods Consist of • SQL Redevelopment • Converting Correlated Sub Queries to Simply Sub Queries • Partitioning/Compression • Index Development • SQL Baselines • Data warehouse tuning cases started to pop up where none of the conventional methods would provide the solution

  4. A Game Changer • Data warehouse performance goals • The Report that Started it All • Local Fund Budget Report • Heavily Utilized Report Running almost 4 minutes • Conventional methods would not work

  5. A Game Changer • Local Fund Budget Report was tuned to return results in about 4 seconds, down from almost 4 minutes • How did it happen? • The SQL in the report was not touched • New indexes were not added • The existing table structure behind the report remained the same SOLUTION: Query Rewrite using Materialized Views

  6. What is a Materialized View First, let’s define a View • A View stores a SELECT statement and executes the stored SELECT statement when referenced via another SQL statement. View: HR_DEPT_VW SELECT DEPTID ,MANAGER_ID FROM DEPT_TBL WHERE CATEGORY = ‘HR’ Table: DEPT_TBL DEPTID MANAGER_ID CATEGORY SELECT COUNT(1) FROM HR_DEPT_VW

  7. What is a Materialized View Now, let’s define a Materialized View • Like a View, a Materialized View stores a SELECT statement, in addition the results of the SELECT are stored in a table. Table: DEPT_TBL DEPTID MANAGER_ID CATEGORY MView: HR_DEPT_MV SELECT DEPTID ,MANAGER_ID FROM DEPT_TBL WHERE CATEGORY = ‘HR’ MView Table: HR_DEPT_MV DEPTID MANAGER_ID

  8. What is a Materialized View Now, let’s define a Materialized View • Like a View, a Materialized View stores a SELECT statement, in addition the results of the SELECT are stored in a table. Table: DEPT_TBL DEPTID MANAGER_ID CATEGORY MView: HR_DEPT_MV SELECT DEPTID ,MANAGER_ID FROM DEPT_TBL WHERE CATEGORY = ‘HR’ SELECT COUNT(1) FROM HR_DEPT_MV MView Table: HR_DEPT_MV DEPTID MANAGER_ID

  9. Purposes of Materialized Views • Replication • Easily replicate tables from one database to another • Enhancing SQL Performance • Facilitate Incremental Warehouse Loading Operations (ETL) • Use Materialized View Logs to track data changes in tables, which will be used to identify records needing to be updated, inserted, and deleted in the data warehouse.

  10. Enhancing SQL Performance • How do Materialized Views help SQL Perform Better? • Results of the associated query are stored in a table • Data can be pre-aggregated and pre-joined, cutting down the resources and time needed to execute the query TABLE: GL_ENTRIES FISCAL_PERIOD_SID ENTRY_AMT 38,020,294 rows MV: MV_GL_ENTRIES SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM MV_GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows 0:0.03 Min 95 rows 2:40.03 Min

  11. Enhancing SQL Performance • There are two ways to utilize MVs to enhance SQL performance • Directly referencing the MV as in the example below • Using the Oracle Query Rewrite functionality TABLE: GL_ENTRIES FISCAL_PERIOD_SID ENTRY_AMT 38,020,294 rows MV: MV_GL_ENTRIES SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM MV_GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows 0:0.03 Min 95 rows 2:40.03 Min

  12. Query Rewrite What is Query Rewrite? • Query rewrite transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. • The transformation is transparent to the end user or application, requiring no intervention and no reference to the materialized view in the SQL statement. • Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

  13. Query Rewrite Example of Query Rewrite TABLE: GL_ENTRIES FISCAL_PERIOD_SID ENTRY_AMT 38,020,294 rows Generate Plan SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID MV: MV_GL_ENTRIES SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows Winner Generate Plan

  14. Query Rewrite Example of Query Rewrite SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID

  15. Query Rewrite Materialized View DDL REFRESH COMPLETE Tells Oracle that when this MV is refreshed all data will first be removed and then a complete reload will occur. Most MVs created for Query Rewrite will be a complete refresh. BUILD IMMEDIATE Tells Oracle to run the SELECT statement and load the MV table right away. The Build can also be deferred. CREATE MATERIALIZED VIEW MV_GL_ENTRIES BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT FISCAL_PERIOD_SID FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) ENTRY_AMT FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID ENABLE QUERY REWRITE Tells Oracle that this MV can be considered for Query Rewrite. If this Query Rewrite is Disabled then Oracle will not consider this MV as an option for Query Rewrite.

  16. Query Rewrite The STALENESS Factor • A MV can be in one of several STALENESS states • FRESH – The data in the MV accurately reflects the data in the base tables • STALE – Data in one or more of the base tables has changed and the MV may no longer accurately reflect the data in the base tables • UNUSABLE – The MV is currently being refreshed • NEEDS_COMPILE – The table structure of one or more of the base tables has changed and the MV needs to be compiled • UNKNOWN – One of two situations cause an UNKNOWN status • An “ALTER MATERIALIZED VIEW….CONSIDER FRESH” has been run • The MV is based on a PREBUILT TABLE (Advanced Topic) A Materialized View will only be considered for Query Rewrite if it’s in one of the two STALENESS states OR STALENESS = STALE and the following database parameter is set query_rewrite_integrity=stale_tolerated

  17. Query Rewrite UPDATE GL_ENTRIES SET ENTRY_AMT = 3 WHERE FISCAL_PERIOD_SID = 12 The STALENESS Factor Winner TABLE: GL_ENTRIES FISCAL_PERIOD_SID ENTRY_AMT 38,020,294 rows Generate Plan SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID MV: MV_GL_ENTRIES SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows Winner STALE Generate Plan

  18. Query Rewrite UPDATE GL_ENTRIES SET ENTRY_AMT = 3 WHERE FISCAL_PERIOD_SID = 12 The STALENESS Factor Winner TABLE: GL_ENTRIES FISCAL_PERIOD_SID ENTRY_AMT 38,020,294 rows Generate Plan BEGIN DBMS_MVIEW.REFRESH (‘MV_GL_ENTRIES’); END; SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID MV: MV_GL_ENTRIES SELECT FISCAL_PERIOD_SID ,SUM(ENTRY_AMT) FROM GL_ENTRIES GROUP BY FISCAL_PERIOD_SID 95 rows Winner FRESH STALE Generate Plan

  19. Query Rewrite The STALENESS Factor • Query Rewrite works best for Materialized Views that are based on tables that remain static throughout the day • Transactional or OLTP databases typically do not work well due to the constant updating of data that will require constant refreshing of the MV to maintain a FRESH state. • Query Rewrite works well with data warehouses

  20. A Real World Example • First, a little lingo Subject Area Dimension Dimension Dimensions have a one-to-many relationship with the Fact Star Schema Fact Dimension Dimension Basically, just a bunch of tables with data

  21. A Real World Example Subject Area Dimension Dimension Fact Dimension Dimension

  22. A Real World Example Local Fund Budget Report

  23. A Real World Example Cost 207,000 3:53 Minutes Generate Plan

  24. A Real World Example CREATE MATERIALZIED VIEW KUALI_ADMIN.KF_MVA_BUDGET_BALANCE … SELECT BUD.FISCAL_PERIOD_SID … , SUM(BUD.UA_BUD_CY_ANN_EXP) FROM SYSADM.PS_UA_EMPL_JOB_DTL EMP , SYSADM.PS_UA_BUD_ACNT_TYP ACNT , KUALI_ADMIN.KF_D_ORG ORG , KUALI_ADMIN.KF_D_FISCAL_PERIOD FP , KUALI_ADMIN.KF_D_PROJECT PROJ , KUALI_ADMIN.KF_D_OBJECT_CODE OC , KUALI_ADMIN.KF_D_ACCOUNT ACCT , KUALI_ADMIN.KF_F_BUDGET_BALANCE BUD WHERE BUD.ACCOUNT_SID = ACCT.ACCOUNT_SID AND BUD.OBJECT_CODE_SID = OC.OBJECT_CODE_SID AND BUD.PROJECT_SID = PROJ.PROJECT_SID AND BUD.FISCAL_PERIOD_SID = FP.FISCAL_PERIOD_SID AND BUD.UA_BUD_ACCT_DEPT_ORG_SID = ORG.ORG_SID AND BUD.BUD_ACCT_TYP_SID = ACNT.BUD_ACCT_TYP_SID (+) AND BUD.EMPL_JOB_DETAIL_SID = EMP.EMPL_JOB_DTL_SID (+) GROUP BY BUD.FISCAL_PERIOD_SID … Pre-Aggregate Let’s Create an Materialized View Materialized View Create Materialized View Pre-Join

  25. A Real World Example Cost 207,000 3:53 Minutes Generate Plan Materialized View Winner Generate Plan Cost 483 4 Seconds

  26. A Real World Example Cost 207,000 3:53 Minutes Generate Plan CBO Cost 207,000 vs 483 MV used 98.5% Less Temp Space for Sorting Materialized View Expensive Joins Winner Explain Plan without MV Generate Plan Cost 483 3 Seconds Explain Plan with MV NO Joins

  27. A Real World Example • Another alternative would have been to create a new, summarized Subject Area • Benefits of Materialized Views over building additional summarized Subject Areas • Materialized Views are more agile and quicker to implement • Existing reports need not be updated to point to a new set of tables • Report writers will not have to be concerned with choosing the best Subject Area from which to create reports

  28. Advanced Concepts

  29. In the Beginning… • Due to differences in tables and joins in each report a different MV was created • Every time a change was made to the report the MV had to be updated • Became a maintenance nightmare • Something had to change or MVs were not going to be a viable option Budget Report 1 Budget MV1 Budget MV2 Budget Report 2 Budget MV3 Budget Report 3 Budget MV4

  30. A New Methodology… Budget Balances Budget Report 1 Budget Balances MV1 Budget Report 2 Budget Report 3 GL Entries GL Entries Report 1 GL Entries MV1 GL Entries Report 2 GL Entries Report 3 Labor Entries Labor Entries Report 1 Labor Entries MV1 Labor Entries Report 2 Labor Entries Report 3

  31. How did we do it… • Must Know Advanced Concepts • Query Delta Joins • Materialized View Delta Joins • Nested Materialized Views

  32. Query Delta Joins • A query delta join is a join that appears in the query but not in the materialized view. • A query delta join will also occur if a field from a dimension is included in the query but is not included in the MV. • In order for the join to work, the materialized view must contain the joining key.

  33. Example Query Delta Join Example Materialized View MV Includes FK columns to the Object Code and Account Dimensions MV Includes Budget Balance Fact joined to Account Dimension

  34. Example Query Delta Join Example Query #1 Query ERD MV ERD Consists of columns, tables, and joins that all exist in the MV • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN)

  35. Example Query Delta Join Example Query #2 Query ERD MV ERD This query adds the ACCOUNT_NBR field which does not exist in our test MV • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD • ACCOUNT_NBR • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) The Delta Join is made possible by including the joining key from the FACT in the MV

  36. Example Query Delta Join Example Query #3 MV ERD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD OBJECT_CD and the Object Code Dimension was added to the query • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) Query ERD • Object Code • OBJECT_CODE_SID • OBJECT_CD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD The Delta Join is made possible by including the joining key from the FACT in the MV • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN)

  37. Materialized View Delta Joins • A materialized view delta join is a join that appears in the materialized view but not the query. • All delta joins in a materialized view are required to be lossless with respect to the result of common joins. (We’ll get to this concept a little later.)

  38. Example Materialized View Delta Join Query Example MV ERD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD This query removes any reference to the Account Dimension, which exists in the MV definition. • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) Query ERD • Budget Balances • SUM(UA_BUD_CY_ANN) MV Delta Join is made possible by guaranteeing a “lossless” join between the FACT and DIMENSION

  39. Combined MV and Query Delta Join Query Example MV ERD • Account • ACCOUNT_SID • ACCOUNT_BUDGET_SHELL_CD • Budget Balances • OBJECT_CODE_SID • ACCOUNT_SID • SUM(UA_BUD_CY_ANN) • Removes the Account Dimension (MV Delta Join) • Adds the Object Code Dimension (Query Delta Join) Query ERD • Object Code • OBJECT_CODE_SID • OBJECT_CD • Budget Balances • OBJECT_CODE_SID • SUM(UA_BUD_CY_ANN)

  40. How are Delta Joins Possible? • Table Relationships Must be Configured Properly • Lossless Joins • Non-Duplicating Joins • Dimension Join Keys must be included in the SELECT of the MV definition

  41. Lossless Joins • A lossless join guarantees that the result of common joins is not restricted • Budget Balances • ACCOUNT_SID • COUNT(1) • Account • ACCOUNT_SID ACCOUNT_SID = ACCOUNT_SID Dimension Fact Lossless Join No rows were lost when Budget Balance was joined to Accounts

  42. Lossless Joins Not a Lossless Join Not a Lossless Join • A lossless join guarantees that the result of common joins is not restricted • Budget Balances • ACCOUNT_SID • COUNT(1) • Employee Detail • EMPL_JOB_DETAIL_SID EMPL_JOB_DETAIL_SID = EMPL_JOB_DTL_SID Dimension Fact Rows were lost when Budget Balance was joined to Employee Detail

  43. Lossless Joins Not a Lossless Join • A lossless join guarantees that the result of common joins is not restricted • Budget Balances • ACCOUNT_SID • COUNT(1) • Employee Detail • EMPL_JOB_DETAIL_SID EMPL_JOB_DETAIL_SID = EMPL_JOB_DTL_SID (+) EMPL_JOB_DETAIL_SID = EMPL_JOB_DTL_SID Dimension Fact Lossless Join Outer Joins will guarantee a Lossless Join

  44. Non-Duplicating Join • A non-duplicating join guarantees that the result of common joins will not cause duplicates • There must be a many-to-one relationship between the joins from the Fact to the Dimensions Many-to-Many Many-to-One

  45. How to let Oracle Know • To let Oracle know of the Lossless and Non-Duplicating joins the following must be configured on the tables in the subject area: • Primary Key constraints must be created on all Dimensions • Foreign Key constraints must be create on Fact join columns • Join columns on the Fact must be set to NOT NULL unless that column will be outer joined to the Dimension

  46. Example Primary Key Constraint • PK and FK constraints can cause havoc and slowness in a data warehouse • Oracle has provided a way to create the constraints while avoiding the baggage DISABLE – Does not enforce the constraint on rows created after the constraint is created. Good for ETL. NOVALIDATE – Does not enforce the constraint on rows that exist in the table when the constraint is created.

  47. Example Primary Key Constraint • If the PK and FK constraints are in DISABLE and NOVALIDATE mode there is one more step that must be taken in order to let Oracle know we can RELY on these constraints RELY – Tells Oracle that regardless of the state of the constraint, Oracle can assume the constraint is valid. Without this MV Rewrite functionality will not work properly if constraints are not enabled.

  48. Include Join Columns in MV Subject Area Dimension Dimension Fact Dimension Dimension

  49. Nested Materialized Views Budget Balances Budget Report 1 4 Minute Refresh Budget Balances Nested MV1 Budget Report 2 Budget Report 3 60 Minute Refresh Budget Balances MV1 Budget Report 4 Budget Report 5 2 Minute Refresh Budget Balances Nested MV2 Budget Report 6 Budget Report 7

More Related