Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

  

 

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

 

Oracle SQL Tuning & CBO Internals

By Kimberly Floss
          

Retail Price $27.95  / £17.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
     
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

 


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.

 
Key Features

* 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:
 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks