ISBN 0-9745993-3-6 ISBN 13 978-0974599335 Library of Congress: 2004101894 300 pages: Perfect bind 9x7 Shelving: Databases/Oracle Oracle In-Focus Series # 16

Oracle SQL Tuning & CBO Internals By Kimberly Floss Retail Price $27.95 Order now and get immediate online access to the code depot Only $19.95 (30% off) Key Features About the Authors Table of Contents Index Reader Comments Errata

Learn to hypercharge your SQL performance with Oracle's Cost-Based Optimizer! By drawing from the combined expertise of the world’s leading Oracle experts, it has been possible to create this indispensable guide to Oracle SQL. Tuning Oracle SQL is the single most important skill of the Oracle professional, and Oracle professionals are challenged to create SQL statements that will support thousands of concurrent executions with sub-second response time. This landmark text focuses on advanced Oracle SQL internals and Oracle indexing management. With combined experience of over a century, the world’s best Oracle authors now share their secrets for Turbocharging Oracle SQL execution. This is a super-advanced book for the senior Oracle professional and it is not appropriate for the beginner. The test explores important internal mechanisms within Oracle and illustrates the powerful and complex internals of Oracle SQL execution. Topics include the internals of Oracle cost-based SQL optimizer, SQL execution internals within the library cache, Oracle SQL coding and optimization techniques, and Oracle index internals. Best of all, this text comes with a ready-to-use code depot that is full of working SQL tuning scripts. By leveraging on the expertise of the world's leading Oracle experts, you can quickly optimize the SQL and indexes inside your Oracle database.

* Hypercharge your SQL Performance * See inside the Oracle Cost-Based Optimizer * Learn the experts' secrets for Turbocharging Oracle SQL * Receive ready-to-run scripts to tune your Oracle SQL * Tune your library cache for super-fast SQL parsing * Understand the internals of Oracle indexing * See when to use Oracle bitmap indexes * Explore the internals of SQL execution * Understand optimizer plan stability (stored outlines) * See how hints can change SQL execution plans * Understand multi-level index partitioning * Learn why database blocksize is critical for Oracle indexes

About the Author:

Kimberly Floss

Kimberly Floss is one of the USA's most respected Oracle Database Administrators and President of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle Performance Tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle User Group and the Midwest Oracle User Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the Information Technology Industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server, and Sybase. She holds a Bachelor's of Science Degree in Computer Information Systems from Purdue University, specializing in Systems Analysis and Design, and an MBA with emphasis in Management Information Systems from Loyola University. Kimberly has extensive experience performing as a DBA on Mainframe, Unix and Windows-based systems, as well as managing DBA teams supporting multiple database platforms. She also works as an Adjunct Professor, teaching SQL Programming / Database Administration at a local community college. A popular presenter at International Oracle conferences, Kimberly lives in the Chicagoland area with her husband, son, and two cats.

Table of Contents:

Chapter 1 – Introduction to SQL tuning The evolution of Oracle SQL Statistics Statspack Plan Stability Materialized Views Introduction to the CBO Optimizer Inside the Oracle10g Optimizer – Donald K. Burleson New SQL Features Exploiting the New 9i Data Caches – Robin Schumacher Conclusion Chapter 2 - Cost-Based SQL optimizer The Cost-Based Optimizer – The Basics Cost-Based Optimization: Whys and Hows – Rick Greenwald Rule-based Optimization The Cost-Based Optimizer Collecting Statistics Which Optimizer to Use Overriding the Decision of the Optimizer Conclusion Internals of the Cost-Based Optimizer – Pete Cassidy Function-Based Indexes Materialized Views Stored Outlines Bitmap Indexes Histograms Hash Joins Hints Indexed Organized Tables Conclusion Histograms The Importance of Histograms with the Cost Based Optimizer Statistics and the Cost-Based Optimizer Cost-Based Optimizer Assumptions How Histograms Help the CBO CBO Behavior without Statistics CBO Behavior with Statistics CBO Behavior with Statistics and Histograms How Histogram Statistics Are Stored Identifying Histogram Candidates Summary SQL Tuning Tips – Donald K. Burleson Compute vs. Estimate Conclusion. Chapter 3 – SQL execution internals Oracle Hints Internals of Oracle Hints My Hint Is Not Working Conclusion Using STATSPACK Utility An In-Depth Look at Oracle’s Correlated Subqueries What Are Non-Correlated Subqueriea? Correlated Subqueries Existence Queries and Correlation From IN to EXISTS NOT EXISTS The Universal and Existential Qualifiers The Way the Query Works Summary Joins Oracle’s Joins - Richard Earp and Sikha Bagui Cartesian Product Equi-joins Joining More Than One Table Outer JoinsNatural Join Outer Join with an AND Outer Joins and Nulls Outer Join Not Included in the Null Condition Outer Join with OR and IN Inline Views and Outer Joins Symmetric Outer Joins Chaining Outer Joins Performance of Outer Joins Self Join Joining Views Joining More than Two Tables Tuning Issues and Processing of Joins Merge Join Nested Loops Hash Joins Summary Using Hash Joins Increasing Hash Joins – Donald K. Burleson Increasing RAM sorting Conclusion

Chapter 4 - Oracle SQL extensions for performance Analytic Functions Analytic Functions The Way Things Were An Analytic Solution Common Requirements Conclusion Advanced Analytical SQL Functions Dropping Columns The Old Way Top N Queries Group By Cub Group By Rollup Function-Based Indexes Descending Indexes Materialized Views Index Column Order Does Matter Index Order and Structure Summary Materialized Views DBMS_STATS Transportable Tablespaces Restrictions on Transporting Tablespaces Locally Managed Tablespaces LogMiner Database Event Triggers Defragging Non-partitioned Tables Explain Plan The Old Way Exporting Selected Rows from a Table Column Definitions for Data Dictionary Tables Init.ora Parameter Definitions Conclusion External Tables Using External Tables in Oracle9i Using External Tables Example Creation of an External Table Hints and External Tables External Table Performance Using External Tables in Parallel Using External Tables to Export Data Multi-Table Insert Using External Tables Using the MERGE Command with External Tables Summary Stored Outlines Oracle 10g New SQL Language Features Grouped Table Outer Join Increased Number of Aggregates per Query Remote Stored Functions in SELECT Statements Case-Insensitive and Accent-Insensitive Query and Sort Enhanced CONNECT BY Support Oracle Expression Filter SQL Regular Expressions Row Timestamp Review Oracle 10g Automatic Database Diagnostics Management Oracle 10g SQL Tuning Advisor Step 1: Create a tuning task Step 2: Execute the tuning task Step 3: See the results Step 4: Determine what is to be implemented, and execute accordingly Conclusion Chapter 5 - Coding for Performance High Performing SQL – Where Do You Start? SQL Analysis Made Easy Using New Performance Views in Oracle9i to Identify Problem SQL Find the Problem Sessions Identify the Resource-Intensive Code Tune the Code/Database for Better Performance Conclusion SQL Perfection Checklist: Step-by-Step Methods for Creating High-Quality Database Code Start with the Basics Know Thy Database EXPLAIN and Understand Correct Obvious Flaws Try Different Combinations Benchmark Your Rewrites Revisit When Necessary Epilogue And If You Can’t Change The Code Index Rebuilds Inside Oracle b-tree Indexes Index Information. Are There Criteria for Index / Table Rebuilding? Histograms Finding the Poorly Running SQL Optimize Query Speed with the Clustering_factor Attribute Rules for Oracle Indexing Use of Nologging SQL*Net Tuning SQL*Net Diagnostics and Performance Tuning SQL*Net Diagnostics Optimizing Query Time with SDU Buffer Size Optimizing Connect Time with Dedicated Server Optimizing Connect Time with MTS Tuning Methodology Based on MTS Statistics Improving the Interface Listener Load Balancing Net8 New Features Connection Pooling: How It Works Summary Conclusion

Index Topics:

_smm_max_size A access_driver_type add_elementary_attribute add_functions ADDM ANALYZE assign_attribute_set attribute_name attribute_set_instance attribute_value auto_sample_size Automatic Database Diagnostic Monitor Automatic Workload Repository avg() B Bitmap indexes Bitmap join indexes build_exception_table C cache Cartesian join Cartesian product catexf.sql catnoexf.sql clear_exprset_stats clustering_factor connect_by_iscycle connect_by_isleaf connect_by_root copy_attribute_set corr() Cost-based Optimizer count() covar_pop() covar_samp() CREATE INDEX create_attribute_set create_stored_outlines cume_dist() D db_block_size db_file_multiblock_read_count db_nk_cache_size dba_advisor_findings dba_advisor_recommendations dba_hist_snapshot dba_indexes dba_objects dba_outline_hints dba_outlines dba_tab_columns dba_tab_histograms dba_tables dba_triggers dbms_expfil dbms_lock dbms_registry dbms_standard dbms_stats dbms_tts dbms_utility default_index_parameters default_xpindex_parameters defrag_index dense_rank() drop_attribute_set E EVALUATE exf$table_alias exfeapvs.plb exfimpvs.plb exfpbs.sql exfsppvs.plb exftab.sql exftyp.sql exfview.sql exfxppvs.plb EXISTS predicate

Explain Plan expression_column external_table_clause F first_rows first_value() Function-based indexes G get_exprset_stats getVarchar() grant_privilege H HASH JOIN Hash joins hash_area_size hash_join_enabled Hints Histograms hs_call_name I i_modify_parameter Index combine access Index fast-full-scans index_combine index_parameters INDEXATTRS Indexed organized tables initexf.sql initsid.ora intrv_busy intrv_idle intrv_ratio J job_queue_processes L lag() large_pool_size last_value() lead() location_identifier log_directory_client M Materialized views max() max_parallel_servers MERGE JOIN method_opt min() min_parallel_servers mts_dispatcher_statistics mts_dispatchers mts_max_dispatchers mts_max_servers mts_multiple_listeners mts_server_statistics mts_servers mts_statistics mv_stats N NESTED LOOP Nested loop joins NESTED LOOPS nls_lang nls_sort NOT EXISTS predicate ntile() O opaque_format_spec Optimizer Plan Stability optimizer_goal optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations optimizer_mode ora_rowscn outln.ol$ outln.ol$_hints over() P parallel_automatic_tuning parallel_clause percent_rank() pga_aggregate_target plan stability Plan Stability plan_table pool_size POSIX prespawn_max prev_idle private

Q query_rewrite_enabled query_rewrite_integrity R rank() ratio_to_report() regexp_instr relational_properties REPLACE DEFAULTS revoke_privilege row_number(). S scn_to_timestamp set skewonly SORT MERGE JOIN sort_area_size sql_tables Star joins statistics_level STATSPACK stddev() stddev_pop() stddev_samp() STOREATTRS stored outlines Stored outlines subquery_factoring_clause SUBSTR sum() sys.event_log T table_properties timeout tnsping trace_directory_client trace_level_client transport_tablespace trcasst trceval U unassign_attribute_set use_hash use_stored_outlines user_expfil_aset_functions user_expfil_attribute_sets user_expfil_attributes user_expfil_def_index_params user_expfil_expression_sets user_expfil_exprset_stats user_expfil_index_params user_expfil_indexes user_expfil_predtab_attributes user_expfil_privileges user_ind_expressions user_indexes user_outline_hints user_outlines user_tables user_unused_col_tabs utlxplp.sql utlxpls.sql V v$dbfile v$hs_parameter v$logmnr_contents v$parameter v$segment_statistics v$sesstat v$sql v$sql_plan v$sql_plan_statistics v$sqlarea v$sysstat v_$dispatcher v_$shared_server v_$sysstat validate_expressions var_pop() var_samp() variance() X xpindex_parameters

Reviews:

Errata: