{"id":15534,"date":"2020-05-25T04:25:37","date_gmt":"2020-05-25T04:25:37","guid":{"rendered":"https:\/\/dbtut.com\/?p=15534"},"modified":"2020-05-25T11:27:29","modified_gmt":"2020-05-25T11:27:29","slug":"performance-tuning-tips-and-techniques","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/","title":{"rendered":"PERFORMANCE TUNING TIPS AND TECHNIQUES"},"content":{"rendered":"

Below are some of the basic hygiene techniques and concepts we should keep in mind to get the maximum performance out of our query .<\/p>\n

    \n
  1. We should always add adequate predicates to our query. We should always use index columns in our query. And also try to use the index columns in our predicates.<\/li>\n
  2. If possible, select only the index columns in the SQL query.<\/li>\n
  3. We should always keep in mind the selectivity of our query. If the selectivity of a table is more than 5%, then there is no certainty if the optimizer would choose the index or not.<\/li>\n
  4. We should never perform or use arithmetic operations on the indexed columns.<\/li>\n
  5. We should avoid using the like character in the beginning\u00a0 like \u2018%on\u2019. Instead we should use the reverse index. Eg where reverse(last_name) like \u2018on%\u2019.<\/li>\n
  6. Do not use functions on the indexed columns instead we should always use a query .<\/li>\n
  7. B-Tree index do not use indexed the null values and this sometimes the suppress the index usage in our queries. To avoid this we can use IS NOT NULL in where clause .Its recommended to use BITMAP Index instead of B-Tree index.<\/li>\n
  8. We should use Truncate instead of Delete (Truncate does not generate redo). Truncate makes unusable index usable again.<\/li>\n
  9. We should always check the datatypes of the columns and compare values because if they don\u2019t match the optimizer my skip the index usage.<\/li>\n
  10. We should avoid using the having by clause because it only restricts or eliminates the rows after they are read from the memory or disc.<\/li>\n
  11. If you don\u2019t need to use all the table in a view, we should avoid using this view in our queries.<\/li>\n
  12. We should always use materialized views, partition table .<\/li>\n
  13. We should create Partition table for queries having low selectivity.<\/li>\n
  14. Its recommended to use bulk collect as it reduces the context switching. Implicit cursor use bulk collect by default by fetching first 100 rows .<\/li>\n
  15. Using Bind variables: Bind variables are used when we execute the same query again and again but only the predicate value change. It increase the performance by decreasing the parse count .<\/li>\n
  16. Bind Variable Peeking : Bind variable peeking is a process when the optimizer waits\/peeks for the first variable before deciding on the execution plan . It will generate the plan only after getting the first bind variable.<\/li>\n
  17. We should use bind variable only when the cardinality\u00a0 of the values in the column is similar .<\/li>\n
  18. We should never use the bind variable if the selectivity of each variable is pretty different .<\/li>\n
  19. After 12c adaptive cursor sharing is used by default.<\/li>\n
  20. Adaptive cursor sharing automatically detects is the query needs another execution plan or can use the existing plan by using bind aware and bind sensitive cursor .<\/li>\n<\/ol>\n
    <\/div>\n

    <\/path><\/svg><\/i> \"Loading\"<\/p>\n

    <\/div>\n","protected":false},"excerpt":{"rendered":"

    Below are some of the basic hygiene techniques and concepts we should keep in mind to get the maximum performance out of our query . We should always add adequate predicates to our query. We should always use index columns in our query. And also try to use the index columns in our predicates. If …<\/p>\n

    <\/div>\n

    <\/path><\/svg><\/i> \"Loading\"<\/p>\n

    <\/div>\n","protected":false},"author":481,"featured_media":15548,"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,"footnotes":""},"categories":[4,1579],"tags":[4339,1498,9597,251,9598,9596],"class_list":["post-15534","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-rdbms","tag-advanced-tuning-options","tag-oracle","tag-performance","tag-performance-tuning","tag-techniques","tag-tips"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":512,"today_views":0},"yoast_head":"\nPERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials<\/title>\n<meta name=\"description\" content=\"PERFORMANCE TUNING TIPS AND TECHNIQUES\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"PERFORMANCE TUNING TIPS AND TECHNIQUES\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-25T04:25:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-05-25T11:27:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png\" \/>\n\t<meta property=\"og:image:width\" content=\"851\" \/>\n\t<meta property=\"og:image:height\" content=\"522\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Amit Vashishth\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Amit Vashishth\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\"},\"author\":{\"name\":\"Amit Vashishth\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315\"},\"headline\":\"PERFORMANCE TUNING TIPS AND TECHNIQUES\",\"datePublished\":\"2020-05-25T04:25:37+00:00\",\"dateModified\":\"2020-05-25T11:27:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\"},\"wordCount\":464,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png\",\"keywords\":[\"advanced tuning options\",\"oracle\",\"performance\",\"performance tuning\",\"techniques\",\"tips\"],\"articleSection\":[\"ORACLE\",\"RDBMS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\",\"name\":\"PERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png\",\"datePublished\":\"2020-05-25T04:25:37+00:00\",\"dateModified\":\"2020-05-25T11:27:29+00:00\",\"description\":\"PERFORMANCE TUNING TIPS AND TECHNIQUES\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png\",\"width\":851,\"height\":522},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PERFORMANCE TUNING TIPS AND TECHNIQUES\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315\",\"name\":\"Amit Vashishth\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g\",\"caption\":\"Amit Vashishth\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/amitvashishth\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials","description":"PERFORMANCE TUNING TIPS AND TECHNIQUES","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/","og_locale":"en_US","og_type":"article","og_title":"PERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials","og_description":"PERFORMANCE TUNING TIPS AND TECHNIQUES","og_url":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/","og_site_name":"Database Tutorials","article_published_time":"2020-05-25T04:25:37+00:00","article_modified_time":"2020-05-25T11:27:29+00:00","og_image":[{"width":851,"height":522,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png","type":"image\/png"}],"author":"Amit Vashishth","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Amit Vashishth","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/"},"author":{"name":"Amit Vashishth","@id":"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315"},"headline":"PERFORMANCE TUNING TIPS AND TECHNIQUES","datePublished":"2020-05-25T04:25:37+00:00","dateModified":"2020-05-25T11:27:29+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/"},"wordCount":464,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png","keywords":["advanced tuning options","oracle","performance","performance tuning","techniques","tips"],"articleSection":["ORACLE","RDBMS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/","url":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/","name":"PERFORMANCE TUNING TIPS AND TECHNIQUES - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png","datePublished":"2020-05-25T04:25:37+00:00","dateModified":"2020-05-25T11:27:29+00:00","description":"PERFORMANCE TUNING TIPS AND TECHNIQUES","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2020\/05\/Ads\u0131z-5.png","width":851,"height":522},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2020\/05\/25\/performance-tuning-tips-and-techniques\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"PERFORMANCE TUNING TIPS AND TECHNIQUES"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/498d05a2429aa44b0ee5e0707bfb8315","name":"Amit Vashishth","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1151b8ba4366b28f37b7864d09bc41e952f55c1e541e29bc6b499dc55f8a502d?s=96&d=mm&r=g","caption":"Amit Vashishth"},"url":"https:\/\/dbtut.com\/index.php\/author\/amitvashishth\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/15534","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/481"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=15534"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/15534\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/15548"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=15534"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=15534"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=15534"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}