ISBN 0-9823061-3-X ISBN 13: 978-0-9823061-3-0 Library of Congress Number: 2014903997 828 pages Perfect Bind -  7.5x9.25 PD 214 Shelving: Database/Oracle Oracle in-Focus Series:  # 45   Oracle Tuning The Definitive Reference Third Edition Donald K. Burleson Retail Price $69.95 USD/  £45.95 / $75.95 Canadian Order now for 40% off ! Only $41.95 (40% off)       Key Features About the Author Table of Contents Index Reader Comments Errata       Get the Oracle Tuning Library Save $100 - All four books for only $118.95  Oracle Tuning: The Definitive Reference 3rd Edition $69.95 Advanced Oracle SQL Tuning: The Definitive Reference $59.95 Oracle Performance Troubleshooting 2nd Ed $34.95 Oracle Tuning Powerscripts $37.95 Oracle has become the world's most flexible and robust database and along with great power comes great complexity.  This complexity requires that the DBA have expert knowledge of Oracle internals.  This book provides a thorough step-by-step approach for holistic Oracle tuning in this challenging information technology era.  It represents the knowledge accumulated from tuning thousands of Oracle databases. Oracle tuning has always been a complex task; however, it has become even more complex as Oracle evolves and yields new techniques for achieving optimal performance in the stressed production environment of today’s high-tech world. Oracle STATSPACK and AWR has introduced a revolution in database tuning. By understanding these time-series tables, we can develop time-series tuning models to predict upcoming outages and dynamically change the instance to accommodate the impending resource changes.  Database tuning efforts must become as sophisticated as the databases themselves. This book strives to show you how to leverage upon the wealth of Oracle performance information so that you can create a robust Oracle database engine, one that maximizes computing resources while minimizing overhead. If you are seeking theory, this is not the book for you.  This book encapsulates the combined knowledge of over a century of hands-on DBA tuning experience, a pragmatic, practical approach for the professional Oracle DBA. This is not a book for beginners. Targeted at the senior Oracle DBA, this comprehensive book gives you all of the knowledge you need to be successful in tuning even the most complex Oracle database.  The code download for this book is packed with ready-to-run scripts to monitor and identify even the most challenging performance issues.   * See how to access Oracle's time-series performance data. * Learn secrets on how to identify performance signatures and shifting workloads over time. * Download the code depot for a gold mine of ready-to-run tuning scripts. * See how to analyze historical trends to predict impending performance problems. * Get tips for understanding Oracle elapsed-time tuning reports. * Create customized performance alerts to fix a problem before it impedes end-user response time. * See how to deploy intelligent Oracle performance optimization. * Understand proactive time-series Oracle tuning techniques. * Learn secrets for optimizing entire SQL workloads. About the Author: Donald Burleson Donald K. Burleson is one of the world’s top Oracle Database experts with more than 25 years of full-time DBA experience.  He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems.   A former Adjunct Professor, Don Burleson has written more than 30 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle OpenWorld and other international database conferences. Donald Burleson's books   Donald Burleson articles   Donald Burleson comments   Don Burleson blog   Free Oracle Tips HTML Text Table of Contents: Using the Online Code Depot Are you ready to tune? Oracle Script Collection Conventions Used in this Book Acknowledgements Preface Chapter 1: Introduction to Oracle Tuning Why Hardware Technology Drives Database Technology How economic trends change Oracle DBA duties Oracle Tuning and Server Consolidation Summary An Introduction to Oracle Tuning Maximizing Throughput vs. Minimizing Response Time Top-down Tuning vs. Bottom-up Tuning Proactive Tuning vs. Reactive Tuning Reactive Oracle Tuning treats the symptoms, not the disease Proactive Oracle Tuning anticipates pending performance problems Knowing your Limits:  Oracle problems that cannot be tuned Oracle Application-level Tuning The Causes of Poor Oracle Performance The Oracle Tuning Hierarchy External Hardware Performance Review Finding Database Bottlenecks Oracle Instance Tuning Oracle SQL Tuning Conclusion Chapter 2: Time Series Tuning for Oracle Managing the Complexity of Oracle Waste not, want not The Pros and Cons of Time Series Tuning The Trumpet of Doom Oracle’s Predictive Models Data Quality and Predictive Validity Sampling Intervals and Data Quality Finding Data Processing Signatures Finding repetition in workloads Workloads and Predictive Analysis What is an Oracle Workload? Using Adaptive Thresholds for Predictive Modeling Using OEM for Predictive Modeling Time Series Tuning Guidelines Scheduling a SGA Reconfiguration Trend-based Oracle Reconfiguration When to Trigger a Dynamic Reconfiguration Approaches to Self-tuning Oracle Databases Tuning a Constantly Changing Database Can Oracle Possess Psychic Abilities? AWR Licensing Options Tracking your Oracle Option Usage Capturing Time Series Metrics Customized AWR Tuning Reports Exception Threshold Reporting Trend Identification with the AWR Conclusion Chapter 3: The Time Model Tuning Approach Inside the Oracle Time Model Views Displaying session details with time model data Real-time Reporting Oracle 12c ASH Analytics Time Model Tables in AWR Time Model Statistics Conclusion Chapter 4: Predictive Modeling Predicting the Future with Oracle Oracle Data Mining and Predictive Analytics The Evolution of Oracle tuning toward Data Mining Oracle Data Mining and Predictive Analytics Components of the Oracle Data Miner Predictive Models Made Easy Exception Reporting with the AWR General Trend Identification with the AWR Correlation Analysis with AWR and ASH Predictive Modeling with dba_hist_filestatxs Conclusion Chapter 5: Inside Oracle SQL Tuning Introduction to Oracle SQL What is your ideal optimizer_mode? Holistic SQL Tuning Understanding SQL Tuning Holistic Oracle SQL Tuning Best Practices for SQL Optimization Is your SQL workload static or dynamic? Successful SQL Tuning Methods The Static Database Workload The Dynamic Database Workload Goals of SQL Tuning Determine Optimal Table Join Order Optimal Table Join Order and Transitive Closure Remove Unnecessary Large-table Full-table Scans Locating full-scan operations Tuning large-table full-table scans Tuning small-table full-table scans Cache Small-table Full-table Scans Verify Optimal Index Usage Verify Optimal Join Techniques Evaluating Oracle Physical Join Performance Tuning by Simplifying SQL Syntax Roadblocks to SQL Tuning The Library Cache and Oracle SQL Performance Optimizing SQL RAM Resources Using cursor_sharing=force Using Adaptive Cursor Sharing Tuning Distributed SQL Queries Subqueries and SQL Basic SQL Subqueries Scalar Subqueries In-line Views (Subqueries in the from Clause) Inside Oracle Views Benefits of Oracle Views The Downside to Using Views Combining Hints and Views Views and join elimination Interrogating SQL Execution Plans Oracle Tuning with Hints When hints appear to be ignored Tuning with the rule hint Why the rule Hint is still popular SQL Tuning with the ordered Hint SQL Tuning with the ordered_predicates Hint How is SQL join cardinality estimated? Tuning with the cardinality hint Oracle12c temporary table enhancements Setting the SQL Optimizer Cost Model Turning on CPU Costing Tuning SQL with "rownum" Filters Using rownum for top-n queries Using rownum with range bound queries Alternatives to rownum Using OPQ in SQL Optimizing Oracle SQL Insert Performance Blocksize and Insert Performance Oracle Delete Tuning High Impact Techniques (over 20% faster): Low Impact Techniques (between 5% and 20% faster) Using Bulking for Delete Performance Oracle Update Tuning High impact techniques (over 20% faster): Low-impact techniques (between 5% and 20% faster): CTAS vs. SQL Update statements Bulking SQL Updates Bulking SQL Inserts Tuning with SQL Profiles Tuning When the SQL Cannot Be Touched Swapping execution plans Swapping SQL Profiles Towards Automated SQL Tuning Conclusion Chapter 6: Inside STATSPACK and AWR Introduction to Tuning with STATSPACK and AWR STATSPACK vs. AWR Inside the AWR tables Using STATSPACK for Oracle Tuning The structure of the STATSPACK tables How STATSPACK Works Trend Reports with STATSPACK Inside the AWR Tables The AWR Data Collection Mechanism Customizing AWR snapshots The Mysterious AWR Performance Tables Inside the AWR Tables Inside the dba_hist Tables Database Wait Events in AWR The AWR Metric Tables AWR System Statistics Operating System Statistics in AWR SQL Statistics in AWR Segment Statistics in AWR Datafile I/O Statistics in AWR Conclusion Chapter 7: AWR and STATSPACK Configuration The Differences between STATSPACK and AWR Installing STATSPACK Install Prerequisites Statistic Management in AWR and STATSPACK Managing STATSPACK The snap_level Parameter The session_id The num_sql What SQL is Captured? STATSPACK SQL Collection Thresholds AWR SQL Collection Thresholds Purging from AWR The stats$sql_summary Table Installing AWR The dbms_workload_repository Package Creating an AWR Report Report Naming Conclusion Chapter 8: Reading an AWR or STATSPACK Report The Evolution of the Elapsed Time Report Generating a STATSPACK Report Create the STATSPACK Report Generating an AWR Report Reading the STATSPACK / AWR Report STATSPACK / AWR Report Summary Cache Sizes Section Load Profile Instance Efficiency Percentage All about Ratios Top 5 Timed Foreground Events CPU and memory section About load averages The Wait Events Section of the AWR / STATSPACK Report Wait Event Histogram Section Instance Activity Section in the STATSPACK / AWR Report Instance Recovery Statistics Section of a STATSPACK / AWR Report PGA Section Process Memory Summary Section Enqueue Statistics Rollback Segments/UNDO Logs Undo Segments Latch Activity Section of a STATSPACK / AWR report Dictionary and Library Cache Stats The Shared Pool Advisory Section The Time Model Statistics section The Operating System Statistics Section The Top SQL Section The Instance Activity Section The I/O Reports Section The Advisory Sections Buffer Pool Advisory The Buffer Wait Statistics Section Conclusion Chapter 9: Tuning with Oracle Optimizer Statistics Oracle SQL Optimizer Statistics The Oracle dbms_stats Package Oracle 12c Improved statistics Managing Schema Statistics with dbms_stats Column Skew and histograms Automating Histogram Sampling with dbms_stats Oracle extended statistics Functions vs. expressions Hybrid histograms:  Expression-based data relationships Using dbms_stats.create_extended_stats Queries on extended statistics Reanalyzing Optimizer Statistics Oracle System Statistics External Costing with the Optimizer Using Dynamic Sampling Sampling Table Scans Oracle12c adaptive execution plans Maintaining a Metadata Infrastructure Conclusion Chapter 10: Monitoring Oracle SQL Identifying Problem SQL Find the Problem Sessions AWR and SQL Tuning Viewing Table and Index Access with AWR Tracing SQL Execution History The full hash join Conclusion       Chapter 11: Oracle Tuning Tools The Evolution of Oracle Tuning Tools The Spirit of Independence A Best Practices Approach to Oracle Tuning The History of Oracle Tuning Techniques An Automated Approach to SQL Tuning How Fully Automated SQL Tuning Works Fully Automated SQL Tuning is not a Panacea Oracle Trace Analyzer (sqltxplain) Oracle Lightweight Onboard Monitor (LTOM) LTOM Features Oracle Trace Utility Inside Oracle Event Tracing Oracle 12c Real-time ADDM Conclusion Chapter 12: Server & Network Tuning Oracle Server Tuning Outside the Oracle Instance Oracle Server Bottlenecks Oracle Server Monitoring Capturing Server-side Metrics OS Statistics for the Cost-based Optimizer OS data inside Oracle views Understanding the load average OS delta report The Oracle OS Watcher utility Starting Oracle OSWatcher Oracle RAM Tuning Memory Limits File Caching RAM swapping Oracle CPU Tuning Viewing CPU Utilization for Oracle Identifying High CPU Usage with vmstat Storing Information from vmstat Disk I/O and Oracle Moore’s Law and Disk Speed Server RAM and Oracle Oracle and the 64-bit Server Technology The New Age of Oracle Server Consolidation Server Metrics and SQL Execution Oracle Network Tuning Conclusion Chapter 13: Tuning the I/O Subsystem Inside Oracle Disk Architecture The Plague of Large Oracle Disks Manual File Placement Oracle 12c Intelligent File Placement ASM File Striping File Segregation Oracle redo log I/O RAID Technology and Oracle RAID 5 is Not for Every Database Oracle and Direct I/O Enabling Oracle Direct I/O Calibrating your disk I/O Monitoring External Disk I/O Capturing external iostat Information Generating iostat Reports Generating ASM disk Reports Solutions to Physical Read Waits Choosing a default blocksize Using Oracle Multiple Blocksizes Reducing Data Buffer Waste with multiple blocksizes Reducing Logical I/O with Multiple Blocksizes Improving Buffer Efficiency with Multiple Blocksizes Improving SQL Execution with Multiple Blocksizes Real World Applications of Multiple Blocksizes Setting the db_block_size with Multiple Blocksizes Allocating Objects into Multiple Block Buffers Oracle Blocksize & Index I/O The Latest Consensus on Using Multiple Blocksizes Vendor Notes on Oracle Multiple Blocksizes Reducing Disk I/O with SSD SSD Vendors for Oracle Oracle Disk Monitoring Examining Global I/O Tracking I/O for Specific Tables Measuring Disk I/O Speed Time Series Monitoring of logical I/O Monitoring Disk I/O with AWR Conclusion Chapter 14: Oracle Instance Tuning Inside Instance Tuning Tune the Instance Before Tuning SQL Automatic Memory Management Manual RAM allocation vs. AMM Sizing the Oracle SGA and PGA Regions Viewing Server RAM Resources Viewing RAM  on Solaris Viewing Oracle RAM on IBM-AIX UNIX Sizing your SGA SGA Sizing on a Dedicated Server Using AMM RAM and Virtual Memory for Oracle Finding the High Water Mark of Oracle User Connections Determining the Optimal PGA Size A Script for Estimating Total PGA RAM Optimizing pga_aggregate_target Rules for adjusting The memory_max_target and memory_target parameters Important caveats in PGA management Sizing your PGA for hash joins Sizing the PGA for Batch Processing Supersizing the PGA A case study RAM hash joins Monitoring Server Resources in MS Windows OS Kernel Parameters Server Settings for Windows Servers Kernel Setting for UNIX and Linux Servers Oracle Parameter Tuning Oracle Hidden Parameters Oracle Parallel Query Parameters SQL Optimizer Parameters Instance Wait Event Tuning Tuning the Oracle Data Buffer Pools Data Buffer Cache Hidden Parameters The Problem of Duplicate RAM Caches Why is Oracle Logical I/O So Slow? Data Block Caching in the SGA Full Table Caching in Oracle Oracle Data Buffer Metrics Using AWR for Buffer Pool Statistics Oracle’s Eight Data Buffer Hit Ratios Viewing Information about SGA Performance AMM and Oracle Instance Tuning Internals of the Oracle Data Buffers Finding Hot Blocks inside the Oracle Data Buffers Viewing the Data Buffer Contents Oracle 12c flash_cache Internals Flash cache; The new data buffer in town The Downside of Mega Data Buffers Allocating Oracle Objects into Multiple RAM data Buffers Automating KEEP Pool Assignment Sizing the KEEP Pool Oracle 12c flash cache Enabling the flash_cache Tuning the RECYCLE Pool Large Blocks and Oracle Instance Caching Finding Baselines Learning Instance Tuning from TPC Performance Benchmarks Rules for adjusting shared_pool_size Sizing the Shared Pool with the Oracle Advisory Utility Rules for Adjusting the Data Buffer Sizes Monitoring RAM usage Tracking hash joins Viewing RAM usage for hash joins in SQL Conclusion Chapter 15: Tablespace Tuning Oracle Tablespace Tuning Inside Oracle Tablespace Tuning ASSM and Tablespace Performance Internal Freelist Management Characteristics of Bitmap Segment Management New High Watermark Pointers Extent Control Header Block Using ASSM with RAC Databases Potential Performance Issues with ASSM Detecting Tablespace Fragmentation Tablespace tuning tips Conclusion Chapter 16: Oracle Table Tuning Oracle table performance The Freelist Unlink Process The Issue of pctused Setting pctfree and pctused Freelists and segment Performance Oracle 11g table compression The overhead of Oracle table compression Faster SQL with Database Reorganizations Managing Row Chaining in Oracle Function-based virtual columns Features and limitations of 11g virtual columns A Summary of Object Tuning Rules Reorganizing Tables for High Performance Oracle Partitioning and table tuning Partition-wise joins Partition pruning Partition Pruning Internals Online Reorganization Tuning SQL Access with clustering_factor Sorted hash cluster tables Creating a sorted hash cluster External Tables and SQL Defining an External Table Internals of External Tables Conclusion Chapter 17: Tuning with indexes SQL Tuning with Indexes Do I have missing indexes? The types of Oracle indexes The Oracle b-tree index Creating a b-tree index Does block size matter? Tuning SQL with bitmapped indexes Distinct key values and bitmap indexes! SQL Tuning with bitmap join indexes How bitmap join indexes work Bitmap join index example When Oracle SQL chooses the wrong index Beware of the fast fix Forcing index usage Not all Indexes are Used in Range Scans Why doesn’t Oracle use my index? When to Rebuild Indexes Tuning with Index Organized Tables Index usage and built-in functions Finding BIF’s Tuning SQL with Function-based Indexes (FBI) Using case statements with a function-based index Indexing on complex functions Statistics and function-based indexes Locating function-based indexes Conclusions on function-based indexes Doing case sensitive searches with indexes SQL Tuning with Oracle*Text Indexes Oracle Text Index re-synchronization Testing new Oracle indexes Testing SQL workloads with invisible indexes Monitoring index usage Monitoring for Index Range Scans Verifying optimal index usage Finding indexing opportunities Find SQL that uses sub-optimal indexes Finding SQL with excessive I/O Finding sub-optimal SQL in the library cache Finding index opportunities in AWR Locating un-used indexes Finding un-used indexes Dropping un-used indexes Locating infrequently used indexes The problem of too many indexes Determining which indexes to delete Large Multi-column Indexes Table Rows and the clustering factor Index reorganization and SQL Performance When rebuilding indexes may help SQL performance When rebuilding indexes might hurt performance Index behavior and Oracle blocksize Choosing candidates for index maintenance Conclusion Chapter 18: Oracle Troubleshooting Introduction to Oracle Troubleshooting What to do with a hung database Emergency Troubleshooting Methods Troubleshooting Limitations Troubleshooting Triage Accurately Measuring Oracle Performance Locating Top Resource Sessions Chapter Conclusion Book Conclusion Index About the Author

Index Topics:

$oracle_sid _always_star_transformation _db_aging_cool_count _db_aging_freeze_cr _db_aging_hot_criteria _db_aging_stay_count _db_aging_touch_time _db_block_cache_clone _db_block_cache_map _db_block_cache_protect _db_block_hash_buckets _db_block_hash_latches _db_block_hi_priority_batch_size _db_block_max_cr_dba _db_block_max_scan_cnt _db_block_med_priority_batch_size _db_block_write_batch _db_percent_hot_default _db_percent_hot_keep _db_percent_hot_recycle _fast_full_scan_enabled _kgl_latch_count _latch_spin_count _optimizer_cost_model _parallelism_cost_fudge_factor _pga_max_size _small_table_threshold _smm_px_max_size 10046 10046 trace event 10046 trace file 10046 wait event 32-bit shops A Active Session History Active Session History (ASH) adaptive alert thresholds Adaptive cursor sharing adaptive threshold add_window_group_member ADDM addm_rpt.sql ad-hoc display ad-hoc query Advisor Central advisor_name advisories alert mechanism alert.log all_rows all_rows optimizer_mode alter index rebuild alter session alter system alter system command alter system flush shared_pool alter table alter table shrink space AMM analysis job analyze table append option Application Server 10g Apps 11i ARCH Artificial Intelligence (AI) ASH ASH tables ASH views ASSM asynch auto option auto_degree auto_task_consumer_group auto_task_job_class auto_tasks_job_class autoextend Automated Database Diagnostic Monitor (ADDM) Automated Memory Management Automated Session History Automated Session History (ASH) automated SQL tuning Automated Storage Management Automated Workload Repository Automated Workload Repository (AWR) Automatic Database Diagnostic Monitor Automatic Diagnostic Database Advisor automatic load balancing Automatic Maintenance Tasks Automatic Memory Management Automatic Memory Management (AMM Automatic Memory Manager Automatic Memory Manager (AMM) Automatic Segment Advisor Automatic segment management Automatic Segment Management Automatic Segment Space Automatic Segment Space Management Automatic Shared Memory Management Automatic Shared Memory Management (ASMM) Automatic Storage Management Automatic Storage Management (ASM) Automatic Workload Repository automatic_ipc average_waiter_count avg_row_len AWR AWR baselines AWR metrics AWR Report AWR/STATSPACK report awr_high_resource_sql.sql awr_report_html awr_report_text awr_sql_scan_sums.sql awrrpt.sql B b*tree baseline_name begin_interval_time begin_time BgnSnap Bi-modal systems bitmap bitmap block bitmap freelists bitmap indexes bitmap join bitmap space management bitmap_merge_area_size blade servers block spreading block_count.sql Blocking lock ratio blocksize Bottleneck analysis break_poll_skip bstat-estat bstat-estat reports Bubble fragmentation buf_blocks.sql buf_keep_pool.sql buffer busy wait buffer busy waits Buffer busy waits buffer cache Buffer cache hit (%) Buffer cache hit ratio Buffer Cache Hit Ratio (BCHR) buffer cache size buffer hit ratio buffer wait statistics buffer_busy_wait buffer_gets_th buffer_pool_keep buffer_pool_recycle bulk binds C C++ cache buffers chain cache buffers LRU chain cache fusion cache hit cache size cardinality Cardinality Cartesian joins cascade catawr.sql catdbsyn.sql CBO CBO statistics chained row fetches chained/migrated rows change data capture close_window cluster interconnect9 clustering_factor clusters CODASYL DBTG Confio Ignite consistent gets consistent_gets convosync correlation matrix Cost Based Optimizer Cost Based Optimizer (CBO) cost-based optimizer covariate analysis CPU bottleneck CPU costs CPU dispatcher run queue CPU dispatching CPU overhead CPU run queue CPU run queue waits cpu_count CPU-bound CPU-bound database Create Job create part (DDL) Create Table As Select (CTAS) create_bitmap_area_size create_job create_snapshot create_tuning_task crontab cross join CTAS current statistics cursor_sharing cursor_space_for_time D data blocks data buffer data buffer hit ratio data buffer hit ratio (DBHR) data buffer pools data buffers data dictionary views data localization Data Manipulation Language Data Manipulation Lock (DML) data quality data transmission delay data warehouse data_object_id Database block gets database blocksize Database Control Database Diagnostic Pack Database Tuning Pack day\ mode DB block gets DB CPU db file parallel write db file scatter read db file scatter reads db file scattered read db file scattered read waits db file scattered reads db file sequential read db file sequential read waits db file sequential reads db time DB time db_16k_cache_size db_2k_cache_size db_32k cache_size db_32k_cache_size db_aging_hot_criteria db_aging_touch_time db_block_buffers db_block_gets db_block_lru_latches db_block_lru_statistics db_block_size db_cache_advice db_cache_size db_file_multiblock_read_count db_file_simultaneous_writes db_files db_keep_cache_size db_nk_cache_size db_recycle_cache_size db_time_in_wait DB2 DBA dba_advisor dba_advisor_actions dba_advisor_commands dba_advisor_def_parameters dba_advisor_definitions dba_advisor_directives dba_advisor_findings dba_advisor_journal dba_advisor_log dba_advisor_object_types dba_advisor_objects dba_advisor_parameters dba_advisor_rationale dba_advisor_recommendations dba_advisor_sqla_rec_sum dba_advisor_sqla_wk_map dba_advisor_sqla_wk_stmts dba_advisor_sqlw_colvol dba_advisor_sqlw_journal dba_advisor_sqlw_parameters dba_advisor_sqlw_stmts dba_advisor_sqlw_sum dba_advisor_sqlw_tables dba_advisor_sqlw_tabvol dba_advisor_sqlw_templates dba_advisor_tasks dba_advisor_templates dba_advisor_usage dba_cache_advice dba_data_files dba_extents dba_feature_usage_statistics dba_hist dba_hist tables dba_hist$buffer_pool_statistics table dba_hist_active_sess_history dba_hist_active_session_history dba_hist_baselines dba_hist_bg_event_summary dba_hist_buffer_pool_stat dba_hist_buffer_pool_stat table dba_hist_buffer_pool_statistics dba_hist_db_cache_advice dba_hist_enqueue_stat dba_hist_event_name dba_hist_event_summary dba_hist_filemetric_history dba_hist_filestatxs dba_hist_latch dba_hist_latch_children dba_hist_latch_misses_summary dba_hist_librarycache dba_hist_metric_name dba_hist_osstat dba_hist_pga_target_advice dba_hist_pgastat dba_hist_rowcache_summary dba_hist_seg_stat dba_hist_service_name dba_hist_service_stat

dba_hist_service_wait_class dba_hist_sessmetric_history dba_hist_sgastat dba_hist_shared_pool_advice dba_hist_snapshot dba_hist_sql_plan dba_hist_sql_summary dba_hist_sqlplan dba_hist_sqlstat dba_hist_sqltext dba_hist_stat_name dba_hist_statname dba_hist_sys_time_model dba_hist_sysmetric_history dba_hist_sysmetric_summary dba_hist_sysstat dba_hist_systat dba_hist_system_event dba_hist_tempstatxs dba_hist_waitclassmet_history dba_hist_waitstat dba_hist_wr_control dba_histograms dba_indexes dba_jobs dba_objects dba_scheduler_job_classes dba_scheduler_jobs dba_scheduler_schedules dba_scheduler_window_groups dba_scheduler_windows dba_scheduler_wingroup_members dba_segments dba_tab_modifications dba_tables dba_tables.avg_row_len dba_users DBHR dbid DbId dbms_advisor dbms_job dbms_mview dbms_redefinition dbms_scheduler dbms_session.set_ev() dbms_shared_pool dbms_space.space_usage dbms_sqlpa dbms_sqltune dbms_sqltune.create_sqlset dbms_stat.gather_system_stats dbms_stats dbms_stats.gather_workload_stats dbms_utility dbms_workload_capture dbms_workload_repository dbms_xplan.display_awr dbmsawr.sql dbmspool.sql DBWR Decision Support System Decision Support System (DSS) Decision Support Systems decision support systems (DSS) Decision Support Systems (DSS) Default buffer cache DEFAULT pool default_degree deleted leaf node deletes dictionary cache dictionary cache dictionary cache dictionary-managed tablespace dictionary-managed tablespaces direct I/O directio Disk bottleneck Disk enqueues disk I/O disk I/O waits disk_read_th dispatcher timer dispatching priority DISTINCT DML driving \table drop indexes drop_window_group DSS duration dynamic sampling dynamic_sampling E efficiency metrics elapsed time end_interval_time end_time EndSnap enqueue enqueue statistics enqueue waits enqueues Enterprise Manager enterprise resource planning equijoin predicate estimate_percent ETL process event tables exception reports excessive logical I/O execute_tuning_task execution history execution plan executions_delta executions_th EXPLAIN explain_rewrite extent management local extent size external tables Extract, Transform, and Load (ETL) Extract, Transformation and Loading (ETL) F Fast Application Notification (FAN) fast_start_parallel_rollback fetch phase fileio.sql script filesystem filesystemio_options first_rows first_rows optimizer_mode flush_level force forcedirectio forecasting fragmentation free_buffer_wait freelist freelist groups freelist_groups freelists from clause full outer join full table scans full-table scan full-table scans function-based indexes G Gallium Arsenide gather auto gather stale gather_schema_stats gather_stats_job gather_table_stats get_io.sql script get_iostat.ksh get_vmstat.ksh glance Graphical User Interface (GUI) grep grid computing Grid computing GROUP BY group_name GUI H hard parse hard parse elapsed time hard parses hash cluster hash cluster tables HASH JOIN FULL OUTER hash joins hash_area_size hash_multiblock_io_count HASH_VALUE having clause hidden parameters high high watermark (HHWM) high water mark (HWM) high watermark High WaterMark (HWM) histogram holistic tuning hypothesis testing Hypothesis testing hypothetical workload I I/O activity I/O bound database I/O overload I/O session I/O statistics I/O-bound IN index Index Organized Tables index range scan index range scans index rebuilding index rebuilds index usage index_ffs indexes Informaticists (data content experts) init.ora init.ora parameter init.ora parameters initialization parameters initrans inline hint insert … select statement insert statement instance activity instance configuration parameters instance recovery instance tuning instance_number InstNum inter-instance block transfers inter-instance parallelism internal transaction list (ITL) waits inter-query parallelism INTERSECT interval intra-instance transportable locks intra-query parallelism io_cost Ion Ion Enterprise Edition Ion tool iostat iostat utility Itanium 2 Itanium 2 architecture J Java Java API java pool Java pool java_pool_size job_name Journal File System (JFS) junk data K Keep buffer cache keep cache keep pool KEEP pool L Laboratory Information Management Systems Large pool large_pool_size large-scale RAC databases large-table full-table scans latch contention latch free latch hit percentage latch sleeps latch statistics latch waits latches least-frequently-used (LRU) left outer join LGWR library cache library cache hit library cache latch library cache miss ratio Lightweight Onboard Monitor (LTOM) listener.ora load profile LOB LOBs Locally Managed Tablespace Locally Managed Tablespaces locally-managed tablespaces lock element cleanup log buffer space Log Writer log_buffer logical I/O (LIO) logical reads long duration metrics low high watermark (LHWM) lsattr lsattr -El lsdev LTOM M Manage Scheduler Manageability Monitor materialize hint materialized views max_interval max_retention maximum transmission unit Memory Advisor memory_max_target merges method_opt metric baselines metric group metric_unit min_interval min_retention minextents MMON modify_snapshot_settings monitoring clause Monolithic servers Moore’s law Moore’s Law MS Windows mts_dispatchers MTU multiblock reads multiblock_read_count multi-instance RAC database multiple blocksizes multiple freelists multivariate statistics mutable joins MySQL

N name natural join nested loop join nested loops join nested loops joins Network bottleneck network bottlenecks Network bottlenecks Network latency network overload network-bound new explain plan next nice values night\ mode no_cpu_costing 1189 Non Uniform Memory Access (NUMA) Non-Uniform Memory Access (NUMA) NOT IN noworkload statistics Null event null values num_rows num_sql O Object fragmentation object partition joins object_growth_trend ODM OEM OEM 10g DB Control OEM workload OEM2GO OLTP on clause on-demand CPU online reorg Online Transaction Processing (OLTP) Online Transaction Processing Systems (OLTP) open_window optimal join techniques optimal parameter optimizer optimizer directives optimizer plan stability Optimizer Plan Stability optimizer statistics optimizer_dynamic_sampling optimizer_features_enable optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations optimizer_mode optimizer_percent_parallel options Oracle 10g Discoverer Oracle Data Guard Oracle Data Miner Oracle Discoverer Oracle Enterprise Manager Oracle Enterprise Manager (OEM) Oracle hints Oracle HTTP server (OHS) Oracle instance tuning Oracle Parallel Query Oracle Parallel Query (OPQ) Oracle Parallel Server Oracle Parallel Server (OPS) Oracle Streams Oracle TPC-H Oracle Trace Analyzer Oracle Wait Interface Oracle Warehouse Builder (OWB) Oracle Web Server Oracle workload oradebug ORDER BY order by clause ordered hint ordered_predicates OS buffer OS parameters outer join P page-in page-out parallel backup/recovery parallel clause parallel DDL statements parallel indexing parallel loading parallel query parallel query dequeue wait parallel query idle wait - Slaves parallel server tables parallel_adaptive_multi_user parallel_automatic_tuning parallel_automatic_tuning (PAT) parallel_index parallel_max_servers parallel_threads_per_cpu parallelism Parallelize deletes parse count (hard) parse_calls_th partitioning pctfree PCTFREE pctfree / pctused pctused PCTUSED Percentage of Maximum perfstat persistent SQL philosophy PGA PGA Advisor PGA cache hit (%) PGA management workarea_size_policy PGA memory management PGA multi-pass executions pga_aggregate_target pga_size.sql phys_reads.sql physical disk I/O physical disk I/O (PIO) physical I/O waits physical read waits physical reads physical_reads physical_writes physpctio.sql query phywrts pipe get PL/SQL lock timer PL/SQL packages plan regression plan9i.sql pmon timer predicate pushing predictive modeling Predictive modeling Proactive models proactive tuning process events processor affinity Processor Queue Length ProfitLogic Program Global Area Program Global Area (PGA) project project manager protocol.ora prtconf Q QIO QIOSTAT query_rewrite_enabled Quest Central R RAC RAC load balancing RAID 0+1 RAID 1+0 RAID 10 RAID 5 RAID striping RAID5 RAID-5 RAM RAM bottleneck RAM overload RAM page-ins RBO RDBMS rdbms ipc message reactive tuning read waits read-only tablespaces read-write head delay Real Application Cluster (RAC) Real Application Clusters Real Application Clusters (RAC) Real Applications Clusters rebuild command recodify_snapshot_settings Recommendations Options Recycle buffer cache recycle pool RECYCLE pool redo allocation latch Redo log buffer remove_window_group_member repeat report_plans report_tuning_task retention right outer join rollback segments Rotational delay row lock waits ROWID values rpt_bhr_all.sql rpt_bhr_awr_hr.sql RRDtool RRDTool run_job run_vmstat.ksh runqueue value runqueue values S SAME SAME (Stripe and Mirror Everywhere) sample_size sample_time sar SAR SAS scalar subqueries scale out scale out approach scale up scale up approach scattered disk reads Schedule Advisor schema SDU Segment Advisor segment space management auto segment statistics select select clause select part (query) select statement sequential disk reads server alerts server consolidation server metrics Server RAM paging server run queue waits service statistics sess_waits_ash.sql session data unit session events Session ID (SID) session_cached_cursors session_id session_state set events set_attribute setall SGA SGA sizing SGA summary tables sga_max_size sga_target sharable_mem_th shared pool shared pool advice Shared Pool Free (%) shared pool size shared_pool shared_pool_size short duration metrics show parameters buffer show sga shrink space signature signature analysis Signature analysis signatures Significance Level skewonly slave wait smon timer SMP servers snap_level snap_time snapdeltafileio_awr.sql snapfileio_10g.sql snapshot set snapshots soft parse soft parses Solid-state Disk (SSD) Solid-State Disk (SSD) sort_area_retained_size sort_area_size sp*.sql SPA space management blocks space_alloc space_usage spauto.sql spcpkg.sql spcreate.sql spctab.sql spcusr.sql spdoc.txt spdrop.sql spdtab.sql spdusr.sql spfile sppurge.sql sprepcon.sql sprepins.sql spreport.sql sprepsql.sql sprsqins.sql SPSS sptrunc.sql spuexp.par SQL SQL Access Advisor SQL Advisor SQL details SQL Performance Advisor SQL Performance Analyzer SQL Performance Analyzer (SPA) SQL Plan Manager (SPM) SQL profile SQL profiles SQL Profiles SQL query SQL Response Time (%) SQL Scripts

9icartcount.sql 9icartsql.sql 9ilarge_scanusers.sql 9iltabscan.sql 9iplanstats.sql 9itabscan.sql 9iunused_indx.sql archhist.sql bgact.sql buffutl.sql bufobjwaits.sql cacheobjcnt.sql cartsession.sql cartsql.sql csesswaits.sql curriosql.sql currlwaits.sql datafileae.sql dictdet.sql fullsql.sql globaccpatt.sql globiostats.sql largescan9i.sql latchdet.sql libdet.sql libobj.sql libwait.sql lockcnt.sql maxext7.sql memhog.sql memsnap.sql objdef.sql objwait.sql physpctio.sql poolhit.sql scatwait.sql sesshitrate.sql sesswaits.sql sgasize.sql sortdet.sql sortusage.sql sqlhitrate.sql syswaits.sql top9isql.sql topiousers.sql topsess.sql topsessdet.sql toptables.sql totpctio.sql totuserspace.sql tsfrag.sql userscans.sql SQL Server SQL Snapshots SQL statement SQL tuning SQL Tuning SQL Tuning Advisor SQL Tuning Set SQL Tuning Set (STS) SQL Tuning Sets SQL Tuning Sets (STS) SQL*DBA SQL*Loader SQL*Net SQL*Net break/reset to client SQL*Net message from client SQL*Net message to client SQL*Net more data to client SQL*Net wait events SQL*Net waits SQL*Plus SQL*Trace sql_id sql_trace SQLAccess sqlnet.ora SQLTuning sqltxplain SSD stacked views standalone database STAR query STAR schema star_query star_transformation star_transformation_enabled start_capture statistcis_level statistician statistics_level stats$bg_event_summary stats$buffer_pool_statistics stats$buffer_pool_statistics table stats$buffered_queues stats$buffered_subscribers stats$event_histogram stats$filestatxs stats$latch stats$latch_children stats$librarycache stats$propagation_receiver stats$propagation_sender stats$rowcache_summary stats$rule_set stats$sesstat table stats$sgastat stats$snapshot stats$sql_summary stats$statspack_parameter stats$streams_apply_sum stats$streams_capture stats$sysstat stats$system_event stats$waitstat statscre.sql statsctab.sql STATSPACK STATSPACK report STATSPACK reports STATSPACK utility statspack.snap STATSPACK/AWR report stop_job Storage Area Networks (SAN) Stripe and Mirror Everywhere STS suboptimal SQL subquery Support Vector Machines swap disk symmetric multiprocessing (SMP) sys.aux_stat$ sys.v_$sysstat sys.v_$system_event System Global Area System Global Area (SGA) system latches System Response Time system tables syswaits.sql script T table fetch continued row table join table join order table scan table scans (long tables) Tablespace fragmentation tablespace partitions TAF tch column 865, 890 tcp.nodelay 665, 666, 667 TDU throughput metrics tim column time model approach time model statistics Time Model Statistics report time series trend charts time series tuning time_waited time-based wait tuning timed_statistics time-series tuning TKPROF tnsnames.ora top top 5 timed events top five timed event top five wait events top SQL Top SQL screen top-down approach topiousers.sql query toptables.sql query Total Cost of Ownership (TCO) totpctio.sql query touch count trace events trace file trace report transaction tables Transparent Application Failover (TAF) Transparent Network Substrate trend-based reconfiguration trend-based tools Tuning Advisors tuning history U undo segment statistics undo segments UNDO tablespace UNION UNIX commands update statement updates use_current_session user_dump_dest using clause utlbstat utlestat V v$ views v$active_sess_hist v$active_session_history v$bh v$buffer_pool_statistics v$db_cache_advice v$enqueue_statistics v$event_histogram v$event_name v$eventmetric v$file_histogram v$filemetric v$filemetric_history v$latch v$librarycache v$metric_name v$metricgroup v$metricname v$osstat v$pga_target_advice v$rowcache v$segment_statistics v$segstat v$segstat_name v$servicemetric v$servicemetric_history v$services v$sess_time_model v$session v$session_event v$session_wait v$session_wait_class v$session_wait_history v$sessmetric v$shared_pool_advice v$sql v$sql_plan v$sql_shared_cursor v$sqlarea v$statname v$sys_time_model v$sysmetric v$sysmetric_history v$sysmetric_summary v$sysstat v$sysstat value v$system_event v$system_wait_class v$temp_histogram v$waitclassmetric v$waitclassmetric_history v$waitstat v_$sql_plan varchar varyio version_count_th very large database (VLDB) very large databases (VLDB) view Views v$sessstat view v$sql_plan v$sql_plan_statistics v$sqlarea v$sysstat v_$sql_plan virtual circuit status Virtual Memory Virtual Memory (VM) vmstat vmstat capture vmstat utility W wait event wait event histogram Wait Event Interface wait event tuning wait events wait_class wait_count wait_time wait_time_detail wait_time_detail.sql where clause WHERE clause window_list window_name Windows Performance Manager WISE tool with clause workarea_size_policy Workload analysis workload statistics wrh$ tables wrh$ views wrh$_active_session_history wrh$_active_session_history_bl wrh$_bg_event_summary wrh$_buffer_pool_statistics wrh$_event_name wrh$_filemetric_history wrh$_filestatxs wrh$_latch wrh$_latch_children wrh$_librarycache wrh$_metric_name wrh$_rowcache_summary wrh$_sessmetric_history wrh$_sgastat wrh$_sql_summary wrh$_sys_time_model wrh$_sys_time_model_bl wrh$_sysmetric_history wrh$_sysmetric_summary wrh$_sysstat wrh$_sysstat_bl wrh$_system_event wrh$_system_event_bl wrh$_waitclassmetric_history wrh$_waitstat wrh$_waitstat_bl wri$ tables wri$_alert_history wri$_dbu_feature_metadata wri$_dbu_feature_usage wri$_dbu_usage_sample wrm$ tables wrm$_snapshot X x$bh x$bh.tch x$kcbcbh x$kcbrbh