set serveroutput on
DECLARE
CURSOR c1 IS
select username
from dba_users
where username like 'IT%'
or username like 'MAITD%';
BEGIN
FOR x IN c1 LOOP
dbms_output.put_line('user ' || x.username || ' processed.' );
execute immediate('GRANT SELECT ON SYS.IND$ TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.OBJ$ TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.TAB$ TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.USER$ TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$DATABASE TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$INSTANCE TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$LATCH TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$LIBRARYCACHE TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$MYSTAT TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$PROCESS TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$ROWCACHE TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SESSION TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SESSTAT TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SESS_IO TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SGASTAT TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$STATNAME TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SYSSTAT TO ' || x.username);
execute immediate('GRANT SELECT ON SYS.V_$SYSTEM_EVENT TO ' || x.username);
END LOOP;
END;
/
Thursday, January 11, 2018
How to bulk-grant privileges to a selection of users using PL/SQL
This anonymous PL/SQL script will select all the desired users, and then grant SELECT on a number of performance views to these.
How to check for table partitions using PL/SQL
I created the following piece of code a long time ago.
It checks if a table is partitioned or not, and list its partition.
It takes the table name and its owner as parameters, and list it.
If you need a template for a simple script using an anonymous PL/SQL block, I assume it could serve a purpose.
For what it's worth, here it is:
It checks if a table is partitioned or not, and list its partition.
It takes the table name and its owner as parameters, and list it.
If you need a template for a simple script using an anonymous PL/SQL block, I assume it could serve a purpose.
For what it's worth, here it is:
set serveroutput on
DECLARE
p_table_name varchar2(100) := '&table_name';
p_table_owner varchar2(100) := '&owner';
p_count number := 0;
BEGIN
SELECT count(*) into p_count
FROM dba_tab_partitions
WHERE table_name = p_table_name
AND table_owner = p_table_owner;
IF (p_count = 0) THEN
dbms_output.put_line('There were no partitions on table ' || p_table_name );
ELSE
For l_rec in (
select p.owner, p.table_name, t.partition_name, t.TABLESPACE_NAME ,p.partitioning_type
from dba_part_tables p, dba_tab_partitions t
where p.table_name = p_table_name
and p.owner = t.TABLE_OWNER
and t.TABLE_NAME = p.TABLE_NAME
)
LOOP
dbms_output.put_line(l_rec.partition_name || ' ' || l_rec.tablespace_name || ' ' || l_rec.partitioning_type);
END LOOP;
END IF;
END;
/
How to check if Unified Auditing is enabled in your database
Check that Unified Auditing is enabled with the following query:
col parameter format a20 col value format a20 set lines 200 select parameter, value from v$option where parameter='Unified Auditing'; exitOutput shows that it is enabled:
PARAMETER VALUE -------------------- -------------------- Unified Auditing TRUE
Wednesday, January 10, 2018
What is "Automatic Reoptimization" introduced in Oracle 12cR1?
Some definitions here, mostly taken directly from Oracls documentation, or quoted from Tim Hall's www.oracle-base.com
Automatic Reoptimization is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement.
Automatic reoptimization takes two forms: Statistics Feedback and Performance Feedback.
Statistics Feedback (formerly known as cardinality feedback and first introduced in Oracle 11gR2) is a type of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates.
Automatic Reoptimization
Automatic Reoptimization is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement.
Automatic reoptimization takes two forms: Statistics Feedback and Performance Feedback.
Statistics feedback
Statistics Feedback (formerly known as cardinality feedback and first introduced in Oracle 11gR2) is a type of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates.
- At the end of an execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution.
- If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use and the statement is marked as "reoptimizable".
- When the query executes again, the optimizer uses the corrected cardinality estimates instead of its usual estimates, allowing a better plan to be determined
- Statistics Feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool.
Performance Feedback
This form of automatic reoptimization helps improve the degree of parallelism automatically chosen for repeated SQL statements when PARALLEL_DEGREE_POLICY is set to ADAPTIVE.
- At the end of an initial execution, the optimizer compares the degree of parallelism chosen by the optimizer with the degree of parallelism computed based on the performance statistics (for example, the CPU time) gathered during the actual execution of the statement
- If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution statistics as feedback. This feedback helps better compute the degree of parallelism for subsequent executions.
Note that even if PARALLEL_DEGREE_POLICY is not set to ADAPTIVE, statistics feedback may influence the degree of parallelism chosen for a statement.
In short, to remember it more easily:
In short, to remember it more easily:
- statistics feedback deals with incorrect cardinality
- performance feedback deals with incorrect parallel execution
Sources:
Monday, December 18, 2017
How to make use of the GATHER_PLAN_STATISTICS hint
Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows.
Notice the case - gather_plan_statistics - I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!
Use the hint in the SELECT part of your query, for example:
Alternatively, after the query has run to completion, check the cursor cache for the details of your specific SQL ID, and use the display_cursor with the SQL ID explicitly set:
Notice the case - gather_plan_statistics - I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!
Use the hint in the SELECT part of your query, for example:
select /*+ gather_plan_statistics */ from mytable t1 join mytable t2.... set linesize 200 set pagesize 0 select * from table (dbms_xplan.display_cursor(NULL,NULL,'TYPICAL ALLSTATS LAST')); exit
Alternatively, after the query has run to completion, check the cursor cache for the details of your specific SQL ID, and use the display_cursor with the SQL ID explicitly set:
set linesize 200 set pagesize 0 select * from table (dbms_xplan.display_cursor( 'dnyrjuumj8psq',1,'TYPICAL ALLSTATS LAST')); exit Either way will work and give you the following example output:
Plan hash value: 4147659309 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1862 (100)| | 3012 |00:08:14.63 | 576K| 553K| | | | | 1 | SORT ORDER BY | | 1 | 2 | 294 | 1862 (1)| 00:00:01 | 3012 |00:08:14.63 | 576K| 553K| 372K| 372K| 330K (0)| |* 2 | FILTER | | 1 | | | | | 3012 |00:08:45.50 | 576K| 553K| | | | | 3 | NESTED LOOPS | | 1 | 2 | 294 | 1861 (1)| 00:00:01 | 3012 |00:08:45.50 | 576K| 553K| | | | | 4 | NESTED LOOPS | | 1 | 2 | 294 | 1861 (1)| 00:00:01 | 3012 |00:08:45.34 | 573K| 553K| | | | | 5 | NESTED LOOPS | | 1 | 2 | 270 | 1859 (1)| 00:00:01 | 3012 |00:08:45.31 | 573K| 553K| | | | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 31 | 3 (0)| 00:00:01 | 439 |00:00:00.27 | 89 | 73 | | | | |* 7 | INDEX RANGE SCAN | T1_IDX4 | 1 | 4 | | 2 (0)| 00:00:01 | 440 |00:00:00.01 | 3 | 1 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 439 | 2 | 208 | 1856 (1)| 00:00:01 | 3012 |00:08:54.15 | 573K| 553K| | | | |* 9 | INDEX RANGE SCAN | T2_IDX4 | 439 | 23118 | | 69 (0)| 00:00:01 | 12M|00:00:54.73 | 38436 | 37439 | | | | |* 10 | INDEX UNIQUE SCAN | T3_IDX1 | 3012 | 1 | | 0 (0)| | 3012 |00:00:00.01 | 9 | 2 | | | | | 11 | TABLE ACCESS BY INDEX ROWID | T3 | 3012 | 1 | 12 | 1 (0)| 00:00:01 | 3012 |00:00:00.01 | 3012 | 2 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Notice the column "E-Rows". It deviates substantially from the column A-Rows, which are the actual rows returned.
So the optimizer is clearly not able to make a good estimation about the number of rows.
Let's add a couple of statements which will give the optimizer better statistics. This particular database is not certified with any of the 12.1 adaptive tuning features, so I will have to feed the optimizer using other methods:
alter session set statistics_level='ALL'; alter session set optimizer_dynamic_sampling=11;
After running it a second time, the following plan is generated:
Plan hash value: 1509429641 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 735K(100)| | 3012 |00:09:44.25 | 572K| 551K| | | | | 1 | SORT ORDER BY | | 1 | 2986 | 428K| 735K (1)| 00:00:29 | 3012 |00:09:44.25 | 572K| 551K| 372K| 372K| 330K (0)| |* 2 | FILTER | | 1 | | | | | 3012 |00:09:44.25 | 572K| 551K| | | | |* 3 | HASH JOIN | | 1 | 2986 | 428K| 735K (1)| 00:00:29 | 3012 |00:09:44.25 | 572K| 551K| 1066K| 1066K| 954K (0)| | 4 | NESTED LOOPS | | 1 | 2986 | 393K| 735K (1)| 00:00:29 | 3012 |00:09:43.01 | 569K| 547K| | | | | 5 | NESTED LOOPS | | 1 | 10M| 393K| 735K (1)| 00:00:29 | 12M|00:00:38.92 | 38488 | 37114 | | | | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 439 | 13609 | 72 (0)| 00:00:01 | 439 |00:00:00.01 | 76 | 0 | | | | |* 7 | INDEX RANGE SCAN | T1_IDX4| 1 | 440 | | 2 (0)| 00:00:01 | 440 |00:00:00.01 | 3 | 0 | | | | |* 8 | INDEX RANGE SCAN | T2_IDX4| 439 | 23118 | | 69 (0)| 00:00:01 | 12M|00:00:28.47 | 38412 | 37114 | | | | |* 9 | TABLE ACCESS BY INDEX ROWID | T2 | 12M| 7 | 728 | 1856 (1)| 00:00:01 | 3012 |00:08:47.65 | 531K| 510K| | | | | 10 | TABLE ACCESS FULL | T3 | 1 | 74866 | 877K| 572 (1)| 00:00:01 | 74866 |00:00:01.22 | 3022 | 3017 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Consider the same column - the E-Rows and the A-Rows, and how much more accurate the optimizer is estimating the rows to be returned.
Notice also that the former of these two plans generates an index lookup on the T3 table, while the ladder generates a full table scan. The FTS proved to be the most efficient, since the runtime dropped from 15 to 9 minutes.
NOTE: You can only get the results you expect when you actually execute the statement. If you try to add the hint along with the EXPLAIN PLAN FOR statement, you will see warnings as these in your output:
Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system levelThese will of course be shown even if you do set the statistics_level to ALL. As Mathew McPeak points out in an aswer on stackoverflow.comThe way you are using it, you are printing the plan of the last statement you explained, not the last statement you executed. And "explain" will not execute the query, so it will not benefit from a gather_plan_statistics hint.
How to use dbms_spm.load_plans_from_sqlset
To load a specific SQL ID with a specific plan hash value from an STS into your SQL Plan Management Base, use:
Documented here
set serveroutput on
declare
l_num_plans PLS_INTEGER;
begin
l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=> 'LongRunningSQL1',sqlset_owner=>'DBADMIN' ,basic_filter=>'sql_id=''83fhwyajathc3'' and plan_hash_value=3643960001 ');
DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/
exit
Documented here
Friday, December 15, 2017
A list of eBS users
To view the users that are installed as a part of an eBS installation, use the following query:
SELECT EBS.ORACLE_USERNAME,U.PROFILE FROM APPS.FND_ORACLE_USERID EBS JOIN DBA_USERS U ON (EBS.ORACLE_USERNAME = U.USERNAME);
Subscribe to:
Posts (Atom)