BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Oracle SQL tuning with hints

Oracle Tips by Burleson Consulting

Oracle tuning with hints

Oracle ensures that the cost-based SQL optimizer becomes more sophisticated with each release.  With each new release, Oracle provides an increasing number of methods for changing the execution plans for SQL statements. While hints are used for tuning as well as documentation, the most common use for Oracle hints is as a debugging tool. The hints can be used to determine the optimal execution plan, and then work backward, adjusting the statistics to make the vanilla SQL simulate the hinted query.

 

Using Oracle hints can be very complicated, and Oracle developers only use hints as a last resort, preferring to alter the statistics to change the execution plan. Oracle contains more than 124 hints, and many of them are not found in the Oracle documentation.

 

Undocumented Hints:

bypass_recursive_check

bypass_ujvc

cache_cb

cache_temp_table

civ_gb

collections_get_refs

cube_gb

cursor_sharing_exact

deref_no_rewrite

dml_update

domain_index_no_sort

domain_index_sort

dynamic_sampling 

dynamic_sampling  _est_cdn

expand_gset_to_union

force_sample_block

gby_conc_rollup

global_table_hints

hwm_brokered

 

ignore_on_clause

ignore_where_clause

index_rrs

index_ss

index_ss_asc

index_ss_desc

like_expand

local_indexes

mv_merge

nested_table_get_refs

nested_table_set_refs

nested_table_setid

no_expand_gset_to_union

no_fact

no_filtering

no_order_rollups

no_prune_gsets

no_stats_gsets

no_unnest       

nocpu_costing

overflow_nomove

piv_gb

piv_ssf

pq_map

pq_nomap

remote_mapped

restore_as_intervals

save_as_intervals

scn_ascending

skip_ext_optimizer

sqlldr

sys_dl_cursor

sys_parallel_txn

sys_rid_order

tiv_gb

tiv_ssf

unnest

use_ttt_for_gsets

 

 

Hints can be used to alter optimizer execution plans.  Remember, an optimizer hint is a directive that is placed inside comments inside the SQL statement and used in those rare cases where the optimizer makes an incorrect decision about the execution plan. Since hints are inside comments, it is important to ensure that the hint name is spelled correctly and that the hint is appropriate to the query.

 

For example, the following hint is invalid because first_rows access and parallel access are mutually exclusive. That is because parallel access always assumes a full-table scan and first_rows favors index access.

 

-- An invalid hint

select /*+ first_rows parallel(emp,8)*/

   emp_name

from

   emp

order by

   ename;

 

Some Oracle professionals will place hints together to reinforce their wishes. For example, if there is a SMP server with eight or more CPUs, one may want to use Oracle Parallel Query to speed up legitimate full-table scans.

 

When using parallel query, one should seldom turn on parallelism at the table level, alter table customer parallel 35, because the setting of parallelism for a table influences the optimizer.  This causes the optimizer to see that the full-table scan is inexpensive. Hence, most Oracle professionals specify parallel query on a query-by-query basis, combining the full hint with the parallel hint to ensure a fast parallel full-table scan:

 

-- A valid hint

select /*+ full parallel(emp,35)*/

   emp_name

from

   emp

order by

   ename;

 

Now that the general concept of hints has been introduced, it is an appropriate to look at one of the most important hints for optimizer tuning.

 

The ordered hint determines the driving table for the query execution and also specifies the order that tables are joined together. The ordered hint requests that the tables should be joined in the order that they are specified in the FROM clause, with the first table in the FROM clause specifying the driving table. Using the ordered hint can save a huge amount of parse time and speed SQL execution because the optimizer is given the best order to join the tables.

 

 

The Ion tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.

 

For example, the following query uses the ordered hint to join the tables in their specified order in the FROM clause. In this example, the execution plan is further refined by specifying that the emp to dept join use a hash join and the sal to bonus join uses a nested loop join:

 

select

/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */

from

   emp,

   dept,

   sal,

   bon

where . . .

 

Of course, the ordered hint is most commonly used in data warehouse queries or in SQL that joins more than five tables.

 

SQL execution is dynamic, and tuning a SQL statement for the current data may not be optimal at a future date.  The following section provides a brief tour of the Oracle10g Automated Workload Repository (AWR) and how it can be used to perform proactive SQL tuning.

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter