Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Contact:
Franz
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
13
Types of System Resources and Limits
The Oracle Database Server, can limit the use of several
types of system resources, including CPU time.
You can control each of these resources at various levels:
·
Session Level
·
Call Level
·
CPU Time
·
Logical reads
·
Limiting Other
resources
Session Level
Every time that a user connects to the database a session
is created. A session consumes CPU time
and memory on the server running the Oracle Database. You can set a number of resource limits at
the session level. If a user exceeds a
session-level resource limit, then Oracle terminates or rolls back the current
statement, and returns a message indicating that the session limit has been
reached. When the user sees this message
the user can either enter a COMMIT, ROLLBACK, or disconnect from the
session. Typically a disconnect will
result in the current transaction being commited. Any other operation that the user tires after
this produces an error. Even after the
current transaction is committed or rolled back, the user can accomplish no
more work during the current session.
These limits must be set with care, because they can cause a fair amount
of disruption with regards, to completing a task, on the application.
Call Level
The call level limits are to do with a SQL statement that
is executed against the database. If
excessive processing is caused by the statement, then the Oracle database
Server, halts the processing of the statement; rolls back the statement, and
returns an error to the user. All the
previous statements are still intact.
The user session is still intact.
So the user can execute another statement that will hopefully not use as
many resources as the previous one.
CPU Time
Average calls require a small amount of CPU time. A SQL statement involving a large amount of
data or a runaway query can consume large amounts of CPU time, reducing the
available CPU resources for other processing.
To prevent this uncontrolled use of CPU time you can set
fixed or dynamic limits on the CPU time allowed for each call, either fixed or
dynamic. You can also limit the total
amount of CPU time for a session The CPU
time limits are measured and set in one-hundredth seconds or 0.01 seconds.
Logical Reads
Because I/O is the bottleneck on today’s powerful
computers, I/O is a very expensive resource, and needs to be properly
managed. Run-away processing can quickly
degrade into excessive use of I/O, which can slow the whole system up. Oracle lets you limit the logical data block
reads for each call and for each session.
The logical data block reads include both physical reads from disk, and
logical reads from memory. The limits
are set by the number of block reads per session or per call.
Limiting Other Resources
You can limit other resources at the session level.
·
Limit on the number of
concurrent sessions for each user.
·
Limit on the amount of
idle time for a session. If the limit is
reached then the current process is rolled back, the session aborted, and an
error message posted for the user. This
limit is measured and set in elapsed minutes.
After a session is aborted, you will see it for a while, until the
background process PMON (process monitor), cleans up the aborted session.
·
Limit the elapsed
connect time for each session. If the limit is exceeded, then the current
transaction is rolled back, the session is dropped, and the resources are
returned to the system. The limit is
measured and set in elapsed minutes.
Because Oracle does not continuously check for elapsed time, it may take
up to five minutes, before Oracle picks up a session that has exceeded its
connect time quota, and cleans it up.
·
Limit the amount of
private SGA space (private SQL areas), is used in a session. Obviously this limit would only be an issue
in systems that are configured with the shared server configuration. If the connection mode is dedicated, then the
private SQL areas would be located in the PGA.
This limit is measured and set as number of bytes of memory in the SGA
of an instance. The characters K or M
specify kilobytes or Megabytes.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment