Thursday, December 28, 2017

READ privilege in Oracle 12c

Starting with Oracle 12c we can now use READ object privilege to enable the users to query database tables, views, materialized views and synonyms. With this new READ object privilege, users can still query the objects like SELECT object privilege, but no more locks on the objects are possible.
 
 
demo@ORCL> create table t as select * from scott.dept;
 
Table created.
 
demo@ORCL> grant READ ON t to scott;
 
Grant succeeded.
 
demo@ORCL> conn scott/tiger
Connected.
scott@ORCL> select count(*) from demo.t;
 
  COUNT(*)
----------
         4
 
scott@ORCL> lock table demo.t in exclusive mode;
lock table demo.t in exclusive mode
                *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 
scott@ORCL> begin
  2     for x in (select * from demo.t for update of deptno)
  3     loop
  4             null;
  5     end loop;
  6  end;
  7  /
        for x in (select * from demo.t for update of deptno)
                                     *
ERROR at line 2:
ORA-06550: line 2, column 31:
PL/SQL: ORA-01031: insufficient privileges
ORA-06550: line 2, column 12:
PL/SQL: SQL Statement ignored
 
 
scott@ORCL>
 
Now let’s remove the READ privilege and grant the SELECT privilege.
 
demo@ORCL> revoke READ on t from scott;
 
Revoke succeeded.
 
demo@ORCL> grant SELECT on t to scott;
 
Grant succeeded.
 
With this SELECT privilege, users can still query the objects.
 
demo@ORCL> conn scott/tiger
Connected.
scott@ORCL> select count(*) from demo.t;
 
  COUNT(*)
----------
         4
 
But lock the rows in the table are possible through SELECT privileges.
 
scott@ORCL> lock table demo.t in exclusive mode;
 
Table(s) Locked.
 
scott@ORCL> rollback;
 
Rollback complete.
 
scott@ORCL> begin
  2     for x in (select * from demo.t for update of deptno)
  3     loop
  4             null;
  5     end loop;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
scott@ORCL> rollback;
 
Rollback complete.
 
scott@ORCL> conn demo/demo
Connected.
demo@ORCL> select grantor,table_name,grantee,privilege
  2  from user_tab_privs_made
  3  where grantor='DEMO'
  4  and table_name ='T'
  5  /
 
GRANTOR    TABLE_NAME           GRANTEE    PRIVILEGE
---------- -------------------- ---------- ----------
DEMO       T                    SCOTT      SELECT
 
demo@ORCL> revoke select on t from scott;
 
Revoke succeeded.
 
demo@ORCL> grant read on t to scott;
 
Grant succeeded.
 
demo@ORCL> select grantor,table_name,grantee,privilege
  2  from user_tab_privs_made
  3  where grantor='DEMO'
  4  and table_name ='T'
  5  /
 
GRANTOR    TABLE_NAME           GRANTEE    PRIVILEGE
---------- -------------------- ---------- ----------
DEMO       T                    SCOTT      READ
 
demo@ORCL>
Posted by at No comments:

Thursday, December 21, 2017

gather_plan_statistics hint for parallel sqls

Had discussed enough about the gather_plan_statistics hint to validate the cardinality estimates, however this need to be revisited for Parallel sql execution in the database.
demo@ORA12C> alter session set statistics_level=all;
 
Session altered.
 
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from big_table b;
 
  COUNT(*)
----------
   1000000
 
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  gqr17qtmdmsmq, child number 0
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
 
Plan hash value: 2894119656
 
----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |      1 |00:00:01.97 |      20 |
|   1 |  SORT AGGREGATE        |           |      1 |      1 |      1 |00:00:01.97 |      20 |
|   2 |   PX COORDINATOR       |           |      1 |        |      4 |00:00:01.97 |      20 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |      0 |      1 |      0 |00:00:00.01 |       0 |
|   4 |     SORT AGGREGATE     |           |      0 |      1 |      0 |00:00:00.01 |       0 |
|   5 |      PX BLOCK ITERATOR |           |      0 |    941K|      0 |00:00:00.01 |       0 |
|*  6 |       TABLE ACCESS FULL| BIG_TABLE |      0 |    941K|      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property
 
 
28 rows selected.
 
demo@ORA12C>
 
As you can see from the parallel execution plan the majority of the rows in the plan have 0 in the A-rows column, this is caused by the nature of parallel execution and how it interacts with the FORMAT we requested in the DBMS_XPLAN.DISPLAY_CURSOR method. The FORMAT parameter value ‘ALLSTATS LAST’ request the execution statistics of the last execution of the cursor be shown.
 
In a parallel execution the last process to execute the cursor is the Query coordinator (QC), typically this QC will execute a small number of operations in the execution plan, while the majority of the operations in the plan was done by the parallel execution server process. So when we issue the DBMS_XPLAN.DISPLAY_CURSOR and ask for the last execution we only get the information about the operations in the plan that the QC actually executed. In this case the only operation that QC did was return the final result to our SQL*Plus session, which is why the line 0 and 1 and 2 have entries in the A-rows column.
 
In order to see A-rows values for all the operations in the plan, we have to use the FORMAT value as ALLSTATS ALL, which will show you the execution statistics for ALL executions of the cursor.
 
demo@ORA12C> set linesize 250
demo@ORA12C> alter session set statistics_level=all;
 
Session altered.
 
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from big_table b;
 
  COUNT(*)
----------
   1000000
 
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS ALL'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
SQL_ID  gqr17qtmdmsmq, child number 1
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
 
Plan hash value: 2894119656
 
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |  1421 (100)|          |        |      |            |      1 |00:00:03.74 |      20 |      0 |
|   1 |  SORT AGGREGATE        |           |      1 |      1 |            |          |        |      |            |      1 |00:00:03.74 |      20 |      0 |
|   2 |   PX COORDINATOR       |           |      1 |        |            |          |        |      |            |      4 |00:00:03.74 |      20 |      0 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |      0 |      1 |            |          |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE     |           |      4 |      1 |            |          |  Q1,00 | PCWP |            |      4 |00:00:14.84 |   19247 |  18570 |
|   5 |      PX BLOCK ITERATOR |           |      4 |   1000K|  1421   (1)| 00:00:01 |  Q1,00 | PCWC |            |   1000K|00:00:14.83 |   19247 |  18570 |
|*  6 |       TABLE ACCESS FULL| BIG_TABLE |     55 |   1000K|  1421   (1)| 00:00:01 |  Q1,00 | PCWP |            |   1000K|00:00:14.83 |   19247 |  18570 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   6 - SEL$1 / B@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
   5 - (rowset=1019)
   6 - (rowset=1019)
 
Note
-----
   - Degree of Parallelism is 4 because of table property
 
 
43 rows selected.
 
demo@ORA12C>
 
We should be very careful about the A-Rows listed here, since those values listed in the execution plan will be cumulative (incremented on every execution)
Running the same sql three times in the session, produced the output (cumulative result in A-Rows columns) like this.
 
demo@ORA12C> alter session set statistics_level=all;
 
Session altered.
 
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from big_table b;
 
  COUNT(*)
----------
   1000000
 
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from big_table b;
 
  COUNT(*)
----------
   1000000
 
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from big_table b;
 
  COUNT(*)
----------
   1000000
 
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS ALL'));
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
SQL_ID  gqr17qtmdmsmq, child number 0
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
 
Plan hash value: 2894119656
 
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      3 |        |  1421 (100)|          |        |      |            |      3 |00:00:08.27 |      62 |      0 |
|   1 |  SORT AGGREGATE        |           |      3 |      1 |            |          |        |      |            |      3 |00:00:08.27 |      62 |      0 |
|   2 |   PX COORDINATOR       |           |      3 |        |            |          |        |      |            |     12 |00:00:08.27 |      62 |      0 |
|   3 |    PX SEND QC (RANDOM) | :TQ10000  |      0 |      1 |            |          |  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |      0 |
|   4 |     SORT AGGREGATE     |           |     11 |      1 |            |          |  Q1,00 | PCWP |            |     11 |00:00:29.82 |   53474 |  51594 |
|   5 |      PX BLOCK ITERATOR |           |     12 |   1000K|  1421   (1)| 00:00:01 |  Q1,00 | PCWC |            |   3000K|00:00:32.11 |   57739 |  55710 |
|*  6 |       TABLE ACCESS FULL| BIG_TABLE |    151 |   1000K|  1421   (1)| 00:00:01 |  Q1,00 | PCWP |            |   2758K|00:00:29.19 |   52765 |  51203 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   6 - SEL$1 / B@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(:Z>=:Z AND :Z<=:Z)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
   5 - (rowset=1019)
   6 - (rowset=1019)
 
Note
-----
   - Degree of Parallelism is 4 because of table property
 
 
43 rows selected.
 
demo@ORA12C>
Posted by at No comments:
Subscribe to: Posts (Atom)