{"id":524,"date":"2008-06-20T10:50:21","date_gmt":"2008-06-20T14:50:21","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/"},"modified":"2008-06-20T10:50:21","modified_gmt":"2008-06-20T14:50:21","slug":"troubleshootingtuning-slow-form-in-apps-11i","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/","title":{"rendered":"Troubleshooting\/Tuning Slow Form in Apps 11i"},"content":{"rendered":"

User reported that one form was taking too much time to save\u00a0record and after sometime form was hanging (records were not saved).<\/p>\n

My initial question to user\u00a0was<\/p>\n

1) Is this happening for all forms or just one form <\/strong>?
\n2) Is this issue happening for all users or a particular user<\/strong> ?<\/p>\n

and answer was, issue effecting all users but limited to a specific form.<\/p>\n

I could easily rule out network issue<\/strong> as problem was specific to particular form and for all users hence it was time to generate trace on particular form<\/strong>.<\/p>\n

Navigate to form right before user was experiencing problem and\u00a0enabled trace\u00a0from front end\u00a0( Help<\/strong>–>Debug<\/strong>–> Trace<\/strong>–> Trace with bind & wait<\/strong>) ;<\/p>\n

<\/a>\"Trace<\/a>\u00a0<\/p>\n

If you don’t see option Debug <\/strong>under Help <\/strong>then check “Profile Option<\/strong>” “Hide Diagnostics menu entry<\/strong>” and ensure value is set to “No<\/strong>”\u00a0<\/p>\n

After enabling trace,\u00a0carry out\u00a0action\u00a0and this should generate trace file<\/p>\n

Location of Trace File<\/u><\/strong>\u00a0\u00a0Trace file should be in udump <\/strong>directory of database. To know location of udump directory login to database as sys or system run query “SQL> show parameter user_dump_dest<\/strong>” This will give you location of user dump directory to find trace file generated .<\/p>\n

<\/u><\/strong><\/p>\n

Generate TKPROF<\/u><\/strong>Next step was to convert raw trace file into tkprof<\/strong> version.<\/p>\n

tkprof<\/strong><tracefile> <tkprofoutput> explain=apps\/passwd sys=no sort(with the options for parse,fetch and execute).<\/p>\n

To know more about raw trace<\/strong> & tkprof<\/strong> visit following links
\n\u00a0
\nSQL Trace & TKPROF in 10g R2<\/a>\u00a0 and following Metalink Note<\/p>\n

224270.1<\/strong> – Trace Analyzer TRCANLZR – Interpreting Raw SQL Traces with Binds and\/or Waits generated by EVENT 10046
\n39817.1\u00a0<\/strong> – Interpreting Raw SQL_TRACE and DBMS_SUPPORT. START_TRACE output
\n117129.1<\/strong> – How to get a Trace for And Begin to Analyze a Performance Issue
\n296559.1<\/strong> – FAQ: Common Tracing Techniques within the Oracle Applications 11i<\/p>\n

While accessing the tkprof file i found noticed a particular “select statement<\/strong>” and its execution plan like\u00a0<\/p>\n

–<\/p>\n

–
\nRead through TKPROF file and raw Trace<\/u><\/strong>
\nWhile accessing the tkprof file\u00a0I found one select statement and its execution plan like<\/p>\n

Rows\u00a0\u00a0\u00a0\u00a0 Execution Plan
\n——-\u00a0 —————————————————
\n\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0 SELECT STATEMENT\u00a0\u00a0 MODE: ALL_ROWS
\n\u00a0\u00a0\u00a02232\u00a0\u00a0 SORT (ORDER BY)
\n\u00a0\u00a0 2232\u00a0\u00a0\u00a0 NESTED LOOPS
\n\u00a0\u00a0 2232\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS\u00a0\u00a0 MODE: ANALYZED (BY INDEX ROWID) OF
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0‘xxxx’ (TABLE)
\n\u00a0791231\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX (RANGE SCAN) OF ‘xxxx_I10’ (INDEX)
\n\u00a0\u00a0 2232\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS\u00a0\u00a0 MODE: ANALYZED (BY INDEX ROWID) OF
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘YYYY'(TABLE)
\n\u00a0\u00a0 2232\u00a0\u00a0\u00a0\u00a0\u00a0 INDEX\u00a0\u00a0 MODE: ANALYZED (UNIQUE SCAN) OF
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘YYYY_U1’ (INDEX (UNIQUE))<\/p>\n

EXECUTION PLAN <\/strong>Before executing an Oracle Statement, Oracle first parse statement and develop execution plan. Execution Plan\u00a0is sequence of operations Oracle Performs to run a statement. To know more about EXECUTION PLAN click here <\/a><\/p>\n

EXPLAIN PLAN<\/strong> statement allows you to submit SQL statement to Oracle and have database prepare execution plan for statement without actually executing it. The execution plan\u00a0is available to you in form of row inserted in special table called PLAN TABLE<\/strong>.\u00a0\u00a0You can query plan table to see steps of execution plan for statement.\u00a0To know more about it click here<\/a><\/p>\n

–<\/u><\/strong><\/p>\n

Compare same\u00a0form with other instance\u00a0
\n<\/u><\/strong>\u00a0Generated explain plan for the same query to other instance where the form was working fine.\u00a0 Like\u00a0EXPLAIN PLAN [SET STATEMENT_ID= <string in single quote> ] [INTO <plan table\u00a0name>] FOR <query>;<\/strong>\u00a0\u00a0
\n\u00a0\u00a0 \u00a0I got explain plan from plan_table, there I found some discrepancy between both the plans. Line\u00a0\u00a0INDEX (RANGE SCAN) OF ‘xxxx_I10’ (INDEX)<\/strong> —- was missing in the explain plan… so\u00a0 I concluded there is some problem with index on the table. I\u00a0asked user to tune the query as this was custom query<\/p>\n

To know more above INDEX RANGE SCAN<\/strong>from performance tuning Guide Click Here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

User reported that one form was taking too much time to save\u00a0record and after sometime form was hanging (records were […]<\/p>\n","protected":false},"author":115,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[42],"tags":[],"class_list":["post-524","post","type-post","status-publish","format-standard","hentry","category-troubleshooting"],"yoast_head":"\nTroubleshooting\/Tuning Slow Form in Apps 11i -<\/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:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Troubleshooting\/Tuning Slow Form in Apps 11i -\" \/>\n<meta property=\"og:description\" content=\"User reported that one form was taking too much time to save\u00a0record and after sometime form was hanging (records were […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/\" \/>\n<meta property=\"article:published_time\" content=\"2008-06-20T14:50:21+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/\",\"name\":\"Troubleshooting\/Tuning Slow Form in Apps 11i -\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG\",\"datePublished\":\"2008-06-20T14:50:21+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage\",\"url\":\"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG\",\"contentUrl\":\"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Troubleshooting\/Tuning Slow Form in Apps 11i\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation & Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Troubleshooting\/Tuning Slow Form in Apps 11i -","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:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/","og_locale":"en_US","og_type":"article","og_title":"Troubleshooting\/Tuning Slow Form in Apps 11i -","og_description":"User reported that one form was taking too much time to save\u00a0record and after sometime form was hanging (records were […]","og_url":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/","article_published_time":"2008-06-20T14:50:21+00:00","og_image":[{"url":"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG","type":"","width":"","height":""}],"author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/","url":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/","name":"Troubleshooting\/Tuning Slow Form in Apps 11i -","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage"},"image":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage"},"thumbnailUrl":"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG","datePublished":"2008-06-20T14:50:21+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#primaryimage","url":"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG","contentUrl":"http:\/\/onlineappsdba.com\/wp-content\/uploads\/2008\/06\/trace.thumbnail.JPG"},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2008\/06\/20\/troubleshootingtuning-slow-form-in-apps-11i\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"Troubleshooting\/Tuning Slow Form in Apps 11i"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation & Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/524","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=524"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/524\/revisions"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=524"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=524"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=524"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}