DBA Hub

📋Steps in this guide1/2

Concurrent Statistics Collection in Oracle Database 12c Release 1 (12.1)

Improve the speed of statistics collection from Oracle database 12c Release 1 onward.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Enabling Concurrent Statistics Collection

From a user perspective, the concurrent statistics collection functionality is very simple. You set the global preference to the required value using the package and Oracle determines if concurrency is appropriate and if so, the level of concurrency to use. The preference is set to OFF by default, as shown below. This can be altered if required using the following command. The allowable values for the preference are shown below. - - Enabled for manual statistics collection only. - - Enabled for automatic statistics collection only. - - Enabled for both manual and automatic statistics collection. - - Disabled.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CONN sys@pdb1 AS SYSDBA

SELECT DBMS_STATS.get_prefs('CONCURRENT') FROM dual;

DBMS_STATS.GET_PREFS('CONCURRENT')
----------------------------------------------------------------------------------------------------
OFF

SQL>

BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'CONCURRENT',
    pvalue  => 'ALL');
END;
/
2

Common Errors

As mentioned previously, concurrent statistics collection requires a combination of the job scheduler, advanced queuing and resource manager. The ability to interact with these features is not granted by default, so if you enable concurrent statistics collection, users may have difficulty gathering statistics themselves, even for objects they own. This issue is show below. The user must be granted the role, or more sensibly the , , privileges. Notice the error message has changed. It is now telling us that resource manager is not enabled. Assigning a resource plan is a prerequisite for using this functionality. As we can see, with resource manager enabled and the required privileges present, we are able to gather statistics again. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CONN test/test@pdb1

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 10000;

EXEC DBMS_STATS.delete_table_stats(USER, 'TAB1');

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

SQL>

CONN sys@pdb1 AS SYSDBA
GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO test;

conn test/test@pdb1
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

*
ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: Resource Manager is not
enabled.
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

SQL>

CONN sys@pdb1 AS SYSDBA
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

conn test/test@pdb1
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!