Visualizing plans grants

A while ago I post about to use plan visualization by Tanel Poder. That has been a tool worth using. Thou that should be a tool also to other developers than DBAs. A-Rows and A-Time are such valuable information about the execution bottlenecs inside SQL execution.

How to get it working without dba rights.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

It is not possible to grant select rights straight to v$ views as Coscan has writen.

grant select on v_$session to rafu;

And a try

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

Giving a grant

grant select on v_$sql_plan_statistics_all to rafu;

And after that no success. The same output.
11.2 documentation says that to use DBMS_XPLAN.DISPLAY_CURSOR the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views.

grant select on v_$sql_plan to rafu;

grant select on v_$sql to rafu;

And we are there. User rafu may use DBMS_XPLAN.DISPLAY_CURSOR with sql_id=>null parameter. Also grant alter session to avoid the need to use gather_plan_statistics hint might be recommended. That is how you enable ALTER SESSION SET statistics_level = ALL;

grant alter session to rafu;

Also giving these grants through a role to developer users is worth considering.

Pivoting EAV

If you are responsible for designing a data model and just consider to invent again and create this fine generic entity attribute value structure, maybe you should consider attending some teaching about the issue. For example some available soon by C.J. Date in and near Finland.

Well maybe you have a EAV model that you have to deal with. Example

SQL> create table eav as
  2  select 1 e, 'first' a, 'Timo' v from dual union all
  3  select 1 e, 'last' a, 'Raitalaakso' v  from dual union all
  4  select 1 e, 'nic' a, 'Rafu' v  from dual union all
  5  select 2 e, 'first' a, 'John' v  from dual union all
  6  select 2 e, 'last' a, 'Doe' v from dual
  7  ;

Table created.

SQL> select * from eav;

         E A     V
---------- ----- -----------
         1 first Timo
         1 last  Raitalaakso
         1 nic   Rafu
         2 first John
         2 last  Doe

You should not query it in a basic case using joins. Most possibly you have tens of joins to the same table.

SQL> select la.e, fi.v firs, la.v las
  2    from eav la, eav fi
  3   where la.e=fi.e
  4     and fi.a='first'
  5     and la.a='last'
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

It is a pivot you want to do.

SQL> select e, firs, las
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las))
  4  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

With the pivot you get the nullable columns also easier without filtering out the whole entity

SQL> select e, firs, las, ni
  2    from eav
  3    pivot (max(v) for a in ('first' as firs, 'last' as las, 'nic' as ni))
  4  ;

         E FIRS        LAS         NI
---------- ----------- ----------- -----------
         1 Timo        Raitalaakso Rafu
         2 John        Doe

Maybe you do not have 11g features available.

SQL> select e
  2       , max(case when a = 'first' then v end) firs
  3       , max(case when a = 'last' then v end) las
  4    from eav
  5   group by e
  6  ;

         E FIRS        LAS
---------- ----------- -----------
         1 Timo        Raitalaakso
         2 John        Doe

And the best thing to do with it might be.

SQL> drop table eav purge;

Table dropped.

Parallel query distribution methods

Figuring out should I recommend parallelizing materialized view refresh for several mvs or use parallel partition wise joins. Found valuable help for the issue just yesterday by Tony Hasler. Will be using both. Unfortunately no blog writing coming up about the issue.

not in null countdown

Just a reminder about not in and nulls. Maybe consider using not exists or anti join if any of the columns in not in list may be null. Or maybe one of the following might be the result you want.

SQL> create table nm as
  2  with le as (select level ev from dual connect by level<4)
  3  select l.ev e,e.ev v from le l, le e;

Table created.

SQL> select count(*) from nm;

  COUNT(*)
----------
         9

SQL> select count(*) from nm where (e,v) not in ((1,1));

  COUNT(*)
----------
         8

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,2));

  COUNT(*)
----------
         7

SQL> select count(*) from nm where (e,v) not in ((1,null));

  COUNT(*)
----------
         6

SQL> select count(*) from nm where (e,v) not in ((1,1),(2,null));

  COUNT(*)
----------
         5

SQL> select count(*) from nm where (e,v) not in ((null,1),(2,null));

  COUNT(*)
----------
         4

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null));

  COUNT(*)
----------
         3

SQL> select count(*) from nm where (e,v) not in ((1,null),(2,null),(3,3));

  COUNT(*)
----------
         2

SQL> select count(*) from nm where (e,v) not in ((null,1),(null,2),(1,3),(2,3));

  COUNT(*)
----------
         1

SQL> select count(*) from nm where (e,v) not in ((null,null),(1,1));

  COUNT(*)
----------
         0

SQL> select count(*)
  2    from nm m
  3   where not exists (select n
  4                       from (select null n from dual) d
  5                      where m.v = d.n
  6                        and m.e = d.n);

  COUNT(*)
----------
         9

SQL> select count(*)
  2    from nm m
  3     left outer join (select 10 e, null n from dual) d
  4       on m.e=d.e
  5  where d.n is null;

  COUNT(*)
----------
         9