|
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) |
 |
Get the
Oracle Tuning Library
Save $100 - All four books for only $118.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.
|
|
* 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:
|
|
