Hi Experts,
Oracle: 11.2.0.1 64x
OS: Windows 2008 Server 64x
I was check the alert log for one of our databases and I saw the following:
VKRM started with pid=45, OS id=8776
Fri Mar 11 22:00:02 2011
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 11 22:00:23 2011
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 12 01:00:05 2011
I havn't seen the VKRM process before, so I had a look the online doc at http://download.oracle.com/docs/cd/E14072_01/server.112/e10820/bgprocesses.htm for a description of the process. The process is the Virtual Scheduler for Resource Manager Process and it manages the CPU scheduling for all managed Oracle processes.
I noticed that every time the VKRM process appeared in the instance alert log, the begin and end SQL Tuning Advisor processes appear with it. I had a look at the maintenance window for the instance and I saw the following:
SYS@>AppD> SELECT WINDOW_GROUP_NAME,ENABLED,NEXT_START_DATE,COMMENTS FROM DBA_SCHEDULER_WINDOW_GROUPS
WINDOW_GROUP_NAME ENABL NEXT_START_DATE COMMENTS
------------------------------ ----- ---------------------------------------- --------------------------------------------------
MAINTENANCE_WINDOW_GROUP TRUE 14-MAR-11 10.00.00.000000 PM US/EASTERN Window group for Automated Maintenance
ORA$AT_WGRP_OS TRUE 14-MAR-11 10.00.00.000000 PM US/EASTERN auto optimizer stats collection
ORA$AT_WGRP_SA TRUE 14-MAR-11 10.00.00.000000 PM US/EASTERN auto space advisor
ORA$AT_WGRP_SQ TRUE 14-MAR-11 10.00.00.000000 PM US/EASTERN sql tuning advisor
Which means that there are maintenance jobs scheduled.. My question is how do those scheduled tasks (maintenance tasks) relate to VKRM. Is VKRM initiated by the OS instanceSCHEDULERservice? or is this controlled by the oracle job_queue_processes parameter?
Also, we noticed some CPU spike when VKRM kicked in, and soon thereafter the CPU consumption dropped by about 5~7% while the database is still under the same load - how does that relate to the above processes?
Any ideas please?
Thanks