{"id":14848,"date":"2020-10-15T13:37:54","date_gmt":"2020-10-15T11:37:54","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/"},"modified":"2020-10-15T13:37:54","modified_gmt":"2020-10-15T11:37:54","slug":"upgrade-to-oracle-19c-performance-issue","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/","title":{"rendered":"Upgrade to Oracle 19c &#8211; performance issue"},"content":{"rendered":"<p>In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked just fine. While most of them finished in less than one hour, we run into one which would have taken days to complete.<\/p>\n<p><u>Issue<\/u><\/p>\n<p>After starting the database upgrade from Oracle 12.2.0.1.0 to Production Version 19.8.0.0.0 the upgrade locked up during compiling:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">@utlrp<\/pre>\n<p>&nbsp;<\/p>\n<p><u>Reason<\/u><\/p>\n<p>One select-statement on the unified_audit_trail was running for hours with no result, blocking the upgrade progress and consuming nearly all database resources. The size of the audit_trail itself was about 35MB, so not the size you would expect such a bottleneck from:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; SELECT count(*) from gv$unified_audit_trail;<\/pre>\n<p>&nbsp;<\/p>\n<p><u>Solution<\/u><\/p>\n<p>After some research and testing (see notes below) I found the following workaround (after killing the upgrade process):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; begin\nDBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(\naudit_trail_type =&gt; DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,\nuse_last_arch_timestamp =&gt; FALSE);\nend;\n\/\nSQL&gt; set timing on;\nSELECT count(*) from gv$unified_audit_trail;\nexec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;\n<\/pre>\n<p>&nbsp;<\/p>\n<p><u>Note<\/u><\/p>\n<p>As a first attempt I used the procedure below, described in Note 2212196.1.<\/p>\n<p>But flush_unified_audit_trail lasted too long, so I killed the process after it ran for one hour. The flash procedure again worked fine after using clean_audit_trail as described above:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; begin\nDBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;\nfor i in 1..10 loop\nDBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;\nend loop;\nend;\n\/<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>A few days later we encountered the same issue on an Oracle 12.1.0.2 database which requires <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/patch\/PatchDetail.jspx?parent=DOCUMENT&amp;sourceId=2212196.1&amp;patchId=25985768\">Patch 25985768<\/a> for executing dbms_audit_mgmt.transfer_unified_audit_records.<\/p>\n<p>This procedure is available out of the box in the Oracle 12.2 database and in the Oracle 12.1.0.2 databases which have been patched with <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/ui\/patch\/PatchDetail.jspx?parent=DOCUMENT&amp;sourceId=2212196.1&amp;patchId=25985768\">Patch 25985768<\/a>.<\/p>\n<p>To avoid to get caught in this trap it is my advise that you gather all relevant statistics before any upgrade from Oracle 12c to 19c and to query gv$unified_audit_trail in advance. This query usually finishes within a few seconds.<\/p>\n<p>&nbsp;<\/p>\n<p><u>Related documents<\/u><\/p>\n<p>Doc ID 2212196.1<\/p>\n<p>https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?_afrLoop=257639407234852&#038;id=2212196.1&#038;_afrWindowMode=0&#038;_adf.ctrl-state=rd4zvw12p_4<\/p>\n<p>Master Note For Database Unified Auditing (Doc ID 2351084.1)<\/p>\n<p>Bug 18920838 : 12C POOR QUERY PERFORMANCE ON DICTIONARY TABLE SYS.X$UNIFIED_AUDIT_TRAIL<\/p>\n<p>Bug 21119008 : POOR QUERY PERFORMANCE ON UNIFIED_AUDIT_TRAIL<\/p>\n<p>Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database (Doc ID 2063340.1)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[656,96,209,67,223,44,219],"type_dbi":[],"class_list":["post-14848","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-12-2","tag-oracle","tag-oracle-12c","tag-performance","tag-pluggable-databases","tag-troubleshooting","tag-upgrade"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Upgrade to Oracle 19c - performance issue - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Upgrade to Oracle 19c - performance issue\" \/>\n<meta property=\"og:description\" content=\"In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-15T11:37:54+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\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:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Upgrade to Oracle 19c &#8211; performance issue\",\"datePublished\":\"2020-10-15T11:37:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/\"},\"wordCount\":375,\"commentCount\":0,\"keywords\":[\"12.2\",\"Oracle\",\"Oracle 12c\",\"Performance\",\"Pluggable Databases\",\"Troubleshooting\",\"Upgrade\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/\",\"name\":\"Upgrade to Oracle 19c - performance issue - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-10-15T11:37:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/upgrade-to-oracle-19c-performance-issue\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Upgrade to Oracle 19c &#8211; performance issue\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Upgrade to Oracle 19c - performance issue - dbi Blog","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:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/","og_locale":"en_US","og_type":"article","og_title":"Upgrade to Oracle 19c - performance issue","og_description":"In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/","og_site_name":"dbi Blog","article_published_time":"2020-10-15T11:37:54+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Upgrade to Oracle 19c &#8211; performance issue","datePublished":"2020-10-15T11:37:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/"},"wordCount":375,"commentCount":0,"keywords":["12.2","Oracle","Oracle 12c","Performance","Pluggable Databases","Troubleshooting","Upgrade"],"articleSection":["Database Administration &amp; Monitoring","Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/","url":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/","name":"Upgrade to Oracle 19c - performance issue - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-10-15T11:37:54+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/upgrade-to-oracle-19c-performance-issue\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Upgrade to Oracle 19c &#8211; performance issue"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14848","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=14848"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/14848\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=14848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=14848"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=14848"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=14848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}