I am testing Concurrent Statistics Gathering using directions from About Concurrent Statistics Gathering. However, I don't see any DBMS_SCHEDULER jobs. I tried it with PDB, CDB and finally with non-CDB. This one it with non-CDB.Session 1:
SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 19 13:35:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SYS@SOL12 > select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SYS@SOL12 > ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DEFAULT_PLAN';
System altered.
SYS@SOL12 > SHOW PARAMETER PROCESSES;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 300
SYS@SOL12 > BEGIN
2 DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','ALL');
3 END;
4 /
PL/SQL procedure successfully completed.
SYS@SOL12 > EXEC DBMS_STATS.GATHER_DATABASE_STATS
PL/SQL procedure successfully completed.
SYS@SOL12 >
While stats is collected I am checking DBA_OPTSTAT_OPERATION_TASKS in Session 2:
SYS@SOL12 > SELECT OPID, TARGET, JOB_NAME,
2 (SYSTIMESTAMP - START_TIME) AS elapsed_time
3 FROM DBA_OPTSTAT_OPERATION_TASKS
4 WHERE STATUS = 'IN PROGRESS'
5 /
no rows selected
SYS@SOL12 > /
no rows selected
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------------------------
602 SYS.AQ$_ALERT_QT_ +000000000 00:00:00.152000
S
SYS@SOL12 > set linesize 132
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------------------------
602 SYS.AQ$_SCHEDULER +000000000 00:00:00.382000
$_EVENT_QTAB_S
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.CDB_RESOURCE_ +000000000 00:00:00.071000
PLAN_DIRECTIVE$
SYS@SOL12 > /
no rows selected
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 CTXSYS.DR$INDEX_O +000000000 00:00:00.014000
BJECT
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 CTXSYS.DR$USER_EX +000000000 00:00:00.023000
TRACT_TKDICT
602 CTXSYS.SYS_C00507 +000000000 00:00:00.001000
3
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.ERROR$ +000000000 00:00:00.199000
SYS@SOL12 > /
no rows selected
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.FGACOL$ +000000000 00:00:00.006000
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.FILE$ +000000000 00:00:00.181000
602 SYS.I_FILE2 +000000000 00:00:00.012000
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.HISTGRM$ +000000000 00:00:00.545000
602 SYS.I_H_OBJ#_COL# +000000000 00:00:00.149000
SYS@SOL12 > /
OPID TARGET JOB_NAME ELAPSED_TIME
---------- ----------------- -------------- ---------------------------------------------------------
602 SYS.HIST_HEAD$ +000000000 00:00:00.879000
602 SYS.I_HH_OBJ#_COL +000000000 00:00:00.336000
#
SYS@SOL12 > select distinct job_name from DBA_OPTSTAT_OPERATION_TASKS
2 /
JOB_NAME
--------------
SYS@SOL12 > select job_name from dba_scheduler_running_jobs;
no rows selected
SYS@SOL12 > /
no rows selected
SYS@SOL12 > /
no rows selected
SYS@SOL12 > /
no rows selected
SYS@SOL12 >
As you can see, there are no DBMS_SCHEDULER jobs. Same works in 11.2.0.3.0 even withour resource manager. Session 1:
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 08:27:36 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl > ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
System altered.
SYS@orcl > SHOW PARAMETER PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150
SYS@orcl > BEGIN
2 DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
3 END;
4 /
PL/SQL procedure successfully completed.
SYS@orcl > EXEC DBMS_STATS.GATHER_DATABASE_STATS
Session 2:
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 08:28:11 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl > select job_name from dba_scheduler_running_jobs;
no rows selected
SYS@orcl > /
JOB_NAME
--------------
ST$SD2077_134
SYS@orcl > /
JOB_NAME
--------------
ST$SD2077_288
ST$SD2077_289
ST$SD2077_287
ST$SD2077_134
ST$SD2077_290
SYS@orcl > /
JOB_NAME
--------------
ST$SD2077_134
SYS@orcl > /
JOB_NAME
--------------
ST$SD2077_134
SYS@orcl > /
JOB_NAME
--------------
ST$SD2077_134
SYS@orcl >
I used DBA_SCHEDULER_RUNNING_JOBS since there is no
DBA_OPTSTAT_OPERATION_TASKS in Oracle 11G.
SY.