Statistical analysis table of stored procedures in case of ORA-00600 error analysis and processing, stored procedures ora-00600

Statistical analysis table of stored procedures in case of ORA-00600 error analysis and processing, stored procedures ora-00600

1. Part of the stored procedure of the statistical analysis table

Createor replace procedure SEA. sp_analyze_XXX_ais V_ SQL _1 varchar2 (2000 ); V_ SQL _2 varchar2 (2000 ); V_ SQL _3 varchar2 (2000 ); V_startdate date; Err varchar2 (2000 );   Begin V_ SQL _1: = 'alter session set workarea_size_policy = Manual '; V_ SQL _2: = 'alter session set sort_area_size = 100 '; EXECUTEIMMEDIATE v_ SQL _1; EXECUTEIMMEDIATE v_ SQL _2; ......

In order to speed up the statistical analysis table and index, we used to increase the sort_area_size of the session TO 2 GB before executing the statistical analysis statement, to increase the proportion of sorting in the memory, the following two statements are used:

V_ SQL _1: = 'altersession set workarea_size_policy = Manual ';

V_ SQL _2: = 'altersession set sort_area_size = 100 ';

This method is absolutely effective for accelerating statistical analysis, and the effect will be very obvious.

 

2. The ORA-00600 error is often reported and interrupted when the stored procedure is running.

Complete error message:

ORA-00600: Internal error code, parameter: [kcblin_3], [103], [1032192], [8192], [8193], [312], [664], [], [], [], [], []

 

3. Cause Analysis

Find reference documents related to this error on metalink: 1177363.1

The reason is that if memory_target is set to manage SGA and PGA, And the PGA value is manually set, it may cause a ORA-00600 [kcblin_3] Error

 

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

 

Memory_target is configured, And the PGA value is manually configured.

 

5. Solution

Delete or comment out 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 = 100 '; EXECUTEIMMEDIATE v_ SQL _1; EXECUTEIMMEDIATE v_ SQL _2;

 

The problem is solved, and no ORA-00600 error is reported in subsequent operations.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244