1, the statistical analysis table of the stored procedure part of the content
CREATE OR REPLACE procedure SEA. sp_analyze_xxx_a is V_sql_1 varchar2(+); V_sql_2 varchar2(+); V_sql_3 varchar2(+); V_startdate date; Err varchar2(+); Begin V_sql_1 : =' alter session set Workarea_size_policy=manual '; V_sql_2 : =' alter session set sort_area_size=2147483640 '; EXECUTE IMMEDIATE v_sql_1; EXECUTE IMMEDIATE v_sql_2; ...... |
This stored procedure, in order to speed up the statistical analysis table and index, the use of the statistical analysis before the execution of the statement, the session sort_area_size to 2GB to increase the proportion of in-memory sequencing strategy, so the following two statements are used:
V_sql_1 : =' altersession set workarea_size_policy=manual ';
V_sql_2 : =' altersession set sort_area_size=2147483640 ';
This approach is absolutely effective for speeding up statistical analysis, and the effect can be very obvious.
2, stored procedures run frequently reported ORA-00600 error and interrupted
Complete error message:
ORA-00600: Internal Error code , Parameters : [Kcblin_3], [103], [1032192],[8192], [8193], [312], [664], [], [], [], [], []
3. Cause analysis
Reference documents related to this error are found on Metalink: 1177363.1
As a result, if Memory_target is set to manage the SGA and PGA, and the value of the PGA is manually set, it may cause the ORA-00600 [Kcblin_3] error to be reported
4. Verify your environment configuration
Sql>showparameter Memory_target
NAME TYPE VALUE ------------------------- --------------------- ------------------ Memory_target bit integer 200G |
Sql>showparameter Pga_aggregate_target
NAME TYPE VALUE ------------------------------- ---------------------- ----------- Pga_aggregate_target bit integer 20G |
It is true that both the Memory_target and the PGA values are configured manually.
5. Solution
Delete or comment The following four lines of code in the stored procedure,
V_sql_1 : =' alter session set Workarea_size_policy=manual '; V_sql_2 : =' alter session set sort_area_size=2147483640 '; EXECUTE IMMEDIATE v_sql_1; EXECUTE IMMEDIATE v_sql_2; |
The problem is resolved, and subsequent runs have never reported a ORA-00600 error.
This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work
Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244
Analysis and treatment of ORA-00600 errors in the stored procedure of statistical analysis table