Transcript Proper Care and Feeding of your Oracle MDB

Proper Care and Feeding of
your Oracle MDB
Recommendations for General Oracle MDB Maintenance
Read the notes on the foils!
DRAFT Copy – Last Revision September 18, 2006
Overview
- Setting up the Oracle MDB
- Basic Performance Considerations
- Oracle Redo Logs
- Oracle Tablespaces
- Regular Maintenance Tasks
- Backup/Recovery Operations
- Tuning & Optimization
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Disclaimer
- The purpose of these guidelines is to provide general
recommendations for the day-to-day maintenance of an
Oracle based MDB
- This presentation is not a substitute for the knowledge
and skills of an Oracle DBA, rather, its aim is to assist an
Oracle novice in performing several of the more basic
tasks.
- For additional guidelines, consult your Oracle DBA.
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Setting Up an Oracle MDB
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance - Overview
- To enable users and applications to view or update data in the
database, Oracle must start a set of processes, called background
processes, and must allocate some memory to be used during
database operation
- The background processes and memory allocated by Oracle together
make up an “instance”
- An instance must be started to read\write information to the database
- The properties of a database instance are specified using instance
initialization parameters. When the instance starts, an initialization
parameter file is read and the instance is configured accordingly
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Setting Up an Oracle MDB
- Install Oracle on a disk with sufficient available space
- Setup an Oracle MDB by running the following command:
- setupmdb –ORA_CONNECT_ID=SID –
ORA_SYS_USER=sys ORA_SYS_PSWD=SYS_PSWD –MDB_NAME=mdb –
MDB_ADMIN_PSWD=MDBADMIN_PSWD –
ORA_TBLSPACE_PATH=X:\ORACLE\PRODUCT\10.2.
0\ORADATA\ORCL
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Basic Performance
Considerations
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance - Performance
Considerations
- For optimal performance the Oracle System Global Area (SGA)
should be large enough to avoid frequent disk reads and writes.
- Oracle SGA has many subcomponents but the following are the most
important ones to us:
- Buffer cache – user processes connected to the database share
access to the buffer cache
- Shared pool – caches information that can be shared among users
- Redo log buffer – improves performance by caching redo
information used for instance recovery
- Large pool – optional area used to buffer large I/O requests
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – Fine Tuning SGA
- To view and modify initialization parameters first select
“Administration” from the Database Home Page
- Then, select “All Initialization Properties” link…
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – Fine Tuning SGA
- This displays a list of CURRENT initialization
parameters
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – Fine Tuning SGA
- To adjust the SGA size, enter a new value for the SGA_TARGET parameter
and click “Apply” at the bottom of the page. 1GB should be a good start for
the MDB as that size also accommodates some growth
- Make sure the Apply changes in SPFile to the current running
instance(s) checkbox is selected. For static parameters, you must restart the
database
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – Fine Tuning SGA
- After adjusting the SGA size, you will see something like this:
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – SGA Memory Parameters
- If Automatic Shared Memory configuration is enabled, Oracle automatically
configures SGA subcomponents - especially buffer cache and shared pool
- Automatic Memory tuning is recommended by Oracle
- If Automatic Shared Memory Management is disabled, it can be enabled on
the Memory Parameters page
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – SGA Memory Advice
- Oracle has a nice feature called Memory Advisor. If you
want to manually configure an SGA subcomponent,
Automatic Shared Memory Mgt. can be disabled and the
Memory Advisor used in order to make informed decisions
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Instance – SGA Memory Advice
- Current SGA size is 1GB – the maximum is 1.5GB. Note
how Advisor shows that extending the current SGA size to
1.5GB will not improve performance
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Redo Log
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Redo Log
- Oracle databases include two or more redo logs
- Click the Redo Log Groups link on the Administration
page to display information for these logs
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Redo Log - Multiplexing
- Redo log files can be multiplexed to enable recovery from failure
involving the redo log files
- Multiplexing also recommended as # of users\apps in db increases
- Redo logs should be stored on different disks
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Redo Log - Multiplexing
- All Redo Log Groups should have same number of
members
- Although not required, it is recommended that all Redo
Log Groups have same number of members
- An Oracle database should have at least 2 Redo Log
Groups
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Tablespaces
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Tablespaces
- An Oracle database consists of one or more tablespaces, which are logical
structures of one or more data files or temp files
- In planning your Oracle MDB installation make sure there is enough space
available for both the MDB data and index tablespaces
- Tablespaces have several parameters that can be viewed by clicking on the
Tablespaces link under Storage on the Administration page:
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Modifying Oracle Tablespaces
- Oracle tablespaces can be modified – to extend them, set
them to automatically extend, take them offline, etc.
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Regular Maintenance Tasks
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Reclaiming Space
- Inserts, updates, and deletes can create empty spaces that are not
big enough to be reused
- In addition to wasting lots of space (potentially), this can also impact
performance
- To reclaim this empty space use the following options:
- Shrink Operation – data is compacted to front of the table. Free
space can either be returned to the tablespace or kept in the table
for future inserts.
- Reorganization Operation – objects are recreated in a different
part of the tablespace. Data is also compacted to the front of the
table, but the tablespace must have free space equal to the size of
the object being reorg’ed.
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Reclaiming Space – Segment Advisor
- Oracle has a nice feature called “Segment Advisor”
- Segment Advisor helps identify tablespaces and tables
that have unused space that can be reclaimed
- Usage and growth statistics are analyzed by Segment
Advisor at regular intervals, but can also be executed
manually
- Results are summarized on the Oracle Enterprise Mgr.
Home page:
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Index Rebuilding
- Oracle 10g will automatically detect and rebuild suboptimal indexes – Automatic Maintenance Tasks
- Oracle shops still schedule periodic index rebuilding –
many IT managers require it because it is a low-risk job
- Oracle B-tree indexes are quite complex, and to really
gather scientific evidence we must examine all of the
index metrics
- Unfortunately, Oracle does not make it easy to capture
information such as which indexes need to be rebuilt
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Oracle Index Rebuilding
- dba_indexes view provides a great deal of information
on indexes – this view can be queried in order to find out
which indexes are MDB indexes
- index_stats also has information on indexes – this
table is populated by the analyze index index_name
validate structure command
- index_stats gets overlaid after each analyze index
index_name validate structure command
- ALTER INDEX INDEX_NAME REBUILD can be used to
rebuild indexes with HEIGHT > 2
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup/Recovery Operations
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup and Recovery Options
- The Oracle backup makes copies of data files, control files, and redo
logs. Backups can be:
- Consistent – no changes in the redo log that have not already
been applied to the data files at the time of the backup – database
must have been shut down normally
- Inconsistent – made while the database is open – i.e. redo logs
contain changes not applied to the datafiles
- Recovering an Oracle database consists of updating database files
restored from a backup with the changes made to the database since
the backup using redo log files
- When restoring data files from a consistent backup, the database
can be opened immediately
- When restoring data files from an inconsistent backup, the
database cannot be opened until changes in the redo logs are
applied to the datafiles
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Basic Backup and Recovery
Recommendations
- Use a flash recovery area – this automates storage
management for most backup-related files
- The flash recovery area should be stored on a different
disk than the working set of database files
- Run the database in ARCHIVELOG mode, so that it is
possible to perform online backups and have data
recovery options such as complete and point-in-time
media recovery
- Use the flash recovery area as an archived log destination
for the database
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Configuring the Flash Recovery Area
- Create a directory to hold the Flash Recovery Area
- Click “Maintenance” from the Database home page
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Configuring the Flash Recovery Area
- Click on ”Recovery Settings.” In the Flash Recovery
section, enter the path to the flash recovery area location
as well as its size
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Configuring ARCHIVELOG Mode
- Click on “Maintenance” from the Database home page
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Configuring ARCHIVELOG Mode
- In the Media Recovery section, check ARCHIVELOG Mode
(if not already checked)
- Best practice is to use the flash recovery area as the redo
log archiving destination
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Settings
- Once Flash Recovery Area is configured and database is running in
ARCHIVELOG mode, you can set the following backup policies:
- How backups are stored
- What data is backed up
- How long backups are retained
- How to optimize backup performance, etc
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Settings
- Database backups created by Recovery Manager (RMAN) can be
stored in two forms:
- Image copies – exact copies of the files they back up. This can be
accomplished by simply copying OS files, but image copies through
RMAN are recorded by the Enterprise Manager in the RMAN
repository, and RMAN can only use files in restore operations if
they are recorded in the repository
- Backup sets – individual BACKUP commands can produce one or
more backup sets. Each backup set consists of several physical
files called backup pieces
- RMAN supports “parallelism” - the use of multiple channels and
server sessions to carry out the work of one backup or recovery task.
Needless to say, parallelism can greatly increase performance on
backup and recovery tasks
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Settings – Device Properties Page
- To direct backups to the Flash Recovery Area, leave the
Disk Backup Location on the Backup Settings page blank
- The preferred Disk Backup Type is “backup set” because
RMAN uses unused block compression to save space
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Settings – Device Properties Page
- Once the above settings are all set, click on Test
Disk Backup
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Policy Settings
- Here you can set policies like backup control and server
parameter files, tablespaces to exclude, etc
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Policy Settings
- Backup Policy page includes several useful options:
- Automatically backup the control file and server parameter file
(SPFILE) with every backup and database structural change – these
are small files that should be backed up up often as they are
fundamental to the database operation
- Optimize the whole database backup by skipping unchanged files
such as read-only and offline datafiles that have been backed up –
this option saves space
- Enable block change tracking for faster incremental backups –
improves performance of incremental backups
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Policy Settings - Exclusions
- You can specify tablespaces to exclude from backup
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backup Policy Settings - Retention
- Retention policies include the following settings: :
- Retain All Backups
- Retain backups that are necessary for a recovery to any time within
the specified number of days (point-in-time recovery)
- Retain at least the specified number of full backups for each
datafile
- For now, choose Retain at least the specified number of full backups
for each datafile with redundancy set to 1, and make sure the Host
Credentials section has proper credentials
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
- Click Schedule Backup under Backup/Recovery on the
Maintenance page:
- The following examples uses customized backups in
order to schedule a one-time or repeated backup of the
MDB
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
- Select “Whole Database” in the Customized Backup
section to make a full backup of the MDB – make sure
credentials are valid
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
- Select a backup destination. Oracle recommends backing up to disk
whenever possible to minimize recovery time. Plus, backups created
on disk can be moved to tape later
- Click Next and give the backup job name and description
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
- In the Schedule section of the Schedule Customized Backup specify
when to start the backup and how often to repeat it (in case the
backup job is being scheduled for some time in the near future). Here
we’ve used the default start time of “Immediately”
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Backing Up the MDB
- Review backup job settings and submit it
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Restore and Recovery
- In the Database Home page click “Maintenance” (see
slide 30)
- Click “Perform Recovery” link on the Maintenance page
under Backup/Recovery
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Restore and Recovery
- On the Recovery page you can recover the whole
database or only select objects
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Recovering the Whole MDB
- Select “Recover to the current time or a previous point-intime” and then click “Perform Whole Database Recovery”
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Recovering the Whole MDB
- Click Yes to confirm the shutdown of the database
- Oracle will shutdown the database
- Click “Perform Recovery” to resume the recovery session once the
Enterprise Manager restarts
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Recovering the Whole MDB
- Once again select “Recover to the current time or a previous point-intime” and click “Perform Whole Database Recovery” (see slide 48)
- Choose whether you want to recover all transactions to the MDB as of
the present time (complete recovery) or only transactions up to a
certain point-in-time. We will recover to the current time in this
example:
- Click Next
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Recovering the Whole MDB
- Although you can specify a new location for the restored files, in our
example we will go with current location. Select “No” and click Next
- Review settings and click Submit
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Recovering the Whole MDB
- Once the operation succeeds, click on Open Database
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Tuning & Optimization
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Optimization
- Monitoring the health of a database and ensuring that it
performs optimally is fundamental
- Oracle helps monitor database health and performance
through the Automatic Database Diagnostic Monitor
(ADDM)
- Oracle will automatically identify issues that require
attention. This includes:
- Alerts
- Emails
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
General DB State and Workload
- The Enterprise Manager home page displays general
database state and workload, and is updated often (every
60 s):
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Setting Metric Thresholds
- You can set metric thresholds by clicking “Manage
Metrics” under Related Links on Database Home page:
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Setting Metric Thresholds
- Click “Edit Thresholds” on the Manage Metrics page
- On the Edit Thresholds page select the metric(s) you want
to set warning and critical thresholds for
- In the example above we are setting threshold values for
Tablespace Space Used (%)
- Click OK and “Update succeeded” message should be
displayed
- Every time a given threshold is crossed, an alert will be
generated
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Performance Advisors
- Oracle has several Performance Advisors:
- Automatic Database Diagnostic Monitor (ADDM) –
enables Oracle to diagnose its own performance and
determine how to resolve any problems identified
- SQL Tuning Advisor – analyzes SQL statements and
makes recommendations for improving performance
- SQL Access Advisor – can be used to tune schema to
a given workload (e.g., recommend creating indexes
and materialized views)
- Memory Advisor – as mentioned previously, Oracle
automatically tunes memory allocation, but this advisor
gives graphical analysis of SGA and PGA settings,
which can be used for “what-if” planning
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Using Advisors to Optimize Database
Performance
- Advisors are powerful tools for database management
- They are especially useful for solving problems in specific areas. For
example, how much would performance increase if I alter the SGA
size?
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Memory Configuration
- As discussed on Fine Tuning the Oracle SGA (slide 11),
fitting the SGA in memory is very desirable because that
reduces paging and improves performance
- If the Oracle MDB server has enough memory, we can
tune the LOCK_SGA parameter to prevent the SGA from
being paged out
- LOCK_SGA is not a CURRENT parameter, but a static –
i.e. SPFile – one. The Oracle instance must be restarted
in order for the new parameter value to take effect
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
V$DB_CACHE_ADVICE View
- The V$DB_CACHE_ADVICE view is populated by setting the dynamic
DB_CACHE_ADVICE parameter to ON
- This view helps determine if Buffer Cache allocation within SGA is sufficient
and helps make informed decisions about increasing/decreasing SGA size
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Buffer Cache Hit Ratio & V$SYSSTAT
- The Buffer Cache Hit Ratio calculates how often a requested block
has been found in the buffer cache without requiring disk access
- This can help tune Buffer Cache allocation and SGA size, which
ultimately translates into adequate performance
- In the example above, the Buffer Cache Hit Ratio is 97.44%,
indicating that both Buffer Cache allocation and SGA size are
appropriate
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Shared Pool & V$LIBRARYCACHE
- As with Buffer Cache, DBAs are more likely to size the Shared Pool
- However, it is important to be aware that, if a Shared Pool is too
small, extra resources are needed to manage the limited amount of
available space, causing contention
- So, when sizing the Shared Pool the goal is to ensure SQL
statements that will be executed multiple times are cached
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Shared Pool & V$SGASTAT, V$SHARED_POOL_ADVICE
- Although having enough room in the Shared Poll to cache SQL statements that will be
executed multiple times is good, having a TOO MUCH free memory in the Shared Pool
is a potential waste of resources
- V$SHARED_POOL_ADVICE can help tuning the Shared Pool
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Redo Log Buffer & V$SYSSTAT
- Server processes making changes to data blocks in the Buffer Cache generate Redo
data in the Log Buffer
- Redo Log Buffer entries are copied to the Redo Log when:
- Log Buffer becomes 1/3 full
- LGWR is posted by a server process performing a COMMIT or ROLLBACK
- DBWR posts LGWR to do so
- Before tuning the value for LOG_BUFFER, we need to find out the space request ratio:
- If the ratio is greater than 1:5000, then increase the size of the redo log buffer. Ideally,
redo log space requests should be near to zero
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Program Global Area (PGA)
- PGA is a private memory region read and written only by
Oracle
- Work area used by memory intensive operators such as:
- Sort-based operators – ORDER BY and GROUP BY
- Hash-join
- Write buffers used by bulk load operations, etc
- Generally speaking, bigger work areas can significantly
improve performance
- By default, automatic PGA memory management is
enabled and its default size is around 20% of the SGA
memory size
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
PGA Performance & V$PGASTAT
- V$PGASTAT – provides statistics (instance-level) on the
PGA memory usage
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
PGA Performance &
V$SQL_WORKAREA_HISTOGRAM
- Let’s look at the result of this query:
- As we can see, 512KB to 1024KB bucket, 2,581 work
areas used an optimal amount of memory, while none ran
in one-pass mode or multi-pass modes
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
PGA Performance &
V$PGA_TARGET_ADVICE
- This view predicts how cache hit percentage and over
allocation count statistics in V$PGASTAT will be impacted
if the PGA_AGGREGATE_TARGET is changed
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.
Questions?
© 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.