From time to time we hit Errors like ORA-600 or ORA-7445, which are triggered by specific SQL statements. For these cases, Oracle developed the SQL Repair Advisor. This is a short demonstration of utilizing the SQL Repair Advisor to avoid ORA-7445 caused by a SQL statement.<\/p>\n
SQL and Error:<\/strong><\/p>\n Execution of SQL Repair Advisor:<\/strong><\/p>\n Activation:<\/strong><\/p>\n Test:<\/strong><\/p>\n Removal of SQL Patch:<\/strong><\/p>\n Deletion of SQL Diag Task:<\/strong><\/p>\n From time to time we hit Errors like ORA-600 or ORA-7445, which are triggered by specific SQL statements. For these cases, Oracle developed the SQL Repair Advisor. This is a short demonstration of utilizing the SQL Repair Advisor to avoid ORA-7445 caused by a SQL statement. SQL and Error: SELECT EXTRACT(YEAR FROM date_new) jahr, EXTRACT(MONTH […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[57,5],"tags":[],"class_list":["post-1096","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1096"}],"version-history":[{"count":8,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1096\/revisions"}],"predecessor-version":[{"id":1105,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1096\/revisions\/1105"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} \r\nSELECT\r\nEXTRACT(YEAR FROM date_new) jahr,\r\nEXTRACT(MONTH FROM date_new) monat,\r\nCOUNT(DISTINCT TRUNC(date_new)) arbeitstage,\r\nSUM(CASE WHEN STRING1 IN (SELECT STRING1\r\nFROM MD.MD1\r\nWHERE STRING1_GROUP = 'CUST')\r\nTHEN\r\nDECODE(FLAG, 'J', 1, 0)\r\nELSE\r\n0\r\nEND) cust_flag\r\nFROM\r\nSCHEMA.TAB1\r\nWHERE\r\ndate_new BETWEEN TO_DATE(201101, 'yyyymm')\r\nAND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)\r\nGROUP BY\r\nEXTRACT(YEAR FROM date_new),\r\nEXTRACT(MONTH FROM date_new)\r\nORDER BY 1,2;\r\n\r\nERROR at line 6:\r\nORA-03113: end-of-file on communication channel\r\nProcess ID: 7746\r\nSession ID: 737 Serial number: 9823\r\n\r\nTue Jul 26 15:19:41 2011\r\nException [type: SIGSEGV, Address not mapped to object] [ADDR:0xA] [PC:0x888A3C9, xtyqbcb()+413] ...\r\nORA-07445: exception encountered: core dump [xtyqbcb()+413] [SIGSEGV] [ADDR:0xA] [PC:0x888A3C9]...\r\n<\/pre>\n
\r\nDECLARE\r\n rep_out CLOB;\r\n t_id VARCHAR2(50);\r\nBEGIN\r\n T_ID := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK( \r\n sql_text => q'#SELECT\r\nEXTRACT(YEAR FROM date_new) jahr,\r\nEXTRACT(MONTH FROM date_new) monat,\r\nCOUNT(DISTINCT TRUNC(date_new)) arbeitstage,\r\nSUM(CASE WHEN STRING1 IN (SELECT STRING1\r\nFROM MD.MD1\r\nWHERE STRING1_GROUP = 'CUST')\r\nTHEN\r\nDECODE(FLAG, 'J', 1, 0)\r\nELSE\r\n0\r\nEND) cust_flag\r\nFROM\r\nSCHEMA.TAB1\r\nWHERE\r\ndate_new BETWEEN TO_DATE(201101, 'yyyymm')\r\nAND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)\r\nGROUP BY\r\nEXTRACT(YEAR FROM date_new),\r\nEXTRACT(MONTH FROM date_new)\r\nORDER BY 1,2#', \r\n task_name => 'ORA7445-xtyqbcb', \r\n problem_type =>DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR\r\n );\r\n DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (t_id);\r\n rep_out := DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK (t_id, DBMS_SQLDIAG.TYPE_TEXT);\r\n DBMS_OUTPUT.PUT_LINE ('Report : ' || rep_out);\r\nEND;\r\n\/\r\n\r\nOutput:<\/strong>\r\n\r\nReport : GENERAL INFORMATION SECTION\r\n-------------------------------------------------------------------------------\r\nTuning Task Name : ORA7445-xtyqbcb\r\nTuning Task Owner : SYS\r\nWorkload Type : Single SQL Statement\r\nScope : COMPREHENSIVE\r\nTime Limit(seconds):\r\n1800\r\nCompletion Status : COMPLETED\r\nStarted at : 07\/26\/2011 15:46:38\r\nCompleted at : 07\/26\/2011 15:46:38\r\n\r\n-------------------------------------------------------------------------------\r\nSchema Name: SYS\r\nSQL ID : 39ac71hjcn0rt\r\n...\r\n\r\n-------------------------------------------------------------------------------\r\nFINDINGS SECTION (1\r\nfinding)\r\n-------------------------------------------------------------------------------\r\n\r\n1- SQL Patch Finding (see explain plans section below)\r\n------------------------------------------------------\r\n A potentially better execution plan was found for this statement.\r\n\r\n Recommendation\r\n\r\n\r\n--------------\r\n - Consider accepting the recommended SQL patch.\r\n execute dbms_sqldiag.accept_sql_patch(task_name =>\r\n 'ORA7445-xtyqbcb', task_owner => 'SYS', replace => TRUE);\r\n\r\n Rationale\r\n ---------\r\n Recommended plan with hash value 1503213169 has number of rows 7, check\r\n\r\nsum 13925076401, execution time 264 and 1972 buffer gets\r\n\r\n-------------------------------------------------------------------------------\r\n\r\n\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n \r\n begin dbms_sqldiag.accept_sql_patch(\r\n task_name =>'ORA7445-xtyqbcb', \r\n task_owner => 'SYS', \r\n replace => TRUE);\r\nend;\r\n\/\r\n<\/pre>\n
\r\nSELECT\r\nEXTRACT(YEAR FROM date_new) jahr,\r\nEXTRACT(MONTH FROM date_new) monat,\r\nCOUNT(DISTINCT TRUNC(date_new)) arbeitstage,\r\nSUM(CASE WHEN STRING1 IN (SELECT STRING1\r\nFROM MD.MD1\r\nWHERE STRING1_GROUP = 'CUST')\r\nTHEN\r\nDECODE(FLAG, 'J', 1, 0)\r\nELSE\r\n0\r\nEND) cust_flag\r\nFROM\r\nSCHEMA.TAB1\r\nWHERE\r\ndate_new BETWEEN TO_DATE(201101, 'yyyymm')\r\nAND ADD_MONTHS(TO_DATE(201107, 'yyyymm'), 1)\r\nGROUP BY\r\nEXTRACT(YEAR FROM date_new),\r\nEXTRACT(MONTH FROM date_new)\r\nORDER BY 1,2;\r\n\r\n JAHR MONAT ARBEITSTAGE CUST_FLAG\r\n---------- ---------- ----------- ----------------\r\n 2011 1 20 0\r\n\r\n\r\n7 rows selected.\r\n\r\nSQL> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n---------------------\r\nSQL_ID 5wdztx81x0r8f, child number 0\r\n-------------------------------------\r\n\r\n\r\nPlan hash value: 1503213169\r\n\r\n-----------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | | 591 (100)| |\r\n|* 1 | TABLE ACCESS FULL | MD1 | 1 | 12 | | 3 (0)| 00:00:01 |\r\n| 2 | SORT GROUP BY | | 27402 | 294K| 2776K| 591 (12)| 00:00:02 |\r\n|* 3 | TABLE ACCESS FULL| TAB1 | 117K| 1260K| | 369 (10)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------------\r\n\r\n\r\n...\r\n\r\n\r\nNote\r\n-----\r\n - SQL patch \"SYS_SQLPTCH_013166b4fb2a0001\" used for this statement\r\n\r\n\r\n66 rows selected.\r\n<\/pre>\n
\r\nexec DBMS_SQLDIAG.DROP_SQL_PATCH ('SYS_SQLPTCH_013166b4fb2a0001');\r\n<\/pre>\n exec DBMS_SQLDIAG.DROP_DIAGNOSIS_TASK ('ORA7445-xtyqbcb');\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"