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:

  1. 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.
  2. 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:

ops$tkyte%ORA12CR1> variable x clob

ops$tkyte%ORA12CR1> begin
  2          dbms_utility.expand_sql_text
  3          ( input_sql_text => 'select * from all_users',
  4            output_sql_text => :x );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."USERNAME" "USERNAME","A1"."USER_ID" "USER_ID","A1"."CREATED" "CREAT
ED","A1"."COMMON" "COMMON" FROM  (SELECT "A4"."NAME" "USERNAME","A4"."USER#" "US
ER_ID","A4"."CTIME" "CREATED",DECODE(BITAND("A4"."SPARE1",128),128,'YES','NO') "
COMMON" FROM "SYS"."USER$" "A4","SYS"."TS$" "A3","SYS"."TS$" "A2" WHERE "A4"."DA
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 

ops$tkyte%ORA12CR1> create table my_table
  2  (  data        varchar2(30),
  3     OWNER       varchar2(30) default USER
  4  )
  5  /
Table created.

ops$tkyte%ORA12CR1> create or replace
  2  function my_security_function( p_schema in varchar2,
  3                                 p_object in varchar2 )
  4  return varchar2
  5  as
  6  begin
  7     return 'owner = USER';
  8  end;
  9  /
Function created.

ops$tkyte%ORA12CR1> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'MY_TABLE',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /
PL/SQL procedure successfully completed.

And then expanding a query against it:

ops$tkyte%ORA12CR1> begin
  2          dbms_utility.expand_sql_text
  3          ( input_sql_text => 'select * from my_table',
  4            output_sql_text => :x );
  5  end;
  6  /
PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."DATA" "DATA","A1"."OWNER" "OWNER" FROM  (SELECT "A2"."DATA" "DATA",
"A2"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "A2" WHERE "A2"."OWNER"=USER@!)
 "A1"

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!
POST A COMMENT

2 Comments:

Blogger Steven Feuerstein said....

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?

Mon Jul 01, 09:44:00 AM EDT  

Blogger Thomas Kyte said....

@Steven,

yes - if you have dbms_utility access, you'll be able to see the expanded text.

but.... you've been able to see that for a long time... with autotrace, with dbms_xplan. Here user A is a user with just create session and select on my_table:


a%ORA12CR1> set autotrace traceonly explain
a%ORA12CR1> select * from ops$tkyte.my_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 3804444429

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MY_TABLE | 1 | 34 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OWNER"=USER@!)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


the predicate information and the plan itself would give you everything you need to reconstruct a semantically equivalent query..

Mon Jul 01, 09:54:00 AM EDT  

POST A COMMENT

<< Home