Download - Oracle9i Performance Tuning
![Page 1: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/1.jpg)
Oracle9i Database Performance Tuning
Electronic Presentation
D11299GC21Edition 2.1June 2003D38323
![Page 2: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/2.jpg)
Copyright © Oracle Corporation, 2003. All rights reserved.
This documentation contains proprietary information of Oracle Corporation. It is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. If this documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:
Restricted Rights Legend
Use, duplication or disclosure by the Government is subject to restrictions for commercial computer software and shall be deemed to be Restricted Rights software under Federal law, as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988).
This material or any portion of it may not be copied in any form or by any means without the express prior written permission of Oracle Corporation. Any other copying is a violation of copyright law and may result in civil and/or criminal penalties.
If this documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with “Restricted Rights,” as defined in FAR 52.227-14, Rights in Data-General, including Alternate III (June 1987).
The information in this document is subject to change without notice. If you find any problems in the documentation, please report them in writing to Education Products, Oracle Corporation, 500 Oracle Parkway, Box SB-6, Redwood Shores, CA 94065. Oracle Corporation does not warrant that this document is error-free.
Oracle and all references to Oracle and Oracle products are trademarks or registered trademarks of Oracle Corporation.
All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.
Authors
Peter KilpatrickShankar RamanJim Womack
Technical Contributors and Reviewers
Mirza AhmadDavid AustinRuth BaylisHoward BradleyPietro ColomboMichele CyranBenoit DagervilleConnie DialerisJoel GoodmanScott GossettLilian HobbsAlexander HunoldSushil KumarRoderick ManalacHoward OstrowDarren PelacchiSander RekveldMaria SeniseRanbir SinghJanet SternWayne StokesTracy StollbergHarald Van BreederodeJohn Watson
Publisher
Joseph Fernandez
![Page 3: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/3.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview of Oracle9i Database Performance Tuning
![Page 4: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/4.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Define the roles associated with the database
tuning process• Describe the dependencies between tuning in
different development phases• Describe service level agreements• Identify tuning goals• Identify common tuning problems• Employ tuning activities during development
and production• Balance performance and safety trade-offs
![Page 5: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/5.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Questions
• Who tunes?– Application designers– Application developers– Database administrators– System administrators
• What to tune?• How much tuning is required?
![Page 6: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/6.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Phases
Tuning can be divided into different phases:• Application design and programming• Database configuration• Adding a new application to an existing database• Troubleshooting and tuning
![Page 7: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/7.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Goals
Tuning goals are usually specified in terms of:• Minimizing response time• Increasing throughput• Increasing load capabilities• Decreasing recovery time
![Page 8: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/8.jpg)
Copyright © 2003, Oracle. All rights reserved.
Common Performance Problems
• Bad session management:– Limits scalability to a point that cannot be exceeded– Makes the system one or two orders of magnitude
slower than it should be
• Bad cursor management• Bad relational design:
– Unnecessary table joins performed– Usually a result of trying to build an object interface
to relational storage
![Page 9: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/9.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Steps During Development
• Tune the design• Tune the application• Tune memory• Tune I/O• Tune contention• Tune the operating system
![Page 10: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/10.jpg)
Copyright © 2003, Oracle. All rights reserved.
Collect a Baseline Set of Statistics
A baseline set of statistics is used to:• Provide a set of statistics that are collected when
the system was operating within the bounds set• Create a hypothesis about what has changed on
the system
![Page 11: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/11.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Steps for a Production Database
1. Define the problem.2. Examine the host system and Oracle statistics.3. Consider some common performance errors.4. Build a conceptual model.5. Implement and measure the change.6. Check that the bottleneck has been resolved.
![Page 12: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/12.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Server Tuning Methodology
• Check alert log and trace files for errors.• Check the parameter file for any diagnostic or
inappropriate parameter setting.• Check memory, I/O, and CPU usage. Identify
processes with resource usage anomalies.• Identify and tune SQL statements that are heavy
consumers of CPU or I/O.
![Page 13: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/13.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Server Tuning Methodology
Tuning response time:• Analyze system performance in terms of work
done (CPU or service time) versus time spent waiting for work (wait time).
• Determine which component consumes the greatest amount of time.
• Drill down to tune that component, if appropriate.
![Page 14: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/14.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Versus Safety Trade-Offs
Factors that affect performance:• Multiple control files• Multiple redo log members in a group• Frequent checkpointing• Backing up data files• Performing archiving• Block check numbers• Number of concurrent users and transactions
![Page 15: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/15.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Create a good initial design• Define a tuning methodology• Perform production tuning• Establish quantifiable goals• List tuning problems• Decide between performance and safety
![Page 16: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/16.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic and Tuning Tools
![Page 17: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/17.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Identify key tuning components of the alert log file• Identify key tuning components of background
trace files• Identify key tuning components of user trace files• Collect statistics with Oracle Enterprise Manager• Describe how Statspack collects statistics• Collect statistics with Statspack• Identify dynamic performance views useful in
tuning• Describe other tools used for tuning
![Page 18: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/18.jpg)
Copyright © 2003, Oracle. All rights reserved.
Maintenance of the Alert Log File
• The alert log file consists of a chronological log of messages and errors.
• Check the alert log file regularly to:– Detect internal errors (ORA-600) and block
corruption errors– Monitor database operations– View the nondefault initialization parameters
• Remove or trim the file regularly after checking.
![Page 19: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/19.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Components of the Alert Log File
The alert log file contains the following informationwhich can be used in tuning the database:• Checkpoint start and end times• Incomplete checkpoints• Time to perform archiving• Instance recovery start and complete times• Deadlock and timeout errors
![Page 20: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/20.jpg)
Copyright © 2003, Oracle. All rights reserved.
Background Processes Trace Files
• The Oracle server dumps information abouterrors detected by any background processinto trace files.
• Oracle Support uses these trace files to diagnoseand troubleshoot.
• These files do not usually contain tuning information.
![Page 21: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/21.jpg)
Copyright © 2003, Oracle. All rights reserved.
User Trace Files
• Server process tracing can be enabled or disabled at the session or instance level.
• A user trace file contains statistics for traced SQL statements in that session.
• User trace files are created on a per server process basis.
• User trace files can also be created by:– Backup control file to trace– Database SET EVENTs
![Page 22: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/22.jpg)
Copyright © 2003, Oracle. All rights reserved.
Views, Utilities, and Tools
Tools and views that are available to the DBA for determining performance:• Oracle Enterprise Manager• Diagnostics and tuning packs• Statspack• v$xxx dynamic troubleshooting and performance
views• dba_xxx dictionary views• Oracle wait events• utlbstat.sql and utlestat.sql scripts
![Page 23: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/23.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise Manager Console
![Page 24: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/24.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager
![Page 25: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/25.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview of Oracle ExpertTuning Methodology
Specify tuning scope
Collect data
View and edit data and rules
Analyze data
Review recommendations
Implement recommendations
![Page 26: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/26.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Using Oracle Expert
![Page 27: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/27.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Using Oracle Expert
![Page 28: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/28.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statspack
• Installation of Statspack using thespcreate.sql script
• Collection of statistics execute statspack.snap• Automatic collection of statistics using the
spauto.sql script• Produce a report using the spreport.sql script• To collect timing information, set
TIMED_STATISTICS = True
![Page 29: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/29.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statspack Output
Information found on the first page:• Database and instance name• Time at which the snapshots were taken• Current sizes of the caches• Load profile• Efficiency percentages of the instance• Top five wait events
![Page 30: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/30.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statspack Output
Information found in the remainder of the document:• Complete list of wait events• Information on SQL statements currently in
the pool• Instance activity statistics• Tablespace and file I/O• Buffer pool statistics
![Page 31: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/31.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statspack Output
Information found in the remainder of the document:• Rollback or undo segment statistics• Latch activity• Dictionary cache statistics• Library cache statistics• System Global Area (SGA) statistics• Startup values for initialization parameters
![Page 32: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/32.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dictionary and Special Views
The following dictionary and special views provide useful statistics after using the dbms_stats package:• dba_tables, dba_tab_columns• dba_clusters• dba_indexes, index_stats• index_histogram, dba_tab_histograms
This statistical information is static until you reexecute dbms_stats.
![Page 33: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/33.jpg)
Copyright © 2003, Oracle. All rights reserved.
Displaying Systemwide Statistics
v$sysstat• statistic# • name• class• value
v$sgastat• pool• name• bytes
v$event_name• event number• name• parameter1 • parameter2 • parameter3
v$system_event• event• total_waits• total_timeouts• time_waited• average_wait
![Page 34: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/34.jpg)
Copyright © 2003, Oracle. All rights reserved.
Displaying Session-Related Statistics
v$statname• statistic# • name • class
v$sesstat• sid• statistic#• value
v$session_wait• sid• seq#• event• p1/2/3• p1/2/3 text• p1/2/3 raw• wait time• seconds_in_wait• state
v$session• sid• serial#• username• osuser
v$session_event• sid• event• total_waits• total_timeouts• time_waited• average_wait• max_wait
v$event_name• event#• name• parameter1• parameter2 • parameter3
![Page 35: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/35.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Wait Events
• A collection of wait events provides informationon the sessions that had to wait or must wait for different reasons.
• These events are listed in the v$event_name view, which has the following columns:– EVENT#– NAME– PARAMETER1– PARAMETER2– PARAMETER3
![Page 36: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/36.jpg)
Copyright © 2003, Oracle. All rights reserved.
The v$event_name View
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------- ---------- ---------- ----------
PL/SQL lock timer duration
alter system set mts_dispatcher waited
buffer busy waits file# block# id
library cache pin handle addr pin address 0*mode+name
log buffer space
log file switch
(checkpoint incomplete)
transaction undo seg# wrap# count
...
286 rows selected.
SQL> SELECT name, parameter1, parameter2, parameter32 FROM v$event_name;
![Page 37: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/37.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statistics Event Views
• v$session_event: Waits for an event for each session that had to wait
• v$session_wait: Waits for an event for current active sessions that are waiting
• v$system_event: Total waits for an event, all sessions together
![Page 38: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/38.jpg)
Copyright © 2003, Oracle. All rights reserved.
The v$session_event View
SID EVENT TOTAL_WAITS AVERAGE_WAIT ---- ------------------------------ ----------- -------------10 buffer busy waits 12 510 db file sequential read 129 0 10 file open 1 0 10 SQL*Net message to client 77 0 10 SQL*Net more data to client 2 0 10 SQL*Net message from client 76 0
SQL> SELECT sid, event, total_waits,average_wait2 FROM v$session_event3 WHERE sid=10;
![Page 39: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/39.jpg)
Copyright © 2003, Oracle. All rights reserved.
The v$session_wait View
SID SEQ# EVENT WAIT STATETIME
---- ------ --------------------------- ----- -------1 1284 pmon timer 0 WAITING2 1697 rdbms ipc message 0 WAITING3 183 rdbms ipc message 0 WAITING4 4688 rdbms ipc message 0 WAITING5 114 smon timer 0 WAITING6 14 SQL*Net message from client -1 WAITED
SHORTTIME
SQL> SELECT sid, seq#, event, wait_time, state2 FROM v$session_wait;
![Page 40: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/40.jpg)
Copyright © 2003, Oracle. All rights reserved.
The v$system_event View
EVENT TOTAL_ TOTAL_ TIME_ AVERAGE_WAITS TIMEOUTS WAITED WAIT
----------------- ------ -------- ------ ----------latch free 5 5 5 1pmon timer 932 535 254430 272.993562process startup 3 8 2.66666667buffer busy waits 12 0 5 5...34 rows selected.
SQL> SELECT event, total_waits, total_timeouts,2 time_waited, average_wait3 FROM v$system_event4 ORDER BY time_waited DESC;
![Page 41: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/41.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dynamic Troubleshootingand Performance Views
V$ views:• Based on X$ tables• Listed in v$fixed_tableX$ tables:• Not usually queried directly• Dynamic and constantly changing• Names abbreviated and obscure
Populated at startup and cleared at shutdown
![Page 42: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/42.jpg)
Copyright © 2003, Oracle. All rights reserved.
Instance/Databasev$databasev$instancev$optionv$parameterv$backupv$px_process_sysstatv$processv$waitstatv$system_event
Troubleshooting and Tuning Views
Diskv$datafilev$filestatv$logv$log_historyv$dbfilev$tempfilev$tempstatv$segment_statistics
Contentionv$lockv$rollnamev$rollstatv$waitstatv$latch
![Page 43: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/43.jpg)
Copyright © 2003, Oracle. All rights reserved.
Troubleshooting and Tuning Views
Memoryv$buffer_pool_statisticsv$db_object_cachev$librarycachev$rowcachev$sysstatv$sgastat
User/Sessionv$lockv$open_cursorv$processv$transactionv$px_sesstatv$px_session
v$sesstatv$session_eventv$sort_usagev$session_waitv$sessionv$session_object_cache
![Page 44: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/44.jpg)
Copyright © 2003, Oracle. All rights reserved.
utlbstat and utlestat Scripts
• Gather performance figures over a defined period.• Produce a hard-copy report.• Should set TIMED_STATISTICS to True.• Execute from SQL*Plus connected as SYSDBA.• Statspack provides clearer statistics.
![Page 45: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/45.jpg)
Copyright © 2003, Oracle. All rights reserved.
DBA-Developed Tools
• Develop your own scripts.• Use the supplied packages for tuning.• Schedule periodic performance checking.• Take advantage of the Enterprise Manager Event
service to track specific situations.• Take advantage of the Oracle Enterprise Manager
Job service to:– Automate the regular execution of administrative
tasks.– Apply tasks that automatically solve problems
detected by the Oracle Enterprise Manager event service.
![Page 46: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/46.jpg)
Copyright © 2003, Oracle. All rights reserved.
Level of Statistics Collection
The initialization parameters that determine the level of statistic collection are:• STATISTICS_LEVEL• TIMED_STATISTICS• TIMED_OS_STATISTICS• DB_CACHE_ADVICE
![Page 47: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/47.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Use the alert log file• Get information from background processes
trace files• Trace user SQL statements• Collect statistics from dictionary and dynamic
performance troubleshooting views• Use the Statspack utility to collect
performance data• Retrieve wait events information
![Page 48: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/48.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Configurationand I/O Issues
![Page 49: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/49.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• List the advantages of distributing different Oracle
file types• Diagnose tablespace usage problems• List reasons for partitioning data in tablespaces• Describe how checkpoints work• Monitor and tune checkpoints• Monitor and tune redo logs
![Page 50: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/50.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Processes and Files
Process Oracle file I/OData files Log Archive Control
CKPT Read/Write Read/Write
DBWn Write
LGWR Write Read/Write
ARCn Read Write Read/Write
SERVER Read/write Read Write Read/Write
![Page 51: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/51.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Guidelines
Basic performance rules are as follows:• Keep disk I/O to a minimum.• Spread your disk load across disk devices
and controllers.• Use temporary tablespaces where appropriate.
![Page 52: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/52.jpg)
Copyright © 2003, Oracle. All rights reserved.
Distributing Files Across Devices
• Separate data files and redo log files.• Stripe table data.• Reduce disk I/O unrelated to the database.
![Page 53: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/53.jpg)
Copyright © 2003, Oracle. All rights reserved.
IO Topology Support
![Page 54: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/54.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tablespace Usage
• Reserve the system tablespace for datadictionary objects.
• Create locally managed tablespaces to avoid space management issues.
• Split tables and indexes into separate tablespaces. • Create rollback segments in their own tablespaces.• Store very large objects in their own tablespace.• Create one or more temporary tablespaces.
![Page 55: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/55.jpg)
Copyright © 2003, Oracle. All rights reserved.
Locally Managed system Tablespace
Create databases that have a locally managed systemtablespace.
CREATE DATABASE mydb …DATAFILE 'system01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL…DEFAULT TEMPORARY TABLESPACE tempTEMPFILE 'temp01.dbf' SIZE 15M…;
![Page 56: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/56.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic Tools for CheckingI/O Statistics
Server I/O utilization System I/O utilization
Data files
Statspack
Performance tools
v$filestatv$tempstatv$datafile
![Page 57: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/57.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using the v$filestat View
SQL> SELECT phyrds, phywrts, d.name 2 FROM v$datafile d, v$filestat f3 WHERE d.file#=f.file#4 ORDER BY d.name;
PHYRDS PHYWRTS NAME---------- -------- --------------------
806 116 /…/u01/system01.dbf168 675 /…/u04/temp01.dbf8 8 /…/u02/sample01.dbf26 257 /…/u02/undots01.dbf
65012 564 /…/u03/users01.dbf8 8 /…/u01/query01.dbf
6 rows selected
![Page 58: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/58.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: I/O Statistics
![Page 59: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/59.jpg)
Copyright © 2003, Oracle. All rights reserved.
I/O Statistics
SQL> SELECT d.tablespace_name TABLESPACE,2 d.file_name, f.phyrds, f.phywrts3 FROM v$filestat f, dba_data_files d4 WHERE f.file# = d.file_id;
TABLESPACE FILE_NAME PHYRDS PHYWRTS------------- ----------------- ------ -------UNDO1 /u02/undots01.dbf 26 257SAMPLE /u02/sample01.dbf 65012 564USERS /u03/users01.dbf 8 8SYSTEM /u01/system01.dbf 806 116TEMP /u04/temp01.dbf 168 675QUERY_DATA /u01/query01.dbf 8 86 rows selected.
![Page 60: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/60.jpg)
Copyright © 2003, Oracle. All rights reserved.
File Striping
• Operating system striping:– Use operating system striping software or a
redundant array of inexpensive disks (RAID).– Determine the right stripe size.
• Manual striping: Use the CREATE TABLE or ALTER TABLE command with the ALLOCATE clause.
![Page 61: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/61.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Full Table Scan Operations
• Investigate the need for full table scans.• Configure the DB_FILE_MULTIBLOCK_READ_COUNT
initialization parameter to:– Determine the number of database blocks the server
reads at once– Influence the execution plan of the
cost-based optimizer
• Monitor long-running full table scans with v$session_longops view.
![Page 62: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/62.jpg)
Copyright © 2003, Oracle. All rights reserved.
Table Scan Statistics
SQL> SELECT name, value FROM v$sysstat 2 WHERE name LIKE '%table scan%';
NAME VALUE-------------------------------------- -----table scans (short tables) 125table scans (long tables) 30table scans (rowid ranges 0table scans (cache partitions) 0table scans (direct read) 0table scan rows gotten 21224table scan blocks gotten 8047 rows selected.
![Page 63: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/63.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Full Table Scan Operations
• Determine the progress of long operations using:
• Use SET_SESSION_LONGOPS to populatev$session_longops.
SQL> SELECT sid, serial#, opname,2 TO_CHAR(start_time,'HH24:MI:SS') AS "START",3 (sofar/totalwork)*100 AS PERCENT_COMPLETE4 FROM v$session_longops;
dbms_application_info.set_session_longops(rindex, slno, "Operation X", obj, 0, sofar,totalwork, "table", "tables");
![Page 64: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/64.jpg)
Copyright © 2003, Oracle. All rights reserved.
Checkpoints
The two most common types of checkpoints are:• Incremental checkpoints
– CKPT updates the control file.
– During a log switch CKPT updates the control fileand the data file headers.
• Full checkpoints– CKPT updates the control file and the data file headers.
– DBWn writes out all buffers on the checkpoint queue.
![Page 65: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/65.jpg)
Copyright © 2003, Oracle. All rights reserved.
Full Checkpoints
Two categories of full checkpoints• Complete• Tablespace
![Page 66: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/66.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: Response Time
![Page 67: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/67.jpg)
Copyright © 2003, Oracle. All rights reserved.
Regulating the Checkpoint Queue
Regulate the checkpoint queue with the following initialization parameters:• FAST_START_IO_TARGET• LOG_CHECKPOINT_INTERVAL• LOG_CHECKPOINT_TIMEOUT• FAST_START_MTTR_TARGET
![Page 68: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/68.jpg)
Copyright © 2003, Oracle. All rights reserved.
Defining and MonitoringFast Start Checkpointing
Use v$instance_recovery to obtain the following information:• RECOVERY_ESTIMATED_IOS• LOG_FILE_SIZE_REDO_BLKS• LOG_CHKPT_TIMEOUT_REDO_BLKS• LOG_CHKPT_INTERVAL_REDO_BLKS• TARGET_MTTR• ESTIMATED_MTTR
![Page 69: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/69.jpg)
Copyright © 2003, Oracle. All rights reserved.
LGWR
Redo Log Groups and Members
Group 2 Group 3Group 1Disk 1
Disk 2
Member
Member
Member
Member
Member
Member
![Page 70: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/70.jpg)
Copyright © 2003, Oracle. All rights reserved.
Online Redo Log File Configuration
• Size redo log files to minimize contention.• Provide enough groups to prevent waiting.• Store redo log files on separate, fast devices.• Monitor the redo log file configuration with:
– v$logfile– v$log– v$log_history
![Page 71: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/71.jpg)
Copyright © 2003, Oracle. All rights reserved.
Increasing the Performance of Archiving
• Allow the LGWR process to write to a disk different from the one the ARCn process is reading.
• Share the archiving work during a temporary increase in workload:
• Increase the number of archive processes.• Change archiving speed:
– LOG_ARCHIVE_MAX_PROCESSES– LOG_ARCHIVE_DEST_n
ALTER SYSTEM ARCHIVE LOG ALL TO <log_archive_dest>
![Page 72: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/72.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic Tools
v$archive_destv$archived_logv$archive_processes
LOG_ARCHIVE_DEST_STATE_nArchived
logs
![Page 73: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/73.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• List the advantages of distributing different Oracle
file types• Diagnose tablespace usage problems• List reasons for segmenting data in tablespaces• Describe how checkpoints work• Monitor and tune checkpoints• Monitor and tune archive logging
![Page 74: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/74.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Shared Pool
![Page 75: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/75.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Determine the size of an object and pin it in the
shared pool• Tune the shared pool reserved space• Describe the user global area (UGA) and session
memory considerations• Measure the library cache hit ratio• List other tuning issues related to the shared pool• Measure the dictionary cache hit ratio• Set the large pool
![Page 76: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/76.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared Pool Contents
Major components of the shared pool are:• Library cache• Data dictionary cache• User global area (UGA) for shared server sessions
Database buffer cache
Redo log
buffer
Shared pool Library cache
Data dictionary cache
User global area
Large pool
UGA
![Page 77: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/77.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared Pool
• Defined by SHARED_POOL_SIZE• Library cache contains statement text, parsed
code, and execution plan.• Data dictionary cache contains definitions for
tables, columns, and privileges from the data dictionary tables.
• UGA contains session information for Oracle Shared Server users when a large pool is not configured.
Shared pool
Librarycache
Datadictionary
cache
UGA
Shared poolShared pool
![Page 78: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/78.jpg)
Copyright © 2003, Oracle. All rights reserved.
The Library Cache
• Used to store SQL statements and PL/SQL blocks that are to be shared by users
• Managed by a least recently used (LRU) algorithm• Used to prevent statements reparsing• Reports error ORA-04031 if the shared pool is out
of free memory
![Page 79: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/79.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared SQL, PL/SQL areas
The Library Cache
Context areafor SELECTstatement 2
Context areafor SELECTstatement 1
SELECTstatement 2
SELECTstatement 1
SELECTstatement 1
![Page 80: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/80.jpg)
Copyright © 2003, Oracle. All rights reserved.
Important Shared Pool Latches
• shared pool: Protects memory allocations in the shared pool
• library cache: Locates matching SQL in the shared pool
![Page 81: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/81.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared Pool and Library Cache Latches
Contention for shared pool latch and library cache latch indicates one or more of the following:• Unshared SQL• Reparsed sharable SQL• Insufficiently sized library cache
![Page 82: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/82.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Library Cache
Reduce misses by keeping parsing to a minimum:• Make sure that users can share statements.• Prevent statements from being aged out by
allocating enough space.• Avoid invalidations that induce reparsing.
![Page 83: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/83.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Library Cache
Avoid fragmentation by:• Reserving space for large memory requirements• Pinning frequently required large objects• Eliminating large anonymous PL/SQL blocks• Enabling the use of large pool for Oracle Shared
Server connections
![Page 84: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/84.jpg)
Copyright © 2003, Oracle. All rights reserved.
Terminology
• Gets: (Parse) The number of lookups for objectsof the namespace
• Pins: (Execution) The number of reads or executions of the objects of the namespace
• Reloads: (Parse) The number of library cache misses on the execution step, thereby causing an implicit reparsing of the SQL statement
• Invalidations: (Parse) If an object is modified then all explain plans that reference the object are marked invalid and must be parsed again
![Page 85: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/85.jpg)
Copyright © 2003, Oracle. All rights reserved.
v$sgastat
v$librarycache
v$sql
v$sqlarea
v$sqltext
v$db_object_cache
Diagnostic Toolsfor Tuning the Library Cache
Parameters affecting the components:SHARED_POOL_SIZE, OPEN_CURSORSSESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIMECURSOR_SHARING, SHARED_POOL_RESERVED_SIZE
sp_m_n.lst
report.txtData dictionary
cache
UGA
Shared pool
Library cache
Shared SQL
and PL/SQLViews
![Page 86: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/86.jpg)
Copyright © 2003, Oracle. All rights reserved.
Are Cursors Being Shared?
• Check gethitration in v$librarycache:
• Determine which statements users are running:
SQL> SELECT sql_text, users_executing, 2 executions, loads3 FROM v$sqlarea;
SQL> SELECT * FROM v$sqltext2 WHERE sql_text LIKE 3 'SELECT * FROM hr.employees WHERE %';
SQL> SELECT gethitratio2 FROM v$librarycache3 WHERE namespace = 'SQL AREA';
![Page 87: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/87.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sharing Cursors
Values for CURSOR_SHARING are:• Exact• Similar• Force
![Page 88: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/88.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines: Library Cache Reloads
• Reloads should be less than 1% of the pins:
• If the reloads-to-pins ratio is greater than 1%, increase the value of the SHARED_POOL_SIZEparameter.
SQL> SELECT SUM(pins) "Executions",2 SUM(reloads) "Cache Misses",3 SUM(reloads)/SUM(pins) 4 FROM v$librarycache;
Executes PROC1 —> 1st pin, 1 loadExecutes PROC1 —> 2nd pin, no reload
Executes PROC1 —> 3rd pin, no reload
Executes PROC1 —> 4th pin, no reload
4 pins and no reloads
![Page 89: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/89.jpg)
Copyright © 2003, Oracle. All rights reserved.
Invalidations
The number of times objects of the namespace weremarked invalid, causing reloads:
SQL> SELECT count(*) FROM hr.employees;SQL> SELECT namespace,pins,reloads,2 invalidations3 FROM v$librarycache;
SQL> execute dbms_stats.gather_table_stats -> ('HR','EMPLOYEES');SQL> SELECT count(*) FROM hr.employees;SQL> SELECT namespace,pins,reloads,2 invalidations3 FROM v$librarycache;
![Page 90: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/90.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing the Library Cache
• Define the global space necessary for stored objects (packages, views, and so on).
• Define the amount of memory used by the usual SQL statements.
• Reserve space for large memory requirements to avoid misses and fragmentation.
• Pin frequently used objects.• Convert large anonymous PL/SQL blocks
into small anonymous blocks callingpackaged functions.
![Page 91: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/91.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared Pool Advisory
SQL> SELECT shared_pool_size_for_estimate AS2 pool_size, estd_lc_size,3 estd_lc_time_saved4 FROM v$shared_pool_advice;
POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED---------- ------------ ------------------
32 8 786840 15 786848 17 786856 17 786864 17 786872 17 786880 17 786888 17 7868
![Page 92: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/92.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise ManagerShared Pool Size Advisor
![Page 93: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/93.jpg)
Copyright © 2003, Oracle. All rights reserved.
Cached Execution Plans
• The Oracle server preserves the execution plan of a cached SQL statement in memory.
• When the SQL statement ages out of the library cache, the corresponding cached execution plan is removed.
• You can use the cached plans to diagnose query performance.
![Page 94: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/94.jpg)
Copyright © 2003, Oracle. All rights reserved.
Views to SupportCached Execution Plans
You can use the v$sql_plan dynamic performance view to view the actual execution plan information for cached cursors.
SQL> SELECT operation, object_owner,2 object_name, cost3 FROM v$sql_plan4 ORDER BY hash_value;
![Page 95: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/95.jpg)
Copyright © 2003, Oracle. All rights reserved.
Support for Cached Execution Plans
• The v$sql view has a column, plan_hash_value, which references the hash_value column of v$sql_plan.
• The column information is a hash value built from the corresponding execution plan.
• The column can be used to compare cursor plans the same way the hash_value column is used to compare cursor SQL texts.
![Page 96: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/96.jpg)
Copyright © 2003, Oracle. All rights reserved.
Global Space Allocation
Stored objects such as packages and views:
SQL statements:
SQL> SELECT SUM(sharable_mem)2 FROM v$db_object_cache;
SUM(SHARABLE_MEM) -----------------
379600
SQL> SELECT SUM(sharable_mem)2 FROM v$sqlarea WHERE executions > 5;
SUM(SHARABLE_MEM) -----------------
381067
![Page 97: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/97.jpg)
Copyright © 2003, Oracle. All rights reserved.
Large Memory Requirements
• Satisfy requests for large contiguous memory• Reserve contiguous memory within the
shared pool
SHARED_POOL_SIZESHARED_POOL_RESERVED_SIZE Data dictionary
cache
UGA
Shared pool
Library cache
Shared SQL
and PL/SQL
v$shared_pool_reserved
![Page 98: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/98.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Shared PoolReserved Space
• Diagnostic tools for tuning:– The v$shared_pool_reserved dictionary view– The supplied aborted_request_threshold
procedure in the dbms_shared_pool package
• Guidelines: Set the SHARED_POOL_RESERVED_SIZE parameter
![Page 99: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/99.jpg)
Copyright © 2003, Oracle. All rights reserved.
Keeping Large Objects
• Find those PL/SQL objects that are not kept in the library cache:
• Pin large packages in the library cache:
SQL> EXECUTE dbms_shared_pool.keep(‘package_name’);
SQL> SELECT * FROM v$db_object_cache2 WHERE sharable_mem > 100003 AND (type='PACKAGE' OR type='PACKAGE BODY' OR4 type='FUNCTION' OR type='PROCEDURE')5 AND kept='NO';
![Page 100: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/100.jpg)
Copyright © 2003, Oracle. All rights reserved.
Anonymous PL/SQL Blocks
Find the anonymous PL/SQL blocks and convertthem into small anonymous PL/SQL blocks that callpackaged functions:
SQL> SELECT sql_text FROM v$sqlarea2 WHERE command_type = 473 AND length(sql_text) > 500;
![Page 101: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/101.jpg)
Copyright © 2003, Oracle. All rights reserved.
Other Parameters Affectingthe Library Cache
• OPEN_CURSORS• CURSOR_SPACE_FOR_TIME• SESSION_CACHED_CURSORS• CURSOR_SHARING
![Page 102: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/102.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Data Dictionary Cache
Use v$rowcache to obtain information about the data dictionary cache.• Content: Definitions of dictionary objects• Terminology:
– gets: Number of requests on objects– getmisses: Number of requests resulting in
cache misses
• Tuning: Avoid dictionary cache misses
![Page 103: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/103.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic Tools for Tuning the Data Dictionary Cache
Sp_1_2.lst
SHARED_POOL_SIZE
Data dictionarycache
UGA
Shared pool
Library cache
Shared SQL
and PL/SQL
v$rowcache:parametergetsgetmisses
![Page 104: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/104.jpg)
Copyright © 2003, Oracle. All rights reserved.
Measuring the Dictionary Cache Statistics
In the Dictionary Cache Stats section of Statspack:• Percent misses should be very low:
– < 2% for most data dictionary objects– < 15% for the entire data dictionary cache
• Cache Usage is the number of cache entriesbeing used.
• Pct SGA is a percentage of usage to allocated size for that cache.
![Page 105: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/105.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Data Dictionary Cache
Keep the percentage of the sum of getmisses to the sum of gets less than 15%:
SQL> SELECT parameter, gets, getmisses 2 FROM v$rowcache;
PARAMETER GETS GETMISSES-------------------------- --------- ---------dc_objects 143434 171dc_synonyms 140432 127
![Page 106: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/106.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statspack report output: (font size incorrect)
If there are too many cache misses, increase theSHARED_POOL_SIZE parameter.
Guidelines: Dictionary Cache Misses
Get Pct Scan Pct Mod Final Pct
Cache Requests Miss Reqs Miss Reqs Usage SGA
------------------ --------- ------ ----- ----- ----- ------ ----
dc_free_extents 2 0. 0 0 3 3
dc_histogram_defs 11 0.0 0 0 49 92
dc_object_ids 19 0.0 0 0 440 98
![Page 107: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/107.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: SharedPool Statistics
![Page 108: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/108.jpg)
Copyright © 2003, Oracle. All rights reserved.
UGA and Oracle Shared Server
Shared pool
PGA
Shared poolor
large pool Stack space
PGA
Stack space
User session
data
Cursor state
UGA
v$statnamev$sesstatv$mystat
OPEN_CURSORSSESSION_CACHED_CURSORS
Cursor state
User session
data
UGA
Dedicated server configuration
Shared server configuration
![Page 109: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/109.jpg)
Copyright © 2003, Oracle. All rights reserved.
Determining the User Global Area Size
• UGA space used by your connection:
• UGA space used by all Oracle Shared Server users:
• Maximum UGA space used by all users:
SQL> SELECT SUM(value) ||'bytes' "Total session memory"2 FROM v$mystat, v$statname3 WHERE name = 'session uga memory'4 AND v$mystat.statistic# = v$statname.statistic#;
SQL> SELECT SUM(value) ||'bytes' "Total session memory"2 FROM v$sesstat, v$statname3 WHERE name = 'session uga memory'4 AND v$sesstat.statistic# = v$statname.statistic#;
SQL> SELECT SUM(value) ||'bytes' "Total max memory"2 FROM v$sesstat, v$statname3 WHERE name = 'session uga memory max'4 AND v$sesstat.statistic# = v$statname.statistic#;
![Page 110: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/110.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared pool
Library cache
Data dictionary cache
User global area
Database buffer cache
Redo log buffer Large pool
Large Pool
• Can be configured as a separate memory area in the SGA, used for memory with:– I/O server processes: DBWR_IO_SLAVES– Backup and restore operations– Session memory for the shared servers– Parallel query messaging
• Used to avoid performance overhead caused by shrinking the shared SQL cache
• Sized by the parameter LARGE_POOL_SIZE
![Page 111: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/111.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Size shared SQL and PL/SQL areas (library cache)• Size data dictionary cache or row cache• Size the large pool• Allow for the user global area, if using Oracle
Shared Server connections
![Page 112: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/112.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Buffer Cache
![Page 113: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/113.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Employ the buffer cache sizing advisor• Describe how the buffer cache is used by different
Oracle processes• Create and manage different buffer caches• Monitor the use of the buffer caches• Identify and resolve buffer cache performance
problems
![Page 114: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/114.jpg)
Copyright © 2003, Oracle. All rights reserved.
Buffer Cache Characteristics
Data files
SGA DB buffer cache
Checkpoint Queue
.
.
..
LRU lists
DBWn
Server
DB_BLOCK_SIZEDB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_BLOCK_CHECKSUM
.
..
.
![Page 115: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/115.jpg)
Copyright © 2003, Oracle. All rights reserved.
Buffer Cache Sizing Parameters
• The buffer cache can consist of independent subcaches for buffer pools and for multipleblock sizes.
• The DB_BLOCK_SIZE parameter determines the primary block size, which is the block size usedfor the system tablespace and the primary buffer caches (recycle, keep, and default).
• The following parameters define the sizes of the caches for buffers for the primary block size:– DB_CACHE_SIZE– DB_KEEP_CACHE_SIZE– DB_RECYCLE_CACHE_SIZE
![Page 116: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/116.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dynamic Buffer CacheAdvisory Parameter
• The buffer cache advisory feature enables and disables statistics gathering for predicting behavior with different cache sizes.
• DBAs can use the information provided by these statistics to size the buffer cache optimally for a given workload.
• The buffer cache advisory is enabled by means of the DB_CACHE_ADVICE initialization parameter:– This parameter is dynamic and can be changed
using ALTER SYSTEM.– Three values are allowed: Off, On, and Ready.
![Page 117: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/117.jpg)
Copyright © 2003, Oracle. All rights reserved.
View to Support Buffer Cache Advisory
• Buffer cache advisory information is collected inthe v$db_cache_advice view.
• The view contains different rows that estimate the number of physical reads for different caches.
• The rows also compute a physical read factor, which is the ratio of the number of estimated reads to the number of actual reads.
![Page 118: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/118.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using the v$db_cache_advice View
SQL> SELECT size_for_estimate "Cache Size (MB)",2 buffers_for_estimate "Buffers",3 estd_physical_read_factor AS4 "Estd Phys Read Factor",4 estd_physical_reads "Estd Phys Reads"5 FROM v$db_cache_advice6 WHERE name = 'DEFAULT'7 AND block_size = (8 SELECT value9 FROM v$parameter
10 WHERE name = 'db_block_size')11 AND advice_status = 'ON';
![Page 119: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/119.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using the Buffer Cache Advisory
![Page 120: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/120.jpg)
Copyright © 2003, Oracle. All rights reserved.
SGA DB buffer cache
Checkpoint Queue
.
.
..
LRU lists
.
..
.
Managing the Database Buffer Cache
DBWn LGWR
Data files
1Server 2
23
4
![Page 121: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/121.jpg)
Copyright © 2003, Oracle. All rights reserved.
SGA DB buffer cache
Checkpoint Queue
.
.
..
.
..
.
Managing the Database Buffer Cache
DBWn LGWR
Server
Data files
LRU lists
![Page 122: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/122.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Goals and Techniques
• Tuning goals:– Servers find data in memory– No waits on the buffer cache
• Diagnostic measures– Wait events– Cache hit ratio– The v$db_cache_advice view
• Tuning techniques:– Reduce the number of blocks required by SQL
statements – Increase buffer cache size– Use multiple buffer pools– Cache tables– Bypass the cache for sorting and parallel reads
![Page 123: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/123.jpg)
Copyright © 2003, Oracle. All rights reserved.
v$sysstat
Diagnostic Tools
SGA
LRU list
Checkpoint queue
Buffer cache
Keep buffer pool
Recycle buffer pool
v$buffer_pool
DB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZE
v$bh
v$sesstatv$system_eventv$session_wait
v$cache
v$buffer_pool_statistics
Statspack Report
v$db_cache_advice
![Page 124: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/124.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager
![Page 125: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/125.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL> SELECT name, value2 FROM v$sysstat3 WHERE name = 'free buffer inspected';
NAME VALUE --------------------------- --------free buffer inspected 183
From v$sysstat:
Buffer Cache Performance Indicators
![Page 126: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/126.jpg)
Copyright © 2003, Oracle. All rights reserved.
More Buffer Cache Performance Indicators
From v$system_event:
SQL> SELECT event, total_waits2 FROM v$system_event3 WHERE event in4 ('free buffer waits',5 'buffer busy waits');
EVENT TOTAL_WAITS ---------------------- -----------free buffer waits 337buffer busy waits 3466
![Page 127: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/127.jpg)
Copyright © 2003, Oracle. All rights reserved.
Measuring the Cache Hit Ratio
• From v$sysstat:
• From the Statspack report:
SQL> SELECT 1 - (phy.value - lob.value - dir.value)2 / ses.value "CACHE HIT RATIO"3 FROM v$sysstat ses, v$sysstat lob,4 v$sysstat dir, v$sysstat phy5 WHERE ses.name = 'session logical reads'6 AND dir.name = 'physical reads direct'7 AND lob.name = 'physical reads direct (lob)'8 AND phy.name = 'physical reads';
Statistic Total Per PerTrans Logon Second
------------------------- -------- ------ --------physical reads 15,238 13.0 15,238.0physical reads direct 863 0.7 863.0Physical reads direct(lob) 0 0 0session logical reads 119,376 101.8 119,376.0
![Page 128: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/128.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines for Using the Cache Hit Ratio
Hit ratio is affected by data access methods:• Full table scans• Data or application design• Large table with random access• Uneven distribution of cache hits
![Page 129: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/129.jpg)
Copyright © 2003, Oracle. All rights reserved.
Buffer Cache Hit Ratio Isn’t Everything
• A badly tuned database can still have a hit ratioof 99% or better.
• Hit ratio is only one part in determining tuning performance.
• Hit ratio does not determine whether a database is optimally tuned.
• Use the Oracle Wait Interface to examine whatis causing a bottleneck.– v$session_wait– v$session_event– v$system_event
• Tune SQL statements.
![Page 130: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/130.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines to Increase the Cache Size
Increase the cache size ratio under the following conditions:• Any wait events have been tuned• SQL statements have been tuned• There is no undue page faulting• The previous increase of the buffer cache
was effective• Low cache hit ratio
![Page 131: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/131.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Multiple Buffer Pools
SGADB buffer caches
Keep pool
Recycle pool
Default pool
![Page 132: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/132.jpg)
Copyright © 2003, Oracle. All rights reserved.
Defining Multiple Buffer Pools
In the Oracle database:• Individual pools have their own size defined by:
– DB_CACHE_SIZE– DB_KEEP_CACHE_SIZE– DB_RECYCLE_CACHE_SIZE
• These parameters are dynamic.• Latches are automatically allocated by Oracle
RDBMS.
![Page 133: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/133.jpg)
Copyright © 2003, Oracle. All rights reserved.
Enabling Multiple Buffer Pools
CREATE INDEX cust_idx …STORAGE (BUFFER_POOL KEEP …);
ALTER TABLE customerSTORAGE (BUFFER_POOL RECYCLE);
ALTER INDEX cust_name_idxSTORAGE (BUFFER_POOL KEEP);
![Page 134: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/134.jpg)
Copyright © 2003, Oracle. All rights reserved.
KEEP Buffer Pool Guidelines
• Tuning goal: Keeping blocks in memory• Size: Holds all or nearly all blocks of the segments
assigned to this pool• Tool: dbms_stats.gather_table_statsSQL> EXECUTE dbms_stats.gather_table_stats -> ('HR','DEPARTMENTS');
SQL> SELECT table_name, blocks2 FROM dba_tables3 WHERE owner = 'HR' 4 AND table_name = 'DEPARTMENTS';
TABLE_NAME BLOCKS---------- ----------DEPARTMENTS 1
![Page 135: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/135.jpg)
Copyright © 2003, Oracle. All rights reserved.
RECYCLE Buffer Pool Guidelines
• Tuning goal: Eliminate blocks from memory when transactions are completed
• Size: Holds only active blocks• Tool: v$cache
SQL> SELECT owner#, name, count(*) blocks2 FROM v$cache3 GROUP BY owner#, name;
OWNER# NAME BLOCKS------ ---------- ----------
5 CUSTOMER 147
![Page 136: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/136.jpg)
Copyright © 2003, Oracle. All rights reserved.
RECYCLE Buffer Pool Guidelines
Tool: v$sess_io
SQL> SELECT s.username, io.block_gets,2 io.consistent_gets, io.physical_reads3 FROM v$sess_io io, v$session s4 WHERE io.sid = s.sid ;
USERNAME BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS-------- ---------- --------------- --------------
HR 21874 2327 1344
![Page 137: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/137.jpg)
Copyright © 2003, Oracle. All rights reserved.
Calculating the Hit Ratio for Multiple Pools
SQL> SELECT name, 1 - (physical_reads /2 (db_block_gets + consistent_gets)) "HIT_RATIO" 3 FROM v$buffer_pool_statistics4 WHERE db_block_gets + consistent_gets > 0;
NAME HIT_RATIO------------------ ----------KEEP .983520845RECYCLE .503866235DEFAULT .790350047
![Page 138: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/138.jpg)
Copyright © 2003, Oracle. All rights reserved.
Identifying Candidate Pool Segments
• Keep pool– Blocks are accessed repeatedly.– Segment size is less than 10% of the default buffer
pool size.
• Recycle pool– Blocks are not reused outside of transaction.– Segment size is more than twice the default buffer
pool size.
![Page 139: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/139.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dictionary Views with BufferPool Information
SQL> SELECT id, name, block_size, buffers2 FROM v$buffer_pool;
ID NAME block_size BUFFERS-- ------- ---------- --------1 KEEP 4096 140002 RECYCLE 4096 20003 DEFAULT 4096 4000
![Page 140: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/140.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Segment Space Management
• Manages free space automatically inside database segments
• Tracks segment free/used space with bitmaps instead of free lists
• Provides better space utilization, especially for the objects with highly varying size rows
• Specified when creating a tablespace
• Supported by Oracle Enterprise Manager
![Page 141: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/141.jpg)
Copyright © 2003, Oracle. All rights reserved.
Auto-Management of Free Space
• Create an auto-managed tablespace:
• Create a table that uses auto-management offree space:
CREATE TABLE bit_seg_table(idnum NUMBER)TABLESPACE bit_seg_ts;
CREATE TABLESPACE BIT_SEG_TSDATAFILE '$HOME/ORADATA/u04/bit_seg01.dbf' SIZE 1MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;
![Page 142: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/142.jpg)
Copyright © 2003, Oracle. All rights reserved.
Free Lists
• A free list for an object maintains a list of blocks that are available for inserts.
• The number of free lists for an object can beset dynamically.
• Single-CPU systems do not benefit greatly from multiple free lists.
• The tuning goal is to ensure that an object has sufficient free lists to minimize contention.
• Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database.
![Page 143: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/143.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnosing Free List Contention
v$waitstat columns:
CLASS “segment header”
COUNT TIMESGAData buffer cache
v$system_eventcolumns:
EVENT “buffer busy waits” TOTAL_WAITS
FREELISTS
![Page 144: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/144.jpg)
Copyright © 2003, Oracle. All rights reserved.
SGAData buffer cache
Resolving Free List Contention
Serverprocess
v$session_wait columns:
EVENT “buffer busy waits” P1 “FILE” P2 “BLOCK” P3 “ID”
dba_segments columns:SEGMENT_NAME SEGMENT_TYPE FREELISTS HEADER_FILE HEADER_BLOCK FREELISTS
Serverprocess
Object ID
![Page 145: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/145.jpg)
Copyright © 2003, Oracle. All rights reserved.
Multiple DBWn Processes
Multiple DB Writer (DBWn) processes:• Can be deployed with DB_WRITER_PROCESSES
(DBW0 to DBW9)• Can be useful for SMP systems with large
numbers of CPUs• Cannot concurrently be used with multiple
I/O slavesThe DBA can turn asynchronous I/O on or off with the DISK_ASYNCH_IO parameter.
![Page 146: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/146.jpg)
Copyright © 2003, Oracle. All rights reserved.
Multiple I/O Slaves
• Provide nonblocking asynchronous I/O requests• Are typically not recommended if asynchronous
I/O is available• Follow the naming convention ora_innn_SID
![Page 147: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/147.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning DBWn I/O
Tune the DB Writer processes by looking at the value of the FREE BUFFER WAITS event.
![Page 148: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/148.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Employ the buffer cache sizing advisor• Describe how the buffer cache is used by different
Oracle processes• Create and manage different buffer caches• Monitor the use of the buffer caches• Identify and resolve buffer cache
performance problems
![Page 149: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/149.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dynamic Instance Resizing
![Page 150: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/150.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Implement dynamic SGA allocation• Dynamically adjust the buffer caches• Dynamically adjust the shared pool
![Page 151: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/151.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dynamic SGA Feature
• The dynamic SGA feature implements an infrastructure to allow the server to change its SGA configuration without shutting downthe instance.
• SGA is limited by SGA_MAX_SIZE.• A dynamic SGA can grow and shrink in response
to a DBA command.
![Page 152: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/152.jpg)
Copyright © 2003, Oracle. All rights reserved.
Unit of Allocation in the Dynamic SGA
• In the dynamic SGA model, the unit of memory allocation is called a granule.
• SGA memory is tracked in granules bySGA components.
• A granule is a unit of contiguous virtual memory allocation.
• Use v$buffer_pool to monitor size of thebuffer caches.
![Page 153: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/153.jpg)
Copyright © 2003, Oracle. All rights reserved.
Granule
• SGA components are allocated and deallocated in units of contiguous memory called granules.
• The size of a granule depends on the estimated total SGA. If the estimated SGA size is:– Less than or equal to 128 MB then the granule size
is 4 MB– Greater than 128 MB then the granule size is 16 MB
![Page 154: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/154.jpg)
Copyright © 2003, Oracle. All rights reserved.
Allocating Granules at Startup
• At instance startup, the Oracle server requests SGA_MAX_SIZE bytes of address space in memory.
• As startup continues, each component will attempt to acquire the number of granules assigned.
• The minimum SGA configuration is three granules:– One granule for fixed SGA (includes redo buffers)– One granule for the buffer cache– One granule for the shared pool
![Page 155: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/155.jpg)
Copyright © 2003, Oracle. All rights reserved.
Adding Granules to Components
• A DBA can dynamically increase memory allocation to a component by issuing an ALTER SYSTEM command.
• Increasing the memory use of a component succeeds only if there is enough free granules to satisfy the request.
• Memory granules are not freed automatically from another component to satisfy the increase.
• Decreasing the size of a component is possible, but only if the granules being released are unused by the component.
![Page 156: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/156.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dynamic Buffer Cache Size Parameters
• Parameters that specify the size of buffer cache components are dynamic and can be changed while the instance is running by means of theALTER SYSTEM command:
• Each parameter is sized independently.• New cache sizes are set to the next granule
boundary.• The allocation size has the following limits:
– It must be an integer multiple of the granule size.– The total SGA size cannot exceed SGA_MAX_SIZE.– DB_CACHE_SIZE can never be set to zero.
ALTER SYSTEM SET DB_CACHE_SIZE = 1100M;
![Page 157: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/157.jpg)
Copyright © 2003, Oracle. All rights reserved.
Example: Increasing the Size of an SGA Component
Initial parameter values:• SGA_MAX_SIZE = 128M• DB_CACHE_SIZE = 88M• SHARED_POOL_SIZE = 32M
• Error message returned: insufficient memory
• Error message returned: insufficient memory• Check v$buffer_pool to confirm shrink status.
• The statement is now processed.
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
ALTER SYSTEM SET DB_CACHE_SIZE = 56M;ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;
![Page 158: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/158.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Implement dynamic SGA allocation• Dynamically adjust the buffer caches• Dynamically adjust the shared pool
![Page 159: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/159.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing Other SGA Structures
![Page 160: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/160.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Monitor and size the redo log buffer• Monitor and size the Java pool• Control the amount of Java session memory used
by a session
![Page 161: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/161.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database buffer cache
Redo log
buffer
Shared pool Library cache
Data dictionary cache
User global area
The Redo Log Buffer
SQL> UPDATE employees2 SET salary=salary*1.13 WHERE employee_id=736;
Serverprocess
LGWR
Control files
ARCn
Archivedlog files
Redo log filesData files
![Page 162: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/162.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing the Redo Log Buffer
• Adjust the LOG_BUFFER parameter.• Default value: Either 512K or 128K * the value of
CPU_COUNT, whichever is greater.
![Page 163: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/163.jpg)
Copyright © 2003, Oracle. All rights reserved.
Archivedlog filesRedo log files
Diagnosing Redo Log Buffer Inefficiency
SQL> UPDATE employees2 SET salary=salary*1.13 WHERE employee_id=736;
Serverprocess
LGRW
ARCH
Serverprocess
SQL> DELETE FROM employees2 WHERE employee_id=7400;
![Page 164: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/164.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Dynamic Views to Analyze Redo Log Buffer Efficiency
v$session_waitLog Buffer Space event
v$sysstatRedo Buffer Allocation RetriesRedo Entries
Redo log buffer
![Page 165: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/165.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager
![Page 166: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/166.jpg)
Copyright © 2003, Oracle. All rights reserved.
Redo Log Buffer Tuning Guidelines
• There should be no Log Buffer Space waits.
• Redo Buffer Allocation Retries value should be near 0 and should be less than 1% of redo entries.
SQL> SELECT name, value2 FROM v$sysstat3 WHERE name IN ('redo entries',4 'redo buffer allocation retries');
SQL> SELECT sid, event, seconds_in_wait, state2 FROM v$session_wait3 WHERE event = 'log buffer space';
![Page 167: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/167.jpg)
Copyright © 2003, Oracle. All rights reserved.
Reducing Redo Operations
Ways to avoid logging bulk operations in the redo log:• Direct Path loading without archiving does not
generate redo.• Direct Path loading with archiving can use
Nologging mode.• Direct Load Insert can use Nologging mode.• Some SQL statements can use Nologging mode.
![Page 168: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/168.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Java Pool Memory
Limit Java session memory usage:• JAVA_SOFT_SESSIONSPACE_LIMIT• JAVA_MAX_SESSIONSPACE_SIZE
SQL> SELECT * FROM v$sgastat
2 WHERE pool = 'java pool';
POOL NAME BYTES
----------- ---------------------- ----------
java pool free memory 30261248
java pool memory in use 19742720
![Page 169: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/169.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing the SGA for Java
• SHARED_POOL_SIZE:– 8 KB per loaded class– 50 MB for loading large JAR files
• Configure Oracle Shared Server• JAVA_POOL_SIZE
– 24 MB default– 50 MB for medium-sized Java application
![Page 170: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/170.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Monitor and size the redo log buffer• Monitor and size the Java pool• Control the amount of Java session memory used
by a session
![Page 171: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/171.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Oracle Shared Server
![Page 172: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/172.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Identify issues associated with managing users in
an Oracle Shared Server environment• Configure the Oracle Shared Server environment
to optimize performance• Diagnose and resolve performance issues with
Oracle Shared Server processes
![Page 173: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/173.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview
Database serverListener
Client
System Global Area
Oracle background processes
Dispatcher processes
Sharedserverprocesses
Oracle servercode programinterface
Request queue Response queues
![Page 174: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/174.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Shared Server Characteristics
• Enables users to share processes• Supports Oracle Net functionality• Increases the number of concurrent users• Useful on servers with remote clients• CPU overhead could possibly increase for each
individual user request
![Page 175: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/175.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Dispatchers
• Use the following dynamic views:– v$shared_server_monitor– v$dispatcher– v$dispatcher_rate
• Identify contention for dispatchers by checking:– Busy rates– Dispatcher waiting time
• Check for dispatcher contention.• Add or remove dispatchers while the database
is open.
![Page 176: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/176.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Shared Servers
• Oracle Shared Servers are started up dynamically.• However, you should monitor the shared
servers by:– Checking for shared server process contention– Adding or removing idle shared servers
![Page 177: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/177.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Process Usage
The v$circuit view displays:• Server address• Dispatcher address• User session address
![Page 178: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/178.jpg)
Copyright © 2003, Oracle. All rights reserved.
Shared Servers and Memory Usage
• Some user information goes into the shared pool.• To reduce the load on the shared pool set a
large pool.• Overall memory demand is lower when using
shared servers.• Shared servers use the user global area (UGA)
for sorts.
![Page 179: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/179.jpg)
Copyright © 2003, Oracle. All rights reserved.
Troubleshooting
Possible causes of problems with the shared server include the following:• The database listener is not running.• The Oracle Shared Server initialization parameters
are set incorrectly.• The dispatcher process has been killed.• The DBA does not have a dedicated connection.• The PROCESSES parameter is too low.
![Page 180: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/180.jpg)
Copyright © 2003, Oracle. All rights reserved.
Obtaining Dictionary Information
Dynamic performance views:• v$circuit• v$dispatcher• v$dispatcher_rate• v$queue• v$shared_server_monitor• v$session• v$shared_server
![Page 181: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/181.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Describe the Oracle Shared Server as a resource-
sharing configuration• List some situations in which it is appropriate to
use the Oracle Shared Server• Monitor dispatcher and server usage• Troubleshoot Oracle Shared Server configuration
![Page 182: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/182.jpg)
Copyright © 2003, Oracle. All rights reserved.
Optimizing Sort Operations
![Page 183: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/183.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• List the operations that use temporary space• Create and monitor temporary tablespaces• Identify actions that use the temporary tablespace• Describe and differentiate disk sorts and memory
sorts• Identify the SQL operations that require sorts• List ways to reduce total sorts and disk sorts• Determine the number of memory sorts performed• Set parameters to optimize sorts
![Page 184: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/184.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Sort Area Management
• Parameters for automatic sort area management:– PGA_AGGREGATE_TARGET
(Ranges from 10 MB to 4000 GB)– WORKAREA_SIZE_POLICY– AUTO | MANUAL
• Replaces all *_AREA_SIZE parameters
![Page 185: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/185.jpg)
Copyright © 2003, Oracle. All rights reserved.
PGA Management Resources
Statistics to manage the PGA_AGGREGATE_TARGETinitialization parameter• Views for monitoring the PGA work area include:
– v$sql_workarea_histogram– v$pgastat– v$sql_workarea_active– v$sql_workarea– v$tempseg_usage
• Views to assist in sizing the PGA work area are:– v$pga_target_advice– v$pga_target_advice_histogram
![Page 186: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/186.jpg)
Copyright © 2003, Oracle. All rights reserved.
Work Area Groups and PGA CacheHit Percentages
… 64-128KB
Percentexecutions
100%
0% 0%
128-256KB …
94%
6% 0%
97%
3%0%
64-128MB
Optimal sizeOne-pass sizeMulti-pass size
![Page 187: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/187.jpg)
Copyright © 2003, Oracle. All rights reserved.
Determining PGA Workload
SQL> SELECT low_optimal_size/1024 AS low_kb,2 (high_optimal_size+1)/1024 AS high_kb,3 ROUND(100*optimal_executions4 /total_executions) AS optimal,5 ROUND(100*onepass_executions6 /total_executions) AS onepass,7 ROUND(100*multipasses_executions8 /total_executions) AS multipass9 FROM v$sql_workarea_histogram10 WHERE total_executions != 011 ORDER BY low_kb;
![Page 188: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/188.jpg)
Copyright © 2003, Oracle. All rights reserved.
Other Views for Work Areas
SQL> SELECT * 2 FROM v$pgastat3 WHERE name = 'cache hit percentage';
NAME VALUE UNIT------------------------- ---------- ---------cache hit percentage 93 percent
![Page 189: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/189.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL> SELECT ROUND(pga_target_for_estimate2 /1024/1024) AS target_mb,3 estd_pga_cache_hit_percentage AS4 cache_hit_percent, estd_overalloc_count5 FROM v$pga_target_advice6 ORDER BY target_mb;
Querying v$pga_target_advice
![Page 190: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/190.jpg)
Copyright © 2003, Oracle. All rights reserved.
Understanding v$pga_target_advice
TARGET_MB CACHE_HIT_PERCENT ESTD_OVERALLOC_COUNT---------- ----------------- --------------------
63 23 367125 24 30250 30 3375 39 1500 58 0600 59 0700 59 0800 60 0900 60 0
1000 61 01500 67 02000 76 03000 83 04000 85 0
![Page 191: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/191.jpg)
Copyright © 2003, Oracle. All rights reserved.
PGA Sizing Advisor Outputin Oracle Enterprise Manager
![Page 192: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/192.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview
The automatic sort area management feature is:• Easier to set up and size than the
*_SORT_AREA parameters• Easier to monitor using the advisory view
![Page 193: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/193.jpg)
Copyright © 2003, Oracle. All rights reserved.
The Sorting Process
If sort space requirement is greater than SORT_AREA_SIZE:
Segments hold data while the server works on another sort run
Serverprocess
Sort run 1 Sort run 2
TEMPORARY tablespace
Sort run 2
Temporary segment
![Page 194: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/194.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sort Area and Parameters
The sort space is in:• The PGA for a dedicated server connection
• The shared pool for Oracle Shared Server connection
Shared pool
PGA
Stack space
User session
data
Cursor state
UGA
Sort area
Stack space
PGA
User session
data
Cursor state
UGA
Sort area
![Page 195: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/195.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sort Area and Parameters
• An execution plan can contain multiple sorts• A single server performing a sort needs:
– An area of SORT_AREA_SIZE, in bytes
– At least one area of SORT_AREA_RETAINED_SIZEfor a join sort
• Each parallel query server needs SORT_AREA_SIZE
• Two sets of servers can be writing at once, so:– Calculate SORT_AREA_SIZE × 2 × degree of
parallelism– Add SORT_AREA_RETAINED_SIZE × degree of
parallelism × number of sorts above two
![Page 196: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/196.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Sorts
• Use automatic sort area management.• Avoid sort operations whenever possible.• Reduce swapping and paging by making sure that
sorting is done in memory when possible.• Reduce space allocation calls by allocating
temporary space appropriately.
![Page 197: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/197.jpg)
Copyright © 2003, Oracle. All rights reserved.
The Sorting Process and Temporary Space
Create a temporary tablespace by using:
Temporary tablespace
2M
One single sort segment
Permanent Objects
2M 2M
temp01.dbf temp02.dbf temp04.dbf
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE'$HOME/ORADATA/u06/temp01.dbf' size 200M;
![Page 198: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/198.jpg)
Copyright © 2003, Oracle. All rights reserved.
Temporary Space Segments
A temporary space segment:• Is created by the first sort• Extends as demands are made on it• Comprises extents, which can be used by
different sorts• Is described in the sort extent pool (SEP)
![Page 199: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/199.jpg)
Copyright © 2003, Oracle. All rights reserved.
Operations Requiring Sorts
Some of the operations that may require sorts are:• Index creation• Parallel insert operations involving index
maintenance• ORDER BY or GROUP BY clauses• DISTINCT values selection• UNION, INTERSECT, or MINUS operators• Sort-merge joins• ANALYZE command execution
![Page 200: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/200.jpg)
Copyright © 2003, Oracle. All rights reserved.
Avoiding Sorts
Avoid sort operations whenever possible:• Use NOSORT to create indexes.
• Use UNION ALL instead of UNION.
• Use index access for table joins.• Create indexes on columns referenced in the
ORDER BY clause.
• Select the columns for analysis.• Use ESTIMATE rather than COMPUTE for
large objects.
![Page 201: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/201.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic Tools
v$sysstat
Sort area (UGA)
SORT_AREA_SIZESORT_AREA_RETAINED_SIZE
Serverprocess TEMPORARY
tablespace
Sort on disk
Sort in memory
PCTINCREASEINITIALNEXT
Statspack
v$sort_usage v$sort_segment
![Page 202: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/202.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL> SELECT d.value "Disk", m.value "Mem",2 (d.value/m.value)*100 "Ratio"3 FROM v$sysstat m, v$sysstat d4 WHERE m.name = 'sorts (memory)'5 AND d.name = 'sorts (disk)';
Disk Mem Ratio--------- --------- ---------
23 206 11.165049
Diagnostics and Guidelines
• In an OLTP system the ratio of disk sorts to memory sorts should be less than 5%.
• Increase the value of SORT_AREA_SIZE /PGA_AGGREGATE_TARGET if the ratio is greaterthan 5%.
![Page 203: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/203.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: Sorts
![Page 204: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/204.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL> SELECT tablespace_name, current_users, total_extents,2 used_extents, extent_hits, max_used_blocks,3 max_sort_blocks4 FROM v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS --------------- ------------- ------------- ----------------------- --------------- ---------------TEMP 2 4 3 20 200 200
Monitoring Temporary Tablespaces
• Default storage parameters apply to sort segments.• Sort segments have unlimited extents.
![Page 205: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/205.jpg)
Copyright © 2003, Oracle. All rights reserved.
Temporary Tablespace Configuration
• Set appropriate storage values.• Set up different temporary tablespaces based on
sorting needs.
• Stripe temporary tablespaces.• Use v$tempfile and dba_temp_files for
information on temporary files.
SQL> SELECT session_num, tablespace, extents, blocks2 FROM v$sort_usage;
SESSION_NUM TABLESPACE EXTENTS BLOCKS ----------- ---------------------- -------- --------
16 TEMP 4 200
![Page 206: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/206.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• List the operations that use temporary space• Create and monitor temporary tablespaces• Identify actions that use the temporary tablespace• Describe the use of disk and memory for sorting• Identify the SQL operations that require sorts• Differentiate between disk and memory sorts• List ways to reduce total sorts and disk sorts• Determine the number of memory sorts performed• Set parameters to optimize sorts
![Page 207: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/207.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Resource Manager
![Page 208: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/208.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Set up Database Resource Manager• Assign users to Resource Manager groups• Create resource plans within groups
![Page 209: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/209.jpg)
Copyright © 2003, Oracle. All rights reserved.
OLTP More resources
DSSuser
OLTPuser
Database resource manager
DSS Less resources Oracle9i
Overview
• Manage mixed workload• Control system performance
![Page 210: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/210.jpg)
Copyright © 2003, Oracle. All rights reserved.
Resource plan directives
Database ResourceManager Concepts
User groups with similarresource needs (one active resource consumer group per session)
Allocates CPU and PQ servers (one active plan)
Assigns groups and resources to plans
Resource consumer
group
Resource plan
![Page 211: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/211.jpg)
Copyright © 2003, Oracle. All rights reserved.
MYDBPLAN
MAIL MAINTGroup
OTHERGroups
ONLINEGroup
BATCHGroup
BUG MAINTGroup
BUGDBPLAN
70% @ L1
100% @ L2
100% @ L3
30% @ L1
100% @ L340% @ L1
100% @ L2 20% @ L1
Using Sub-Plans to Limit CPU Utilization
MAILDBPLAN
80% @ L1
POSTMANGroup
![Page 212: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/212.jpg)
Copyright © 2003, Oracle. All rights reserved.
Method
Round-robin
Emphasis
Absolute
Resource
CPU to sessions
CPU to groups
Parallel degree
Recipient
Groups
Plans
Plans
Resource Allocation Methods
![Page 213: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/213.jpg)
Copyright © 2003, Oracle. All rights reserved.
Resourceconsumer
group
Allocation methods
SYS_GROUP
OTHER_GROUPS
LOW_GROUP
100% 0% 0% 0
0% 100% 0% 0
0% 0% 100% 0
P1CPU P2CPU P3CPU P1//
The Initial Plan: SYSTEM_PLAN
![Page 214: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/214.jpg)
Copyright © 2003, Oracle. All rights reserved.
Administering the DatabaseResource Manager
• Assign the resource manager system privilegesto the administrator.
• Create resource objects with the package dbms_resource_manager:– Resource consumer groups– Resource plans– Resource plan directives
• Assign users to groups with the package dbms_resource_manager_privs.
• Specify the plan to be used by the instance.
![Page 215: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/215.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise Manager:Resource Manager
![Page 216: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/216.jpg)
Copyright © 2003, Oracle. All rights reserved.
dbms_resource_manager_privs.grant_system_privilege (grantee_name => 'OE',privilege_name => 'ADMINISTER_RESOURCE_MANAGER',admin_option => False );
Assigning the Resource Manager Privilege
Assign the resource manager system privileges to the administrator.
![Page 217: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/217.jpg)
Copyright © 2003, Oracle. All rights reserved.
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group (consumer_group => 'OLTP', comment => 'Online users' );
Creating Database ResourceManager Objects
• Create resource objects with the dbms_resource_manager package.
• Create a pending area.
• Create resource consumer groups.
![Page 218: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/218.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Database ResourceManager Objects
• Create resource plans.
• Create resource plan directives.
dbms_resource_manager.create_plan (plan => 'NIGHT', comment => 'DSS/Batch priority, ...' );
dbms_resource_manager.create_plan_directive (plan => 'NIGHT',group_or_subplan => 'SYS_GROUP',comment => '...',cpu_p1 => 100,parallel_degree_limit_p1 => 20);
![Page 219: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/219.jpg)
Copyright © 2003, Oracle. All rights reserved.
Active Session Pool
DBAs use Database Resource Manager to limit the amount of concurrent active sessions per resource consumer group by defining an active session pool.Benefits of an active session pool:• DBAs can meet performance service-level
objectives by limiting the concurrent system workload.
• The number of servers taking resources in the system is reduced. This avoids inefficient paging, swapping, and other resource depletion.
![Page 220: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/220.jpg)
Copyright © 2003, Oracle. All rights reserved.
Active Session Pool Mechanism
• The active session pool:– Size can be set per resource consumer group.– Size is limited to only one per consumer group.– Is the maximum number of concurrently active
sessions.– Is defined as a session currently part of an active
transaction, query, or parallel operation.
• When the active session pool is filled with active sessions, all subsequent sessions attempting to become active are queued.
• Individual parallel slaves do not count toward the number of sessions. The entire parallel operation counts as one active session.
![Page 221: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/221.jpg)
Copyright © 2003, Oracle. All rights reserved.
Active Session Pool Parameters
The active session pool is defined by setting theparameters:• ACTIVE_SESS_POOL_P1
– Identifies the number of active sessions that establishes the resource consumer group's threshold
– Default is 1000000• QUEUEING_P1
– Indicates how long, in seconds, any session will wait on the queue before aborting the current operation
– Default is 1000000
![Page 222: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/222.jpg)
Copyright © 2003, Oracle. All rights reserved.
Setting the Active Session Pool
Example:
OLTP: Set no limit on concurrent active sessions.BATCH: Set to limit concurrent active sessions to 5. QUEUEING_P1, set to 600, aborts all operations that wait on the queue for more than ten minutes.
GROUP
OLTP
BATCH
ACTIVE SESSION POOL
ACTIVE_SESS_POOL_P1 = 5
QUEUEING_P1 = 600
![Page 223: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/223.jpg)
Copyright © 2003, Oracle. All rights reserved.
Maximum Estimated Execution Time
• The Database Resource Manager can estimate the execution time of an operation proactively.
• A DBA can specify a maximum estimated execution time for an operation at the resource consumer group level.
• Operation will not start if the estimate is longer than MAX_EST_EXEC_TIME.
• The benefit of this feature is the elimination of the exceptionally large job that uses too many system resources.
• The default is 1000000 seconds.
![Page 224: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/224.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Consumer Group Switching
• The Database Resource Manager automatically switches a session’s consumer group based on the following resource plan directive parameters:– SWITCH_GROUP: Group switched to. Default is NULL.– SWITCH_TIME: Active time in seconds. Default is
1000000.– SWITCH_ESTIMATE: If value is True, execution time
estimate is used to decide whether to switch an operation even before it starts. Default is False.
• This feature can be used to limit the resources consumed by long-running operations.
![Page 225: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/225.jpg)
Copyright © 2003, Oracle. All rights reserved.
Undo Quota
The UNDO_POOL plan directive:• Limits the amount of undo space that can be used• When exceeded, prevents DML• SELECT statements are still allowed• Is specified in kilobytes• Default is 1000000
![Page 226: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/226.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Database ResourceManager Objects
• Validate the pending area.
• Commit the pending area.
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
![Page 227: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/227.jpg)
Copyright © 2003, Oracle. All rights reserved.
Assigning Users to Consumer Groups
• Assign users to groups.
• Set the initial consumer group for users:
dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name => 'MOIRA',consumer_group => 'OLTP',grant_option => False );
dbms_resource_manager.set_initial_consumer_group (user => 'MOIRA',consumer_group => 'OLTP' );
![Page 228: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/228.jpg)
Copyright © 2003, Oracle. All rights reserved.
Setting the Resource Plan for an Instance
• Specify the plan to be used by the instance.• Specify the RESOURCE_MANAGER_PLAN
initialization parameter.
• Change the resource plan without shutting down and restarting the instance.
RESOURCE_MANAGER_PLAN=day
ALTER SYSTEMSET RESOURCE_MANAGER_PLAN=night;
![Page 229: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/229.jpg)
Copyright © 2003, Oracle. All rights reserved.
dbms_session.switch_current_consumer_group (new_consumer_group => 'DSS',old_consumer_group => v_old_group,initial_group_on_error => False );
Changing a Consumer GroupWithin a Session
The user or the application can switch the current consumer group.
![Page 230: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/230.jpg)
Copyright © 2003, Oracle. All rights reserved.
dbms_resource_manager.switch_consumer_group_for_sess (session_id => 7,session_serial => 13,consumer_group => 'OLTP');
dbms_resource_manager.switch_consumer_group_for_user (user => 'MOIRA',consumer_group => 'OLTP');
Changing Consumer Groups for Sessions
• Can be set by DBA for a session
• Can be set by DBA for all sessions for a user
![Page 231: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/231.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Resource Manager Information
• dba_rsrc_plans plans and status• dba_rsrc_plan_directives plan directives• dba_rsrc_consumer_groups consumer groups• dba_rsrc_consumer_group_privs users/roles• dba_users column
initial_rsrc_consumer_group• dba_rsrc_manager_system_privs users/roles
![Page 232: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/232.jpg)
Copyright © 2003, Oracle. All rights reserved.
Resource Plan Directives
SQL> SELECT plan, group_or_subplan, cpu_p1, cpu_p2,2 cpu_p3, parallel_degree_limit_p1, status3 FROM dba_rsrc_plan_directives;
![Page 233: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/233.jpg)
Copyright © 2003, Oracle. All rights reserved.
Current Database ResourceManager Settings
• v$session: Contains the resource_consumer_group column that shows the current group for a session
• v$rsrc_plan: A view that shows the active resource plan
• v$rsrc_consumer_group: A view that contains statistics for all active groups
![Page 234: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/234.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to do the following:• Set up Database Resource Manager• Assign users to Resource Manager groups• Create resource plans within groups
![Page 235: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/235.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL Statement Tuning
![Page 236: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/236.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Control optimizer options• Use optimizer hints• Employ plan stability• Use stored outlines• Use SQL Trace and TKPROF
![Page 237: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/237.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview
The purpose of this lesson is:• To provide methods to determine the resources
used by SQL statements:– Oracle Enterprise Manager– Statspack– Explain plan– SQL Trace and TKPROF– Autotrace
• To determine which SQL statements possibly require tuning
• Not to tune the actual SQL statements
![Page 238: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/238.jpg)
Copyright © 2003, Oracle. All rights reserved.
Optimizer Modes
There are two types of optimizer modes:• Rule-based:
– Uses a ranking system– Syntax- and data dictionary-driven
• Cost-based:– Chooses the path with lowest cost– Statistics-driven
![Page 239: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/239.jpg)
Copyright © 2003, Oracle. All rights reserved.
Setting the Optimizer Mode
• At the instance level:– optimizer_mode =
{Choose|Rule|First_rows|First_rows_n|All_rows}
• At the session level:– ALTER SESSION SET optimizer_mode =
{Choose|Rule|First_rows|First_rows_n|All_rows}
• At the statement level:– Using hints
![Page 240: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/240.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Hints in a SQL Statement
SQL> CREATE index gen_idx on customers2 (cust_gender);
SQL> SELECT /*+ INDEX(customers gen_idx)*/2 cust_last_name, cust_street_address,3 cust_postal_code4 FROM sh.customers5 WHERE UPPER (cust_gender) = ‘M';
![Page 241: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/241.jpg)
Copyright © 2003, Oracle. All rights reserved.
Optimizer Plan Stability
• Users can stabilize execution plans, to force applications to use a desired SQL access path.
• A consistent execution path is thereby maintained through database changes.
• This is done by creating a stored outline consisting of hints.
• The OPTIMIZER_FEATURES_ENABLE parameter enables the optimizer to retain CBO features of previous versions.
![Page 242: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/242.jpg)
Copyright © 2003, Oracle. All rights reserved.
Plan Equivalence
• SQL statement text must match the text in astored outline.
• Plans are maintained through:– New Oracle versions– New statistics on objects– Initialization parameter changes– Database reorganization– Schema changes
![Page 243: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/243.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Stored Outlines
SQL> ALTER SESSION2 SET CREATE_STORED_OUTLINES = train;
SQL> SELECT … FROM … ;SQL> SELECT … FROM … ;
SQL> CREATE OR REPLACE OUTLINE co_cl_join2 FOR CATEGORY train ON3 SELECT co.crs_id, ...4 FROM courses co5 , classes cl6 WHERE co.crs_id = cl.crs_id;
![Page 244: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/244.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Stored Outlines
• Set the USE_STORED_OUTLINES parameter to True or to a category name:
• Both CREATE_STORED_OUTLINES and USE_STORED_OUTLINES can be set at the instance or session level.
SQL> ALTER SESSION2 SET USE_STORED_OUTLINES = train;
SQL> SELECT … FROM … ;
![Page 245: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/245.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Private Outlines
Private outlines are:• Edited without affecting the running system• Copies of current storage outlines• Controlled using the USE_PRIVATE_OUTLINES
parameter
![Page 246: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/246.jpg)
Copyright © 2003, Oracle. All rights reserved.
Editing Stored Outlines
Editing and using private outlines:• Create the outline tables in the current schema.• Copy the selected outline to private outline.• Edit the outline stored as a private outline.• To use the private outline, set the
USE_PRIVATE_OUTLINE parameter. • To allow public access to the new stored outline,
overwrite the stored outline.• Reset USE_PRIVATE_OUTLINE to False.
![Page 247: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/247.jpg)
Copyright © 2003, Oracle. All rights reserved.
Maintaining Stored Outlines
• Use the outln_pkg package to:– Drop outlines or categories of outlines– Rename categories
• Use the ALTER OUTLINE command to:– Rename an outline– Rebuild an outline– Change the category of an outline
• Outlines are stored in the outln schema
![Page 248: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/248.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise Manager:Maintaining Stored Outlines
![Page 249: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/249.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview of Diagnostic Tools
• Statspack• EXPLAIN PLAN• SQL trace and TKPROF• SQL*Plus autotrace feature• Oracle SQL Analyze
![Page 250: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/250.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL Reports in Statspack
The following reports on statements are providedby Statspack:• SQL ordered by gets• SQL ordered by reads• SQL ordered by executions• SQL ordered by parse calls
![Page 251: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/251.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: Top SQL
![Page 252: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/252.jpg)
Copyright © 2003, Oracle. All rights reserved.
Generate the Execution Plan
• Can be used without tracing• Needs the plan_table table utlxplan.sql• Create the explain plan:
SQL> EXPLAIN PLAN FOR2 SELECT last_name FROM hr.employees;
![Page 253: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/253.jpg)
Copyright © 2003, Oracle. All rights reserved.
Query the plan_table Table
Query plan_table to display the execution plans:
• Query plan_table directly.• Use script utlxpls.sql (hide Parallel Query
information).• Use script utlxplp.sql (show Parallel Query
information).• Use the dbms_xplan package.
SQL> SELECT *2 FROM TABLE(dbms_xplan.display);
![Page 254: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/254.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using SQL Trace and TKPROF
To use SQL trace and TKPROF:• Set the initialization parameters.
• Run the application.
• Format the trace file with TKPROF.
• Interpret the output.
SQL> ALTER SESSION SET sql_trace = True;
SQL> ALTER SESSION SET sql_trace = False;
![Page 255: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/255.jpg)
Copyright © 2003, Oracle. All rights reserved.
Enabling and Disabling SQL Trace
• At the instance level:SQL_TRACE = {True|False}
• At the session level:
SQL> ALTER SESSION SET2 SQL_TRACE = {True|False};
SQL> EXECUTE dbms_session.set_sql_trace2 ({True|False});
SQL> EXECUTE2 dbms_system.set_sql_trace_in_session3 (session_id, serial_id, {True|False});
![Page 256: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/256.jpg)
Copyright © 2003, Oracle. All rights reserved.
Formatting the Trace Filewith TKPROF
$ tkprof tracefile.trc output.txt [options]
tracefile.trc output.txt
USER_DUMP_DEST
![Page 257: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/257.jpg)
Copyright © 2003, Oracle. All rights reserved.
TKPROF Statistics
• Count: Number of execution calls• CPU: CPU seconds used• Elapsed: Total elapsed time• Disk: Physical reads• Query: Logical reads for consistent read • Current: Logical reads in current mode • Rows: Rows processed
![Page 258: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/258.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL*Plus Autotrace
• Create the plan_table table.• Create and grant the plustrace role.
Autotrace syntax:
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL> GRANT plustrace TO scott;
SET AUTOTRACE [ Off | On | Traceonly ][ Explain | Statistics ]
![Page 259: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/259.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Describe how the optimizer is used• Describe how hints are used• Explain the concept of plan stability• Explain the use of stored outlines• Use SQL Trace and TKPROF
![Page 260: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/260.jpg)
Copyright © 2003, Oracle. All rights reserved.
Managing Statistics
![Page 261: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/261.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Collect system statistics• Collect statistics on indexes and tables• Collect and manage histogram statistics• Copy statistics between databases• Monitor indexes to determine usage
![Page 262: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/262.jpg)
Copyright © 2003, Oracle. All rights reserved.
Managing Statistics
Use the dbms_stats package:
• gather_table_stats• gather_index_stats• gather_schema_stats• gather_database_stats• gather_stale_stats
![Page 263: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/263.jpg)
Copyright © 2003, Oracle. All rights reserved.
Table Statistics
• Number of rows• Number of blocks and empty blocks• Average available free space• Number of chained or migrated rows• Average row length• Last analyze date and sample size• Data dictionary view: dba_tables
![Page 264: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/264.jpg)
Copyright © 2003, Oracle. All rights reserved.
Collecting Segment-Level Statistics
Statistics collected and the method of collection:• Logical reads: Sampled• Buffer busy waits: Continuous• Db block changes: Sampled• Physical reads: Continuous• Physical writes: Continuous• Physical reads direct: Continuous• Physical writes direct: Continuous• Global cache cr blocks served: Continuous• Global cache current blocks served: Continuous• ITL waits: Continuous• Row lock waits: Continuous
![Page 265: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/265.jpg)
Copyright © 2003, Oracle. All rights reserved.
Querying Segment-Level Statistics
Segment-level statistics are queried using:• v$segstat_name: Lists the segment statistics
being collected• v$segstat: Displays the statistic value, statistic
name, and other basic information• v$segment_statistics: Displays the segment
owner and tablespace name in addition to all the rows contained in v$segstat
![Page 266: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/266.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Dynamic Sampling
Dynamic sampling should be used when:• A better plan could be found• The cost of collecting the statistics is minimal
compared to the execution time• The query is executed many times
![Page 267: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/267.jpg)
Copyright © 2003, Oracle. All rights reserved.
Enabling Dynamic Sampling
Dynamic sampling is set using:• OPTIMIZER_DYNAMIC_SAMPLING = 0
Dynamic sampling is not performed• OPTIMIZER_DYNAMIC_SAMPLING = 1
Dynamic sampling performed when:– The query accesses more than one table.– A table has not been analyzed and there is no index.– The optimizer determines that a full table scan is
required due to nonexistent statistics.• OPTIMIZER_DYNAMIC_SAMPLING > 1
– The higher the value the more aggressive dynamic sampling is performed. The upper limit is 10.
![Page 268: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/268.jpg)
Copyright © 2003, Oracle. All rights reserved.
Index Statistics
• Index level (height)• Number of leaf blocks and distinct keys• Average number of leaf blocks per key• Average number of data blocks per key• Number of index entries• Clustering factor• Data dictionary view: dba_indexes
![Page 269: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/269.jpg)
Copyright © 2003, Oracle. All rights reserved.
Index Tuning Wizard
![Page 270: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/270.jpg)
Copyright © 2003, Oracle. All rights reserved.
Column Statistics
• Number of distinct values• Lowest value, highest value (stored in RAW [binary]
format)• Last analyze date and sample size• Data dictionary view: user_tab_col_statistics
![Page 271: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/271.jpg)
Copyright © 2003, Oracle. All rights reserved.
Histograms
• Histograms describe the data distribution of a particular column in more detail.
• They give better predicate selectivity estimates for unevenly distributed data.
• You create histograms with the dbms_stats.gather_table_stats procedure.
• Data dictionary views: dba_histograms,dba_tab_histograms
![Page 272: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/272.jpg)
Copyright © 2003, Oracle. All rights reserved.
Generating Histogram Statistics
Histogram statistics are generated by:
SQL> EXECUTE dbms_stats.gather_table_stats -2 ('HR','EMPLOYEES', METHOD_OPT => -3 'FOR COLUMNS SIZE 10 salary');
![Page 273: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/273.jpg)
Copyright © 2003, Oracle. All rights reserved.
Gathering Statistic Estimates
• dbms_stats.auto_sample_size:New estimate_percent value
• METHOD_OPT options:– REPEAT: New histogram with same number of buckets
– AUTO: New histogram based on data distributionand application workload
– SKEWONLY: New histogram based on data distribution
SQL> EXECUTE dbms_stats.gather_schema_stats( -2 ownname => 'OE', estimate_percent => -3 DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt -4 => 'for all columns size AUTO');
![Page 274: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/274.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Statistic Collecting
For the dbms_stats.gather_schema_statsprocedure set OPTIONS to:
• Gather Stale• Gather Empty• Gather AutoSQL> EXECUTE dbms_stats.gather_schema_stats -2 (OWNNAME => 'OE', -3 OPTIONS => 'GATHER AUTO');
![Page 275: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/275.jpg)
Copyright © 2003, Oracle. All rights reserved.
Optimizer Cost Model
• Three columns in plan_table are:– cpu_cost: Estimated CPU cost of the operation
– io_cost: Estimated I/O cost of the operation
– temp_space: Estimated temporary space (in bytes) used by the operation
• Includes CPU usage• Accounts for the effect of caching• Accounts for index prefetching
![Page 276: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/276.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using System Statistics
• System statistics enable the CBO to use CPU and I/O characteristics.
• System statistics must be gathered on a regular basis; this does not invalidate cached plans.
• Gathering system statistics equals analyzing system activity for a specified period of time.
![Page 277: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/277.jpg)
Copyright © 2003, Oracle. All rights reserved.
Gathering System Statistics
Procedures of the dbms_stats package used to collect system statistics:• gather_system_stats• set_system_stats• get_system_stats
![Page 278: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/278.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Gathering of System Statistics
• Collect statistics for OLTP:
• Collect statistics for OLAP:
SQL> EXECUTE dbms_stats.gather_system_stats -2 (interval => 120, stattab => 'mystats', -3 statid => 'OLTP');
SQL> EXECUTE dbms_stats.gather_system_stats -2 (interval => 120, stattab => 'mystats', -3 statid => 'OLAP');
![Page 279: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/279.jpg)
Copyright © 2003, Oracle. All rights reserved.
Manual Gathering of System Statistics
• Start manual system statistics collection in the data dictionary:
• Generate the workload• End system statistics collection:SQL> EXECUTE dbms_stats.gather_system_stats -2 (gathering_mode => 'STOP');
SQL> EXECUTE dbms_stats.gather_system_stats -2 (gathering_mode => 'START');
![Page 280: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/280.jpg)
Copyright © 2003, Oracle. All rights reserved.
Import System Statistics Example
• For daytime (OLTP):
• For nighttime (OLAP):
SQL> EXECUTE dbms_stats.import_system_stats -2 (stattab => 'mystats', statid => 'OLTP');
SQL> EXECUTE dbms_stats.import_system_stats -2 (stattab => 'mystats', statid => 'OLAP');
![Page 281: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/281.jpg)
Copyright © 2003, Oracle. All rights reserved.
Copying Statistics Between Databases
Copyto user table
User-defined statistics table Data dictionary
User-defined statistics tableData dictionary
ExportImport
Copy usertable to DD
1
23
4
![Page 282: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/282.jpg)
Copyright © 2003, Oracle. All rights reserved.
Example: Create the Statistics Table
Step 1. Create the table to hold the statistics:
dbms_stats.create_stat_table(‘SH' /* schema name */,'STATS' /* statistics table name */,'SAMPLE' /* tablespace */);
![Page 283: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/283.jpg)
Copyright © 2003, Oracle. All rights reserved.
Example: Copy the Statistics to a Table
Step 2. Copy the statistics to a table:
Step 3. Export the stats table and then import it into the second database.
dbms_stats.export_table_stats(’SH’ /* schema name */,’SALES’ /* table name */, NULL /* no partitions */,’STATS’ /* statistics table name */,’CRS990601’ /* id for statistics */, TRUE /* index statistics */);
![Page 284: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/284.jpg)
Copyright © 2003, Oracle. All rights reserved.
Example: Import the Statistics
Step 4. Copy the statistics into the data dictionary:
dbms_stats.import_table_stats(’SH’ /* schema name */,’SALES’ /* table name */, NULL /* no partitions */,’STATS’ /* statistics table name */,’CRS990601’ /* id for statistics */, TRUE /* index statistics */);
![Page 285: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/285.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Collect system statistics• Collect statistics on indexes and tables• Describe the use of histograms• Copy statistics between databases• Determine usage of indexes
![Page 286: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/286.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Oracle Blocks Efficiently
![Page 287: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/287.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Use automatic segment space management• Use manual segment space management• Describe the use of Oracle block parameters• Recover space from sparsely populated segments• Describe and detect chaining and migration of
Oracle blocks• Perform index reorganization
![Page 288: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/288.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Storage Hierarchy
Tablespace
Segments
Blocks
ExtentsExtentsExtents
![Page 289: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/289.jpg)
Copyright © 2003, Oracle. All rights reserved.
Allocation of Extents
To avoid the disadvantages of dynamic extent allocation:• Create locally managed tablespaces.• Size the segments appropriately.• Monitor segments ready to extend.
![Page 290: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/290.jpg)
Copyright © 2003, Oracle. All rights reserved.
Avoiding Dynamic Allocation
• To display segments with less than 10% free blocks:
• To avoid dynamic allocation:
SQL> SELECT owner, table_name, blocks, empty_blocks2 FROM dba_tables3 WHERE empty_blocks/(blocks+empty_blocks) < .1;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS------ ---------- ---------- ------------HR EMPLOYEES 1450 50HR COUNTRIES 460 40
SQL> ALTER TABLE hr.employees ALLOCATE EXTENT;Table altered.
![Page 291: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/291.jpg)
Copyright © 2003, Oracle. All rights reserved.
Locally Managed Extents
• Create a locally managed tablespace:
• With the Oracle database the default extent management is local.
SQL> CREATE TABLESPACE user_data_12 DATAFILE3 ‘/oracle9i/oradata/db1/lm_1.dbf’4 SIZE 100M5 EXTENT MANAGEMENT LOCAL6 UNIFORM SIZE 2M;
![Page 292: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/292.jpg)
Copyright © 2003, Oracle. All rights reserved.
Pros and Cons of Large Extents
• Pros– Are less likely to extend dynamically– Deliver small performance benefit– Enable you to read the entire extent map with a
single I/O operation
• Cons– Free space may not be available– Unused space
![Page 293: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/293.jpg)
Copyright © 2003, Oracle. All rights reserved.
The High-Water Mark
Empty blocks(rows deleted)
High-watermark
Empty blocks (never used)
Extent 1
Extent 2
Segment header block
![Page 294: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/294.jpg)
Copyright © 2003, Oracle. All rights reserved.
The High-Water Mark
• The high-water mark is:– Recorded in the segment header block– Set to the beginning of the segment on creation– Incremented in five-block increments as rows
are inserted– Reset by the TRUNCATE command
• Never reset by using DELETE statements
![Page 295: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/295.jpg)
Copyright © 2003, Oracle. All rights reserved.
Table Statistics
Populate the table statistics using the dbms_statspackage and then query the values in dba_tables:
SQL> EXECUTE dbms_stats.gather_table_stats -> ('HR','EMPLOYEES');PL/SQL procedure successfully completed.
SQL> SELECT num_rows, blocks, empty_blocks as empty,2 avg_space, chain_cnt, avg_row_len3 FROM dba_tables4 WHERE owner = 'HR'5 AND table_name = 'EMPLOYEES';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN-------- ------ ----- --------- --------- -----------
13214 615 35 1753 0 184
![Page 296: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/296.jpg)
Copyright © 2003, Oracle. All rights reserved.
The dbms_space Package
DECLARE owner VARCHAR2(30);table_name VARCHAR2(30);seg_type VARCHAR2(30);tblock NUMBER;
...BEGIN
dbms_space.unused_space('&owner','&table_name','TABLE',tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock);
dbms_output.put_line(...END;/
![Page 297: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/297.jpg)
Copyright © 2003, Oracle. All rights reserved.
Recovering Space
Below the high-water mark:• Use the Export and Import utilities to:
– Export the table– Drop or truncate the table– Import the table
• Or use the Alter Table Employees Movecommand to move the table.
Above the high-water mark, use the Alter Table Employees Deallocate Unused; command.
![Page 298: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/298.jpg)
Copyright © 2003, Oracle. All rights reserved.
Database Block Size
Minimize block visits by:• Using a larger block size• Packing rows tightly• Preventing row migration
Tablespace
Blocks
Segments
Extents
![Page 299: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/299.jpg)
Copyright © 2003, Oracle. All rights reserved.
The DB_BLOCK_SIZE Parameter
The database block size:• Is defined by the DB_BLOCK_SIZE parameter
• Is set when the database is created• Is the minimum I/O unit for data file reads• Is 2 KB or 4 KB by default, but up to 64 KB
is allowed• Cannot be changed easily• Should be an integer multiple of the OS block size• Should be less than or equal to the OS I/O size
![Page 300: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/300.jpg)
Copyright © 2003, Oracle. All rights reserved.
Small Block Size: Pros and Cons
• Pros– Reduces block contention– Is good for small rows– Is good for random access
• Cons– Has a relatively large overhead– Has a small number of rows per block– Can cause more index blocks to be read
![Page 301: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/301.jpg)
Copyright © 2003, Oracle. All rights reserved.
Large Block Size: Pros and Cons
• Pros– Less overhead– Good for sequential access– Good for very large rows– Better performance of index reads
• Cons– Increases block contention– Uses more space in the buffer cache
![Page 302: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/302.jpg)
Copyright © 2003, Oracle. All rights reserved.
PCTFREE and PCTUSED
Inserts
Inserts
Inserts
1
3
2
4
Inserts
![Page 303: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/303.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines for PCTFREE and PCTUSED
• PCTFREE– Default is 10– Zero if no UPDATE activity
– PCTFREE = 100 × UPD / (Average row length)
• PCTUSED– Default is 40– Set if rows are deleted– PCTUSED = 100 – PCTFREE – 100 × Rows ×
(Average row length) / Block size
![Page 304: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/304.jpg)
Copyright © 2003, Oracle. All rights reserved.
Migration and Chaining
Index Table
Migration
Chaining
![Page 305: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/305.jpg)
Copyright © 2003, Oracle. All rights reserved.
Detecting Migration and Chaining
Use the ANALYZE command to detect migrationand chaining:
Statistic Total Per transaction ... ------------------------- ----- --------------- ...table fetch continued row 495 .02 …
SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;PL/SQL procedure successfully completed.SQL> SELECT num_rows, chain_cnt FROM dba_tables2 WHERE table_name='ORDERS';NUM_ROWS CHAIN_CNT
--------- ---------168 102
Detect migration and chaining by using Statspack:
![Page 306: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/306.jpg)
Copyright © 2003, Oracle. All rights reserved.
Selecting Migrated Rows
SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;Table analyzed.
SQL> SELECT owner_name, table_name, head_rowid2 FROM chained_rows3 WHERE table_name = 'ORDERS';
OWNER_NAME TABLE_NAME HEAD_ROWID ---------- ---------- ------------------SALES ORDER_HIST AAAAluAAHAAAAA1AAASALES ORDER_HIST AAAAluAAHAAAAA1AAB
...
![Page 307: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/307.jpg)
Copyright © 2003, Oracle. All rights reserved.
Eliminating Migrated Rows
• Export/Import– Export the table.– Drop or truncate the table.– Import the table.
• Move table command:– Alter Table Employees Move
• Copying migrated rows– Find migrated rows using ANALYZE.– Copy migrated rows to new table.– Delete migrated rows from original table.– Copy rows from new table to original table.
![Page 308: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/308.jpg)
Copyright © 2003, Oracle. All rights reserved.
Index Reorganization
• Indexes on volatile tables are a performance problem.
• Only entirely empty index blocks go to thefree list.
• If a block contains only one entry,it must be maintained.
• You may need to rebuild indexes.
![Page 309: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/309.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Index Space
To collect usage statistics regarding an index:
To view statistics collected:
Rebuild indexes with wastage greater than 20%:
To coalesce indexes (alternative to REBUILD):
SQL> EXECUTE dbms_stats.gather_index_stats -> ('OE','CUSTOMERS_PK');
SQL> ALTER INDEX oe.customers_pk REBUILD;
SQL> SELECT name,2 (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS wastage3 FROM index_stats;
SQL> ALTER INDEX oe.customers_pk COALESCE;
![Page 310: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/310.jpg)
Copyright © 2003, Oracle. All rights reserved.
Deciding Whether to Rebuildor Coalesce an Index
Rebuild Coalesce
Quickly moves index to Cannot move index to another tablespace. another tablespace.
Higher costs: Requires more Lower costs: Does not disk space. require more disk space.
Creates new tree, shrinks Coalesces leaf blocks within height if applicable. same branch of tree.
Enables you to quickly change Quickly frees up index storage and tablespace leaf blocks for use. parameters without having todrop the original index.
![Page 311: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/311.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Index Usage
• Gathering statistics using an Oracle supplied package:
• Gathering statistics at index creation:
• Gathering statistics when rebuilding an index:
SQL> EXECUTE dbms_stats.gather_index_stats(‘HR’,’LOC_COUNTRY_IX’);
SQL> ALTER INDEX hr.loc_country_ix REBUILD2 COMPUTE STATISTICS;
SQL> CREATE INDEX hr.loc_country_ix2 ……………………5 COMPUTE STATISTICS;
![Page 312: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/312.jpg)
Copyright © 2003, Oracle. All rights reserved.
Identifying Unused Indexes
• To start monitoring the usage of an index:•
• To query the usage of the index:
• To stop monitoring the usage of an index:
SQL> ALTER INDEX hr.emp_name_ix2 MONITORING USAGE;
SQL> ALTER INDEX hr.emp_name_ix2 NOMONITORING USAGE;
SQL> SELECT index_name, used2 FROM v$object_usage;
![Page 313: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/313.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned to do the following:• Use automatic segment space management• Use manual segment space management
– Manage extents and Oracle blocks– Ensuring effective use of space– Determine the high-water mark
• Describe the use of Oracle Block parameters• Recover space from sparsely populated segments• Describe and detect chaining and migration of
Oracle blocks• Perform index reorganization
![Page 314: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/314.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Oracle Data StorageStructures Efficiently
![Page 315: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/315.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able todo the following:• Compare and evaluate the different storage
structures• Examine different data access methods• Implement different partitioning methods
![Page 316: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/316.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data Storage Structures
Cluster Index-organized
table
Heap table
Organization by value
Heap SortedClustered
Partitionedtable
![Page 317: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/317.jpg)
Copyright © 2003, Oracle. All rights reserved.
Selecting the Physical Structure
Factors affecting the selection:• Rows read in groups• SELECT or DML statements
• Table size• Row size, row group, and block size• Small or large transactions• Using parallel queries to load or for SELECT
statements
![Page 318: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/318.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data Access Methods
To enhance performance, you can use the following data access methods:• Clusters• Indexes• B-tree (normal or reverse key)• Bitmap• Function based• Index-organized tables• Materialized views
![Page 319: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/319.jpg)
Copyright © 2003, Oracle. All rights reserved.
Clusters
Clustered orders and order_item tables
Cluster Key(ORD_NO)101 ORD_DT CUST_CD
05-JAN-97 R01 PROD QTYA4102 20A5675 19 W0824 10
102 ORD_DT CUST_CD07-JAN-97 N45
PROD QTYA2091 11G7830 20 N9587 26
Unclustered orders and order_item tables
ORD_NO ORD_DT CUST_CD------ ------ ------
101 05-JAN-97 R01102 07-JAN-97 N45
ORD_NO PROD QTY ...----- ------ ------
101 A4102 20102 A2091 11102 G7830 20102 N9587 26101 A5675 19101 W0824 10
![Page 320: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/320.jpg)
Copyright © 2003, Oracle. All rights reserved.
Cluster Types
Index cluster Hash cluster
Hash function
![Page 321: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/321.jpg)
Copyright © 2003, Oracle. All rights reserved.
Situations Where Clusters Are Useful
Criterion
Uniform key distribution
Evenly distributed key values
Rarely updated key
Often joined master-detail tables
Predictable number of key values
Queries using equality predicate on key
Hash
X
X
X
X
X
Index
X
X
X
![Page 322: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/322.jpg)
Copyright © 2003, Oracle. All rights reserved.
Partitioning Methods
The following partitioning methods are available:• Range• Hash• List• Composite
Rangepartitioning
Hashpartitioning
Compositepartitioning
Listpartitioning
![Page 323: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/323.jpg)
Copyright © 2003, Oracle. All rights reserved.
1
2
3
The partition key is week_no.VALUES LESS THAN must be specified as a literal.Physical attributes can be set per partition.
Range Partitioning Example
CREATE TABLE sales (acct_no NUMBER(5),person VARCHAR2(30),sales_amount NUMBER(8),week_no NUMBER(2)) PARTITION BY RANGE (week_no) (PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0,PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1,...…
PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 );
12 3
![Page 324: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/324.jpg)
Copyright © 2003, Oracle. All rights reserved.
Hash Partitioning Overview
• Easy to Implement• Enables better performance for PDML and
partition-wise joins• Inserts rows into partitions automatically based on
the hash of the partition key• Supports (hash) local indexes• Does not support (hash) global indexes
![Page 325: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/325.jpg)
Copyright © 2003, Oracle. All rights reserved.
List Partitioning Example
SQL> CREATE TABLE locations2 (location_id, street_address,3 postal_code, city, state_province,4 country_id)5 STORAGE(INITIAL 10K NEXT 20K)6 TABLESPACE users7 PARTITION BY LIST (state_province)8 (PARTITION region_east9 VALUES('MA','NY','CT','ME','MD'),10 PARTITION region_west11 VALUES('CA','AZ','NM','OR','WA'),12 PARTITION region_south13 VALUES('TX','KY','TN','LA','MS'),14 PARTITION region_central15 VALUES('OH','ND','SD','MO','IL'));
![Page 326: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/326.jpg)
Copyright © 2003, Oracle. All rights reserved.
Default Partition for List Partitioning
Create a DEFAULT list partition for all values not covered by other partitions:
CREATE TABLE customer...PARTITION BY LIST (state)(PARTITION p1 VALUES ('CA','CO'),PARTITION p2 VALUES ('FL','TX'),PARTITION p3 VALUES (DEFAULT));
![Page 327: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/327.jpg)
Copyright © 2003, Oracle. All rights reserved.
Composite Partitioned Table Overview
• Ideal for both historical data and data placement• Provides high availability and manageability, like
range partitioning• Improves performance for parallel DML and
supports partition-wise joins• Allows more granular
partition elimination• Supports composite local indexes• Does not support composite
global indexes
![Page 328: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/328.jpg)
Copyright © 2003, Oracle. All rights reserved.
GlobalNonpartitioned index
Local partitioned index
Global Partitioned Index
Partitioned Indexes for Scalable Access
Tablepartition
Tablepartition
Tablepartition
Tablepartition
![Page 329: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/329.jpg)
Copyright © 2003, Oracle. All rights reserved.
Partition Pruning
Partition pruning: Only the relevant partitions are accessed.
99-May
99-Apr
99-Feb
99-Jan
99-Mar
99-Jun
sales
SQL> SELECT SUM(sales_amount)2 FROM sales3 WHERE sales_date BETWEEN4 TO_DATE(‘01-MAR-1999’,5 ‘DD-MON-YYYY’) AND6 TO_DATE(‘31-MAY-1999’,7 ‘DD-MON-YYYY’);
![Page 330: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/330.jpg)
Copyright © 2003, Oracle. All rights reserved.
Nonpartition-wise join
Full partition-wise join
Partial partition-wise join
Query slave Partition Partitioned table
1 2
3
Partition-Wise Join
![Page 331: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/331.jpg)
Copyright © 2003, Oracle. All rights reserved.
Statistics Collection forPartitioned Objects
• You can gather object-, partition-, or subpartition level statistics.
• There are GLOBAL or NON-GLOBAL statistics.• The dbms_stats package can gather global
statistics at any level for tables only.• It is not possible to gather:
– Global histograms– Global statistics for indexes
![Page 332: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/332.jpg)
Copyright © 2003, Oracle. All rights reserved.
CALL dbms_stats.gather_table_stats (ownname => ‘o901’,tabname => ‘sales’,partname => ‘feb99’,granularity => ‘partition’);
CALL dbms_stats.gather_index_stats (ownname => ‘o901’,indname => ‘isales’,partname => ‘s1’);
Some dbms_stats Examples
![Page 333: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/333.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to do the following:• Compare and evaluate the different storage
structures• Examine different data access methods• Implement different partitioning methods
![Page 334: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/334.jpg)
Copyright © 2003, Oracle. All rights reserved.
Application Tuning
![Page 335: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/335.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Explain the role of the DBA in tuning applications• Move tables using the ALTER TABLE command• Redefine a table online• Create different types of indexes• Build and manage index-organized tables• Explain and plan OLTP, DSS, and hybrid systems
![Page 336: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/336.jpg)
Copyright © 2003, Oracle. All rights reserved.
The Role of the Database Administrator
• Application tuning is the most important part of tuning.
• DBAs are not always directly involved in application tuning.
• However, DBAs must be familiar with the impact that poorly written SQL statements can have upon database performance.
![Page 337: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/337.jpg)
Copyright © 2003, Oracle. All rights reserved.
Moving Tables Using ALTER TABLE
Tables can be moved using the ALTER TABLEcommand. This allows:• Privileges and constraints to be kept• The table structure to be changed• Movement to another tablespace• The command to be parallelized
SQL> ALTER TABLE hr.employees2 MOVE3 TABLESPACE users;
![Page 338: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/338.jpg)
Copyright © 2003, Oracle. All rights reserved.
Redefining a Table Online
Online table redefinition enables you to:• Modify the storage parameters of the table• Move the table to a different tablespace in the
same schema• Add support for parallel queries• Add or drop partitioning support• Re-create the table to reduce fragmentation• Change the organization of a normal table (heap
organized) to an index-organized table andvice versa
• Add or drop a column
![Page 339: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/339.jpg)
Copyright © 2003, Oracle. All rights reserved.
B-Tree Indexes
Index entry headerKey column lengthKey column valueRow ID
Root
Branch
Leaf
Index entry
![Page 340: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/340.jpg)
Copyright © 2003, Oracle. All rights reserved.
Rebuilding Indexes
To assist in the rebuilding of indexes use:• ONLINE: Keeps the index available during the
rebuild operation• COMPUTE STATISTICS: Collects the statistics while
rebuilding the index
![Page 341: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/341.jpg)
Copyright © 2003, Oracle. All rights reserved.
Compressed Indexes
When creating the index:
When rebuilding the index:
Specify NOCOMPRESS (the default) to disable key compression.
SQL> CREATE INDEX emp_last_name_idx2 ON hr.employees3 (last_name, first_name)4 COMPRESS;
SQL> ALTER INDEX emp_last_name_idx2 REBUILD COMPRESS;
![Page 342: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/342.jpg)
Copyright © 2003, Oracle. All rights reserved.
Bitmap Indexes
<Blue, 10.0.3, 12.8.3, 1000100100010010100><Green, 10.0.3, 12.8.3, 0001010000100100000>
<Red, 10.0.3, 12.8.3, 0100000011000001001><Yellow, 10.0.3, 12.8.3, 0010001000001000010>
Key StartROWID
EndROWID Bitmap
Table
Index
Block 10
Block 11
Block 12
File 3
Block 13
![Page 343: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/343.jpg)
Copyright © 2003, Oracle. All rights reserved.
Bitmap Indexes
• Used for low-cardinality columns• Good for multiple predicates• Uses minimal storage space• Best for read-only systems• Good for very large tables
![Page 344: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/344.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating and MaintainingBitmap Indexes
SQL> CREATE BITMAP INDEX departments_idx2 ON hr.departments(manager_id)3 STORAGE (INITIAL 200K NEXT 200K4 PCTINCREASE 0 MAXEXTENTS 50)5* TABLESPACE indx;
![Page 345: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/345.jpg)
Copyright © 2003, Oracle. All rights reserved.
B-Tree Indexes and Bitmap Indexes
B-Tree Indexes Bitmap Indexes
Suitable for high-cardinality Suitable for low-cardinality columns columns
Updates on keys relatively Updates to key columns very inexpensive expensive
Inefficient for queries using Efficient for queries usingAND/OR predicates AND/OR predicates
Row-level locking Bitmap segment-level locking
More storage Less storage
Useful for OLTP Useful for DSS
![Page 346: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/346.jpg)
Copyright © 2003, Oracle. All rights reserved.
KEY ROWID----- -------------------1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001... ...
EMPLOYEE_ID LAST_NAME ...----------- ---------7499 ALLEN7369 SMITH7521 WARD ...7566 JONES7654 MARTIN7698 BLAKE 7782 CLARK ... ... ...
Reverse Key Index
Index on employee_id column employees table
![Page 347: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/347.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Reverse Key Indexes
• Create a reverse key unique index:
• Create a unique index, then alter the index to be reverse key:
SQL> CREATE UNIQUE INDEX i1_t1 ON t1(c1)2 REVERSE PCTFREE 303 STORAGE(INITIAL 200K NEXT 200K4 PCTINCREASE 0 MAXEXTENTS 50)5 TABLESPACE indx;
SQL> CREATE UNIQUE INDEX i2_t1 ON t1(c2);SQL> ALTER INDEX i2_t1 REBUILD REVERSE;
![Page 348: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/348.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise Manager:Index Management
![Page 349: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/349.jpg)
Copyright © 2003, Oracle. All rights reserved.
ROWID
Index-Organized Tables
Regular table access IOT access
Non-key columns
Key column
Row header
![Page 350: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/350.jpg)
Copyright © 2003, Oracle. All rights reserved.
Index-Organized Tablesand Heap Tables
• Compared to heap tables, IOTs have:– Faster key-based access to table data– Reduced storage requirements– Secondary indexes and logical rowids
• IOTs have the following restrictions:– Must have a primary key– Cannot be clustered
![Page 351: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/351.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Index-Organized Tables
SQL> CREATE TABLE country2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL,4 country_name VARCHAR2(40),5 currency_name VARCHAR2(25),6 currency_symbol VARCHAR2(3),7 map BLOB,8 flag BLOB,9 CONSTRAINT country_c_id_pk10 PRIMARY KEY (country_id))11 ORGANIZATION INDEX12 TABLESPACE indx13 PCTTHRESHOLD 2014 OVERFLOW TABLESPACE users;
![Page 352: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/352.jpg)
Copyright © 2003, Oracle. All rights reserved.
Segment = SYS_IOT_OVER_nIOT_type = IOT_OVERFLOWSegment_type = TABLE
Segment = COUNTRY_C_ID_PKIOT_type = IOTSegment_type = INDEXIndex_type = IOT - TOP
IOT Row Overflow
Remaining part of the rowRows within
PCTTHRESHOLD
indx tablespace users tablespace
![Page 353: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/353.jpg)
Copyright © 2003, Oracle. All rights reserved.
Querying dba_tables for IOT Information
SQL> SELECT table_name, iot_name, iot_type2 FROM dba_tables;
TABLE_NAME IOT_NAME IOT_TYPE----------------- -------- ------------COUNTRY IOTSYS_IOT_OVER_2268 COUNTRY IOT_OVERFLOW
![Page 354: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/354.jpg)
Copyright © 2003, Oracle. All rights reserved.
Querying dba_indexesand dba_segments for IOT information
SQL> SELECT index_name, index_type,2 tablespace_name, table_name2 FROM dba_indexes;
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME--------------- ---------- ---------- ----------COUNTRY_C_ID_PK IOT - TOP INDX COUNTRY
SQL> SELECT segment_name, tablespace_name,2 segment_type3 FROM dba_segments;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE----------------- --------------- ------------SYS_IOT_OVER_2268 USER TABLECOUNTRY_C_ID_PK INDX INDEX
![Page 355: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/355.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using a Mapping Table
SQL> CREATE TABLE country 2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL 4 , country_name VARCHAR2(40) 5 , currency_name VARCHAR2(25) 6 , currency_symbol VARCHAR2(3) 7 , CONSTRAINT country_c_id_pk 8 PRIMARY KEY (country_id)) 9 ORGANIZATION INDEX10 MAPPING TABLE TABLESPACE users;
![Page 356: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/356.jpg)
Copyright © 2003, Oracle. All rights reserved.
Maintaining a Mapping Table
• Collect statistics on a mapping table by analyzing the IOT.
• Query the dba_indexes view to determine the percentage accuracy of the mapping table.
• Rebuild the mapping table if required, using the ALTER TABLE command.
• Use the MINIMIZE RECORDS_PER_BLOCK clause of ALTER TABLE for the mapping table.
SQL> SELECT index_name, pct_direct_access2 FROM dba_indexes3 WHERE pct_direct_access IS NOT NULL;
![Page 357: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/357.jpg)
Copyright © 2003, Oracle. All rights reserved.
The ANALYZE Statement
Use the ANALYZE statement to:• VALIDATE STRUCTURE• LIST CHAINED ROWS• Collect statistics not used by the optimizer, such
as information on free list blocks• Sample a number (instead of a percentage) of
rows
SQL> ANALYZE TABLE hr.employees VALIDATE STRUCTURE;
![Page 358: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/358.jpg)
Copyright © 2003, Oracle. All rights reserved.
Oracle Enterprise Manager:Collect Statistics
![Page 359: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/359.jpg)
Copyright © 2003, Oracle. All rights reserved.
OLTP Systems
• High-throughput, insert- and update-intensive• Large, continuously growing data volume• Concurrent access by many users• Tuning goals:
– Availability– Speed– Concurrency– Recoverability
![Page 360: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/360.jpg)
Copyright © 2003, Oracle. All rights reserved.
OLTP Requirements
• Explicit extent allocation• Indexes:
– Not too many (B-tree better than bitmap)– Reverse key for sequence columns– Rebuilt regularly
• Clusters for tables in join queries:– Index clusters for growing tables– Hash clusters for stable tables
• Materialized views• Index-organized tables
![Page 361: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/361.jpg)
Copyright © 2003, Oracle. All rights reserved.
OLTP Application Issues
• Use declarative constraints instead of application code.
• Make sure that code is shared.• Use bind variables rather than literals for optimally
shared SQL.• Use the CURSOR_SHARING parameter.
![Page 362: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/362.jpg)
Copyright © 2003, Oracle. All rights reserved.
Decision Support Systems(Data Warehouses)
• Queries on large amounts of data• Heavy use of full table scans• Tuning goals:
– Fast response time– Focus on SQL
statement tuning
• The Parallel Query feature is designed for data warehouse environments
Data
![Page 363: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/363.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data Warehouse Requirements
Storage allocation:• Set the block size and
DB_FILE_MULTIBLOCK_READ_COUNT carefully.• Make sure that extent sizes are multiples of this
parameter value.• Run dbms_stats regularly.
![Page 364: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/364.jpg)
Copyright © 2003, Oracle. All rights reserved.
Further Data Warehouse Requirements
• Evaluate the need for indexes:– Use bitmap indexes when possible.– Use index-organized tables for (range) retrieval by
primary keys.– Generate histograms for indexed columns that are
not distributed uniformly.
• Clustering: Consider hash clusters for performance access.
![Page 365: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/365.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data Warehouse Application Issues
• Parsing time is less important.• The execution plan must be optimal:
– Use the Parallel Query feature.– Tune carefully, using hints if appropriate.– Test on realistic amounts of data.– Consider using PL/SQL functions to code logic
into queries.
• Bind variables are problematic.
![Page 366: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/366.jpg)
Copyright © 2003, Oracle. All rights reserved.
Hybrid Systems
OLTP Data Warehouse
Performs index searches More full table scans
Uses B-tree indexes Uses bitmap indexes
Uses reverse key indexes Uses index-organized tables
CURSOR_SHARING set to CURSOR_SHARING should Similar can assist performance be left on Exact
Should not use Parallel Query Employs Parallel Query for large operations
PCTFREE according to PCTFREE can be set to 0expected update activity
Shared code and bind variables Literal variables and hints
Uses ANALYZE indexes Generates histograms
![Page 367: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/367.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to do the following:• Explain the role of the DBA in tuning applications• Move tables using the ALTER TABLE command• Redefine a table online• Create different types of indexes • Build and manage index-organized tables• Explain and plan OLTP, DSS, and hybrid systems
![Page 368: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/368.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Materialized Views
![Page 369: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/369.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able todo the following:• Create materialized views• Refresh materialized views• Create nested materialized views• Create UNION ALL materialized views• Explain the use of query rewrites• Enable and control query rewrites
![Page 370: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/370.jpg)
Copyright © 2003, Oracle. All rights reserved.
Materialized Views
• Instantiations of a SQL query• May be used for query rewrites• Refresh types:
– Complete or Fast– Force or Never
• Refresh modes:– Manual– Automated (synchronous or asynchronous)
![Page 371: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/371.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating Materialized Views
SQL> CREATE MATERIALIZED VIEW2 depart_sal_sum AS3 SELECT d.department_name, SUM(e.salary)4 FROM hr.departments d, hr.employees e5 WHERE d.department_id = e.department_id6 GROUP BY d.department_name;
![Page 372: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/372.jpg)
Copyright © 2003, Oracle. All rights reserved.
Refreshing Materialized Views
The required parameters are:• A list of materialized views to refresh• The refresh method: F-Fast, ?-Force, C-Complete • Set push_deferred_rpc to True, if using
updatable materialized views • Refresh after errors
– True: Allows the process to continue after an error– False: Refresh will stop with errors (default value)
• Atomic refresh– True: All refreshes are done in one transaction– False: Each refresh is a separate transaction
SQL> EXEC dbms_mview.refresh ('SALES_MV', -2 'F', '', True, False, 0,0,0, False);
![Page 373: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/373.jpg)
Copyright © 2003, Oracle. All rights reserved.
Materialized Views: Manual Refreshing
• Refresh specific materialized views:
• Refresh materialized views based on one or more base tables:
• Refresh all materialized views that are due to be refreshed:
dbms_mview.refresh(’CUST_SALES’, parallelism => 10);
dbms_mview.refresh_dependent (’SALES’);
dbms_mview.refresh_all_mviews;
![Page 374: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/374.jpg)
Copyright © 2003, Oracle. All rights reserved.
Nested Materialized Views
TOTAL_SALES
PRODUCTS SALESCUSTOMERS
PROD_MV SALES_CUST_MV Level 1
Level 0
Level 2
![Page 375: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/375.jpg)
Copyright © 2003, Oracle. All rights reserved.
Nested Materialized View Example
![Page 376: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/376.jpg)
Copyright © 2003, Oracle. All rights reserved.
Union All Materialized Views
![Page 377: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/377.jpg)
Copyright © 2003, Oracle. All rights reserved.
Query Rewrite Overview
• To use materialized views instead of the base tables, a query must be rewritten.
• Query rewrites are transparent and do not require any special privileges on the materialized view.
• Materialized views can be enabled or disabled for query rewrites.
• Query rewrites can:– Ignore alphabetic case– Recognize equivalent joins– Compare the defining text of a named view
![Page 378: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/378.jpg)
Copyright © 2003, Oracle. All rights reserved.
Query Rewrites
• The QUERY_REWRITE_ENABLED initialization parameter must be set to True.
• The QUERY REWRITE privilege allows users to enable materialized views.
• The Summary Advisor of the dbms_olap package has options to use materialized views.
![Page 379: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/379.jpg)
Copyright © 2003, Oracle. All rights reserved.
Creating a Materialized View
SQL> CREATE MATERIALIZED VIEW sales_summary2 TABLESPACE users3 PARALLEL (DEGREE 4)4 BUILD IMMEDIATE5 ENABLE QUERY REWRITE6 AS7 SELECT p.prod_name,8 SUM (s.quantity_sold),8 SUM (s.amount_sold)9 FROM sales s, products p10 WHERE s.prod_id = p.prod_id11 GROUP BY p.prod_name;
![Page 380: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/380.jpg)
Copyright © 2003, Oracle. All rights reserved.
Materialized Views andQuery Rewrites: Example
SQL> SELECT p.prod_name,SUM (s.quantity_sold),2 SUM (s.amount_sold)3 FROM sales s, products p4 WHERE s.prod_id = p.prod_id5 GROUP BY p.prod_name;
SQL> select operation, object_name2 from v$sql_plan3 where object_name like 'SALES%';
OPERATION NAME---------------------- -----------------SELECT STATEMENTTABLE ACCESS SALES_SUMMARY
![Page 381: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/381.jpg)
Copyright © 2003, Oracle. All rights reserved.
Enabling and ControllingQuery Rewrites
• Initialization parameters:– OPTIMIZER_MODE– QUERY_REWRITE_ENABLED– QUERY_REWRITE_INTEGRITY
• Dynamic and session-level parameters: – QUERY_REWRITE_ENABLED– QUERY_REWRITE_INTEGRITY
• Hints: REWRITE and NOREWRITE• Dimensions
![Page 382: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/382.jpg)
Copyright © 2003, Oracle. All rights reserved.
Disabling Query Rewrites: Example
SQL> SELECT /*+ NOREWRITE */2 p.prod_name, SUM (s.quantity_sold),3 SUM (s.amount_sold)4 FROM sales s, products p5 WHERE s.prod_id = p.prod_id6 GROUP BY p.prod_name;
SQL> SELECT p.operation, p.object_name2 FROM v$sql_plan p, v$sql s3 WHERE p.address = s.address4 AND sql_text LIKE 'SELECT /*+ NO%';
![Page 383: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/383.jpg)
Copyright © 2003, Oracle. All rights reserved.
Union All Query Rewrite
CREATE MATERIALIZED VIEW sales_cube_mvENABLE QUERY REWRITEASSELECT ...GROUPING_ID(calendar_year,……) gid,GROUPING(calendar_year) grp_y,...GROUPING(cust_city) grp_c,
FROM sales s, times t, customers cWHERE s.time_id=t.time_idAND s.cust_id=c.cust_id
GROUP BYGROUPING SETS((calendar_year, cust_city),(calendar_year,..., cust_state_province),(calendar_year,..., cust_city));
![Page 384: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/384.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using the dbms_mview Package
The package contains the following procedures:• explain_mview• explain_rewrite• refresh• refresh_all_mviews
![Page 385: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/385.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to do the following:• Create materialized views• Refresh materialized views• Creating nested materialized views• Create UNION ALL materialized views• Explain the use of query rewrites• Enable and control query rewrites
![Page 386: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/386.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring and DetectingLock Contention
![Page 387: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/387.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Define levels of locking• Identify causes of contention• Prevent locking problems• Use Oracle utilities to detect lock contention• Resolve contention in an emergency• Resolve deadlock conditions
![Page 388: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/388.jpg)
Copyright © 2003, Oracle. All rights reserved.
Locking Mechanism
• Automatic management• High level of data concurrency
– Row-level locks for DML transactions– No locks required for queries
• Multi-version consistency• Exclusive and Share lock modes• Locks held until commit or rollback operations
are performed
![Page 389: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/389.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data Concurrency
Transaction 1
SQL> SELECT salary 2 FROM employees3 WHERE id = 10;
SALARY---------
1000
SQL> UPDATE employees2 SET salary=salary*1.13 WHERE id= 24878;
1 row updated.
SQL> UPDATE employees2 SET salary=salary+1200;
13120 rows updated.
SQL> UPDATE employees2 SET salary=salary*1.13 WHERE id= 24877;
1 row updated.
Transaction 2
![Page 390: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/390.jpg)
Copyright © 2003, Oracle. All rights reserved.
Two Types of Locks
• DML or data locks:– Table-level locks– Row-level locks
• DDL or dictionary locks
(TM)
(TX)
![Page 391: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/391.jpg)
Copyright © 2003, Oracle. All rights reserved.
DML Locks
A DML transaction gets at least two locks:• A shared table lock• An exclusive row lock
![Page 392: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/392.jpg)
Copyright © 2003, Oracle. All rights reserved.
Enqueue Mechanism
The enqueue mechanism keeps track of:• Users waiting for locks• The requested lock mode• The order in which users requested the lock
![Page 393: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/393.jpg)
Copyright © 2003, Oracle. All rights reserved.
Table Lock Modes
These table lock modes are automatically assignedby the Oracle server:• Row Exclusive (RX): INSERT, UPDATE, DELETE• Row Share (RS): SELECT... FOR UPDATE
![Page 394: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/394.jpg)
Copyright © 2003, Oracle. All rights reserved.
Manually Locking a Table
Manually acquired in LOCK TABLE statement:
• Share (S)– No DML operations allowed– Implicitly used for referential integrity
SQL> LOCK TABLE hr.employees IN share MODE;
![Page 395: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/395.jpg)
Copyright © 2003, Oracle. All rights reserved.
Manually Locking a Table
• Share Row Exclusive (SRX)– No DML operations or Share mode allowed– Implicitly used for referential integrity– No index is required on the foreign key column in
the child table
• Exclusive (X)– No DML or DDL operations allowed by other
sessions– No manual locks allowed by other sessions– Queries are allowed
![Page 396: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/396.jpg)
Copyright © 2003, Oracle. All rights reserved.
DML Locks in Blocks
Row 6
Block Header
Lock bytes
Row 1
1
2
TX slot 1 TX slot 2
![Page 397: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/397.jpg)
Copyright © 2003, Oracle. All rights reserved.
DDL Locks
• Exclusive DDL locks are required for:– DROP TABLE statements
– ALTER TABLE statements
– (The lock is released when the DDL statement completes.)
• Shared DDL locks are required for:– CREATE PROCEDURE statements
– AUDIT statements
– (The lock is released when the DDL parse completes.)
• Breakable parse locks are used for invalidating statements in the shared SQL area.
![Page 398: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/398.jpg)
Copyright © 2003, Oracle. All rights reserved.
Possible Causes of Lock Contention
• Unnecessarily high locking levels• Long-running transactions• Uncommitted changes• Other products imposing higher-level locks
![Page 399: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/399.jpg)
Copyright © 2003, Oracle. All rights reserved.
Diagnostic Tools for MonitoringLocking Activity
Transaction 1UPDATE employeesSET salary =salary x 1.1;
Transaction 2 Transaction 3
v$lockv$locked_objectdba_waitersdba_blockers
UPDATE employeesSET salary =salary x 1.1WHERE empno = 1000; UPDATE employees
SET salary =salary x 1.1
WHERE empno = 2000;
![Page 400: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/400.jpg)
Copyright © 2003, Oracle. All rights reserved.
Transaction 1
Guidelines for Resolving Contention
UPDATE employeesSET salary = salary x 1.1WHERE empno = 1000;
>COMMIT/ROLLBACK;
UPDATE employeesSET salary = salary x 1.1WHERE empno = 1000;
1 row updated;
Transaction 2
>ALTER SYSTEM KILL SESSION ‘10,23’;
9:00
9:05
10:30
11:30
![Page 401: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/401.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: Locks
![Page 402: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/402.jpg)
Copyright © 2003, Oracle. All rights reserved.
Transaction
2
Transaction
1
Deadlocks
UPDATE employeesSET salary = salary x 1.1WHERE empno = = 1000;
UPDATE employeesSET salary = salary x 1.1WHERE empno = 2000;
ORA-00060:Deadlock detected while waiting for resource
UPDATE employeesSET manager = 1342WHERE empno = 2000;
UPDATE employeesSET manager = 1342WHERE empno = 1000;
9:00
9:15
9:16
![Page 403: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/403.jpg)
Copyright © 2003, Oracle. All rights reserved.
Deadlocks
ORA-00060:Deadlock detected while waiting for resource
Tracefile
Serverprocess
in USER_DUMP_DEST directory
SID_ora_PID.trc
UNIX
![Page 404: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/404.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned to do the following:• Define levels of locking• Identify causes of contention• Prevent locking problems• Use Oracle utilities to detect lock contention• Resolve contention in an emergency• Resolve deadlock conditions
![Page 405: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/405.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning the Operating System
![Page 406: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/406.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Describe different system architectures• Describe the primary steps of OS tuning• Identify similarities between OS and DB tuning• Understand virtual memory and paging• Explain the difference between a process and a
thread
![Page 407: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/407.jpg)
Copyright © 2003, Oracle. All rights reserved.
Operating System Tuning
OS
Memory
SGA
Oracleprocesses
Non-Oracleprocesses
OS and DBfiles
![Page 408: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/408.jpg)
Copyright © 2003, Oracle. All rights reserved.
System Architectures
The Oracle database can run on different system architectures. Some examples are:• Uni Processor systems• Symmetric multiprocessing systems (SMP)• Massively parallel processing systems (MPP)• Clustered systems• Nonuniform memory architecture systems (NUMA)
![Page 409: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/409.jpg)
Copyright © 2003, Oracle. All rights reserved.
Virtual and Physical Memory
MMU
Physicalmemory
Virtualmemory
Page tablepossibly with ISM
![Page 410: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/410.jpg)
Copyright © 2003, Oracle. All rights reserved.
Memory
Process Page
Swap device
Paging and Swapping
![Page 411: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/411.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Memory
• Database tuning can improve paging performance by locking SGA into real memory.
• The DBA should monitor real and virtual memory use.
• The DBA should use intimate shared memory, ifit is available.
![Page 412: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/412.jpg)
Copyright © 2003, Oracle. All rights reserved.
Memory
I/Ocontroller
I/Ocontroller
I/Ocontroller
System bus
CPU
Tuning I/O
![Page 413: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/413.jpg)
Copyright © 2003, Oracle. All rights reserved.
Understanding Different I/O System Calls
Operating systems can perform disk I/O in two different ways:• Normal (blocking) I/O• Asynchronous (nonblocking) I/O is implemented
on most platforms and file systems
![Page 414: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/414.jpg)
Copyright © 2003, Oracle. All rights reserved.
CPU Tuning
• Guidelines:– Maximum CPU busy rate: 90%– Maximum OS/User processing ratio: 40/60– CPU load balanced across CPUs
• Monitoring:– CPU– Process
![Page 415: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/415.jpg)
Copyright © 2003, Oracle. All rights reserved.
Process versus Thread
Thread Threads
SQL*PLUS process
Oracle.exeprocessT
hreads
Processes
SQLPLUS
arc0
pmon
smon
dbw0
lgwr
ckpt
Oracle processes
![Page 416: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/416.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Describe different system architectures• Describe the primary steps of OS tuning• Identify similarities between OS and DB tuning• Understand virtual memory and paging• Explain the difference between a process and
a thread
![Page 417: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/417.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Overview
![Page 418: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/418.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Follow the Oracle tuning methodology to diagnose
and resolve performance problems • Improve your tuning skills • Use Oracle tools to diagnose performance
problems
![Page 419: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/419.jpg)
Copyright © 2003, Oracle. All rights reserved.
Approach to Workshop
The workshop is intended to provide:• A group-oriented and interactive experience• Intensive hands-on diagnosis and problem
resolution• Proactive participant involvement
![Page 420: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/420.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Background
The workshop is based on the XYZ Company, a fictional entity that has the following characteristics:• XYZ Company is new and still small:
– Shares a database server with other companies– Currently has four employees who use the database
• System was set up by a part-time DBA trainee• Database performance is unacceptable• XYZ Company is seeking help from a consulting
group• Number of database users will be increasing
![Page 421: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/421.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Outline
You are provided with scripts and tools to:• Configure your database with a tuning problem
based on a selected scenario• Execute a simulated workload against your
detuned database• Collect performance statistics for your database• Analyze your data and make changes to improve
your database performance• Confirm that your changes have been beneficial
by executing the same or a more intense workload simulation
![Page 422: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/422.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Configuration
Commandwindow
Workshopfolder
WindowsExplorer
SQL*Plus session in E:\Labs
![Page 423: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/423.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Overview
Step 1: Start the setup scriptStep 2: Respond to promptsStep 3: Verify configurationStep 4: Preserve your setupStep 5: Open the workshop window
![Page 424: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/424.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 1
![Page 425: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/425.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 2
…
…
![Page 426: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/426.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 3
![Page 427: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/427.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 3
![Page 428: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/428.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 4
![Page 429: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/429.jpg)
Copyright © 2003, Oracle. All rights reserved.
Workshop Setup: Step 5
![Page 430: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/430.jpg)
Copyright © 2003, Oracle. All rights reserved.
Steps to Run a Scenario
1. Run the setup script for your selected scenario.2. Confirm that the Statspack job is running.3. Generate a workload for the current four users.4. Query tables to gather statistics interactively.5. Identify two snapshots and generate a Statspack report
from them.6. Analyze your data and determine what problems to
address.7. Make changes to improve performance.8. Repeat these steps (except step 1) until you achieve
acceptable performance.9. Repeat steps 2 through 8 again, substituting the
workload for the planned 20 users in step 2.10. Prepare your class presentation when you are satisfied
with your work.
![Page 431: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/431.jpg)
Copyright © 2003, Oracle. All rights reserved.
Run Scenario Setup Script
Assorted performance areas
PGA performance
Data access performance
Redo log buffer performance
Database buffer cache performance
Shared pool performance
Scenario Description
6.bat
5.bat
4.bat
3.bat
2.bat
1.bat
Setup Icon
![Page 432: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/432.jpg)
Copyright © 2003, Oracle. All rights reserved.
Check Statspack Job
SQL> CONNECT perfstat/perfstat
SQL> SHOW PARAMETER job_queue_processes
SQL> SELECT job, log_user, what, next_date, 2 next_sec, interval3 FROM user_jobs;
![Page 433: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/433.jpg)
Copyright © 2003, Oracle. All rights reserved.
Check Statspack Job
SQL> @E:\Labs\student\Wkshop\Setup\spauto
PL/SQL procedure successfully completed.
Job number for automated statistics collection for this instance~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~. . .
JOBNO----------
1
Job queue process~~~~~~~~~~~~~~~~~. . .NAME TYPE VALUE--------------------- ------------- -------------job_queue_processes integer 4
Next scheduled run~~~~~~~~~~~~~~~~~~. . .
JOB NEXT_DATE NEXT_SEC---------- --------------- ----------
1 09-APR-03 23:00:00
![Page 434: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/434.jpg)
Copyright © 2003, Oracle. All rights reserved.
Generate a Workload
Simulates 4 users to testcurrent load performance
Simulates 20 users to testfuture load performance
![Page 435: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/435.jpg)
Copyright © 2003, Oracle. All rights reserved.
Collect Statistics Interactively
![Page 436: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/436.jpg)
Copyright © 2003, Oracle. All rights reserved.
Identify Statspack Snapshots
![Page 437: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/437.jpg)
Copyright © 2003, Oracle. All rights reserved.
Create Statspack Report
![Page 438: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/438.jpg)
Copyright © 2003, Oracle. All rights reserved.
Analyze Statistics
![Page 439: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/439.jpg)
Copyright © 2003, Oracle. All rights reserved.
Apply Desired Changes
SQL> ALTER SYSTEM 2 SET db_cache_size = 251658243 SCOPE = BOTH;
System altered.
SQL> ALTER SYSTEM 2 SET log_buffer = 524288 3 SCOPE = SPFILE;
System altered.
![Page 440: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/440.jpg)
Copyright © 2003, Oracle. All rights reserved.
Evaluate Impact of Changes
T
Content?Tried 20 users? Go to step 10
T
Repeat steps2 through 6
Want to try 20 users?
Go to step 9T
Go to step 10
![Page 441: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/441.jpg)
Copyright © 2003, Oracle. All rights reserved.
Test Performance with More Users
![Page 442: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/442.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summarize Findings
• What changes you made• Why you made those changes• How your changes impacted performance• What else you might have investigated• How you could improve your methodology
![Page 443: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/443.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Implement the Oracle tuning methodology• Drill down the performance problems suggested
by Statspack reports• Tune a database to support current and
anticipated work loads• Justify the changes that are made to a database
configuration for tuning purposes
![Page 444: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/444.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Undo Segments
![Page 445: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/445.jpg)
Copyright © 2003, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following:• Describe the concept of automatic undo
management• Create and maintain the automatic managed undo
tablespace• Set the retention period• Use dynamic performance views to check rollback
segment performance• Reconfigure and monitor rollback segments• Define the number and sizes of rollback segments• Allocate rollback segments to transactions
![Page 446: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/446.jpg)
Copyright © 2003, Oracle. All rights reserved.
Undo Segments: Usage
Transaction rollback
Transaction recovery
Undo (Rollback) segment
Read consistency
Control files
Data files Redo logfiles
![Page 447: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/447.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Less Undo Space Per Transaction
• The design of the application should allow usersto commit transactions regularly.
• Developers should not code long transactions.
![Page 448: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/448.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using Less Undo Space
• Import– Set COMMIT = Y– Size the set of rows with the BUFFER keyword
• Export: Set CONSISTENT = N• SQL*Loader operations: Set the commit intervals
with ROWS• Developers should make sure that the
transactions are not unduly long
![Page 449: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/449.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Undo Management
• The automatic undo management feature simplifies the management of undo segments.
• Set the UNDO_MANAGEMENT parameter to:– AUTO for automatic undo management
– MANUAL for managing rollback segments manually
• The UNDO_RETENTION parameter specifies the time (in seconds) to retain undo information.
![Page 450: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/450.jpg)
Copyright © 2003, Oracle. All rights reserved.
Automatic Undo Management Tablespaces
• Create a tablespace for automatic undo management in one of the following ways:– Using the UNDO TABLESPACE clause in the
CREATE DATABASE command
– Using the CREATE UNDO TABLESPACE command
• MINIMUM EXTENT and DEFAULT STORAGE are system generated for undo tablespaces.
• Restrictions:– Database objects cannot be created in this
tablespace.– You can specify the data file and the
extent_management clause only.
![Page 451: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/451.jpg)
Copyright © 2003, Oracle. All rights reserved.
Altering an Undo Tablespace
• The ALTER TABLESPACE command can be used to make changes to undo tablespaces.
• The following example adds another data file to the undo tablespace:
• You cannot take an undo tablespace offline that has an active undo segment.
ALTER TABLESPACE undotbs1ADD DATAFILE ‘/u02/oradata/testdb/undotbs1_02.dbf’AUTOEXTEND ON;
![Page 452: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/452.jpg)
Copyright © 2003, Oracle. All rights reserved.
Switching Undo Tablespaces
• A DBA can switch from using one undo tablespace to another.
• Only one undo tablespace per instance can be assigned as active.
• Switching is performed by using theALTER SYSTEM command:
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
![Page 453: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/453.jpg)
Copyright © 2003, Oracle. All rights reserved.
Dropping an Undo Tablespace
The DROP TABLESPACE command can be used to drop an undo tablespace:
• An undo tablespace can be dropped only if:– It is not the active undo tablespace– It is not utilized by an active transaction
• Queries that require a read-consistent image of undo data that is stored in an dropped undo tablespace will return an error.
DROP TABLESPACE undotbs_2;
![Page 454: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/454.jpg)
Copyright © 2003, Oracle. All rights reserved.
Setting UNDO_RETENTION
UNDO_RETENTION parameter:• Is specified in time (seconds)• A target value. If space is required, then
committed data will be overwritten.• Controls the amount of undo data that is to be
retained after committing
![Page 455: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/455.jpg)
Copyright © 2003, Oracle. All rights reserved.
Other Parameters for AutomaticUndo Management
• UNDO_MANAGEMENT: Specifies whether the database uses Auto or Manual mode
• UNDO_TABLESPACE: Specifies a particular undo tablespace to be used
• UNDO_SUPPRESS_ERRORS: Set to True, this parameter suppresses errors while attempting to execute manual operations, such as ALTER ROLLBACK SEGMENT ONLINE, while in Auto mode
![Page 456: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/456.jpg)
Copyright © 2003, Oracle. All rights reserved.
Monitoring Automatic Undo Management
• Use v$undostat view to monitor undo segments.• This view is available for both Manual and
Auto mode.• The undoblks column displays the number of
undo blocks allocated.
![Page 457: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/457.jpg)
Copyright © 2003, Oracle. All rights reserved.
Using v$undostat
SQL> SELECT begin_time, end_time, undoblks,2 txncount, maxquerylen3 FROM v$undostat;
BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT--------------- --------------- -------- --------25-oct-01:06:04 25-oct-01:06:14 234 1225-oct-01:05:44 25-oct-01:05:54 587 2125-oct-01:05:34 25-oct-01:05:44 1,187 4525-oct-01:05:24 25-oct-01:05:34 346 1525-oct-01:05:14 25-oct-01:05:24 642 23……
![Page 458: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/458.jpg)
Copyright © 2003, Oracle. All rights reserved.
Performance Manager: Rollback/Undo
![Page 459: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/459.jpg)
Copyright © 2003, Oracle. All rights reserved.
Overview
Setting manual rollback segments is:• Optional in the Oracle database• Time consuming for the DBA
![Page 460: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/460.jpg)
Copyright © 2003, Oracle. All rights reserved.
Rollback Segment Activity
Active extentInactive extent
4 3
1 2
>update>update>insert>insert>update
T2
T1
T1 T2
T3T4
>update>update>insert>insert>update
![Page 461: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/461.jpg)
Copyright © 2003, Oracle. All rights reserved.
Rollback Segment Header Activity
• Rollback segment headers contain entries for their respective transactions.
• Every transaction must have update access.
4 3
12
T4
T5
T6
T2
T1
T3
![Page 462: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/462.jpg)
Copyright © 2003, Oracle. All rights reserved.
Growth of Rollback Segments
Active extent
Inactive extent
1 2
4 3
1 2
3
4
5
New extent
![Page 463: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/463.jpg)
Copyright © 2003, Oracle. All rights reserved.
Tuning Manually ManagedRollback Segments
Goals in tuning rollback segments:• Transactions should never wait for access to
rollback segments.• Rollback segments should not extend during
normal running.• Users and utilities should try to use less rollback
per transaction.• No transaction should ever run out of
rollback space.• Readers should always see the read-consistent
images they need.
![Page 464: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/464.jpg)
Copyright © 2003, Oracle. All rights reserved.
Data
Diagnostic Tools
4 3
1 2
SGA
Header
Statspackoutput
v$rollstatv$rollname
v$sysstat
v$system_eventv$waitstat
v$transactionv$session
T3T4T5
![Page 465: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/465.jpg)
Copyright © 2003, Oracle. All rights reserved.
SQL> SELECT class, count FROM v$waitstat2 WHERE class LIKE '%undo%';
orSQL> SELECT event, total_waits, total_timeouts2 FROM v$system_event3 WHERE event LIKE 'undo segment tx slot';
orSQL> SELECT sum(waits)* 100 /sum(gets) "Ratio",2 sum(waits) "Waits", sum(gets) "Gets"3 FROM v$rollstat;
Diagnosing Contention for Manual Rollback Segment Header
If the number of waits for any rollback header is greater than 1% of the total number of requests, then create more rollback segments.
![Page 466: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/466.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines: Number of Manual Rollback Segments (RBSs)
• OLTP: One RBS for four transactions• Batch: One rollback segment for each
concurrent job
Large rollback
Small RBS
SQL> SET TRANSACTION USE2 ROLLBACK SEGMENT large_rbs;
Small RBS
Small RBSSmall RBS
Small RBS
![Page 467: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/467.jpg)
Copyright © 2003, Oracle. All rights reserved.
Guidelines: Sizing ManualRollback Segments
Rollback segment 1 = Rollback segment 2
INITIAL = NEXT = 2n Mb
MINEXTENTS = 20
OPTIMAL = 20 * INITIAL
0.00
0.30
0.100.20
0.40
0.50
0 10 20 30 40
Probability of extending
Numberof extents
![Page 468: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/468.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing Transaction Rollback Data
• Deletes are expensive for rollback activity.• Inserts use minimal rollback space.• Updates use rollback space, depending on the
amount of data changed in the transaction.• Index maintenance adds rollback.
SQL> SELECT s.username, t.used_ublk,2 t.start_time3 FROM v$transaction t, v$session s4 WHERE t.addr = s.taddr;
![Page 469: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/469.jpg)
Copyright © 2003, Oracle. All rights reserved.
Sizing Transaction Rollback Data
• The number of bytes in rollback segments before execution of statements:
• After execution of statements:
SQL> SELECT usn,writes2 FROM v$rollstat;
SQL> SELECT usn,writes2 FROM v$rollstat;
![Page 470: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/470.jpg)
Copyright © 2003, Oracle. All rights reserved.
Possible Problems Caused by Small Rollback Segments
• The transaction fails for lack of rollback space.• A “snapshot too old” error occurs if the statement
requires data that has been modified, committed, and the rollback data is no longer available.
![Page 471: Oracle9i Performance Tuning](https://reader031.vdocuments.us/reader031/viewer/2022012401/552913fc5503466b2e8b4618/html5/thumbnails/471.jpg)
Copyright © 2003, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:• Describe the concept of automatic undo
management• Create and maintain the automatic managed
undo tablespace• Set the retention period• Use dynamic performance views to check rollback
segment performance• Reconfigure and monitor rollback segments• Define the number and sizes of rollback segments• Allocate rollback segments to transactions