Database Configuration
Database Configuration -- Server Parameter File (SPFILE)
Database Configuration -- Automatic Undo Management
Database Configuration -- Automatic SQL Execution Memory Management
Database Configuration -- Memory Management
Database Configuration -- Server Generated Alerts
Database Configuration -- Database Control
Database Resource Manager
Database Scheduler
Space Management -- Oracle Managed Files
Space Management -- Resumable Space Allocation
Space Management -- Space Management
Space Management -- Reorganization
Performance Tuning -- Automatic Workload Repository (AWR)
Performance Tuning -- Wait Model
Performance Tuning -- Optimizer
Performance Tuning -- SQL Tuning
Performance Tuning -- Automatic Statistics Collection
Performance Tuning -- Performance Diagnostics and Tuning
Database Upgrades and Migrations -- Upgrade
Answers
What is the impact on database performance of turning on all the new database manageability features?
All the new manageability features with the exception of Automatic SGA
Memory Tuning are enabled when the initialization parameter
STATISTICS_LEVEL is set to TYPICAL, which is its default value. When
the STATISTICS_LEVEL parameter is set to TYPICAL and Automatic SGA
Memory Tuning is also enabled, the impact on performance is under 5%.
In fact, of the various applications tested, the worst-case performance
degradation observed was only about 3.4%, while most applications
showed 1% or smaller performance impact. Keep in mind that performance
overhead is relative to a perfectly tuned application. Given that most
real-world applications are not perfectly tuned, they can actually
expect to see a performance improvement in their applications since
many of the new manageability features proactively enhance database
performance without requiring any manual intervention by a DBA or a
performance engineer.
Modified: 01-MAR-04
Ref #: ID-4448
How many CDs are needed to install Oracle Database 10g Release 1?
On
Linux (32-bit), Windows (32 bit), and Solaris (64 bit), a typical
installation of Oracle Database 10g Release 1 requires only one CD. On
other platform such as HP-UX, IBM AIX, HP Tru 64 Unix, etc., a typical
database installation is spread across 2 CDs.
Modified: 27-MAY-04
Ref #: ID-5468
Can the information contained in Automatic Workload Repository (AWR) be used for charge back purposes?
The Automatic Workload Repository (AWR) in Oracle Database 10g does
contain the resource consumption statistics rolled up at the Service
Level. Services are logical abstractions for managing workloads
in 10g and are used to divide workloads into mutually dijoint
groupings.
Out-of-the-box, the Service Stats are captured by AWR once
an hour and can be queried through the DBA_HIST_SERVICE_STAT
view. Also, we provide a report to view the AWR information.
To run the AWR report, run the following from a SQL/Plus session:
@?/rdbms/admin/awrrpt.sql
The script will prompt you for a set of IDs that correspond
to the relevant time periods.
The report will display the DB Time, DB CPU, Physical
Reads, and Logical Reads for each Service. Here is
a sample report output for the section of interest:
Service Statistics DB/Inst: A235/a235 Snaps: 25-28
-> ordered by DB Time
-> us - microsecond - 1000000th of a second
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 3.0 2.9 2 3,612
SYS$BACKGROUND 0.0 0.0 3,096 69,615
a235.regress.rdbms.dev.us.oracle 0.0 0.0 0 0
Modified: 23-JUN-04
Ref #: ID-5568
The GATHER_STATS_JOB (Automatic Statistics Collection) has suddenly stopped to run. What should I check?
First verify whether it is true that the GATHER_STATS_JOB is no longer
running by querying the all_scheduler_job_log table. You'll find
entries in this table for each job run.
If there are no recent entries for the GATHER_STATS_JOB, then make sure
that the windows in the maintenance window group are still properly
opening and closing by querying all_scheduler_window_log.
If as you say the maintenance windows still properly open, then either
the job has been - disabled, or - it's schedule is no longer pointing
to the window group
As SYS issue the following query. The results should be similar. If the
job isn't even there it might have been deleted. To fix the schedule,
or to enable the job you can use the routines in the dbms_scheduler
package, or use EM.
SQL> select schedule_name, enabled, state, last_start_date from
all_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
SCHEDULE_NAME
----------------------------------------------------------
ENABL STATE
----- ---------------
LAST_START_DATE
----------------------------------------------------------
MAINTENANCE_WINDOW_GROUP
TRUE SCHEDULED
16-SEP-04 10.00.02.114558 PM -07:00
Modified: 20-SEP-04
Ref #: ID-5828
Can the new server manageability features be used with Real Application Clusters?
Yes, begining Oracle9i Release 2, all manageability features can be
used with RAC. In Oracle9i Release 1, dynamic SGA feature is not
supported while using RAC.
Modified: 16-OCT-02
Ref #: ID-1876
How is SPFILE different from a PFILE (init.ora file)?
Unlike "init.ora" files, SPFILEs always reside on the server. Since
they are in binary format, the only way change their content is to use
the ALTER SYSTEM.......SCOPE=SPFILE | BOTH command.
It is also not possible to specify the name and location of the SPFILE with the STARTUP command. You can either use the default
SPFILE or use the pfile parameter SPFILE to point the instance to a non-default SPFILE.
Dynamic changes made to initialization parameter value can be made
persistent in the SPFILE since it can be updated directly by the
server.
Finally, all member instances of a Real Application Cluster database use single SPFILE as against using individual PFILEs.
Modified: 06-JUL-01
Ref #: ID-1854
Why should I use a SPFILE?
SPFILEs provide certain benefits which are not available while using PFILEs.
- Since the SPFILE can be updated by the server, the administrator does not
need to manually update the parameter file each time a dynamic change is made.
- Since it resides on the server, the instance can be started without having
a client side parameter file. This eliminates the proliferation of client
side parameter files.
- In the Real Application Cluster environment, there are fewer files to
manage since all instances of a RAC database use the same SPFILE.
- In a future release, RMAN will be able to back up SPFILEs.
Modified: 06-JUL-01
Ref #: ID-1855
How does SPFILE store instance specific parameters?
Parameter specification has been modified in Oracle9i to include
instance qualifier. So a globally applicable parameter is represented
as "*.
" whereas an instance specification parameter (let us say for instance A) can be specified as "A.". It is the administrator's responsibility to format the parameter appropriately before generating the SPFILE.
Modified: 06-JUL-01
Ref #: ID-1856
What is the default search order for the STARTUP command?
If you execute the STARTUP command without any PFILE specification, Oracle
will search the default location (e.g. $ORACLE_HOME/dbs) in the following order:
1. spfile{$ORACLE_SID}.ora
2. spfile.ora
3. init{$ORACLE_SID}.ora (Server Side)
Modified: 06-JUL-01
Ref #: ID-1857
How can I direct my instance to a non-default SPFILE?
This can be done either by creating a symbolic link at the default
location or by creating a text parameter file ("init.ora" file)
containing
SPFILE parameter.
Modified: 06-JUL-01
Ref #: ID-1858
Why is it so difficult to use a non-default SPFILE?
SPFILE is envisioned as being as integral part of the database as
control file is. We would therefore like to discourage people from
using different SPFILEs to start the instance. Since SPFILEs provide us
with an infrastructure for self tuning parameter values,
changing it will mean losing all those information. It is therefore,
highly recommended use single SPFILE throughout the life of an
instance.
Modified: 06-JUL-01
Ref #: ID-1859
How Do I create a SPFILE?
To create SPFILE manually, use the CREATE SPFILE command. This requires
the existence of an text parameter file which must be
created prior to running this command. However if you are using DBCA to
create a database, it will create the SPFILE automatically
for you.
Modified: 06-JUL-01
Ref #: ID-1860
How Do I change the content of a SPFILE when the instance is not available?
Since the contents of an SPFILE can be changed only using the ALTER
SYSTEM command, it can not directly edited when the
instance is unavailable. In such a situation, you will need to export
the contents of theSPFILE to a text parameter ("init.ora) file using
the CREATE PFILE command, make the changes in the PFILE and regenerate
the SPFILE.
Modified: 06-JUL-01
Ref #: ID-1861
How Do I backup my SPFILE?
Currently you will have to backup your SPFILE just like any other OS
file. In a future release, RMAN will be able to back them up.
Modified: 06-JUL-01
Ref #: ID-1862
What happens if I lose my SPFILE?
If you loose the SPFILE while the instance is up, updates to the spfile
will fail with an error message. However the instance will keep
running. You can then restore a backup copy of the SPFILE or create a
new one but it will not be used by the instance until the next
startup.
Modified: 06-JUL-01
Ref #: ID-1863
Does OEM support SPFILEs?
Yes, you can use Oracle9i Enterprise Manager to edit the contents of an SPFILE.
Modified: 06-JUL-01
Ref #: ID-1864
Where can I find out more details about SPFILE?
For further details, please refer to our self paced training at http://st-curr.us.oracle.com/oracle9i/Manage_Server/lesson04.htm.
Modified: 06-JUL-01
Ref #: ID-1865
I am using Automatic Undo Management but still keep on getting ORA-1555 (snapshot too old) error messages. How can I prevent it?
ORA -1555 errors are generated when the undo data required
to create a read consistent snapshot is not available. This can happen due to
two reasons.
1. The undo_retention parameter, which tells the database
instance how long should it preserve a piece of undo data, is set to smaller
than the required value.
2. The undo tablespace is not large enough.
You may therefore need to adjust the setting of the undo_retention parameter
based on the duration of the longest running query. Executing the following
query should give you an idea of what this value should be:
select max(maxquerylen) from
v$undostat;
The view v$undostat displays the undo usage information for
the last one week and hence, should be able to guide you reasonable well in
setting the value of the undo_retention parameter. This must also be backed up
making enough space available in the undo tablespace, since the database may be
forced to dishonor the undo retention in order to avoid a transaction failure.
The EM undo tablespace advisory can tell you how much space is needed to support
a given retention setting.
To summarize therefore, you first need to set the parameter undo_retention
appropriately (using v$undostat) and, then make sure that enough undo space is
available (using EM undo space advisory). Many administrators hate using the
datafile autoextend feature but this is one of the cases where it may help you
avoid ORA-1555 errors if you don't wish to go through the trouble of sizing the
undo tablespace upfront.
Modified: 07-MAY-02
Ref #: ID-1810
How big should the undo tablespace be if I wish to retain undo data for a week?
It is totally dependent on the rate at which the data is being changed
in your database. The Database retains a week worth of statistics about
undo generation which can be used to calculate the space required to
retain the undo data for a given retention period. Oracle9i Enterprise
Manager uses these statistics to provide an advisory which estimates
the size of the undo tablespace for different values of undo retention
time. Another way to estimate the size of the undo tablespace is to
measure the amopunt of archive log data generated in a representative
week.
Modified: 06-JUL-01
Ref #: ID-1823
What is Automatic Undo Management?
Automatic Undo Management provides a new and extremely simple way to
manage undo (Rollback) data in a database. It enables
the database server to automatically manage allocation and management
of Undo space among various active sessions. Administrators
merely need to create an UNDO tablespace with sufficient disk space
instead of manually creating a number of rollback segments and
strategically assigning transactions to rollback segments large enough
to accommodate generated rollback data.
Modified: 06-JUL-01
Ref #: ID-1866
Am I required to use Automatic Undo Management in Oracle9i?
No, you do not. You can still use rollback segments in the manual undo management mode. However we strongly recommend using
Automatic Undo management for better manageability, more efficient space usage and higher performance.
Modified: 06-JUL-01
Ref #: ID-1867
Is it possible to use both automatic as well as manual undo management (Rollback Segments) at the same time?
No, a database can either use automatic or manual undo management but not both at the same time. It is however possible for undo
tablespaces and manually created rollback segments to coexist in a database but only one of them will be used at a time.
Modified: 06-JUL-01
Ref #: ID-1868
Can I have more than one undo tablespace in a database?
While you can create more than one undo tablespace in a
database, an instance can use only one of them at a time. In a Real
Application Clusters environment, you must create at least as many undo
tablespaces as the number of instances in the cluster since an
undo tablespace can not be shared among instances. For non-RAC
databases, there is little reason for having multiple undo
tablespaces.
Modified: 06-JUL-01
Ref #: ID-1869
Is Automatic Undo Management compatible with existing applications or scripts designed to use rollback segments?
By default, any command related to rollback segments will fail in Oracle9i with an error message while using Automatic Undo
Management. The idea behind this behavior is to make users aware of obsolete commands and facilitate changing the
script/application. However, it is possible to direct Oracle9i to suppress such errors by setting a new init.ora parameter
(UNDO_SUPPRESS_ERRORS) in which case Oracle will ignore any command related to rollback segments usage while using
Automatic Undo Management.
Modified: 06-JUL-01
Ref #: ID-1870
Can Oracle Apps use Automatic Undo Management?
Undo
management is purely a database issue with very little visibility to
applications. Therefore if you migrated an existing Oracle
Apps database to Automatic Undo Management, we do not foresee any
problems. However since the database creation scripts used
by Apps installer may not have been updated, your default database may
still be configured to use the manual undo management.
Modified: 06-JUL-01
Ref #: ID-1871
Can the Automatic SQL Execution Memory Management feature be used with Oracle Shared Server?
Automatic SQL Execution Memory Management feature currently can not be used with Oracle Shared
Server (Formerly known as Multithreaded Server). You must use a dedicated server configuration to take advantage of it.
Modified: 06-JUL-01
Ref #: ID-1873
What are the memory requirements for running Oracle Database 10g
Oracle
Database 10g requires 256 MB of memory to run adequately. If Enterprise
Manager Database Control is also running on the same machine, then the
memory requirement goes up to 512 MB.
Modified: 01-MAR-04
Ref #: ID-4466
What's new in 10g for database alerts?
First of all, a majority of the previously supported Enterprise Manager
alerts are now pushed from the database server. The server alerts makes
use of statistic data cached in SGA. Some alerts are issued on demand
during a database operation. So the monitoring cost of the alert
metrics is much lower than in earlier versions. And because of this
improvement in efficiency, some alerts are enabled out of box with a
default threshold, e.g., tablespace space usage alert.
Modified: 01-MAR-04
Ref #: ID-4529
What is the maintenance windows used for? Can I
remove it if I don't want to run automatic statistics collection job
GATHER_STATS_JOB?
The maintenance window is some predefined time window for
system maintenance tasks. By default, not only GATHER_STATS_JOB is
scheduled to run in these windows, certain internal maintenance tasks
also run in them. If the maintenance windows are removed by the DBA,
GATHER_STATS_JOB will not run and the internal maintenance tasks will
run whenever the system deems necessary. So it is better to keep the
maintenance windows around and adjust them to a time where your system
is not under heavy load.
Modified: 01-MAR-04
Ref #: ID-4531
Does initialization parameter STATISTIC_LEVEL setting have any impact on alert monitoring?
Yes. If STATISTIC_LEVEL is set to BASIC, the database server stops
monitoring all threshold-based alerts, i.e., any alerts that can have
thresholds set for them.
Modified: 01-MAR-04
Ref #: ID-4533
How can I manually configure database control?
Database control is automatically configured during database creation,
provided the database was created via Database Configuration Assistant
(DBCA). If the database was created using scripts, DB Control can be
configured manually by running the "emca" script, which usually resides
in $ORACCLE_HOME/bin directory.
Modified: 21-MAY-04
Ref #: ID-5369
I installed the 10g software using "Custom" option and
did not select Enterprise Manager. How can I now install and configure Database Control?
You need to first install the Enterprise Manager software.
This can be done by using another custom install. Once the software is
installed, the "emca" script can be run to configure DB control with
any existing Oracle10g databases. If you are creating a new database,
using DBCA will ensure that DB Control is configured automatically.
Modified: 21-MAY-04
Ref #: ID-5371
How can I configure EM Database Control with pre-10g databases?
You
can not. Database Control is only available with Oracle10g. You need to
use Enterprise Manager Grid Control which can manage all supported
database versions.
Modified: 21-MAY-04
Ref #: ID-5373
My customer would like to use Oracle's
Database Resource Manager whilst also running an operating resource manager such has Sun's
Solaris Resource Manager, or HP's Process Resource Manager. Are there any limitations?
Yes.
Oracle Database Resource Manager can not run concurrently with Solaris Resource
Manager. Results are unpredictable because neither "knows" about the other. But
resource managers are justified based upon predictability!
Modified: 15-JUN-00
Ref #: ID-1643
What are the limitations when an Oracle instance
is managed by a resource manager such as HP's Process Resource Manager or Sun's Solaris
Resource Manager?
When running under operating system resource managers (including SRM), the Oracle
database is supported only if all these conditions are met:
- Each instance is assigned to a dedicated operating system resource manager group or
managed entity.
- The dedicated entity running all the instance's processes must run at one priority (or
resource consumption) level.
- Memory management and process management are not enabled.
- NOTE: Management of individual Oracle processes at different priority
levels is not supported. Severe consequences, including instance crashes, can result. You
can get the same nasty results if SRM is permitted to manage memory on which an Oracle
instance is pinned.
Modified: 15-JUN-00
Ref #: ID-1644
What is the difference between Database Resource
Manager's DEFAULT_CONSUMER_GROUP and OTHER_GROUPS?
If a user is not assigned to a user group, he/she is a member of DEFAULT_CONSUMER_GROUP.
One reason for this consumer group is administrative time savings. For example, 25% of all
users might be explicitly assigned to OLTP, with another 5% specified as members of the
DSS group. The remaining 70% would already be members of the DEFAULT_CONSUMER_GROUP. If
the remaining 70% should be prioritized similarly, and therefore managed as a single
group, no more work is required to assign users to groups. Because of
DEFAULT_CONSUMER_GROUP, everyone belongs to a group (either explicitly-assigned, or
DEFAULT_CONSUMER_GROUP). Like all consumer groups, specifying the DEFAULT_CONSUMER_GROUP
in the active plan is optional.
OTHER_GROUPS is not a group in that users can not be assigned to it. Users map to user
groups one-to-one, and every user belongs to a group (DEFAULT or explicitly assigned, see
above). When a group is not included in the active plan, its members default to the
resources assigned to OTHER_GROUPS. To ensure that this "safety net" is always
in place, and therefore that the administrator explicitly assigns resources to
"everyone else", OTHER_GROUPS must be part of every plan.
Modified: 15-JUN-00
Ref #: ID-1645
How does Oracle Database Resource Manager limit the
allocation of resources to a consumer group? Is it like the UNIX "nice" command,
which Oracle recommends against using?
Oracle Database Resource Manager does not change process priorities at the
operating system level. Database Resource Manager controls CPU usage solely by restricting
the number of running processes.
Modified: 15-JUN-00
Ref #: ID-1646
It appears that the only way Database Resource
Manager can be implemented is with an application designed so that each user has their own
unique user id. Since most large applications run with one schema user, is Resource
Manager practical for any large application?
Making a few changes to a complex application is the practical way to implement Database
Resource Manager. As the Oracle Applications team proved in their 11i implementation, the
changes did not require a redesign, nor lots of new code.
Since Oracle Applications starts a new session anytime responsibilities change (e.g.,
when accessing a different module), not much added effort was required to include the
single-command (Database Resource Manager) procedure call to switch the session's consumer
group assignment at session start-up. Oracle Applications required changes in just one
place to integrate with Database Resource Manager. Oracle developers in the core
applications infrastructure group implemented the required changes in libraries called by
the various application modules.
The Oracle Applications Release 11i support is very granular. DBA's can associate an
individual program to a resource consumer group or a group of programs to a resource
consumer group. They can also associate a class of online users to a resource consumer
group. For instance, they can say all "Order Entry" application users belong to
consumer group 1 and "Human Resources" application users belong to consumer
group 2.
Modified: 16-JUN-00
Ref #: ID-1678
Does the Database Resource Manager work with Oracle Parallel Server and Real Application Clusters?
Yes. The Database Resource Manager governs and allocates resources at
an instance level. It is enabled on each node of the cluster by
specifying the instance specific RESOURCE_MANAGER_PLAN initialization
parameter. Resource plans used by one instance do not affect the
behavior of other instances in the cluster. It is therefore possible to
enable the same resource plan cluster wide or enable different plans on
different nodes.
Modified: 01-MAY-02
Ref #: ID-1752
How does Database Resource Manager use Adaptive
Degree of Parallelism (ADOP)? For example: If I have a low priority
user come in and spawn 6 PQ slaves and then a higher priority user
comes by and needs more resources than available, what happens ?
We use Adaptive Degree of Parallelism (ADOP) to decide the
optimal degree of parallelism for an operation within the constraints
of parallel degree resource directive limit. In other words, if a
resource plan specifies a maximum parallel degree of for a consumer
group as 4, we do not always let a session belonging to that group run
with 4 PQ slaves. We decide the degree of parallelism using ADOP
algorithm based on current system load. As far as this example is
concerned, the degree of parallelism of high priority process will be
determined by the current load on the system. The resource allocation
of the low priority group will be capped to its quota and even though
it may have started with relatively large number of PQ slaves, it will
run comparatively slower due to resource consumption cap.
Modified: 16-JUN-00
Ref #: ID-1785
What are the wait events associated with Database Resource Manager?
All wait events associated with Resource Manager are prefixed with
"resmgr:". For a complete listing of Resource manager events, execute
the following query:
SELECT name from v$event_name where name like 'resmgr:%';
NAME
----------------------------------------------------------------
resmgr:wait in actses run
resmgr:waiting in end wait
resmgr:waiting in check
resmgr:waiting in system stop
resmgr:waiting in enter
resmgr:waiting in shutdown
resmgr:waiting in check2
resmgr:waiting in enter2
resmgr:waiting in run (queued)
resmgr:waiting in end wait2
10 rows selected.
Modified: 28-JUN-00
Ref #: ID-1792
If a user is not in any group, what will happen to that user for CPU and Parallelism?
Any user who is not assigned a consumer group explicitly automatically
becomes a member of "DEFAULT_CONSUMER_GROUP" which is automatically
created at the time of database set up. Thereafter an administrator has
the option including this group in a resource plan if they want to assign
resources to these users explicitly. Otherwise all the consumer groups
which are not explicitly assigned resources in a resource plan share the
resources allocated to "OTHER_GROUPS" (this one is automatically created
as well). For this reason every valid plan must have a resource plan
directive to allocate resources to "OTHER_GROUPS".
Note: Users can not be assigned to "OTHER_GROUPS" directly since it is not
a regular consumer group. Think of it as a place holder for all the
explicitly defined consumer groups in the database which is not included
in the current (active) resource plan.
Modified: 13-JUL-00
Ref #: ID-1795
Does Oracle support dynamic reconfiguration of the host system, for example, adding or taking resource without a system reboot.
Beginning with Oracle9i, Oracle does have the capability to support
dynamic system reconfiguration. In Oracle9i, the Database Server
periodically pings the OS and adjusts the CPU_COUNT parameter, if
required. This allows new workload to take advantage of the newly added
processors. Similarly, the new dynamic memory management features of
Oracle9i (i.e. Dynamic SGA, Automatic SQL Execution Memory Management)
make it possible to alter the amount of memory used by an active
instance without requiring an instance reboot. Using these
capabilities, an Oracle9i Database can adjust itself in response to any
dynamic resource changes at the hardware or OS level.
Unlike CPU, where the changes are detected automatically, the memory
areas must be resized manually using the appropriate ALTER SYSTEM
command before or after a dynamic reconfiguration operation. That is,
one must execute these commands to shrink the SGA and PGA before taking
away memory from an Oracle instance. Conversely, the SGA and PGA must
be grown in order to take advantage of newly added memory. Some
operating system vendors, like Sun, provide wrapper scripts to automate
this process.
Also, since not all configuration parameters, whose values are
calculated based on CPU_COUNT at the time of start up, currently adjust
themselves automatically to changed number of CPUs, any drastic dynamic
change in the number of CPUs may lead to sub-optimal database
performance. In other words, if the number of CPU on a machine is
dynamically changed from 2 to, let us say 32, the database may not be
able to take the full advantage of additional processing power due to
sub-optimal configuration. However Oracle will be able to take full
advantage of dynamically adding CPUs in situations where the number of
CPUs is modestly increased -- e.g., from 8 to 10 or 12.
Providing full dynamic reconfiguration support in the database,
which will remove all such limitations, is a goal that we are currently
working towards.
Everything stated above applies to a RAC database as well. However,
one must be using Oracle9i Release 2 to be able to dynamic resize SGA
in a RAC environment since the dynamic feature is not supported for RAC
in Release 1.
Modified: 05-DEC-02
Ref #: ID-1815
In Oracle9i Database Resource Manager, is the
consumer group switch time based on elapsed time of a running query or
on actual CPU time of that the query has used?
Neither. It's based on the time it would take the query to run if it
were
alone on the system. In other words, it is basically elapsed time minus
any time it has to wait for CPU because a CPU is too busy to run it.
Modified: 05-JUN-01
Ref #: ID-1847
Has any thing been done in Oracle9i to improve the usability of the Database Resource Manager?
It is now possible to administer the Database Resource Manager using
Oracle Enterprise Manager. The GUI will make it extremely easy to
manage even the most complex Resource Manager implementations.
Oracle9i also allows users to quickly create a simple resource plan,
adequate for most environments, using a single command
(DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN). This procedure
significantly simplifies the creation of a resource plan as DBAs no
longer need to execute different commands to create a pending area,
create necessary consumer groups and specify resource allocation
directives.
Modified: 05-JUN-01
Ref #: ID-1849
Does the scheduler support job dependencies?
Not in 10gR1. If you have a customer who is interested in this feature please contact vira.goorah@oracle.com
Modified: 29-JAN-04
Ref #: ID-4358
Can the GET_JOB_ARGUMENT_VALUE procedure also retrieve arguments set with the SET_JOB_ANYDATA_ARGUMENT_VALUE procedure?
There is no get_job_argument_value call. Argument values will be visible through the *_scheduler_job_args views.
Modified: 29-JAN-04
Ref #: ID-4359
Can the information written to the job log be customized?
No.
Modified: 29-JAN-04
Ref #: ID-4360
Can the name of the job be retrieved from within the job?
Yes
Modified: 29-JAN-04
Ref #: ID-4361
Does the Scheduler support instance affinity?
No the Scheduler supports Service `affinity¿ but not instance affinity.
Let's assume a cluster has 5 nodes. Service A consists of nodes 1 and 2
and Service B consists of nodes 3, 4 and 5. You can specify that a job
class has service affinity for Service B. This means that the jobs
belonging to that job class will ONLY run on nodes 3, 4 or 5.
At the job level you can specify Instance Stickiness. This implies that
the Scheduler will attempt to run a job on the instance that it last
ran on. However if this is not possible (instance is either down or
overloaded) the job will run on another instance.
Modified: 29-JAN-04
Ref #: ID-4362
When would you use the SCHEDULER_ADMIN role
instead of the MANAGE SCHEDULER system privilege? What are the
advantages of using one over the other?
The MANAGE SCHEDULER privilege allows a DBA to control resources
associated with the scheduler (create and maintain windows, window
groups and classes) and to set system-wide scheduler settings
(set_scheduler_attribute) and to forceably kill jobs.
The SCHEDULER_ADMIN role includes these privilege as well as ALL other
scheduler system privileges. It would allow a user to execute any
program, execute any class, create jobs, programs and schedules in
anyone's schema (except SYS)and do anything that MANAGE SCHEDULER
allows.
Because it allows the creation of jobs in any schema, it allows the
running of PL/SQL code as any user (except SYS). This makes it a very
powerful role, which should be granted with caution.
Modified: 29-JAN-04
Ref #: ID-4363
Will the output from dbms_output go to the job log?
No the Scheduler does not track output from jobs.
Modified: 29-JAN-04
Ref #: ID-4364
Does the Scheduler have support for daylight saving time?
Yes.
You must set the DEFAULT_TIMEZONE scheduler attribute if you want your
job or window to follow daylight savings adjustments. For instance, if
your database resides in Paris, you would set this to 'Europe/Warsaw'.
Daylight saving adjustments will not be followed if you specify an
absolute offset, for instance '-8:00' would only be correct for half of
the year in San Francisco. If no value is specified for this attribute,
the scheduler uses the time zone of systimestamp when the job or window
is enabled. This is always an absolute offset and will not follow
daylight savings adjustments.
Modified: 29-JAN-04
Ref #: ID-4365
What are the Scheduler attributes?
Default_timezone, Log_history, Max_job_slave_processes.
Modified: 29-JAN-04
Ref #: ID-4366
What privilege is required to manage Scheduler attributes?
The MANAGE SCHEDULER privilege is required.
Modified: 29-JAN-04
Ref #: ID-4367
From within a job while it is running it is possible to disable the job so that its next run will not happen?
Yes using the SET_ATTRIBUTE call you can disable the job
Modified: 29-JAN-04
Ref #: ID-4368
Can the MANAGE SCHEDULER privilege be granted to any user or does the user have to have DBA privileges?
This
privilege can be granted to anybody but is meant for somebody that is a
DBA type person, i.e. one that manages the behavior of the scheduler.
You do not need DBA privileges as well.
Modified: 29-JAN-04
Ref #: ID-4369
Can the job metadata be modified from within the job? Can the job attributes be changed from within the job while it's running?
Yes job attributes can be changed using the SET_ATTRIBUTE call.
Modified: 29-JAN-04
Ref #: ID-4370
What are the object privileges that can be granted on a job?
ALTER
Modified: 09-FEB-04
Ref #: ID-4388
How does retry work with Oracle Scheduler?
Jobs
will be retried a max 6 times or until the next scheduled date of the
job. First retry will be after 1 sec then after 10 sec, then 100 sec
and so on until one day.
Modified: 21-APR-04
Ref #: ID-5108
Which platforms support external jobs?
Supported:
Solaris64, Linux, HP64, TRU64, IA64 HP, IA64 Linux, Solaris x86, MAC, z/Linux, AMD, Windows.
Planned: VMS
Not supported: OS390, AIX
Modified: 23-APR-04
Ref #: ID-5148
What are Oracle Managed Files (OMF) and what type of customers can take advantage of it?
In order to make life simpler for DBAs,
Oracle9i "Oracle Managed File" (OMF) feature simplifies
database administration by eliminating the need for administrators to directly
manage the files of an Oracle database. This feature allows for specifying
operations in terms of database objects. Oracle internally use the standard
operating system (OS) file system interfaces to create and delete files as
needed for tablespaces, online logs and controlfiles. DBAs merely need to
specify the location of these files using new initialization parameters. Oracle
then ensure creation of a file with a unique name and delete it when the
corresponding object is dropped.
OMF allows you to specify one default
location for datafiles and up to five default locations for confrolfile and
online redo log files. It can significantly simply the management of database
files for small test or development databases where performance is not the key
concern. In order to optimize I/O performance, we recommend using a volume
manager with SAME configuration. Refer to our OOW 2000 paper titled "Optimal Storage Configuration Made Easy"
for detailed information on the SAME architecture.
Also, since this feature is dependent on
file system interfaces, it can not be used with raw devices.
Modified: 06-JUL-01
Ref #: ID-1872
Does Resumable Space Allocation require any application change?
Resumable Space Allocation is not enabled by default, it has to be done
so explicitly at the session level. This can be done either by
changing the application or by using a log on trigger.
Modified: 06-JUL-01
Ref #: ID-1874
Which Operations/Utilities support Resumable Space Allocation?
Any DDL or DML statement which causes dynamic space allocation can be executed in the resumable mode. For a detailed list of
such SQL statements, please refer to Oracle9i documentation. Resumable Space Allocation is also supported by export, import and
SQL Loader.
Modified: 06-JUL-01
Ref #: ID-1875
In Oracle 10g, can you un-truncate a table like you can un-drop table using the FLASHBACK DROP command?
There is no way to un-truncate in Oracle 10g.
Modified: 01-MAR-04
Ref #: ID-4517
What is the difference between index shrink and index coalescence?
Shrink performs coalesce first to pack the leaf blocks. Neither shrink nor coalesce reduce the height of the index tree.
Modified: 01-MAR-04
Ref #: ID-4519
What's the differences between defragmentation (coalescing)
and re-organization (rebuilding, moving)?
Defragmentation or coalescing is an in-place data operation that compacts the data file
and frees up unused disk space. No additional disk space is required during the compacting
process. Oracle9i can defragment indexes in place.
Data re-organizations such as rebuilding indexes and moving a table or index requires
additional temporary disk space during the re-organization process. These operations
essentially make a new copy of the object. After the reorganization is complete, the
original object is deleted and its disk space freed up. Oracle9i can re-organizing
indexes, partitioned indexes, IOTs and partitioned IOTs.
Both operations make tables and indexes more contiguous, free up disk space and improve
database performance.
Modified: 26-MAR-02
Ref #: ID-1665
Can Transportable Tablespace work between Oracle8i and Oracle9i?
To use the transportable tablespaces feature, the COMPATIBLE
initialization parameter for both the source and target databases
must be set to 8.1 or higher. If the block size of any tablespace being
transported is different from the standard block size for the
target database, the COMPATIBLE initialization parameter must be set to
9.0 or higher for the target database. You are not required
to be running the same release of Oracle for both the source and target
database. Oracle guarantees that the transportable
tablespace set is compatible with the target database. If not, an error
is signaled at the beginning of the plug-in operation. It is always
possible to transport a tablespace from a database running an older
release of Oracle (starting with Oracle8i) to a
database running a newer release of Oracle (for example, Oracle9i).
Modified: 22-AUG-02
Ref #: ID-1813
What are the Oracle9i online data reorganization capabilities?
Oracle8i introduced many useful online data reorganization features
such as creating, rebuilding, and coalescing indexes online; moving and
reorganizing index-organized tables (IOTs) online. Oracle9i further
extends Oracle's online capabilities: support for IOT secondary
indexes, support for additional index types, and support for analyze
validate structure online. The latest Oracle9i release adds rename
column and constraint online capabilities. But the most exciting online
feature in Oracle9i is the online data redefinition feature. The
feature allows Oracle database administrators to redefine tables
online. For example, administrators can transform a heap table to an
IOT or change a non-partition table to a partition table online.
Moreover, during the redefinition administrators can change the table's
physical or storage attributes, move the table to a new tablespace,
modify the table structure, or transform data in the table. See
additional details in the following technical white papers "Oracle9i
Online Data Reorganization and Redefinition" and "Oracle8i and Oracle9i
Data Reorganization and Feature Comparisons." The white papers are
available at: http://technet.oracle.com/deploy/availability/
Modified: 26-MAR-02
Ref #: ID-1831
What steps are involved in an online table redefinition?
There are five basic steps to redefine a table.
1. Create a new image of the table with all of the desired attributes.
2. Start the redefinition process.
3. Create any triggers, indexes, grants and constraints on the new image of the table.
4. Optionally synchronize and validate data in the new image of the table periodically.
5. Complete the redefinition of the table.
See Oracle documentation for additional details and examples.
Modified: 22-MAY-01
Ref #: ID-1832
When to use online index and IOT reorganization vs. online table redefinition?
Both online IOT reorganization and online table
redefinition can perform similar functions.
Oracle recommends using online index and IOT
reorganization feature for:
- Index reorganization (create, rebuild,
coalesce)
- Index-organized table reorganization (move)
Oracle recommends using online table
redefinition feature for:
- Heap table reorganization
- Changing heap or IOT table physical or
storage attributes (i.e. partition)
- Adding, dropping, and renaming columns in
a table (normal drop or rename column command should be used for simple cases)
- Transforming data in a table
- When administrators want to validate data
before completing a table redefinition or have control over when to complete a table
redefinition (i.e. application upgrade)
Modified: 26-MAR-02
Ref #: ID-1833
What objects are contained in SYSAUX tablespace?
Similar to the SYSTEM tablespace, the SYSAUX tablespace is meant to be
used for storing Oracle System objects. Although the database will not
prevent users from placing non-system objects in the tablespace, it is
in general a good practice for users to avoid doing that.
In Oracle 10g, there are around 20 occupants of the SYSAUX tablespace.
Examples of occupants are: the Automatic Workload repository (AWR),
Oracle Streams, LogMiner, etc.
Each occupant has its own space consumption policy. The current amount
of space occupied by each SYSAUX occupant can be obtained by querying
the V$SYSAUX_OCCUPANTS view. The SPACE_USAGE_KBYTES column shows the
number of Kbytes used by each of the occupants.
Modified: 01-MAR-04
Ref #: ID-4452
How should I size the SYSAUX tablespace properly?
Size
estimates can be obtained differently based on two following scenarios:
i) Scenario A: You are creating a new SYSAUX tablespace (i.e., either
during the creation of a new Oracle 10g database or migrating from a
release prior to Oracle 10g), you should use the following rough
guidelines. For a small system allocate 500MB. A system with 2 CPUs,
average of 10 active* sessions, and 500 user objects (tables and
indexes) is considered a small system. For a medium size system
allocate 2 GB of space. A system with 8 CPUs, average of 20 active
sessions, and 5,000 user objects (tables/indexes) is considered a
mid-size system. For a large system, allocate 5 GB of space. A system
with 32 CPUs, average of 100 active sessions, and 50,000 user objects
(tables/indexes) is considered a large system. Note: An active session
is a session that is executing a call (i.e., doing work). This is not
to be confused with connected sessions, which are basically the number
of logons to the database.
ii) Scenario B: Your Oracle 10g database has been running for more than ½ a day. In this case, you can obtain a more accurate estimate of the
size of SYSAUX when it reaches its steady state. You can use the SQL
script UTLSYXSZ.sql in the $ORACLE_HOME/rdbms/admin directory to
estimate the amount of space needed in SYSAUX tablespace, based on the
current configuration of the database (e.g., INTERVAL and RETENTION
settings of AWR) and an extrapolation from the activities that have
been seen in your database since it was created.
Please keep in mind that the UTLSYXSZ.sql does not take into
consideration user objects that may be created in SYSAUX tablespace.
Modified: 01-MAR-04
Ref #: ID-4455
What is Automatic Workload Repository (AWR)?
The
Automatic Workload Repository (AWR) is an integral piece of the server
manageability functionality introduced in Oracle Database 10g. It
provides a self-managing repository for storing workload statistics
that are crucial for system monitoring and performance analysis. The
AWR resides in the SYSAUX tablespace.
Modified: 01-MAR-04
Ref #: ID-4450
Is there any interface to take a manual AWR snapshot?
The
database system takes automatic snapshots according to the INTERVAL
setting. However, AWR also supports manual snapshots taken by users at
any point in time using Enterprise Manager (AWR link is found under the "Administration" tab in the "Workload" section) or the PL/SQL command
line api. The following PL/SQL api can be used to take manual AWR
snapshots:
execute dbms_workload_repository.create_snapshot();
Modified: 01-MAR-04
Ref #: ID-4457
How do I obtain an AWR report for a certain time period?
First,
you need to identify the AWR snapshots that cover your target time
period. This can easily be done by going to the Enterprise Manager AWR
Snapshots page. Command line users can query the DBA_HIST_SNAPSHOT view
to identify the best set of AWR snapshots. An AWR report, for the pair
of snapshots, can then be obtained using Enterprise Manager "View
Report" feature or by executing the SQL*Plus AWRRPT.SQL script which
can be found in the $ORACLE_HOME/rdbms/admin directory.
Modified: 01-MAR-04
Ref #: ID-4459
What are wait classes?
A wait class is a logical collection of a set of wait events. Wait
classes are extremely useful in finding which component or resource of
the database is having performance problems. In Oracle 10g, there are
12 wait classes namely, Administrative, Application, Cluster,
Commit,Concurrency, Configuration, Idle, Network, Other, Scheduler,
System I/O, and User I/O.
For any given wait event, you can find the wait classes it belongs to from the V$EVENT_NAME view using the following SQL.
select wait_class from V$EVENT_NAME where name = '';
Modified: 26-MAR-04
Ref #: ID-4790
What improvements are included in the Enhanced
Wait Model introduced in Oracle 10g to facilitate proactive performance
diagnosis?
The Enhanced Wait Model introduced in Oracle 10g is a core building
block for many of the performance management features introduced in
this release. Below is a brief list of the important stats improvements
in the performance area:
-- Wait events are classified into wait classes (see the v$event_name)
-- v$active_session_history view provides a sampled history of active
sessions in the system. -- v$sys_time_model and v$sess_time_model views
divide up database time in a different manner as compared to the wait
model. -- v$sql view contains additional columns that give the time
spent by the SQL statement in the wait classes as well as in the PL/SQL
and java interpreters. -- v$sys_metrics view provides rates for a bunch
of statistics based on 3 fixed time intervals (e.g., "logins/sec" for
the last 15sec, 1min, etc.) In Oracle 10g stats are persisted onto disk
automatically, this repository is called "Automatic Workload
Repository". Automatic performance diagnosis is now done in the
database by the Automatic Database Diagnostic Monitor (ADDM). It looks
at the above-mentioned statistics in addition to other information to
come up with a list of top performance issues and recommendations to
fix them. Oracle Enterprise Manager exposes these features and also
provides nice live graphs for performance drilldowns using the new
stats.
Modified: 01-MAR-04
Ref #: ID-4464
Is the rule based optimizer still present and supported in Oracle 10g?
The
rule based optimizer (RBO) is present, but not supported, in Oracle
10g. You should plan to start the migration to the cost based optimizer
(CBO) soon.
Modified: 01-MAR-04
Ref #: ID-4507
What resources are available to encourage customers to move to the cost based optimizer (CBO) and ease their migration?
Metalink
provides links to two white papers describing the advantages of the CBO
as well as RBO-to-CBO migration techniques. We also have an updated
white paper on CBO migration written for Oracle World, which is
available at:
http://files.oraclecorp.com/content/AllPublic/Users/Users-G/george.lumpkin-Public/cbo_migration_new.doc
Modified: 01-MAR-04
Ref #: ID-4509
In Oracle 10g, are SQL Profiles and Stored Outlines exported/imported during a full database export/import?
Yes.
Modified: 01-MAR-04
Ref #: ID-4511
What indexes types does the SQL Access Advisor recommend?
For Oracle 10g, the SQL Access Advisor only advises on bitmap and
b-tree index types. However, it does recognize the existence of the
other index types.
Modified: 01-MAR-04
Ref #: ID-4513
What dimensions are used to identify top SQL in the AWR?
For
Top SQL, we collect top SQL on the following dimensions:
-- CPU Time
-- Elapsed Time
-- Parse Calls
-- Sharable Memory
-- Version Count
When initialization parameter STATISTICS_LEVEL = TYPICAL, we collect
the Top 30 SQL statements for each criteria. When STATISTICS_LEVEL =
ALL, we collect the top 100 SQL statements for each criteria.
For top Segments, we on the following dimensions:
-- Logical reads
-- Wait count
-- RAC stats
-- Largest space used delta
-- Round-robin scheme to make sure old space statistics are eventually
flushed
We capture top 100 top segments for each dimension.
Modified: 01-MAR-04
Ref #: ID-4515
We had fake index functionality in Oracle9i for
users to test indexes without implementing them. Is this still
available in Oracle 10g?
We use fake index and fake materialized view (MV) functionality within
Access Advisor to evaluate our candidates. In fact, each of our
recommendations will have been validated using the fake artifacts.
Thus, by using Access Advisor, users are benefiting from this
capability automatically.
Modified: 05-APR-04
Ref #: ID-4972
Why do I not see a report in SQL*PLUS after a call to dbms_sqltune.report_tuning_task?
You need to set up SQL*PLUS environment properly before you can see the
report. You can do this by using the following command: 'set long
1000000'.
Modified: 05-APR-04
Ref #: ID-4974
Why does the report I generate through a call to
dbms_sqltune.report_tuning_task not include information about every SQL
statement in the STS that was tuned?
This can happen either because you specified an object_id or
result_limit argument to the function, or because SQL tuning ran out of
time when it was tuning the STS. To remedy this, re-tune the STS, this
time passing a bigger `time_limit¿ argument to
dbms_sqltune.create_tuning_task. Tuning tasks default to a time limit
of 1800 seconds (30 minutes), which may not be enough for some tuning
sets.
Modified: 05-APR-04
Ref #: ID-4976
When I try to drop my SQL Tuning Set (STS), I get error "ORA-13757: SQL tune workload is active."
Something else is currently using this SQL Tuning Set
(notified through a call to dbms_sqltune.add_sqlset_reference). If you
have created a tuning task using this SQL Tuning Set, you will need to
drop the task before you can drop the Tuning Set.
Modified: 05-APR-04
Ref #: ID-4978
Why is the SQL Profile I created not used when I execute the SQL statement?
The optimizer is not forced to use a SQL Profile -- it will
only do so if the plan suggested by the profile is better than the one
it found itself. Usually SQL Profiles are only ignored if the profile
was created when the object statistics were stale, but were then later
gathered, thus obviating the existing profile. To avoid this, re-run
SQL Tuning Advisor after gathering statistics to get the updated SQL
Profiles.
Modified: 05-APR-04
Ref #: ID-4980
Can the SQL Profile created on one machine be moved or exported to another?
The short answer is no. The only exception to this is when
you do a full database export/import. In this case, everything along
with SQL Profiles will be imported into the target database. Moving SQL
Profiles from one database to another, if it were allowed, will not
necessarily produce the same execution plan in the target database as
the source database. This is because SQL Profiles, which are statistics
pertaining to a particular SQL statement, are very specific to the SQL
statement and database environment, and unless both the databases have
identical environments, moving SQL profiles between them will not
produce the behavior you may be expecting, as we cannot guarantee that
the optimizer will produce the same plan in the target database as the
source database. This is why we only move SQL Profiles for full
database exports/imports.
Modified: 08-APR-04
Ref #: ID-5031
In Oracle 10g, does the automatic statistics collection job analyze all the objects in the database every night?
No.
It only analyzes the objects that do not have statistics or whose
statistics are stale. The statistics are considered to be stale when
total amount of modifications to the table reach 10% or if the table is
truncated since last analyze.
Modified: 01-MAR-04
Ref #: ID-4536
I want to manually analyze a table in the same way as the gather_stats_job would do. How would I do that?
You
would use the gather_table_stats procedure. The default values of the
gather_table_stats procedure are now the same as the job in 10.1.0.2.
Modified: 01-MAR-04
Ref #: ID-4538
Can I specify the job to skip over a particular table?
Yes. Use the LOCK_TABLE_STATS procedure. (Use the UNLOCK_TABLE_STATS procedure to unlock.)
Modified: 01-MAR-04
Ref #: ID-4540
How do I force the automatic statistics collection job to run on demand?
Run the following stmt:
execute dbms_scheduler.run_job('gather_stats_job');
Modified: 01-MAR-04
Ref #: ID-4542
For some reason, the new statistics collected by the job results in sub-optimal plan. How do I go back to the old statistics?
You can use the restore procedures of dbms_stats package to restore statistics as of a specific time.
Modified: 01-MAR-04
Ref #: ID-4544
How do I disable automatic statistics collection?
You can disable the job using the Enterprise Manager
scheduler interface or alternatively, you could execute the following
at the command line:
execute dbms_scheduler.disable('gather_stats_job', true);
Modified: 01-MAR-04
Ref #: ID-4546
What is Database time or DB time?
Database time (shortly called as DB time) is the total time the database
spent in processing SQL statements from user sessions. It does not
include any idle time spent in a user session, for example, time spent
waiting for the next user request. It does not include any background
processing time.
From the user's point of view, this is
Bookmark