Prepared By : Uday Shah (HOD-IT)
E-Mail : rupareleducation@gmail.com
Contact No : 7600044051
Database Resource Management and Task Scheduling
Question: Automated database
maintenance tasks
·
Oracle database has automated save common maintenance tasks
typically performed by database administrators.
·
These automated maintenance tasks are performed when the
system loaded is expected to be light.
·
You can enable and disable individual maintenance tasks, and
can configure when these tasks run and what resource allocation they are
allotted.
·
Automated maintenance tasks that are started automatically
at regular intervals to perform maintenance operation 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 predefine time intervals that are intended to occurs during a period of low system load.
·
You can optimize maintenance windows based on the resource usage
pattern of your database and disable certain default windows from running.
You can also create your own maintenance windows.
Oracle database has three predefine
automated maintenance tasks.
1. Automatic optimizer statistics collection: Collects
optimizer statistics for all schema objects in the database for which there are
no statistics or only state statistics. The statistics gathered by this task
are used by the SQL query optimizer to improve the performance of SQL
execution.
2. 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.
3. 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.
Question: Managing resources with Oracle
Database Resource Manger.
·
Oracle database resource manager enables you to manage
multiple workloads within a database that are challenging for system and
database resource.
·
When database resource allocation decisions are left to the
operating system, you may encounter the following problems with workload
management.
·
Exclusive overhead: Exclusive overhead results from
operating system context switching between oracle database server process when
the number of server processes is high.
·
Inefficient scheduling: The operating system reschedules
database servers while they hold letches, while 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 session.
Question: 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
procedure and function in the Oracle.
·
The scheduler enables you to control when and where various
computing tasks take place in the enterprise environment.
·
The scheduler helps you effectively mange 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 window needed.
·
The scheduler provides sophisticated, flexible enterprise
scheduling functionality, which you can use to run data base program units for external
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 a job 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 resource by job
class
Controlling job
prioritization based on schedules
Manage and monitor jobs
Execute and mange jobs in a
clustered environment
Question:
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 object follow the naming rules for database
objects exactly and share the SQL namespace with other database objects.
·
Follows SQL naming rules to name scheduler objects in the
DBMS SCHEDULER package.
o
By default, scheduler objects name are uppercase unless they
are surround 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”.
o
This naming rules are also followed in those cases where comma-delimited
lists of scheduler object names are used within the DBMS_SCHEDULER package.
o
You create one or more jobs using the DBMS SCHEDULER CREATE
JOB or DBMS SCHEDULER.
o
CREATE_JOBS procedures or enterprise manager. You use the
CREATE JOB procedure to create a single job.
o
This procedure is overloaded to enable you to create
different types of jobs that are based on different objects.
o
You can create multiple jobs in single transaction using the
CREATE JOBS procedure.
Question:
Administering oracle scheduler
·
You must have the SCHEDULER_ADMIN role to perform all oracle
scheduler administration tasks. Typically, database administration 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 user nam;
·
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 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>
GREANT MANGE 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 group purge scheduler logs.
:: Best Of Luck ::
Please give your comment and share it....
