Administration Workshop II 12-34. Migrating your database to ASM Storage
- Obtain the file names of the current control files and online redo logs by using
V$CONTROLFILEandV$LOGFILE. - Shut down the database consistently. Modify the server parameter file of your database as follows:
- Set the necessary OMF destination parameters to the desired ASM disk group.
- Remove the
CONTROL_FILEparameter.
- Run the following RMAN script:
STARTUP NOMOUNT; # replace '/u1/c1.ctl' for the location of the control file RESTORE CONTROLFILE FROM '/u1/c1.ctl'; ALTER DATABASE MOUNT; # replace the disk group '+dgroup1' for your disk group name BACKUP AS COPY DATABASE FORMAT '+dgroup1'; SWITCH DATABASE TO COPY; # replace '/u1/log1' for your online redo log name and '+dgroup1' for your disk group name SQL "ALTER DATABASE RENAME '/u1/log1' TO '+dgroup1' "; # Repeat RENAME command for all online redo log members ... ALTER DATABASE OPEN RESETLOGS; SQL "ALTER DATABASE TEMPFILE '/u1/temp1' DROP";
- Delete the old database files.
Administration Workshop II 12-34. Obtaining file names of the current control files and online redo logs
Obtain them by querying V$CONTROLFILE and V$LOGFILE
Administration Workshop II 11-20. Shrinking Segments by Using SQL
To shrink an object
ALTER TABLE employees SHRINK SPACE [CASCADE]
To shrink a LOB (must be issued separately since SHRINK SPACE CASCADE does not cascade to LOB)
ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE)
Shrink the overflow segment of an Index Organized Table (IOT)
ALTER TABLE employees OVERFLOW SHRINK SPACE
Administration Workshop II 9-32. SQL Tuning Advisor Workload Source
- Current and recent SQL activity
- Import workload from SQL repository
- User-defined workload; import SQL from a Table or View
- Create a Hypothetical workload from the following Schemas and Tables (separated by commas)
Administration Workshop II 9-30. SQL Access Advisor Overview
The SQL Access Advisor can recommend the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL because they can result in significant performance improvements in data retrieval.
- A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques.
- B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Administration Workshop II 9-14. Statistics for n-tier environments
In an n-tier environment where there is an application server that is pooling database connections, viewing sessions may not provide the information yhou need to analyze performance. Grouping sessions into service names enables you to monitor performance more accurately. These two views provide the same information that their like-named session counterparts provide, except that the information is presented at the service level rather than at the session level:
V$SERVICE_WAIT_CLASSshows wait statistics for each service, broken down by wait class.V$SERVICE_EVENTshows the same information asV$SERVICE_WAIT_CLASS, except that it is further broekn down by event ID.
Viewing pool sizes
From Administration Workshop II 8-23
SELECT name, value, isdefault FROM v$parameter WHERE name LIKE '%size';
Administration Workshop II 8-21. Manually tuning pool sizes
SELECT SUM(bytes)/1024/1024 size_mb
FROM v$sgastat WHERE pool = 'shared pool';
Determine the size for the autotuned component in the SGA
SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components;
Memory Management
Source: Administration Workshop II 8-14.
To be taken into account when manually tuning the pool sizes:
- OLTP systems require large buffer cache
- Parallel batch jobs require large value for large pool
Now, it’s typical to have a system that runs large OLTP jobs during the day (requiring a large buffer cache) and runs parallel batch jobs at night (requiring a large value for the large pool). In this system you would have to simultaneously configure both the buffer cache and the large pool to acoommodate your peak requirements, if using manual pool sizing. This means wasting memory. Using Automatic Shared Memory Management (ASMM) you would maximize memory utilization by automatically adjusting the pool sizes.
Oracle Memory Structures (SGA)
- Shared Pool: stores info shared by multiple sessions. It’s sub-divided into 3 areas:
- Library Cache: fully parsed or compiled SQL statements or PL/SQL blocks (Procedures, Functions, Triggers, Packages and Anonymous PL/SQL blocks)
- Data Dictionary Cache: holds definition of data dictionary objects in memory
- User Global Area: contains session information if using Oracle shared server. See more on Administration Workshop II 8-10, 8-28 and full explaination of UGA, PGA
- Java Pool: used for all session-specific Java code and data within the Java engine. The OracleJVM memory manager allocates all other Java states during run-time execution from the Java pool, including the shared in-memory representation of Java method and class definitions, as well as the Java objects migrated to session space at end-of-call.
- However, Shared Pool is used by the class loader within the JVM. The class loader uses about 8KB of memory per loaded class. The shared pool is also used when compiling Java source code in the database or when using Java resource objects in the database. Shared pool memory is also consumed when you create call specifications and as the system tracks dynamically loaded Java classes at run time. (Administration Workshop II 8-12)
- Redo Log Buffer: The redo entries copied from the user’s memory space contain the information necessary to reconstruct or redo changes made to the database by DML and DDL operations.
LGWRwrites to disk from redo log buffer when:- A user process commits a transaction
- Every three seconds or when the redo log buffer is one-third full or contains at least 1 MB of data
- When a
DBWnprocess writes modified buffers to disk, if the corresponding redo log data has not already been written to disk (Administration Workshop II). Correction taken from Administration Workshop I 14-12:LGWRwrites beforeDBWnwrites.
