Articles Comments

Oracle DBA & All IT » Database, DBA, Oracle, Oracle Tips, Tuning Performance » Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning)

Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning)

หายไปนานคิดว่าจะเอาเรื่องอะไรมาเขียนดีน๊า… แล้วก็คิดว่าวันนี้เรามาเล่นใบ้คำกับ oracle ดีกว่า ดูซิว่า Database อันชาญฉลาดอย่าง Oracle จะรู้จักคำใบ้ที่เราจัดให้หรือเปล่า หุหุ..
 

วันเลยเลยเสนอตอนที่มีชื่อว่า "Hint คำใบ้ช่วย Tuning Performance" เรื่องที่จะเสนอต่อไปนี้คงช่วยในการ tune SQL และ PL/SQL ได้ไม่มากก็น้อย แต่คงจะไม่ลงลึก เพียงแต่มาเล่าให้ฟังว่าคืออะไร ส่วนจะใช้กันแบบไหนก็คงจะต้องเอาไปปรับกันเอาเองตามอัธยาศัย
 

การใช้ Hint ใน SQL หรือ PL/SQL นั้นคือก็เหมือนกับการบอกใบ้ให้ Oracle รู้ว่าเราต้องการอะไร Oracle ก็จะจัดการให้ตามที่เราต้องการ

ซึ่ง Hint นั้นจะอยู่ภายใต้เครื่องหมาย /*+ HINT */

SELECT      /*+ hint –or– text */    statement body 

ข้อควรระวัง ซึ่งถ้าเราเขียนไม่ถูกต้อง Oracle จะมองเห็นเป็นเพียงแค่ Comment ธรรมดาเท่านั้น

ตัวอย่างการใช้งาน

การบังคับให้ Oracle อ่าน Full Table Scan ไม่ต้องใช้ Index

SELECT /*+ FULL(x) */ FROM tab1 x WHERE col1 = 10;

หรือถ้าเราต้องการบังคับให้ใช้ Index ตัวที่เราต้องการก็ตามนี้เลย

SELECT /*+ INDEX(x,emp_idx1) */ ... FROM scott.emp x...

หรือเราไม่ต้องการให้ใช้ Index ตัวไหนก็สั่งได้เช่นกัน Oracle ก็จะไปใช้ตัวอื่นที่เราไม่ได้กำหนดว่า NO_INDEX ไว้แทน

SELECT /*+ NO_INDEX(x emp_idx1) */ … FROM scott.emp x…

แถมตัวอย่างให้อีกตัวที่ผู้เขียนใช้บ่อย คือการแตก Process ให้ช่วยกันทำงาน เช่นตัวอย่างด้านล่างเป็นการแตกออกมาช่วยกัน 2 Process พร้อมๆกัน แต่ต้องระวังไม่ให้มากไปจะไปดึง Performance ของคนอื่นเค้าได้

SELECT /*+ PARALLEL(x,2) */ FROM scott.emp x ….

ข้อควรระวัง
PARALLEL(x,2) ที่ใช้ใน Hint ทั้งหลายนั้นคือ Alias ที่เรากำหนดให้กับ Table ที่ชื่อ emp ถ้าเรากำหนดเป็น a ค่าใน () ก็จะต้องเป็น PARALLEL(a,2) หรือถ้าไม่มีการประกาศ Alias ก็ให้ใส่ชื่อ Table เต็มๆเข้าไปเลย PARALLEL(emp,2) ถ้ามีการอ้างอิงไม่ถูกต้อง Hint นั้นก็จะไร้ความหมายจากคำใบ้ ก็จะเป็นแค่ Comment ที่เราใส่ไว้เท่านั้นเอง

HINT ของ Oracle มีเยอะมากด้านล่างนี้จะเป็นแค่บางส่วน และด้านล่างของตารางจะแสดง Hint ที่ใน Document ของ Oracle ไม่ค่อยจะเอามาแสดงให้เราเห็น
 

Oracle Hint

Meaning

+

Must be immediately after comment indicator, tells Oracle this is a list of hints.

ALL_ROWS

Use the cost based approach for best throughput.

CHOOSE

Default, if statistics are available will use cost, if not, rule.

FIRST_ROWS

Use the cost based approach for best response time.

RULE

Use rules based approach; this cancels any other hints specified for this statement.

Access Method Oracle Hints:

 

CLUSTER(table)

This tells Oracle to do a cluster scan to access the table.

FULL(table)

This tells the optimizer to do a full scan of the specified table.

HASH(table)

Tells Oracle to explicitly choose the hash access method for the table.

HASH_AJ(table)

Transforms a NOT IN subquery to a hash anti-join.

ROWID(table)

Forces a rowid scan of the specified table.

INDEX(table [index])

Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.

INDEX_ASC (table [index])

Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.

INDEX_DESC(table [index])

Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.

INDEX_COMBINE(table index)

Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.

INDEX_FFS(table index)

Perform a fast full index scan rather than a table scan.

MERGE_AJ (table)

Transforms a NOT IN subquery into a merge anti-join.

AND_EQUAL(table index index [index index index])

This hint causes a merge on several single column indexes. Two must be specified, five can be.

NL_AJ

Transforms a NOT IN subquery into a NL anti-join (nested loop).

HASH_SJ(t1, t2)

Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

MERGE_SJ (t1, t2)

Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

NL_SJ

Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

Oracle Hints for join orders and transformations:

 

ORDERED

This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

STAR

Forces the largest table to be joined last using a nested loops join on the index.

STAR_TRANSFORMATION

Makes the optimizer use the best plan in which a start transformation is used.

FACT(table)

When performing a star transformation use the specified table as a fact table.

NO_FACT(table)

When performing a star transformation do not use the specified table as a fact table.

PUSH_SUBQ

This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.

REWRITE(mview)

If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.

NOREWRITE

Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't come from a materialized view.

USE_CONCAT

Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.

NO_MERGE (table)

This causes Oracle to join each specified table with another row source without a sort-merge join.

NO_EXPAND

 Prevents OR and IN processing expansion.

Oracle Hints for Join Operations:

 

USE_HASH (table)
 

This causes Oracle to join each specified table with another row source with a hash join.

USE_NL(table)

This operation forces a nested loop using the specified table as the controlling table.

USE_MERGE(table,[table, – ])

This operation forces a sort-merge-join operation of the specified tables.

DRIVING_SITE

The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

LEADING(table)

The hint causes Oracle to use the specified table as the first table in the join order.

Oracle Hints for Parallel Operations:

 

[NO]APPEND

This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.

NOPARALLEL (table

This specifies the operation is not to be done in parallel.

PARALLEL(table, instances)

This specifies the operation is to be done in parallel.

PARALLEL_INDEX

Allows parallelization of a fast full index scan on any index.

Other Oracle Hints:

 

CACHE

Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.

NOCACHE

Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

[NO]APPEND

For insert operations will append (or not append) data at the HWM of table.

UNNEST

Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.

NO_UNNEST

Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.

PUSH_PRED

 Pushes the join predicate into the view.

 

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

 

ลองนำไปใช้ดูน่าจะมีประโยชน์ไม่มากก็น้อย ไม่ใช่ว่าการใช้ Hint จะทำให้ Performance ดีเสมอไป ทั้งนี้ทั้งนั้นขึ้นอยู่กับการเลือกใช้ถ้าเลือกผิดคิดผิด SQL นั้นก็อาจจะช้ายิ่งกว่าเดิมก็ได้

 

.. Preview : 23223

Related Search:

  • คำใบ้
  • index oracle คือ

Written by

บอกเล่าสิ่งที่พบเจอมาในการทำงาน ประสบการณ์การทำงานด้าน DBA ถ่ายทอดกันด้วยภาษาง่ายๆ บ้านๆ " ทุกอย่างไม่อยาก แต่... แค่ตั้งใจไม่พอ ต้องลงมือทำ และทำ GoodLuck " Fanpage: www.facebook.com/DBAor .. "Oracle Database Consultant " ..

Filed under: Database, DBA, Oracle, Oracle Tips, Tuning Performance · Tags: , , , , , , , , , , , , ,

  • Excellent content, I am viewing back often to discover refreshes.

  • Really like the modern look. I really liked this content. Bless you for a good entry.

    • A rolling stone is worth two in the bush, tnkhas to this article.

  • It’s interesting to find how challenging the content side is for some

    • Thouhgt it wouldn’t to give it a shot. I was right.

    • It was dark when I woke. This is a ray of sshunine.

  • lengthy and in depth article but full of useful information

  • Thank you very much for that astonishing article

  • radio manele fm

    It’s interesting to find how challenging the content side is for some

  • I hope this was a very interesting post thanks for writing it

    • A mliilon thanks for posting this information.

    • Gosh, I wish I would have had that ifnromation earlier!

  • really loved the article added to my favourites

  • he used the same technique in the end and it worked

  • GREAT REVIEW! I pretty much agree with pretty much all you said in your post, especially at the beggining of your article. Thank you, this info is very useful as always. Plase check my free ipad 2 blog, hope you will find interesting information about free ipad 2 there! Keep up the good work! You’ve got +1 more reader of your super blog:) Isabella S.

  • Do you have a Facebook page or Twitter? Would love to follow you there, I’m on my iPhone and love reading your stuff!

    • Not bad at all fellas and galals. Thanks.

  • Maki…as always, great post here.

  • 5 star article brilliant. I am new to blogging and you used a langauge I can understand

  • GREAT REVIEW! I agree with pretty much all you said in your article, especially at the beggining of your article. Thank you, your post is very useful as always. Keep up the good work! You’ve got +1 more reader of your web blog:) Isabella S.

  • Thank you very much for that big article

    • Ah yes, nicely put, erevyone.

    • So true. Honesty and everything recognzied.

  • Great review! You actually touched some curious things here. I came across it by using Yahoo and I’ve got to admit that I already subscribed to the RSS feed, will be following you on my iphone 🙂

  • I agree with your Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle DBA & All IT, superb post.

  • Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle DBA & All IT Great goods from you, man. I’ve understand your stuff previous to and you’re just too fantastic. I actually like what you’ve acquired here, really like what you are stating and the way in which you say it. You make it entertaining and you still care for to keep it sensible. I can’t wait to read far more Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle DBA & All IT again from you. Thanks For Share .

  • Hey blogger, nice work with the choice of theme on this blog. It looks reall nice.

  • Knowledge wants to be free, just like these atircles!

  • Thank you for writing out this post, I like the rest of your blog as well.

  • I agree with your Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle DBA & All IT, superb post.

  • I agree with your Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle DBA & All IT, excellent post.

  • Pingback: Hint คำใบ้ช่วย Tuning Performance (Oracle SQL Hints Tuning) | Oracle in Thai | Oracle in Thai | Oracle User Group in Thailand |()