{"id":1586,"date":"2024-06-25T13:59:31","date_gmt":"2024-06-25T08:29:31","guid":{"rendered":"https:\/\/adilfahim.com\/myblog\/?p=1586"},"modified":"2024-06-25T14:00:57","modified_gmt":"2024-06-25T08:30:57","slug":"oracle-database-19c-enable-automatic-indexing","status":"publish","type":"post","link":"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/","title":{"rendered":"Oracle Database 19c | Enable Automatic Indexing"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69e734a05b23c\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69e734a05b23c\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#What_is_Automatic_Indexing\" >What is Automatic Indexing?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Pros_of_Enabling_Automatic_Indexing\" >Pros of Enabling Automatic Indexing:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Cons_of_Enabling_Automatic_Indexing\" >Cons of Enabling Automatic Indexing:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Additional_Considerations_for_SAP_Databases\" >Additional Considerations for SAP Databases:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Enabling_Automatic_Indexing\" >Enabling Automatic Indexing<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Managing_Automatic_Indexes\" >Managing Automatic Indexes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Viewing_Automatic_Indexes\" >Viewing Automatic Indexes<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Example_Workflow\" >Example Workflow<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/adilfahim.com\/myblog\/oracle-database-19c-enable-automatic-indexing\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"What_is_Automatic_Indexing\"><\/span>What is Automatic Indexing?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/adilfahim.com\/myblog\/wp-content\/uploads\/2024\/06\/Automatic_Indexing.png\" alt=\"Automatic Indexing\" width=\"467\" height=\"436\" class=\"alignnone size-full wp-image-1587\" srcset=\"https:\/\/adilfahim.com\/myblog\/wp-content\/uploads\/2024\/06\/Automatic_Indexing.png 467w, https:\/\/adilfahim.com\/myblog\/wp-content\/uploads\/2024\/06\/Automatic_Indexing-300x280.png 300w\" sizes=\"auto, (max-width: 467px) 100vw, 467px\" \/><\/p>\n<p>Automatic Indexing is a feature introduced in Oracle 19c that automates index management. It analyzes workloads, recommends creating or dropping indexes, and can even implement those recommendations. This can improve query performance without requiring a DBA to manually manage indexes.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Pros_of_Enabling_Automatic_Indexing\"><\/span>Pros of Enabling Automatic Indexing:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Improved Performance: Automatic Indexing can identify and create indexes for frequently used queries, leading to faster execution times.<\/p>\n<p>Reduced DBA workload: DBAs can spend less time manually analyzing queries and creating indexes, freeing them for other tasks.<\/p>\n<p>Simplified Management: Automatic Indexing automates the index lifecycle, reducing the risk of human error and ensuring appropriate indexes are in place.<\/p>\n<p>Adaptive to Workload Changes: The feature can adapt to changing workloads, automatically dropping or rebuilding indexes as needed.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cons_of_Enabling_Automatic_Indexing\"><\/span>Cons of Enabling Automatic Indexing:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Overhead: Automatic Indexing introduces additional processing overhead to analyze workloads and manage indexes. This might be a concern for heavily loaded systems.<\/p>\n<p>Loss of Control: While Automatic Indexing can improve performance, it might not always create the most optimal indexes for specific needs. DBAs lose some control over index creation strategy.<\/p>\n<p>Limited Functionality: Automatic Indexing is currently only available in Oracle 19c Enterprise Edition on Exadata systems. It might not be suitable for all environments.<\/p>\n<p>Monitoring Required: Even with Automatic Indexing enabled, monitoring its activity and the resulting indexes is crucial to ensure they are performing as expected.<\/p>\n<p>Also Read &#8211; <a href=\"https:\/\/adilfahim.com\/myblog\/sap-on-oracle-19-backup-performance\/\" target=\"_blank\" rel=\"noopener\">SAP on Oracle Backup Performance<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Additional_Considerations_for_SAP_Databases\"><\/span>Additional Considerations for SAP Databases:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>SAP Workloads: Automatic Indexing might need some tuning for specific SAP workloads, as SAP queries often have complex access patterns.<\/p>\n<p>Testing: It&#8217;s essential to thoroughly test Automatic Indexing in a non-production SAP environment before deploying it to production systems.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Enabling_Automatic_Indexing\"><\/span>Enabling Automatic Indexing<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To enable Automatic Indexing, you can use the DBMS_AUTO_INDEX package. Here are the steps:<br \/>\n1. Enable Automatic Indexing:<br \/>\n &#8220;`sql<br \/>\n BEGIN<br \/>\n DBMS_AUTO_INDEX.CONFIGURE(&#8216;AUTO_INDEX_MODE&#8217;, &#8216;IMPLEMENT&#8217;);<br \/>\n END;<br \/>\n \/<br \/>\n &#8220;`<br \/>\n The IMPLEMENT mode means that indexes created by Automatic Indexing will be used in SQL statements.<br \/>\n2. Verify Configuration:<br \/>\n You can verify that Automatic Indexing is enabled using the following query:<br \/>\n &#8220;`sql<br \/>\n SELECT PARAMETER_NAME, PARAMETER_VALUE<br \/>\n FROM DBA_AUTO_INDEX_CONFIG<br \/>\n WHERE PARAMETER_NAME = &#8216;AUTO_INDEX_MODE&#8217;;<br \/>\n &#8220;`<br \/>\n### Monitoring Automatic Indexing<br \/>\nYou can monitor the activity of Automatic Indexing using the following views:<br \/>\n1. DBA_AUTO_INDEX_CONFIG:<br \/>\n This view shows the configuration parameters for Automatic Indexing.<br \/>\n &#8220;`sql<br \/>\n SELECT * FROM DBA_AUTO_INDEX_CONFIG;<br \/>\n &#8220;`<br \/>\n2. DBA_AUTO_INDEX_REPORT:<br \/>\n This view provides a report of the Automatic Indexing activity.<br \/>\n &#8220;`sql<br \/>\n SELECT REPORT<br \/>\n FROM DBA_AUTO_INDEX_REPORT<br \/>\n ORDER BY REPORT_ID DESC;<br \/>\n &#8220;`<br \/>\n3. DBA_AUTO_INDEX_TASKS:<br \/>\n This view provides information about the tasks performed by Automatic Indexing.<br \/>\n &#8220;`sql<br \/>\n SELECT * FROM DBA_AUTO_INDEX_TASKS;<br \/>\n &#8220;`<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Managing_Automatic_Indexes\"><\/span>Managing Automatic Indexes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You can manage the indexes created by Automatic Indexing using the DBMS_AUTO_INDEX package. Here are some common tasks:<br \/>\n1. Disable Automatic Indexing:<br \/>\n &#8220;`sql<br \/>\n BEGIN<br \/>\n DBMS_AUTO_INDEX.CONFIGURE(&#8216;AUTO_INDEX_MODE&#8217;, &#8216;OFF&#8217;);<br \/>\n END;<br \/>\n \/<br \/>\n &#8220;`<br \/>\n2. Drop All Automatic Indexes:<br \/>\n &#8220;`sql<br \/>\n BEGIN<br \/>\n DBMS_AUTO_INDEX.DROP_AUTO_INDEXES;<br \/>\n END;<br \/>\n \/<br \/>\n &#8220;`<br \/>\n3. Report on Automatic Indexes:<br \/>\n Generate a detailed report on the indexes created by Automatic Indexing:<br \/>\n &#8220;`sql<br \/>\n SET LONG 1000000<br \/>\n SET PAGESIZE 0<br \/>\n SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY<br \/>\n FROM DUAL;<br \/>\n &#8220;`<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Viewing_Automatic_Indexes\"><\/span>Viewing Automatic Indexes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To view the indexes created by Automatic Indexing, you can use the following query:<br \/>\n&#8220;`sql<br \/>\nSELECT INDEX_NAME, TABLE_NAME, AUTO, STATUS<br \/>\nFROM DBA_INDEXES<br \/>\nWHERE AUTO = &#8216;YES&#8217;;<br \/>\n&#8220;`<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example_Workflow\"><\/span>Example Workflow<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Here&#8217;s an example workflow to enable Automatic Indexing, monitor it, and then generate a report:<br \/>\n1. Enable Automatic Indexing:<br \/>\n &#8220;`sql<br \/>\n BEGIN<br \/>\n DBMS_AUTO_INDEX.CONFIGURE(&#8216;AUTO_INDEX_MODE&#8217;, &#8216;IMPLEMENT&#8217;);<br \/>\n END;<br \/>\n \/<br \/>\n &#8220;`<br \/>\n2. Monitor Automatic Indexing:<br \/>\n &#8220;`sql<br \/>\n SELECT * FROM DBA_AUTO_INDEX_CONFIG;<br \/>\n SELECT * FROM DBA_AUTO_INDEX_TASKS;<br \/>\n &#8220;`<br \/>\n3. Generate Report:<br \/>\n &#8220;`sql<br \/>\n SET LONG 1000000<br \/>\n SET PAGESIZE 0<br \/>\n SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY<br \/>\n FROM DUAL;<br \/>\n &#8220;`<br \/>\n4. View Automatic Indexes:<br \/>\n &#8220;`sql<br \/>\n SELECT INDEX_NAME, TABLE_NAME, AUTO, STATUS<br \/>\n FROM DBA_INDEXES<br \/>\n WHERE AUTO = &#8216;YES&#8217;;<\/p>\n<p>Read <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/arpls\/DBMS_AUTO_INDEX.html\" target=\"_blank\" rel=\"noopener\">Oracle Documentation<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Automatic Indexing can be a valuable tool for improving performance and simplifying index management in Oracle 19c for SAP databases. However, it&#8217;s important to weigh the pros and cons and consider the specific needs of your SAP environment before enabling it. Remember, monitoring and potentially fine-tuning the feature are essential for optimal results.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>What is Automatic Indexing? Automatic Indexing is a feature introduced in Oracle 19c that automates index management. It analyzes workloads, recommends creating or dropping indexes, and can even implement those recommendations. This can improve query performance without requiring a DBA to manually manage indexes. Pros of Enabling Automatic Indexing: Improved Performance: Automatic Indexing can identify [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"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,"footnotes":""},"categories":[8],"tags":[1104,1103,1105,1106],"class_list":["post-1586","post","type-post","status-publish","format-standard","hentry","category-sap-updates","tag-automatic-indexing","tag-oracle-19c","tag-sap-database","tag-sap-on-oracle"],"_links":{"self":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts\/1586","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/comments?post=1586"}],"version-history":[{"count":3,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts\/1586\/revisions"}],"predecessor-version":[{"id":1590,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts\/1586\/revisions\/1590"}],"wp:attachment":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/media?parent=1586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/categories?post=1586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/tags?post=1586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}