Oracle Tutorial: DBA task-ensure the accuracy of statistical information

Recently, the project has entered the cut over stage, which is under great pressure and never before. My task is to optimize long running SQL so that it can be completed within three hours. Yesterday, a Long running SQL statement appeared. It ran for 16 hours. After two hours of struggle, it was optimized to 2 hours and 10 minutes.

Although the Long running SQL has nothing to do with the statistics, I still propose to ensure the accuracy of the statistics. As a DBA, I have to customize policies for collecting statistics and related scripts. Below is a script for ensuring the accuracy of statistics.

Note: This script is applicable to data warehouses and Oracle11g. If you are using OLTP or Oracle10g, modify some conditions by yourself.

DECLARE
CURSOR STALE_TABLE IS
Select owner,
SEGMENT_NAME,
CASE
WHEN SIZE_GB <0.5 THEN
30
WHEN SIZE_GB> = 0.5 AND SIZE_GB <1 THEN
20
WHEN SIZE_GB> = 1 AND SIZE_GB <5 THEN
10
WHEN SIZE_GB> = 5 AND SIZE_GB <10 THEN
5
WHEN SIZE_GB> = 10 THEN
1
End as percent,
8 AS DEGREE
FROM (select owner,
SEGMENT_NAME,
SUM (BYTES/1024/1024/1024) SIZE_GB
FROM DBA_SEGMENTS
Where owner = 'adwu _ OPTIMA_AP11'
AND SEGMENT_NAME IN
(SELECT/* + UNNEST */DISTINCT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE (LAST_ANALYZED is null or STALE_STATS = 'yes ')
And owner = 'adwu _ OPTIMA_AP11 ')
Group by owner, SEGMENT_NAME );

BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
For stale in STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => STALE. OWNER,
TABNAME => STALE. SEGMENT_NAME,
ESTIMATE_PERCENT => STALE. PERCENT,
METHOD_OPT => 'for all columns size auto ',
DEGREE => 8,
GRANULARITY => 'all ',
CASCADE => TRUE );
End loop;
END;
/

Based on the actual situation, you can choose to run the above script every night when the database is not busy.