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)