--------------------------------------------------------------------------------------------------------------------------
Prepared By : Uday Shah - HOD(IT)
Contact No : 7600044051
Email : rupareleducation@gmail.com
· Automated Database Maintenance Tasks
Oracle Database has automated several
common maintenance tasks typically performed by database administrators.
These automated maintenance tasks are
performed when the system load is expected to be light.
You can enable and disable individual
maintenance tasks, and can configure when these tasks run and what resource
allocations they are allotted.
Automated
maintenance tasks are tasks that are started automatically at regular intervals
to perform maintenance operations on the database.
An
example is a task that gathers statistics on schema objects for the query
optimizer. Automated maintenance tasks run in maintenance
windows, which are predefined time intervals that are intended to
occur during a period of low system load.
You
can customize maintenance windows based on the resource usage patterns of your
database, or disable certain default windows from running.
You
can also create your own maintenance windows.
Oracle
Database has three predefined automated maintenance tasks:
Automatic Optimizer Statistics
Collection — Collects optimizer statistics for all schema objects
in the database for which there are no statistics or only stale statistics. The
statistics gathered by this task are used by the SQL query optimizer to improve
the performance of SQL execution.
Automatic Segment Advisor — Identifies segments that have space available for
reclamation, and makes recommendations on how to defragment those segments. You can also run the Segment Advisor manually
to obtain more up-to-the-minute recommendations or to obtain recommendations on
segments that the Automatic Segment Advisor did not examine for possible space
reclamation.
Automatic SQL Tuning Advisor — Examines the performance of high-load SQL statements,
and makes recommendations on how to tune those statements. You can configure
this advisor to automatically implement SQL profile recommendations.
· About Oracle Database Resource Manager
Oracle Database Resource Manager (the
Resource Manager) enables you to manage multiple workloads within a database
that are contending for system and database resources.
When database resource allocation
decisions are left to the operating system, you may encounter the following
problems with workload management:
Excessive overhead
Excessive overhead results from operating
system context switching between Oracle Database server processes when the
number of server processes is high.
Inefficient scheduling
The operating system deschedules database
servers while they hold latches, which is inefficient.
Inappropriate allocation of resources
The operating system distributes resources
equally among all active processes and cannot prioritize one task over another.
Inability to manage database-specific
resources, such as parallel execution servers and active sessions
· Oracle Scheduler Concepts
Oracle Database includes Oracle Scheduler,
an enterprise job scheduler to help you simplify the scheduling of hundreds or
even thousands of tasks.
Oracle Scheduler (the Scheduler) is
implemented by the procedures and functions in the DBMS SCHEDULER PL/SQL package.
The Scheduler enables you to control when
and where various computing tasks take place in the enterprise environment.
The Scheduler helps you effectively manage
and plan these tasks. By ensuring that many routine computing tasks occur
without manual intervention, you can lower operating costs, implement more
reliable routines, minimize human error, and shorten the time windows needed.
The Scheduler provides sophisticated,
flexible enterprise scheduling functionality, which you can use to:
Run database program units
Run external executables, executables that
are external to the database
Schedule job execution using the following
methods:
Time-based scheduling
You can schedule a job to run at a
particular date and time, either once or on a repeating basis.
Event-based scheduling
You can start jobs in response to system
or business events.
Dependency scheduling
You can set the Scheduler to run tasks
based on the outcome of one or more previous tasks.
Prioritize jobs based on business requirements.
Controlling Resources by Job Class
Controlling Job Prioritization based on Schedules
Manage and monitor jobs
Execute
and manage jobs in a clustered environment
· Scheduling Jobs with Oracle Scheduler
You operate Oracle Scheduler by creating
and managing a set of Scheduler objects.
Each Scheduler object is a complete
database schema object of the form schema.name.
Scheduler objects follow the naming rules
for database objects exactly and share the SQL namespace with other database
objects.
Follow SQL naming rules to name Scheduler
objects in the DBMS SCHEDULER package.
By default, Scheduler object names are
uppercase unless they are surrounded by double quotes. For example, when
creating a job, job_name => 'my_job' is the same as job_name
=> 'My_Job' and job_name => 'MY_JOB', but different
from job_name => '"my_job"'.
These naming rules are also followed in
those cases where comma-delimited lists of Scheduler object names are used
within the DBMS_SCHEDULER package.
You create one or more jobs using
the DBMS SCHEDULER.CREATE JOB or DBMS SCHEDULER.
CREATE_JOBS procedures or Enterprise
Manager. You use the CREATE JOB procedure to create a single job.
This procedure is overloaded to enable you
to create different types of jobs that are based on different objects.
You can create
multiple jobs in a single transaction using
the CREATE JOBS procedure.
Administering Oracle
Scheduler
You must have the SCHEDULER_ADMIN role
to perform all Oracle Scheduler administration tasks. Typically, database
administrators already have this role with the ADMIN option as part
of the DBA role. For example, users SYS and SYSTEM are
granted the DBA role. You can grant this role to another
administrator by issuing the following statement:
SQL> GRANT SCHEDULER_ADMIN TO username;
Because the SCHEDULER_ADMIN role
is a powerful role allowing a grantee to execute code as any user, you should
consider granting individual Scheduler system privileges instead. Object and
system privileges are granted using regular SQL grant syntax. An example is if
the database administrator issues the following statement:
SQL> GRANT CREATE JOB TO scott;
After this statement is
executed, scott can create jobs, schedules, programs, file watchers,
and credentials in his schema. Another example is if the database administrator
issues the following statement:
SQL> GRANT MANAGE SCHEDULER TO adam;
After
this statement is executed, adam can create, alter, or drop windows,
job classes, or window groups. He will also be able to set and retrieve
Scheduler attributes and purge Scheduler logs.
