Shulou(Shulou.com)06/01 Report--

1. View alert_PROD.log

[error message]: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1

View the basic information of temporary tablespaces and their usage:

View basic information:

Select dtf.TABLESPACE_NAME, dtf.file_name, dtf.BYTES / 1024 / 1024 as "MB" from dba_temp_files dtf order by dtf.TABLESPACE_NAME

Usage View:

SELECT d.tablespace_name "Name", TO_CHAR (NVL (a.bytes / 1024 / 1024 / 1024, 0), '99999990.900') "Size (G)", TO_CHAR (NVL (t.hwm, 0) / 1024 / 1024 / 1024,' 99999999.99999') "HWM (G)", TO_CHAR (NVL (t.hwm / a.bytes * 100,0), '990.00') "HWM%" TO_CHAR (NVL (t.bytes / 1024 / 1024 / 1024, 0), '99999999.99999') "Using (G)", TO_CHAR (NVL (t.bytes / a.bytes * 100,0), '990.00') "Using%" FROM sys.dba_tablespaces d, (select tablespace_name, sum (bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name Sum (bytes_cached) hwm, sum (bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name (+) AND d.tablespace_name = t.tablespace_name (+) AND d.extent_management like 'LOCAL' AND d.contents like' TEMPORARY'

By looking at the tablespace in the temporary tablespace group, you can tell that the tablespace is in an abnormal state.

2. Error starting ORA-1652 trace

Enable tracing of the database at the session level:

ALTER SESSION SET EVENTS '1652 trace name errorstack'

Enable tracking of the database at the system level:

ALTER SYSTEM SET EVENTS '1652 trace name errorstack'

Write this parameter to the spfile file:

ALTER SYSTEM SET EVENT = '1652 trace name errorstack' SCOPE = SPFILE

3. View the error log in alert_PROD.log

Errors in file / ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc: more / ora/prod/12.1.0/admin/PROD_erpdb/diag/rdbms/prod/PROD/trace/PROD_ora_5165.trc

-Error Stack Dump-ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1- Current SQL Statement for this session (sql_id=cfrvgzfkpxp49)-Note: the middle is the code that needs to be optimized-PL/SQL Stack-

4. Print the SQL execution plan

Press F5 for the pl/sql Developer tool.

5. Analyze the implementation plan

Clause of full table scan (Table access full).

The sentence is analyzed by SQL-optimized rules.

6. Optimize the important part of the SQL in the Test system.

(1) before performing optimization:

(2) create an index

Create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS (nvl (CPMD.TRANSACTION_ID,0))

(3) after modification

7. Check the optimization results in the Test system.

Comparison before and after optimization

Name

Before optimization

After optimization

Scanning mode

TABLE ACCESS FULL

INDEX RANGE SCAN

Cost

12735

eight hundred and forty four

IO cost

12681

eight hundred and forty two

CPU cost

447695496

17435302

8. Deploy in Prod system

(1) backup CUX_PA_MAT_A_DETS_COLLECT_PKG to local

(2) create an index

Create index ind_CPMD_TRANSACTION_ID on cux.CUX_PA_MATERIAL_A_DETAILS (nvl (CPMD.TRANSACTION_ID,0))

(3) optimize and add comments

A, WHERE CPMD.TRANSACTION_ID is not null is replaced by WHERE NVL (CPMD.TRANSACTION_ID,0) > 0B, AND MTLN.LOT_NUMBER NOT LIKE'% |%'is replaced by: AND INSTR (MTLN.LOT_NUMBER,' |')

0

(4) Code checking

Check item: a, check whether the modified code is correct

B. Check whether the source code has been commented

C, check whether comments are added after modifying the code (including time, function, modifier)

(5) execute the code and resubmit the request

9. Perform the query operation to view the results

Comparison before and after overall optimization:

Name

Before optimization

After optimization

Scanning mode

TABLE ACCESS FULL

INDEX RANGE SCAN

Cost

51651

seven hundred and fifty one

IO cost

51423

seven hundred and forty

CPU cost

1885854949

94466486

9. Check whether there is an ORA-1652 error in alert_PROD.log

Beginning log switch checkpoint up to RBA [0x8c9.2.10], SCN: 5965544065707Tue Mar 20 16:19:47 2018Thread 1 advanced to log sequence 2249 (LGWR switch) Current log# 1 seq# 2249 mem# 0: / data/prod/proddata/log01a.dbf Current log# 1 seq# 2249 mem# 1: / data/prod/proddata/log01b.dbfTue Mar 20 16:19:51 2018Archived Log entry 2248 added for thread 1 sequence 2248 ID 0x1556bb36 dest 1:Tue Mar 20 16:24:52 2018Completed checkpoint up to RBA [0x8c9.2.10] SCN: 5965544065707Tue Mar 20 16:27:28 2018Incremental checkpoint up to RBA [0x8c9.a420.0], current log tail at RBA [0x8c9.21670.0] Tue Mar 20 16:47:32 2018Incremental checkpoint up to RBA [0x8c9.32675.0], current log tail at RBA [0x8c9.3d64f.0] Tue Mar 20 16:58:03 2018Tue Mar 20 17:00:03 2018Closing scheduler windowClosing Resource Manager plan via scheduler windowClearing Resource Manager plan via parameterTue Mar 20 17:07:35 2018Incremental checkpoint up to RBA [0x8c9.547b9.0] Current log tail at RBA [0x8c9.5979d.0] Tue Mar 20 17:27:37 2018Incremental checkpoint up to RBA [0x8c9.5eece.0], current log tail at RBA [0x8c9.609cf.0]

10. Turn off ORA-1652 tracking events. The corresponding shutdown script is as follows:

ALTER SESSION SET EVENTS '1652 trace name context off'; ALTER SYSTEM SET EVENTS' 1652 trace name context off'; ALTER SYSTEM RESET EVENT SCOPE = SPFILE SID ='*'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.