Monday, July 01, 2013

12c - SQL Text Expansion

Here is another small but very useful new feature in Oracle Database 12c - SQL Text Expansion. It will come in handy in two cases:

You are asked to tune what looks like a simple query - maybe a two table join with simple predicates. But it turns out the two tables are each views of views of views and so on... In other words, you've been asked to 'tune' a 15 page query, not a two liner.

You are asked to take a look at a query against tables with VPD (virtual private database) policies. In order words, you have no idea what you are trying to 'tune'.

A new function, EXPAND_SQL_TEXT, in the DBMS_UTILITY package makes seeing what the "real" SQL is quite easy. For example - take the common view ALL_USERS - we can now:

TATS#"="A3"."TS#" AND "A4"."TEMPTS#"="A2"."TS#" AND "A4"."TYPE#"=1) "A1"

Now it is easy to see what query is really being executed at runtime - regardless of how many views of views you might have. You can see the expanded text - and that will probably lead you to the conclusion that maybe that 27 table join to 25 tables you don't even care about might better be written as a two table join.

Further, if you've ever tried to figure out what a VPD policy might be doing to your SQL, you know it was hard to do at best. Christian Antognini wrote up a way to sort of see it - but you never get to see the entire SQL statement: http://www.antognini.ch/2010/02/tracing-vpd-predicates/. But now with this function - it becomes rather trivial to see the expanded SQL - after the VPD has been applied. We can see this by setting up a small table with a VPD policy

Not an earth shattering new feature - but extremely useful in certain cases. I know I'll be using it when someone asks me to look at a query that looks simple but has a twenty page plan associated with it!

2 Comments:

Excellent stuff, Tom, and a follow-up regarding VPD: I had thought that Oracle wanted to make it very hard for users to see the security policies applied automatically to one's SQL. But it seems now with 12.1 that if you can connect to a session that has access to DBMS_UTILITY, you will be able to see how VPD is being used to deny you access. Is this desired in a secure environment?

About Me

The views expressed are my own and not necessarily those of Oracle and its affiliates. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.
I've been using Oracle since 1988. I've been working at Oracle since 1993 (version 7.0). I spend way too much time working on asktom.oracle.com...