p. 1

oracle® database performance tuning guide 10g release 1 10.1 part no b10752-01 december 2003

[close]

p. 2

oracle database performance tuning guide 10g release 1 10.1 part no b10752-01 copyright © 2000 2003 oracle corporation all rights reserved graphic designer valarie moore contributors james barlow vladimir barriere eric belden qiang cao sunil chakkappen sumanta chatterjee alvaro corena benoit dageville dinesh das karl dias vinayagam djegaradjane harvey eneman bjorn engsig mike feng cecilia gervasio bhaskar ghosh ray glasstone leslie gloyd connie dialeris green joan gregoire lester gutierrez lex de haan karl haas brian hirano lilian hobbs andrew holdsworth mamdouh ibrahim hakan jacobsson christopher jones srinivas kareenhalli feroz khan stella kister herve lejeune yunrui li juan loaiza diana lorentz george lumpkin joe mcdonald bill mckenna mughees minhas sujatha muthulingam gary ngai michael orlowski kant c patel richard powell mark ramacher shankar raman uri shaft vinay srihari sankar subramanian margaret susairaj hal takahara venkateshwaran venkataramani nitin vengurlekar stephen vivian simon watt andrew witkowski graham wood khaled yagoub and mohamed zait the programs which include both the software and documentation contain proprietary information of oracle corporation they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright patent and other intellectual and industrial property laws reverse engineering disassembly or decompilation of the programs except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited the information contained in this document is subject to change without notice if you find any problems in the documentation please report them to us in writing oracle corporation does not warrant that this document is error-free except as may be expressly permitted in your license agreement for these programs no part of these programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose without the express written permission of oracle corporation if the programs are delivered to the u.s government or anyone licensing or using the programs on behalf of the u.s government the following notice is applicable restricted rights notice programs delivered subject to the dod far supplement are commercial computer software and use duplication and disclosure of the programs including documentation shall be subject to the licensing restrictions set forth in the applicable oracle license agreement otherwise programs delivered subject to the federal acquisition regulations are restricted computer software and use duplication and disclosure of the programs shall be subject to the restrictions in far 52.227-19 commercial computer software restricted rights june 1987 oracle corporation 500 oracle parkway redwood city ca 94065 the programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications it shall be the licensee s responsibility to take all appropriate fail-safe backup redundancy and other measures to ensure the safe use of such applications if the programs are used for such purposes and oracle corporation disclaims liability for any damages caused by such use of the programs oracle is a registered trademark and oracle store oracle9i pl/sql sqlnet and sqlplus are trademarks or registered trademarks of oracle corporation other names may be trademarks of their respective owners.

[close]

p. 3

contents send us your comments xv preface xvii audience organization related documentation conventions documentation accessibility xviii xviii xxi xxii xxiv what s new in oracle performance xxvii oracle database 10g release 1 10.1 new and updated features for performance tuning xxviii part i 1 performance tuning performance tuning overview introduction to performance tuning performance planning instance tuning sql tuning introduction to performance tuning features and tools automatic performance tuning features additional oracle tools 1-2 1-2 1-2 1-5 1-6 1-7 1-8 iii

[close]

p. 4

part ii 2 performance planning designing and developing for performance oracle methodology understanding investment options understanding scalability what is scalability system scalability factors preventing scalability system architecture hardware and software components configuring the right system architecture for your requirements application design principles simplicity in application design data modeling table and index design using views sql execution efficiency implementing the application trends in application development workload testing modeling and implementation sizing data estimating workloads application modeling testing debugging and validating a design deploying new applications rollout strategies performance checklist 2-2 2-2 2-3 2-3 2-4 2-5 2-7 2-7 2-10 2-13 2-13 2-14 2-14 2-17 2-17 2-19 2-21 2-22 2-22 2-23 2-24 2-24 2-26 2-26 2-27 3 performance improvement methods the oracle performance improvement method steps in the oracle performance improvement method a sample decision process for performance conceptual modeling top ten mistakes found in oracle systems 3-2 3-3 3-5 3-6 iv

[close]

p. 5

emergency performance methods 3-8 steps in the emergency performance method 3-9 part iii 4 optimizing instance performance configuring a database for performance performance considerations for initial instance configuration initialization parameters configuring undo space sizing redo log files creating subsequent tablespaces creating and maintaining tables for good performance table compression reclaiming unused space indexing data performance considerations for shared servers identifying contention using the dispatcher-specific views identifying contention for shared servers 4-2 4-2 4-4 4-5 4-5 4-7 4-8 4-9 4-9 4-10 4-11 4-13 5 automatic performance statistics overview of data gathering database statistics operating system statistics interpreting statistics automatic workload repository accessing the automatic workload repository with oracle enterprise manager managing snapshot and baseline data with apis workload repository views workload repository reports 5-2 5-3 5-5 5-8 5-10 5-12 5-13 5-16 5-17 6 automatic performance diagnostics introduction to database diagnostic monitoring 6-2 automatic database diagnostic monitor 6-3 addm analysis results 6-4 v

[close]

p. 6

an addm example setting up addm accessing addm with oracle enterprise manager diagnosing database performance issues with addm views with addm information 6-5 6-6 6-7 6-8 6-12 7 memory configuration and use understanding memory allocation issues oracle memory caches automatic shared memory management dynamically changing cache sizes application considerations operating system memory use iteration during configuration configuring and using the buffer cache using the buffer cache effectively sizing the buffer cache interpreting and using the buffer cache advisory statistics considering multiple buffer pools buffer pool data in v$db_cache_advice buffer pool hit ratios determining which segments have many buffers in the pool keep pool recycle pool configuring and using the shared pool and large pool shared pool concepts using the shared pool effectively sizing the shared pool interpreting shared pool statistics using the large pool using cursor_space_for_time caching session cursors configuring the reserved pool keeping large objects to prevent aging cursor_sharing for existing applications 7-2 7-2 7-3 7-4 7-6 7-6 7-7 7-8 7-8 7-8 7-12 7-14 7-16 7-17 7-17 7-19 7-20 7-20 7-21 7-24 7-29 7-35 7-36 7-40 7-41 7-42 7-44 7-45 vi

[close]

p. 7

maintaining connections configuring and using the redo log buffer sizing the log buffer log buffer statistics pga memory management configuring automatic pga memory configuring olap_page_pool_size 7-47 7-48 7-49 7-49 7-50 7-52 7-68 8 i/o configuration and design understanding i/o basic i/o configuration lay out the files using operating system or hardware striping manually distributing i/o when to separate files three sample configurations oracle-managed files choosing data block size 8-2 8-2 8-2 8-6 8-7 8-9 8-10 8-11 9 understanding operating system resources understanding operating system performance issues using operating system caches memory usage using operating system resource managers solving operating system problems performance hints on unix-based systems performance hints on windows systems performance hints on midrange and mainframe computers understanding cpu context switching finding system cpu utilization checking memory management checking i/o management checking network management checking process management 9-2 9-2 9-3 9-4 9-5 9-6 9-6 9-6 9-7 9-9 9-10 9-10 9-11 9-11 9-11 vii

[close]

p. 8

10 instance tuning using performance views instance tuning steps 10-2 define the problem 10-3 examine the host system 10-4 examine the oracle statistics 10-7 implement and measure change 10-12 interpreting oracle statistics 10-13 examine load 10-13 using wait event statistics to drill down to bottlenecks 10-14 table of wait events and potential causes 10-16 additional statistics 10-18 wait events statistics 10-21 sqlnet events 10-23 buffer busy waits 10-25 db file scattered read 10-27 db file sequential read 10-29 direct path read and direct path read temp 10-31 direct path write and direct path write temp 10-33 enqueue enq waits 10-34 free buffer waits 10-37 latch events 10-40 log file parallel write 10-45 library cache pin 10-45 library cache lock 10-45 log buffer space 10-46 log file switch 10-46 log file sync 10-47 rdbms ipc reply 10-48 idle wait events 10-48 11 tuning networks understanding connection models shared server configuration detecting network problems using dynamic performance views for network performance 11-2 11-2 11-6 11-6 viii

[close]

p. 9

understanding latency and bandwidth solving network problems finding network bottlenecks dissecting network bottlenecks using array interfaces adjusting session data unit buffer size using tcp.nodelay using connection manager 11-7 11-8 11-9 11-10 11-13 11-14 11-14 11-14 part iv 12 optimizing sql statements sql tuning overview introduction to sql tuning goals for tuning reduce the workload balance the workload parallelize the workload identifying high-load sql identifying resource-intensive sql gathering data on the sql identified automatic sql tuning features developing efficient sql statements verifying optimizer statistics reviewing the execution plan restructuring the sql statements controlling the access path and join order with hints restructuring the indexes modifying or disabling triggers and constraints restructuring the data maintaining execution plans over time visiting data as few times as possible 12-2 12-2 12-2 12-3 12-3 12-3 12-3 12-5 12-6 12-7 12-8 12-8 12-9 12-17 12-21 12-22 12-22 12-22 12-22 ix

[close]

p. 10

13 automatic sql tuning automatic sql tuning overview 13-2 query optimizer modes 13-2 types of tuning analysis 13-2 sql tuning advisor 13-6 input sources 13-6 tuning options 13-7 advisor output 13-7 accessing the sql tuning advisor with oracle enterprise manager 13-7 using sql tuning advisor apis 13-8 managing sql profiles with apis 13-10 accepting a sql profile 13-11 altering a sql profile 13-11 dropping a sql profile 13-11 sql tuning sets 13-12 accessing sql tuning sets with oracle enterprise manager 13-12 managing sql tuning sets 13-13 sql tuning information views 13-16 14 the query optimizer optimizer operations 14-2 choosing an optimizer goal 14-3 optimizer_mode initialization parameter 14-4 optimizer sql hints for changing the query optimizer goal 14-5 query optimizer statistics in the data dictionary 14-6 enabling and controlling query optimizer features 14-6 enabling query optimizer features 14-6 controlling the behavior of the query optimizer 14-8 understanding the query optimizer 14-9 components of the query optimizer 14-10 reading and understanding execution plans 14-15 understanding access paths for the query optimizer 14-18 full table scans 14-18 rowid scans 14-20 index scans 14-21 x

[close]

p. 11

cluster access hash access sample table scans how the query optimizer chooses an access path understanding joins how the query optimizer executes join statements how the query optimizer chooses execution plans for joins nested loop joins hash joins sort merge joins cartesian joins outer joins 14-27 14-28 14-28 14-28 14-29 14-30 14-30 14-32 14-34 14-35 14-36 14-36 15 managing optimizer statistics understanding statistics automatic statistics gathering gather_stats_job enabling automatic statistics gathering considerations when gathering statistics manual statistics gathering gathering statistics with dbms_stats procedures when to gather statistics system statistics managing statistics restoring previous versions of statistics exporting and importing statistics restoring statistics versus importing or exporting statistics locking statistics for a table or schema setting statistics estimating statistics with dynamic sampling handling missing statistics viewing statistics statistics on tables indexes and columns viewing histograms 15-2 15-3 15-3 15-4 15-4 15-6 15-7 15-11 15-11 15-13 15-13 15-14 15-15 15-15 15-16 15-16 15-18 15-19 15-19 15-20 xi

[close]

p. 12

16 using indexes and clusters understanding index performance 16-2 tuning the logical structure 16-2 index tuning using the sqlaccess advisor 16-3 choosing columns and expressions to index 16-4 choosing composite indexes 16-5 writing statements that use indexes 16-6 writing statements that avoid using indexes 16-6 re-creating indexes 16-7 compacting indexes 16-8 using nonunique indexes to enforce uniqueness 16-8 using enabled novalidated constraints 16-9 using function-based indexes for performance 16-10 using partitioned indexes for performance 16-11 using index-organized tables for performance 16-12 using bitmap indexes for performance 16-12 using bitmap join indexes for performance 16-12 using domain indexes for performance 16-13 using clusters for performance 16-14 using hash clusters for performance 16-15 17 optimizer hints understanding optimizer hints 17-2 type of hints 17-2 specifying hints 17-3 using hints with views 17-10 using optimizer hints 17-12 hints for optimization approaches and goals 17-12 hints for access paths 17-15 hints for query transformations 17-23 hints for join orders 17-31 hints for join operations 17-32 hints for parallel execution 17-36 additional hints 17-41 xii

[close]

p. 13

18 using plan stability using plan stability to preserve execution plans using hints with plan stability storing outlines enabling plan stability using supplied packages to manage stored outlines creating outlines using and editing stored outlines viewing outline data moving outline tables using plan stability with query optimizer upgrades moving from rbo to the query optimizer moving to a new oracle release under the query optimizer 18-2 18-2 18-4 18-4 18-4 18-5 18-6 18-9 18-10 18-12 18-12 18-14 19 using explain plan understanding explain plan how execution plans can change minimizing throw-away looking beyond execution plans explain plan restrictions the plan_table output table running explain plan identifying statements for explain plan specifying different tables for explain plan displaying plan_table output customizing plan_table output reading explain plan output viewing parallel execution with explain plan viewing parallel queries with explain plan viewing bitmap indexes with explain plan viewing partitioned objects with explain plan examples of displaying range and hash partitioning with explain plan examples of pruning information with composite partitioned objects examples of partial partition-wise joins examples of full partition-wise joins 19-2 19-2 19-3 19-4 19-5 19-5 19-6 19-6 19-7 19-7 19-8 19-9 19-10 19-12 19-13 19-14 19-14 19-16 19-18 19-20 xiii

[close]

p. 14

examples of inlist iterator and explain plan 19-21 example of domain indexes and explain plan 19-22 plan_table columns 19-23 20 using application tracing tools end to end application tracing 20-2 accessing the end to end tracing with oracle enterprise manager 20-3 managing end to end tracing with apis and views 20-3 using the trcsess utility 20-7 syntax for trcsess 20-8 sample output of trcsess 20-8 understanding sql trace and tkprof 20-9 understanding the sql trace facility 20-9 understanding tkprof 20-11 using the sql trace facility and tkprof 20-11 step 1 setting initialization parameters for trace file management 20-12 step 2 enabling the sql trace facility 20-14 step 3 formatting trace files with tkprof 20-15 step 4 interpreting tkprof output 20-20 step 5 storing sql trace facility statistics 20-26 avoiding pitfalls in tkprof interpretation 20-29 avoiding the argument trap 20-29 avoiding the read consistency trap 20-29 avoiding the schema trap 20-30 avoiding the time trap 20-31 avoiding the trigger trap 20-32 sample tkprof output 20-32 sample tkprof header 20-32 sample tkprof body 20-33 sample tkprof summary 20-36 glossary index xiv

[close]

p. 15

send us your comments oracle database performance tuning guide 10g release 1 10.1 part no b10752-01 oracle corporation welcomes your comments and suggestions on the quality and usefulness of this document your input is an important part of the information used for revision sssss did you find any errors is the information clearly presented do you need more information if so where are the examples correct do you need more examples what features did you like most if you find any errors or have any other suggestions for improvement please indicate the document title and part number and the chapter section and page number if available you can send comments to us in the following ways s s s electronic mail infodev_us@oracle.com fax 650 506-7227 attn server technologies documentation manager postal service oracle corporation server technologies documentation 500 oracle parkway mailstop 4op11 redwood shores ca 94065 usa if you would like a reply please give your name address telephone number and optionally electronic mail address if you have problems with the software please contact your local oracle support services xv

[close]