PLSQL _ Statistical Information Series 2 _ errors in program performance (CASE) and plsql statistics

PLSQL _ Statistical Information Series 2 _ errors in program performance (CASE) and plsql statistics

Created By BaoXinjian

I. Summary

In a stable database with performance, all jobs must be completed within a certain period of time.

In the past, in a banking system, a job was originally completed in 30minutes, but after 3hours, it was still running

Excluding the impact of data size changes, you need to check whether the parsing plan of the program is changed. In a stable system, the explain plan corresponding to the job is usually fixed through sqlplan management and rarely changed.

Check whether the sqlplan explain corresponding to the job changes.

In general, changes to the resolution plan will also cause such problems.

 

Ii. Solution

Step 1. view the corresponding session of the program

select sid, sql_id from v$session where sid = 1001

Step2. view the SQL statement currently running the corresponding session

select b.begin_interval_time, 
       a.disk_reads_total, 
       a.buffer_gets_total,
       a.buffer_gets_delta, 
       a.rows_processed_total, 
       a.plan_hash_value  from dba_hist_sqlstat a, dba_hist_snapshot b   where a.snap_id = b.snap_id 
   and sql_id = '466bpq7055f4c' 
order by 1 desc;  

Step3. check whether the resolution plan corresponding to the long-running SQL statement has been changed during this period.

select* from v$sql_plan

Step 4. view the statistical time of the table

select name, last_analized from dba_tables

Step 5. generate statistical information for the table

exec dbms_stats.gather_table_stats(
            ownname=>'SCOT',
            tabname=>'GAVIN',
            estimate_percent=>3,
            degree=>32,
            granularity=>'GLOBAL',
            cascade=>FALSE,
            no_invalidate=>FALSE);

Thanks and Regards