Analysis and treatment of ORA-00600 errors in the stored procedure of statistical analysis table

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.