Why is my MV Query Rewrite not happening?

You have designed the model and implemented the dimensions and fact tables. Data is clean. You have implemented all the required PK-FK relationships, created required indexes, gathered statistics and built the required materialized views.

The analytic design team have completed report and issues SQL query! Oops...
You expected the response time of < 10 seconds, instead the front end is clocking for over 4 minutes, now.

You quickly investigate the active sessions in Oracle and see the BI query is performing FTS on your largest FACT table. Why is it not using the Materialized View you so painstakingly built? Why is Query Rewrite not happening?

DBMS_MVIEW.EXPLAIN_REWRITE comes to rescue!

Conditions that must be satisfied for MV Query Rewrite to occur

The conditions under which Oracle will Rewrite queries using MV are well documented here.

In summary following techniques are used for MV query rewrite.

• Text Match Rewrite
• Join Back
• Aggregate Computability
• Aggregate Rollup
• Rollup Using a Dimension
• When Materialized Views Have Only a Subset of Data
• Partition Change Tracking (PCT) Rewrite
• Multiple Materialized Views

In addition if your MV is not fresh (not expoected to be due to batch or on-demand rebuild) then it is required to set QUERY_REWRITE_INTEGRITY at session (or system level) to TRUSTED or STALE_TOLERATED.

When the MV is not used for Query Rewrite, how do we find out WHY? 

We'll use DBMS_MVIEW.EXPLAIN_REWRITE.

Script for building dimension and fact tables are at the end of document. Borrowed from ASKTOM site.

Based on the setup and MV creation following query does NOT rewrite. Let us check WHY?

We'll create REWRITE_TABLE.

CREATE TABLE REWRITE_TABLE(

  statement_id          VARCHAR2(30),   -- id for the query

  mv_owner              VARCHAR2(30),   -- owner of the MV

  mv_name               VARCHAR2(30),   -- name of the MV

  sequence              INTEGER,        -- sequence no of the msg

  query                 VARCHAR2(2000), -- user query

  query_block_no        INTEGER,        -- block no of the current subquery

  rewritten_txt         VARCHAR2(2000), -- rewritten query

  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg

  pass                  VARCHAR2(3),    -- rewrite pass no

  mv_in_msg             VARCHAR2(30),   -- MV in current message 

  measure_in_msg        VARCHAR2(30),   -- Measure in current message 

  join_back_tbl         VARCHAR2(30),   -- Join back table in message 

  join_back_col         VARCHAR2(30),   -- Join back column in message

  original_cost         INTEGER,        -- Cost of original query

  rewritten_cost        INTEGER,        -- Cost of rewritten query

  flags                 INTEGER,        -- associated flags

  reserved1             INTEGER,        -- currently not used 

  reerved2              VARCHAR2(10));   -- currently not used;

  

truncate table REWRITE_TABLE;  

DECLARE

  qrytext VARCHAR2(500)  :='  select /*+ REWRITE_OR_ERROR */  time_hierarchy.qtr_yyyy, sum(sales_amount)

       from sales, time_hierarchy

      where sales.trans_date = time_hierarchy.day

      group by time_hierarchy.qtr_yyyy';

    idno    VARCHAR2(30) :='ID1';

BEGIN

  DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno);

END;

/

SELECT message FROM rewrite_table ORDER BY sequence;

QSM-01150: query did not rewrite    

QSM-01082: Joining materialized view, SALES_MV, with table, TIME_HIERARCHY, not possible  

QSM-01102: materialized view, SALES_MV, requires join back to table, TIME_HIERARCHY, on column, QTR_YYYY    

APPENDIX A - Scripts

Set up for data :

set autotrace traceonly statistics;

set timing on;

drop table sales purge;

create table sales

    (trans_date date, cust_id int, sales_amount number );

insert /*+ APPEND */ into sales

    select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,

            mod(rownum,100) CUST_ID,

            abs(dbms_random.random)/100 SALES_AMOUNT

      from all_objects;

commit;

begin

   for i in 1 .. 4

   loop

       insert /*+ APPEND */ into sales

       select trans_date, cust_id, abs(dbms_random.random)/100 

         from sales;

       commit;

   end loop;

end;

/

select count(*) from sales;

drop table time_hierarchy;

create table time_hierarchy

(day, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)

as

select distinct

  trans_date    DAY,

  cast (to_char(trans_date,'yyyymm') as number) MMYYYY,

  to_char(trans_date,'mon-yyyy') MON_YYYY,

  'Q' || ceil( to_char(trans_date,'mm')/3) || ' FY'

      || to_char(trans_date,'yyyy') QTR_YYYY,

  cast( to_char( trans_date, 'yyyy' ) as number ) YYYY

 from sales;

exec dbms_stats.gather_table_stats( user, 'SALES', cascade=>true );

exec dbms_stats.gather_table_stats( user, 'TIME_HIERARCHY', cascade=>true );

exec dbms_stats.gather_table_stats( user, 'SALES_MV', cascade=>true );

drop materialized view sales_mv;

create materialized view sales_mv

   build immediate

   refresh on demand

   enable query rewrite

   as

   select sales.cust_id, sum(sales.sales_amount) sales_amount,

          time_hierarchy.mmyyyy

     from sales, time_hierarchy

    where sales.trans_date = time_hierarchy.day

    group by sales.cust_id, time_hierarchy.mmyyyy;

Check Query Rewrite:

select name, value from v$parameter

where name in ('query_rewrite_enabled','query_rewrite_integrity');

alter session set query_rewrite_enabled = TRUE ;

alter session set query_rewrite_integrity = TRUSTED;

alter session set query_rewrite_integrity = STALE_TOLERATED;

Installing ADF Faces Components Demo

In this post the we’ll walk through the steps for downloading ADF Faces Components Demo in Jdeveloper environment. I am using Jdeveloper version 11.1.1.6.

You need OTN account (free account) for downloading the WAR file.

Access the downloadable WAR file (rcf-dvt-demo.war) from OTN which can be accessed here.

This is a WAR file containing a comprehensive demo of all the components as well as various framework features. The WAR file contains the source code for the demo.

The rich client component runtime demo can be started from inside Oracle JDeveloper 11 using the integrated Oracle Weblogic Server.

Create a directory under C:\Jdeveloper\mywork directory. This is the directory where Jdeveloper create/stores all applications by default.

Depending on file associations in your browser on your PC, this file may download as ZIP file. Just rename the file to .war extension from .zip.

Start Oracle JDeveloper and choose File | New from the menu. Select Applications and press Ok.

In the Create Application dialog type adffacesdemo as the application name and press Finish.

This creates new application workspace and project. The project is not needed and you may delete the project, choosing File | Delete Project file from disk in the Oracle JDeveloper 11g menu.

Choose File | New from the JDeveloper menu and select Projects.

In the list of items, select Project from WAR File and press Ok.

Skip the first dialog panel, and provide a name for the project, e.g. adffacesdemo, on the second.

Keep the Directory information .

On the third panel, use the file browser to select the ADF Faces demo WAR file on your file system and finish the wizard.

This step will take 1 or 2 minutes…

Save the application.

Double click onto the project node to open the project properties and select the Run | Debug | Profile option.

Press the Edit button and select the Tool Settings. In the Before Running section, uncheck the Make Project option and close the dialog pressing Ok.

Expand the project and select index.jspx under the Web Content node and Choose Run from the right mouse context menu.

The Face DVT Demo application will open up in your default browser.

Good Luck.

Assigning primary key value using Oracle Sequence

Many applications define tables with primary key which is populated using values from database sequence. There are several posts on the net that describe these techniques with some links listed below as references. In ADF applications there are several approaches to implement assignment of primary key values.

1. Using Groovy Expression in Entity class.

2. Overriding the create method of the Entity class.

3. Using database trigger.

1. Assign Primary Key values using ADF Entity level using Groovy Expression

SequenceImpl class is used in groovy expression of the key attribute of the entity. getSequenceNumber() method of the SequenceImpl class can be invoked using the database sequence.

In the Entity or the updatable View Object editor the following expression can be set in the properties of the key attribute as shown below.

(new oracle.jbo.server.SequenceImpl("T1_SEQ",object.getDBTransaction())).getSequenceNumber()

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

1. Assign Primary Key values by overriding create method of the entity class

To override the create method of the entity class, generate  the java Accessors  (source code)  as shown below.

Once the java class is created modify the create method with following code.

    @Override

    protected void create(AttributeList attributeList) {

        super.create(attributeList);

        SequenceImpl seq = new SequenceImpl("T2_SEQ", getDBTransaction());

        Integer seqNextval;

        seqNextval = seq.getSequenceNumber().intValue();

        setT2Id(seqNextval);

    }

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

3. Assign Primary Key values by using database trigger

Use the database navigator to access the table, right-click on the table and select Create (PK from Sequence…) option.

Once a trigger is created, change the attribute type of the primary key column in the entity class to DBSequence.

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

Note that a negative value is assigned until the actual INSERT is executed on the table in the database, which fires the trigger to assign new sequence value. This negative value is used to manage the entity relationships in the framework until changes are saved to the database. Once the changes are saved the framework will refresh the trigger assigned key value.

Till Next post  Bye.

Reference:

ADF Insider Video by Grant Ronald

Oracle Documentation

Chris Muir Blog

SQL Developer 4.0 - "Streams has already been closed"!

This error in SQL Developer (4.0) appears when you execute an SQL in SQL Worksheet with some errors. Any further execution of the SQL results in same error.

Once you correct the SQL and rerun the SQL you get the same error…

Any further execution of the SQL gives the same error. However if you “refresh” the result set SQL developer re-executes the SQL properly and returns he result.

SQL developer 4.0 has lots of new functionality and performance better.

Happy exploring…

Transaction Backout - Oracle Flashback Technology

Oracle introduced Flashback Technology many years back (9i), and with version 11g TRANSACTION_BACKOUT capability was introduced.

This is a very powerful feature, which allows a developer or DBA to BACKOUT specific committed transactions. This feature leverages UNDO and supplemental log data to identify the DML changes involved in a specific transaction and the other dependent transactions to BACKOUT the transaction.

Please Note: This feature will allow you to violate Database consistency and Atomicity of Transactions. So use it with care!!!

However, in development and testing environment, I find this feature useful for performing repeated end-to-end testing for applications involving of complex applications.

Specifically in testing of transactions involving

Complex setup of test data.

Test data setup with workflow/different users/roles involvement.

Transaction testing involving external system interfaces, large batch processes or time sensitive transactions.

Transaction changes the state of data in such a way that process to reverse the state is complex.

In these situations, the effort for setting up the test data is significantly more than test step itself. We can use TRANSACTION_BACKOUT feature to revert back the transaction and repeat the test many times.

Let us explore the details of FLASHBACK transactions, specifically the BACKOUT feature.

What is Flashback Transaction BACKOUT?

Flashback Transaction Back-Out is a logical recovery option to roll back a target transaction and its dependent transactions while the database remains online. A dependent transaction is related by either a write-after-write (WAW) relationship, in which a transaction modifies the same data that was changed by the target transaction, or a primary key constraint relationship, in which a transaction re-inserts the same primary key value that was deleted by the target transaction. Flashback Transaction utilizes undo and the redo generated for undo blocks, to create and execute a compensating transaction for reverting the affected data back to its original state.

For Flashback Transactions to work, Oracle has to capture sufficient information in UNDO and supplemental logs.

For Oracle Flashback Transaction to work following are the requirements.

Database must be in ARCHIVELOG mode. (How to switch to ARCHIVELOG mode)

Supplemental Log (with primary key) must be turned on. (How to turn on Supplemental Logging)

User must have following privilege.

EXECUTE privilege on DBMS_FLASHBACK package

GRANT EXECUTE on DBMS_FLASHBACK to userid;

SELECT ANY TRANSACTION privilege.

GRANT SELECT ANY TRANSACTION to userid;

Please note this is a very powerful privilege and should be granted only after care evaluation of security/audit requirements.

In addition the user must have CRUD privilege on the tables that FBT BACKOUT is working on!!!

WAW Dependency

A transaction can have a write-after-write (WAW) dependency, meaning that a transaction updates or deletes row data, that has been inserted or updated by a previous transaction. In these situations, Oracle FBT BACKOUT feature provides options to back out all dependent transactions (keeping DB transaction-ally consistent) or allow you to break the transaction consistency.

The FBT BACKOUT can be used using command line or OEM. The rest of this posting will walk through an example of FBT BACKOUT using command line interface. The OEM interface is lot more intuitive but may be cumbersome when working with complex and large transactions.

Setup

The setup requirements and sample of FBT BACKOUT is well described at Oracle Tutorials. I’ll walk through an example in the context of repeatable testing.

User HR schema (user).

The transaction (referred as T1 includes 6 DMLs.

Also included is a transaction T2 (2 DMLs) that is not related to T1 but is dependent on T1.

Our goal is to repeatedly test the T1 after reverting T1 after each test.

In our test script, we’ll perform following steps.

Create the initially populate the table

Perform two transactions on the table.

Verify the data changes.

Identify the transactions (using XID) that’ll be

BACKOUT the transactions.

Verify that data changes are backed out and original values are restored.

Reapply the transaction by running the same script.

Verify the data changes.

Table’s definition and script to populate them are as follows.

drop table ta;

CREATE table TA (ca int primary key, va varchar2(20));

-- Inserted 10 rows for set up.

insert into ta select level ca, 'AAAA - ' || to_char(to_date(level,'jsp')) from dual connect by level < 5;

commit;

Now with this setup completed, we can run the transactions and explore TRANSACTION BACKOUT feature.

Note:

Both update and delete in T1 are dependent on the insert of T1. Of course this is not transaction dependency.

Note transaction T2 (a different transaction from T1) inserts additional rows and however updates some rows created by transaction T1. Thus T2 transaction is dependent on T1: WAW dependency. In order to BACKOUT transaction T1 without compromising data integrity we must revert transaction T2.

Following script shows the transactions.

SQL> --drop table ta purge;

SQL> --CREATE table TA (ca int primary key, va varchar2(20));

SQL> delete from ta

9 rows deleted.

SQL> commit

Commit complete.

SQL> insert into ta select level ca, 'AAAA - ' || to_char(to_date(level,'jsp')) from dual connect by level &lt; 5

4 rows created.

SQL> commit

Commit complete.

SQL> prompt After initial setup...

After initial setup...

SQL> select ora_rowscn, ta.* from ta

ORA_ROWSCN         CA VA                 

---------- ---------- --------------------

  31925488          1 AAAA - 01-JAN-12   

  31925488          2 AAAA - 02-JAN-12   

  31925488          3 AAAA - 03-JAN-12   

  31925488          4 AAAA - 04-JAN-12   

4 rows selected.

SQL> insert into ta values( 10 , 'BBBB - Ins by T1')

1 row created.

SQL> insert into ta values( 11 , 'BBBB - Ins by T1')

1 row created.

SQL> insert into ta values( 12 , 'BBBB - Ins by T1')

1 row created.

SQL> insert into ta values( 13 , 'BBBB - Ins by T1')

1 row created.

SQL> update ta set va= 'UUUU - Upd by T1' where ca in (1,11)

2 rows updated.

SQL> delete from ta where ca in (2)

1 row deleted.

SQL> commit

Commit complete.

SQL> prompt After Transaction T1...

After Transaction T1...

SQL> select ora_rowscn, ta.* from ta

ORA_ROWSCN         CA VA                 

---------- ---------- --------------------

  31925493          1 UUUU - Upd by T1   

  31925493          3 AAAA - 03-JAN-12   

  31925493          4 AAAA - 04-JAN-12   

  31925493         10 BBBB - Ins by T1   

  31925493         11 UUUU - Upd by T1   

  31925493         12 BBBB - Ins by T1    

  31925493         13 BBBB - Ins by T1   

7 rows selected.

SQL> insert into ta values( 21, 'BBBB - Ins by T2' )

1 row created.

SQL> insert into ta values( 22, 'BBBB - Ins by T2' )

1 row created.

SQL> update ta set va= 'UUUU - Upd by T2' where ca in (3, 10)

2 rows updated.

SQL> commit

Commit complete.

SQL> prompt After Transaction T2...

After Transaction T2...

SQL> select ora_rowscn, ta.* from ta

ORA_ROWSCN         CA VA                 

---------- ---------- --------------------

  31925495          1 UUUU - Upd by T1   

  31925495          3 UUUU - Upd by T2   

  31925495          4 AAAA - 04-JAN-12   

  31925495         10 UUUU - Upd by T2   

  31925495         11 UUUU - Upd by T1   

  31925495         12 BBBB - Ins by T1   

  31925495         13 BBBB - Ins by T1   

  31925495         21 BBBB - Ins by T2   

  31925495         22 BBBB - Ins by T2   

9 rows selected.

Now that transactions have been run, we can go to OEM, and look and manage transactions. Though transactions can be managed by command line environment (described here), OEM provides a robust interface that eases the tasks considerably.

After the transaction BACKOUT…

ORA_ROWSCN         CA VA                 

---------- ---------- --------------------

  31950709         11 UUUU - Upd by T1   

  31950709          1 UUUU - Upd by T1   

  31950709         12 BBBB - Ins by T1   

  31950709         13 BBBB - Ins by T1   

  31950709         21 BBBB - Ins by T2   

  31950709         22 BBBB - Ins by T2   

  31950709          3 UUUU - Upd by T2   

  31950709          4 AAAA - 04-JAN-12   

  31950709         10 UUUU - Upd by T2   

9 rows selected.

At this point we can re-run the transaction that was BACKED-OUT again.

There are views that describe the status of the transactions that were BACKED-OUT.

This way logical transactions can be rolled-back and reapplied for repeated testing.

select * from dba_flashback_txn_state;

select * from dba_flashback_txn_report;

Exceptions:

When performing TRANSACTION BACKOUT there are several errors/ exceptions one can encounter. Some of the common ones are described below.

Transaction back-out is not supported over DDLs on the object. If a transaction is picked that is before a DDL issued on the DB object OEM will not allow the transactions to be backed-out.

Also if the DB is not in ARCHIVELOG mode this feature will not be available.

Hope this helps.

References:

Following references were used.

Oracle Documentation

AskTom Site

TOAD Documentation

Oracle Flash (Zahid Karim BLOG)

More Oracle Documentations

PSOUG dbms_flashback

Oracle Tutorials

Oracle Cloud : Getting Started

Having worked with Oracle Database, APEX, ADF, and Amazon Cloud (AWS) for some time now, like many others I was waiting for arrival of Oracle Cloud. Finally in 2012, Oracle has started delivering its Cloud Offerings. As a not-so-early-adapters, I started a trial account to explore the Oracle Public Cloud (OPC). My expectation was that Oracle Cloud would be something similar to AWS… Boy I was wrong!

Oracle has included some good introductory video’s (on YouTube) and OBE documentation to get started and get feet wet. They can be found here.

In this series of posts, I’ll describe some of my experiences in Oracle Cloud.

Covered in this post:

1. Basic OPC specific terminology

2. Creating an Oracle Cloud account

3. Accessing the Database Service

4. Explore APEX on OPC

1. Basic OPC Terminology

Account - An account corresponds to an individual, organization, or company that is an Oracle customer. An account has one or more Account Administrators who are responsible for creating and nominating the other administrator types, creating identity domains, and managing the customer account information. If you already have an account on Oracle.com, this is the account that is used. This is in alignment with Oracle single sign-on (SSO) strategy.

Data center - Oracle provides data centers in various geographical regions. An identity domain and the services associated with it must belong to a specific data center. For optimal performance when purchasing a service subscription, select a data center closest to the majority of your user population. Normal OPC service users, application developers, administrators and end users will not be directly concerned (or even aware) of Data Centers. However, Cloud administrator and architects need to be aware of and use data center selection taking factors such a network latency, failovers, high-availabilities etc.

Identity domain - Identity domains control authentication and authorization who can log in and what they can access once they log in. Multiple services can be associated with a single identity domain to share user definitions and authentication. Users in an identity domain can be granted different levels of access to each service associated with the domain. An Oracle Cloud Service has to belong to an Identity Domain. Identity Domains control service user authentication and authorization. Service users in an identity domain can have different levels of access.

A self-service administration tool, Oracle Identity Console is used in the provisioning and management of services for end user and administrator accounts for all Oracle Cloud services in the Account. Note these end users and administrators are for OPC services and not that of applications developed and deployed in OPC.

Service - A software offering in the Oracle Cloud. Oracle offers social, application, and platform services. Oracle applications (like HCM, ERP, Financials) are also being offered as services. From developers perspective Java and Developer platforms are offered as service. In this post we’ll explore Database service with APEX. You can request one or more service offerings. They are priced independently, however they are interoperable. For each Service that you request you can assign a unique name within the identity domain.

2. Creating an Oracle Cloud Account

The procedure to procure a free trial account (or a paid services account) is described in Oracle Cloud Tutorials here.

After you activate your account, the confirmation email you receive could be a bit confusing. IT was for me. The review of OPC terminology will help. I’ll briefly review the sections here after activation of one Database Service. OPC account was activated at the same time, as this was the first services I requested. Note that any additional service I request will use the same OPC account.

The email has following 5 sections. Each section contains URL for access, userid/password for access

Service Details

This section contains URL for accessing the Service, userid/password for access, data center name and an FTP account. This FTP account is specific for the service. More on this later. Also included in this section is account administration URL. This account administration is Oracle account with Single Sign On (SSO) and can be used for administer ALL services under this account.

Identity Domain Details

An Identity Domain is created in association with the Service request for the first time. Users accounts of the Services (not your applications deployed in services) can be administered under this Identity Domain.

This section includes URL for Identity Domain access and Identity Management Console. Every OPC service is associated with a unique Identity Domain.

Account Details

This section contains Oracle SSO account details. IF this is the first time your Oracle account has been created then you are required to change the password.

Domain FTP Account Details

There is also an FTP account created for the Identity Domain. This FTP account is different from Service FTP account. This is used to upload/download data to Identity domain.

3. Accessing OPC Database Service

You can access the Database Service using the URL provided under Service Detail section. Following login screen is available.

When you login to the OPC database service APEX user interface (dashboard) which is already configured with a workspace is available. Note the workspace name at right top corner.

If you have worked with APEX before, you’ll recognize the this user interface. This platform provides complete environment for complete application development in a team environment using Oracle database capabilities. We’ll explore the sample application that is already installed in the next section.

2. Explore APEX in OPC

Home screen, by default provides access to Application Builder, SQL Workshop, Team Development, and Administration area. We’ll explore these in later posts. Each of these areas provide functionality for Rapid application development.

There a sample database application available. Click on Application Builder to access the Sample Application. Note that we can right away Run the application or Edit the application to customize it.

In addition there are several prepackaged application that can be installed/deployed to your workspace and customized (if the application is NOT LOCKED.

More in building and customizing APEX applications in future posts.

Till then BYE!

References:

1.  Oracle Cloud Documentation

2.  Oracle Cloud