Plsql_ Statistical Information Series 1_ Previous normal program because of statistical information too long sudden performance problems (case)

2014-11-15 Created by Baoxinjian

I. Summary

In a stable database of performance, all jobs need to be completed within a certain amount of time

Previously in a banking system, suddenly a job was originally completed in 30minutes, but after 3hours, still running

Excluding the impact of the change in the size of the data, you need to see if the program's resolution plan changes, in a stable system, the job corresponding to the explain plan is generally fixed by SQLPlan management, rarely change

So you need to see if the job corresponds to the SQLPlan explain change

In general, resolving plan changes can also cause such problems

Second, the way to solve

Step1. View the session for the program

Select  from where = 1001

Step2. View the SQL that corresponds to the session currently running

  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. See if the resolution plan for that long-running SQL has changed during this period

Select *  from V$sql_plan

Step4. To view the statistics time for a table

Select  from Dba_tables

Step5. Generating statistics on a table

exec  dbms_stats.gather_table_stats (ownname< Span style= "color: #808080;" >=>   scot   ' , Tabname=>   " gavin  , Estimate_percent=>  3 , Degree=>  Span style= "color: #800000; Font-weight:bold; " >32 , Granularity=>   " global  , cascade  =>  false,no_invalidate=>  FALSE); 

Thanks and regards

Plsql_ Statistical Information Series 1_ Previous normal program because of statistical information too long sudden performance problems (case)