Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack (Part No A86647-01) (Release 9 0 1) (2001)

Send Us Your Comments......Page 13
Preface......Page 15
Part I Getting Started with the Oracle Tuning Pack......Page 21
1 Introduction to the Oracle Tuning Pack......Page 23
Oracle SQL Analyze......Page 24
Outline Management......Page 25
Starting the Tuning Pack Products......Page 26
Using Help......Page 27
Configuring SQL Analyze......Page 29
Part II Part II Getting Started with Oracle SQL Analyze......Page 31
3 Introduction to Oracle SQL Analyze......Page 33
Benefits of Oracle SQL Analyze......Page 34
SQL Tuning as Part of an Overall Tuning Process......Page 35
SQL Tuning Issues......Page 36
Analyzing Explain Plans......Page 37
Adding Hints......Page 38
The SQL Tuning Process......Page 39
Step Three: Tuning the Statement......Page 40
Step Four: Verifying Your Results......Page 41
4 Starting a Tuning Session......Page 43
Assigning the SQLADMIN Role......Page 44
Oracle SQL Analyze Repository......Page 45
Oracle SQL Analyze Main Window......Page 46
The Navigator Pane......Page 47
SQL Text Pane......Page 48
Details Pane......Page 49
Selecting a Statement for Tuning......Page 50
Selecting Statements with TopSQL......Page 51
Entering a New Statement......Page 55
Saving......Page 56
Understanding Statistical Information......Page 57
Instance Based Parameters......Page 58
Session Based Parameters......Page 63
Analyzing Logical Structure......Page 71
Viewing Object Properties......Page 72
Table Properties......Page 73
Cluster Properties......Page 75
Index Properties......Page 78
Cost-Based and Rule-Based Optimization......Page 81
Understanding Performance Statistics......Page 85
Using SQL History......Page 86
Understanding Explain Plans......Page 87
Viewing Execution Statistics......Page 93
Comparing SQL Statements and Explain Plans......Page 94
Tuning SQL Statements......Page 97
Understanding Index Tuning Recommendations......Page 98
Understanding Hints......Page 100
Understanding Rules-of-Thumb......Page 103
Use TRUNC differently to enable indexes......Page 105
The SQL Tuning Wizard Process......Page 108
Using the Hint Wizard......Page 110
How to Verify SQL Performance Improvement......Page 111
Part III Getting Started with Graphical Explain Plans......Page 113
8 SQL Explain Plan......Page 115
Stepping Through the SQL Explain Plan......Page 117
Part IV Getting Started with Oracle Expert......Page 119
Advantages of Using Oracle Expert......Page 121
Database Tuning Issues......Page 122
What Are the Types of Performance Tuning?......Page 123
Ways to Use Oracle Expert......Page 124
Sample Tuning Session......Page 125
Steps in Oracle Expert Methodology......Page 127
Setting the Scope of the Tuning Session......Page 128
Managing SQL History Data......Page 129
Reviewing Recommendations......Page 130
Inputs and Outputs......Page 131
Tuning Inputs......Page 132
Generated Output......Page 133
Starting Oracle Expert......Page 135
Identifying the Database to Tune......Page 137
Creating a SQL History......Page 138
Creating a Tuning Session......Page 141
Creating a Tuning Session Manually......Page 142
Setting the Scope of a Tuning Session......Page 143
Instance Optimizations......Page 144
SQL Reuse Opportunities......Page 145
Optimal Data Access......Page 146
Selecting Values for Tuning Session Characteristics......Page 147
Modifying a Tuning Session......Page 149
Saving Tuning Session Data to a Log File......Page 150
Collecting the Collection Classes......Page 151
How Oracle Expert Collects Class Data......Page 152
Collecting Data Efficiently......Page 153
Collecting the Database Class......Page 154
Re-Collecting Database Class Data......Page 155
Collecting Instance Class Data from One or More Instances......Page 156
Collecting Instance Class Data from a File......Page 157
Getting Less Conservative Instance Tuning Recommendations......Page 158
Collecting Schema Class Data from One or More Instances......Page 159
Collecting the System Class......Page 161
Collecting System Class Data from a File......Page 162
Collecting the Workload Class......Page 163
Workload Options......Page 164
Collecting Workload Class Data from a File......Page 165
Starting a Collection......Page 166
Collecting Invalid Data......Page 167
Review Page......Page 169
Databases......Page 170
Workload Applications......Page 173
Analyzing Data Efficiently......Page 175
Tunable Rule......Page 176
Invalid Objects Discovered During an Analysis......Page 177
Reviewing Tuning Recommendations......Page 178
Implementing Tuning Recommendations......Page 181
How to Use the Implementation Files......Page 182
Generating an Analysis Report......Page 183
Generating a Cross Reference Report......Page 184
Defining the Appropriate Tuning Scope......Page 187
Using Iterative Tuning for Improved Performance......Page 188
Taking Advantage of Rules......Page 189
Use SQL History to Avoid Extra SQL Collections......Page 191
Advantages of Using Oracle Expert for Initial Configuration......Page 193
Performing Initial Configuration for a Database......Page 194
User-Provided Information......Page 195
20 Autotune......Page 197
Viewing Autotune Recommendations......Page 198
Implementing Autotune Recommendations......Page 199
Database Workloads......Page 201
Collecting Workload Information with Oracle Trace......Page 203
Collecting Workload Information from the SQL History......Page 204
Specifying Importance Values......Page 205
Part V Getting Started with Oracle Index Tuning Wizard......Page 207
When to Use the Index Tuning Wizard......Page 209
Index Tuning Wizard Interface......Page 210
Index Recommendations......Page 211
Finish......Page 212
Part VI Getting Started with the Reorg Wizard......Page 213
23 Introduction to the Reorg Wizard......Page 215
When to use the Reorg Wizard......Page 216
Reorganizing an Entire Tablespace......Page 217
Reorganization Process......Page 218
Part VII Getting Started with the Tablespace Map......Page 223
24 Introduction to the Tablespace Map......Page 225
Viewing the Tablespace Map......Page 226
Analyzing Segments in a Tablespace......Page 228
Viewing Tablespace Analysis Results......Page 229
Launching Reorg Wizard to Correct Space Problems......Page 230
Part VIII Getting Started with Stored Outlines......Page 233
25 Introduction to Outline Management......Page 235
Accessing the Outline Management Tool......Page 236
Using the Outline Management Tool......Page 237
Accessing the Outline Editor......Page 239
Using Categories......Page 241
Editing an Outline......Page 242
Changing the Join Method......Page 243
Changing the Access Method......Page 245
Glossary......Page 249
Index......Page 271