CONFIGURE UNDO RETENTION
Snapshot too old
Rollback records needed by a reader for consistent read are overwritten by other writers.
UNDO_RETENTION DEFAULT 900 IN SECONDS
IGNORE UNDO_RETENTION UNLESS GUARANTEE IS ENABLE
Automatic tuning of undo retention is not supported for lobs columns
Active undo: undo data will always be kept until transaction that generated completes with commit or rollback
Unexpired undo: after commit or rollback became to unexpired undo and still stored to satisface reading consistency requirement
(cuando finaliza con el commit or rollback, permanecerán allí para satisfacer las lecturas consistentes
Expired undo is data no longer needed to read consistency and may overwritten at any time as space in undo segment reused
Snapshot too old
Rollback records needed by a reader for consistent read are overwritten by other writers.
The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!
Oracles does this by reading the "before image" of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.
SMON System Monitor
- Initially has the task of mounting and opening database
- Open a database by locating and validating all datafile and online log file
- Once database is opened and in use is responsible for many housekeeping such:
- Coating free space in datafile
- Cleanup unused temporary segments.
- Perform recovery at instance startup.
PMON Process Monitor
- If the session has terminated abnormally PMON will destroy the server process, returns the PGA memory to operation’s system and rollback any incomplete transaction.
- Performs process recovery when a user process fails
- Clean up database buffer cache
- Frees resources that are used by the user process
- Monitors sessions for idle session timeout
- Dynamically registers database service with listeners
- When DBWR write on disk
- No free space within DATA BUFFER CACHE (When server process needs free space in database buffer cache to read new block)
- too many dirty buffers within DATA BUFFER CACHE
- 3-seconds timeout (limited number off dirty buffers are writte
- When there is a checkpoint (all dirty buffers are written)
- After Shutdown immediate
- When any tablespace is taken offline, read only, drop or truncated & when to begin the backup.
- After alter system checkpoint.
Incremental checkpoint (fast_start_mttr_target)
- By reducing the checkpoint time to be more frequent than a log switch, Oracle will recover and re-start faster in case of an instance crash
- Advancing the Incremental checkpoint position
Note: No hay full checkpoint en switch log file from oracle 8
Note: DBWn puede escribir transacción que no tengan commit a disk eso es normal
CKPT Checkpoint
- Keep track of where in the redo stream the Incremental position is
- If it necessary call DBWN to write out dirty buffers in order to push the checkpoint position forward.
Current checkpoint position is the point in the redo stream at which recovery must begin in the event of an instance crash
Continually updated the control file with current checkpoint position
LGWR Log Write
- Flushing the Log buffers to Online Log Files
- When the are Commit LGWR written in real time and users session hang white change vector is written
-Before make any changes to data block buffer cache it's write out the change vector that It's about to apply to buffer log
- Log buffer is full
- If DBWn is about to write dirty buffers, it will send signal to LGWR to flushing the Log buffers to Online Log File to ensure the transaction usually be reversed the transaction uncommitted
- We can say the LGWR written every 3 second because it usually write before DBWR
Guarantee transaction The session has been hang white change vector is written
MMON Manageability Monitor
- Self-monitoring
- Self-tunning
- Gather of statistics about activity and performance
- Launches ADDM automatic database diagnostic monitor
- Every hour write information in data dictionary (AWR)
- By default, they're kept for only eight days
Note: No third-party tools can ever have the direct memory access to instance that MMON has.
MMNL the Manageability Monitor light
- Asist MMON
- If buffer is full before MMON has to flush them MMNL will take responsibility for flushing data
MMAN Memory Manager
- It allocate memory between PGA and SGA
- It is process for ASMM Automatics Shared Memory Manager
LREG
- Allow to connect users via the listeners
- In advance environment such a RAC that maybe there are more than one instance; LREG will also update the listeners with information regarding workload and performance. In early version this function was performance by PMON
ARCn Archive log
RECO Recovery
- It is for Distribute transaction (remote and local) two phase commit if anything goes wrong anywhere RECO takes action to cancel the commit and roll back the work in all database.
V$bgprocess
V$process
MEMORY
MANDATORY
- THE DATABASE BUFFER CACHE
- db_cache_size
- db_2k_cache_size
- db_4k_cache_size
- db_6k_cache_size
- db_8k_cache_size
- db_16k_cache_size
- db_32k_cache_size
- THE SHARED POOL
- shared_pool_size (The Library Cache, The Data Dictionary, The Sql and PL/SQL Function Result cache)
- shared_pool_reserved_size
- THE LOG BUFFER.
- log_buffer. (It’s set by default, it’s statistic )
OPTIONAL
- THE LARGE POOL
- large_pool_size (Shared Server, Parallel Execution, Recovery Manager )
- THE JAVA POOL
- java_pool_size (Java Object, NO Java Code, Java Code is record in shared pool )
- THE STREAM POOL
- streams_pool_size (Oracle Stream)
AMM Automatic Memory Management
SGA + Total PGA
OPTION 1
- MEMORY_TARGET <> 0
- MEMORY_MAX_TARGET <>0
OPTION 2
- MEMORY_TARGET <> 0
- MEMORY_MAX_TARGET <> 0
- PGA_AGGREGATE_TARGET <> 0
- SGA_TARGET <> 0 (ASMM)
-Oracle can let transfer memory between PGA and SGA on demand
V$MEMORY_DYNAMIC_COMPONENTS show current size of structure
v$MEMORY_RESIZE_OPS Show resize of last 800 resizing operations.
AMM is implement by memory broker, which implement with two boudground process MMON and MMAN
note: In linux is not posible to enable AMM if huge pages is in use, solaris is possible for project
PGA V$PGA_TARGET_ADVICE DBA_HIST_PGA_TARGET_ADVICE
SGA V$SGA_TARGET_ADVICE DBA_HIST_SGA_TARGET_ADVICE
MEMORY V$MEMORY_TARGET_ADVICE DBA_HIST_MEMORY_TARGET_ADVICE
DB Cache V$DB_CACHE_ADVICE DBA_HIST_DB_CACHE_ADVICE
JAVA POOL V$JAVA_POOL_ADVICE DBA_HIST_JAVA_POOL_ADVICE
STREAMS POOL V$STREAMS_POOL_ADVICE DBA_HIST_STREAMS_POOL_ADVICE
SHARED POOL V$SHARED_POOL_ADVICE DBA_HIST_SHARED_POOL_ADVICE
AMM Tuning
V$MEMORY_TARGET_ADVICE
V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_RESIZE_OPS
Note: overhead for resize operations
ASMM (Automatic Shared Memory Management)
sga_target
sga_max_size
SGA_TARGET <> 0
SGA_MAX_SIZE <> 0
- THE DATABASE BUFFER CACHE = 0
- THE SHARED POOL = 0
- THE LOG BUFFER = 0
- THE LARGE POOL = 0
- THE JAVA POOL = 0
- THE STREAM POOL = 0
It’s Also possible to set traditional parameter from different value to zero for setting minimum value, ASMM will configure automatically it from that size.
PGA (Private Global Area)
workarea_size_policy
pga_aggregate_limit
pga_aggregate_target
PGA
- Sorting rows
- Merging bitmaps
- Variable
- The call stack
To check DATABASE
Archived
archive log list
To check if it is a RAC
select parallel from v$instance;
To check if it is a Data Guard
select protection_level from v$database;
To check if it is a Golden Gate
select * from dba_streams_administrator;
OMF Oracle Management File
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
PERFORMANCE
V$STATNAME listing every statistics gathered, grouping then into classes.
V$SYSSTAT The current statistics for each statistic for entire instance, accumulated since instance started
V$SESSTAT The current value of each statistic for each logged-on session
V$MYSTAT The statistics for your current logged-on
V$EVENT_NAME listing every wait event, grouping then into class
V$SYSTEM_EVENT The number of time each wait event has occurred and the total time
V$SESSION_EVENT The number of time each wait event has occurred for each currently logged-on session and total time spend by that session waiting on the event, accumulated since the session started.
V$SQLAREA
SQL TUNING ADVISOR
- STATISTICS ANALYSIS
- SQL PROFILING (ADICIONAL STATISTISC AND STORED IN A PROFILE)
- ACCESS PATH
- STRUCTURE ANALIZED
SQL TUNING ADVISOR can use a number of sources for it’s analysis
- The SQL currently cached in library cache of shared pool
- a precreated set of statements
- statements retrieved from AWR
- and individual as hoc statement
It’s limited to created index
Database express has Sql Running Advisor
DBMS_SQLTUNE
1-CREATE_TUNNING_TASK
2-EXECUTE_TUNNING_TASK
3-REPPORT_TUNNING_TASK
DBA_ADVISOR_LOG Task Name, status, execution statistics at all task
DBA_ADVISOR_TASK more information about advisor task
V$ADVISOR_PROGRESS more information about completion status and time for each advisor task
SQL ACCESS ADVISOR
WORKLOAD SPECIFICATION ARE:
- A single SQL statements
- A SQL segment tuning set
- Current SQL cached contents
- A hypothetical workload imputed from DDL of the set objects
Recomendation
- Indexes (b-tree, bitmap, and function based)
- Materialised Views and Materialised View Logs
- Partion strategies
DBMS_ADVISOR OR ENTERPRISE MANAGER
DBMS_ADVISOR.QUICK_TUNE
MORE RECOMENDATIONS THAN SQL_TUNING
E.G.
1.DBMS_ADVISOR.QUICK_TUNE
2.CREATE DIRECTORY TUNE_SCRIPTS AS ‘/U01/TUNNESCRIPTS/‘;
3.READ THE SCRIPT WITH VI
STATISTICS
GATHER STATISTICS MANANUALY
DBMS_STATS. Packages
GATHER_DATABASE_STATS Analysed whole database
GATHER_SQUEMA_STATS Analysed all objects in one squema
GATHER_TABLE_STATS
GATHER_INDEX_STATS
ARGUMENTS ARE :
CASCADE analyse table as well index
ESTIMATE_PERCENT
DEGREE parallel processes
NO_INVALIDATE
GRANULARY
METHOD_OPT
OPTIONS
OPERATION SYSTEM
Environment
DISPLAY
ORACLE_BASE
ORACLE_SID
ORACLE_HOME
if there is not enough space in tmp set:
TMPDIR
TMP
Parameter Replaced by Resource Control Recommended Value
noexec_user_stack NA 1
semsys:seminfo_semmni project.max-sem-ids 100
semsys:seminfo_semmns NA 1024
semsys:seminfo_semmsl process.max-sem-nsems 256
semsys:seminfo_semvmx NA 32767
shmsys:shminfo_shmmax project.max-shm-memory 4294967296
shmsys:shminfo_shmmni project.max-shm-ids 100
O.S Group
SYSDBA
SYSOPER
SYSASM
SYSBACKUP
SYSDG
SYSKM
