{"id":11606,"date":"2019-10-25T12:15:12","date_gmt":"2019-10-25T12:15:12","guid":{"rendered":"https:\/\/ittutorial.org\/?p=11606"},"modified":"2020-11-13T12:22:28","modified_gmt":"2020-11-13T12:22:28","slug":"sql-tuning-tips-and-tricks-tutorial-oracle-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/","title":{"rendered":"SQL Tuning Tips and Tricks Tutorial in Oracle -2"},"content":{"rendered":"<p>I will continue to explain\u00a0Oracle SQL Tuning Tips and Tricks in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-11609 size-large\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-1024x460.jpg\" alt=\"\" width=\"618\" height=\"278\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-1024x460.jpg 1024w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-300x135.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-768x345.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial.jpg 1227w\" sizes=\"auto, (max-width: 618px) 100vw, 618px\" \/><\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"color: #ff0000;\">Oracle SQL Tuning Tips and Tricks<\/span><\/h2>\n<p>&nbsp;<\/p>\n<p>Read first article before this.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"Cez0uOGS01\"><p><a href=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-1\/\">SQL Tuning Tips and Tricks Tutorial in Oracle -1<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Tuning Tips and Tricks Tutorial in Oracle -1&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-1\/embed\/#?secret=EKgHZuFsfD#?secret=Cez0uOGS01\" data-secret=\"Cez0uOGS01\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #ff0000;\">SQL Tuning Tips and Tricks<\/span><\/h3>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>11- Avoid Function usage like Substr<\/strong><\/span><\/p>\n<p>Try to avoid using too many functions, especially don&#8217;t\u00a0 use SUBSTRING, instead use LIKE clause.<\/p>\n<p>Difference between <strong>substr<\/strong> and <strong>like<\/strong> is Such as &#8220;where name like &#8216;DEV%&#8221;;&#8221; will use an index whereas a substr such as &#8220;where substr(name,1,2) = &#8216;Dev&#8217;; will require a function-based index (and extended optimizer statistics) for optimal performance.<\/p>\n<p>Using the substr clause is best for large production queries, when you provide a function-based index and deploy extended optimizer statistics on the target column.<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>12- Use Truncate instead of Delete<\/strong><\/span><\/p>\n<p>You should prefer <strong>truncate<\/strong> table instead of <strong>delete<\/strong> table if you delete too many rows on a table. Too many delete operations cause fragmentation and performance problems.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11611\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/truncate-vs-delete.jpg\" alt=\"\" width=\"1280\" height=\"720\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/truncate-vs-delete.jpg 1280w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/truncate-vs-delete-300x169.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/truncate-vs-delete-768x432.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/truncate-vs-delete-1024x576.jpg 1024w\" sizes=\"auto, (max-width: 1280px) 100vw, 1280px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>13- Avoid Using Triggers<\/strong><\/span><\/p>\n<p>You should avoid using of TRIGGERS. Just use it as a last resort. It is better to use CONSTRAINT and STORED PROCEDURE to maintain the integrity of your databases.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11612\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/avoid-triggers.png\" alt=\"\" width=\"300\" height=\"287\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>14- Use Bulk DML instead of Single DML<\/strong><\/span><\/p>\n<p>You should use Bulk Insert,Delete and Update instead of Single Insert,Delete and Update if you are executing Bulk operation.<\/p>\n<p>Bulk insert,Delete and Update will give your system a huge performance boost.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11613\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance.jpg\" alt=\"\" width=\"482\" height=\"326\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance.jpg 482w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance-300x203.jpg 300w\" sizes=\"auto, (max-width: 482px) 100vw, 482px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>15-\u00a0Avoid unnecessary columns<\/strong><\/span><\/p>\n<p>Unnecessary columns in Select clause incur more I\/O on the database\u00a0and increase network traffic.<\/p>\n<p>You should analyze all columns in select or DML clause and if it is unnecessary, you should remove that columns in queries. Mostly\u00a0Using SELECT * is not true for database and related query performance.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11614\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/unnecessary-columns.jpg\" alt=\"\" width=\"690\" height=\"380\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/unnecessary-columns.jpg 690w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/unnecessary-columns-300x165.jpg 300w\" sizes=\"auto, (max-width: 690px) 100vw, 690px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>16- Avoid Distinct clause<\/strong><\/span><\/p>\n<p>DISTINCT operator causes sorting which slows down the SQL execution time.<\/p>\n<p>If it is possible, it is better that remove Distinct clause in Select.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11615\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Distinct-Value.png\" alt=\"\" width=\"900\" height=\"450\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Distinct-Value.png 900w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Distinct-Value-300x150.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Distinct-Value-768x384.png 768w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>17-\u00a0Use MINUS instead of EXISTS<\/strong><\/span><\/p>\n<p><strong>Minus<\/strong> operator is mostly running in a faster execution plan than\u00a0<span id=\"t1d_10\" class=\"t s3_10 f6\"><strong>NOT IN<\/strong> and <strong>NOT Exists<\/strong> operator.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11619\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/minus-union-union-all-intersect.jpg\" alt=\"\" width=\"845\" height=\"532\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/minus-union-union-all-intersect.jpg 845w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/minus-union-union-all-intersect-300x189.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/minus-union-union-all-intersect-768x484.jpg 768w\" sizes=\"auto, (max-width: 845px) 100vw, 845px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>18- Use Alias for table column<\/strong><\/span><\/p>\n<p>If you use more than one table in a query, you should use table aliases for column\u00a0to avoid confusion.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>19- Use Parallel hint for\u00a0large data access<\/strong><\/span><\/p>\n<p>If you execute any SQL which is\u00a0accessing large data sets, then you may use Parallel hint to perform it in parallel.<\/p>\n<p>When you use parallel hint, you should check operating system CPU counts and cores to specify how many parallel will you use it.<\/p>\n<p>If lots of people use parallel hint everytime, operating system and database can\u00a0 <span style=\"color: #ff0000;\"><strong>slow down or power off<\/strong> <\/span>because of CPU bootleneck.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11616\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/parallel-oracle.gif\" alt=\"\" width=\"688\" height=\"193\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>20- Use COUNT(1) instead of COUNT(*)<\/strong><\/span><\/p>\n<p>Using COUNT(1) instead of COUNT(*) is recommended\u00a0 for SQL query performance optimization<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>I will continue to explain tips and tricks of SQL Tuning in the next article.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><a href=\"https:\/\/ittutorial.org\/performance-tuning-and-sql-tuning-tutorial-in-the-oracle-database\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"color: #ff0000;\">Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.<\/span><\/a><\/h5>\n","protected":false},"excerpt":{"rendered":"<p>I will continue to explain\u00a0Oracle SQL Tuning Tips and Tricks in this article.<\/p>\n","protected":false},"author":1,"featured_media":11609,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,5085,6603],"tags":[6564,6580,6570,6572,6568,6567,6576,6566,6560,6562,6585,6549,6571,6561,6546,6593,6545,6563,6574,6875,6573,6569,10533,10535,6577,6579,6565,6550,6547,6534,6555,6554,6541,6533,6536,6535,6538,6532,6537,6594,6551,6590,6591,6876,10536,6588,6589,10534,6548,6557,6553,6539,6543,6542,6556,6586,6544,6583,6592,6874,6601,6602,6595,6584,6596,6587,6599,6582,6600,6581,6598,4530,6597,6575,6578,6540],"class_list":["post-11606","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-performance-tuning","category-sql-tuning","tag-alter-system-set-cursor_sharing-12c","tag-bind-variable-in-java","tag-bind-variables-in-oracle-dynamic-sql","tag-bind-variables-in-oracle-forms","tag-bind-variables-in-oracle-pl-sql-examples","tag-cursor-in-oracle","tag-cursor-sharing-and-bind-variable-usage","tag-cursor-sharing-force-oracle","tag-cursor_sharing-explained","tag-cursor_sharing-performance-tuning","tag-database-optimization-techniques-sql-server","tag-how-to-create-index-in-oracle-to-improve-performance","tag-how-to-declare-bind-variable-in-oracle-sql-developer","tag-how-to-enable-adaptive-cursor-sharing-12c","tag-how-to-improve-database-performance-in-oracle-11g","tag-how-to-make-select-query-faster-in-oracle","tag-how-to-optimize-sql-query-performance-in-oracle","tag-how-to-reduce-hard-parsing-in-oracle-12c","tag-how-to-remove-bind-variable-in-oracle","tag-how-to-tune-the-query-in-oracle-using-explain-plan","tag-how-to-use-bind-variables-in-oracle-reports","tag-how-to-use-bind-variables-in-oracle-stored-procedure","tag-online-oracle-sql-query-performance-tuning","tag-oracle-12c-performance-tuning-tips","tag-oracle-bind-variables-view","tag-oracle-cursor-bind-variable-example","tag-oracle-cursor_sharing","tag-oracle-database-optimization-techniques","tag-oracle-improve-query-performance","tag-oracle-performance-tuning-12c","tag-oracle-performance-tuning-18c","tag-oracle-performance-tuning-19c","tag-oracle-performance-tuning-basics","tag-oracle-performance-tuning-blogs","tag-oracle-performance-tuning-interview-questions","tag-oracle-performance-tuning-pdf","tag-oracle-performance-tuning-scenarios","tag-oracle-performance-tuning-tips","tag-oracle-performance-tuning-tutorial","tag-oracle-pl-sql-performance-tuning-tips-techniques-pdf","tag-oracle-query-large-table-performance","tag-oracle-sql-performance-tuning","tag-oracle-sql-tricks","tag-oracle-sql-tuning-book","tag-oracle-tips","tag-performance-tuning-in-database","tag-performance-tuning-in-oracle","tag-performance-tuning-in-oracle-11g","tag-performance-tuning-in-oracle-12c-with-examples","tag-performance-tuning-in-oracle-19c","tag-performance-tuning-in-oracle-19c-with-examples","tag-performance-tuning-in-oracle-for-beginners","tag-performance-tuning-in-oracle-for-developer","tag-performance-tuning-in-oracle-interview-questions","tag-performance-tuning-in-oracle-tutorial","tag-performance-tuning-in-sql-server-2012-step-by-step","tag-pl-sql-performance-tuning","tag-query-optimization-in-sql-server-with-example","tag-query-optimization-techniques-in-oracle","tag-query-tuning-in-oracle-interview-questions","tag-sql-code-performance","tag-sql-performance-query","tag-sql-performance-tuning","tag-sql-performance-tuning-tutorial","tag-sql-query-optimization-techniques","tag-sql-query-optimization-tool","tag-sql-query-performance-tuning","tag-sql-query-performance-tuning-tips","tag-sql-select-performance","tag-sql-server-query-optimization-tips","tag-sql-server-query-optimizer","tag-sql-tuning-advisor","tag-sql-view-performance-optimization","tag-substitution-variable-in-pl-sql-example","tag-what-is-bind-variable-in-oracle-with-examples","tag-what-is-performance-tuning-in-oracle-with-examples"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=11606"}],"version-history":[{"count":9,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11606\/revisions"}],"predecessor-version":[{"id":32293,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11606\/revisions\/32293"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/11609"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=11606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=11606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=11606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}