Jobs Automatic For The calculation of statistics


v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
w:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}

Normal
14
false
false
false
IT
X-NONE
X-NONE
MicrosoftInternetExplorer4


/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Tabella normale”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

First you have checked the size of each diagram so that you can understand how they guide the work of collecting statistics in order to make improvements on the performance of database

Owner                               Objects      size MB

—————————— ———— ————

USER_BIG                              386       74,516

PR2K                                    211        8,424

SAST                                     31          152

ASINT                                    28           85

APEX_030200                           2,561           78

MDSYS                                 1,973           66

ORDDATA                                 257           14

OLAPSYS                                 721            6

CTXSYS                                  388            5

WMSYS                                   332            4

EXFSYS                                  312            4

ORDSYS                                2,512            1

PUBLIC                               28,021            0

FLOWS_FILES                              13            0

OWBSYS_AUDIT                             12            0

ORDPLUGINS                               10            0

ORACLE_OCM                                8            0

SI_INFORMTN_SCHEMA                        8            0

APPQOSSYS                                 5            0

OWBSYS                                    2            0

The scheme appears to be the largest userbig. Within this scheme, identified three tables in addition to being the most used ones are also subject to massive loads daily lasting about 12 hours. They reported significant slowdowns during and after loading.

OWNER                          TABLE_NAME                     TABLESPACE_NAME          

—————————— —————————— ————————–

USER_BIG                       BIG_TAB                         USER_BIG_USR           

USER_BIG                       BIGTXT                          USER_BIG_TXT           

USER_BIG                       BIGRAS                          USER_BIG_USR           

 

The first test was carried out by recalculating the statistics of two of the three tables mentioned and it seemed immediately clear a significant improvement in the operations of SQL.
Just for this purpose have been implemented two new deputies job on this calculation:

 

JOB_ACTION                                    NEXT_RUN_DATE

——————————————— ————————————

BEGIN DBMS_STATS.GATHER_TABLE_STATS (         08-FEB-13 15:15:00,000000 EUROPE/ROME

ownname => “USER_BIG”,

tabname => “BIG_TAB”,

degree  => 4,

cascade => TRUE);

END;                                       

 

BEGIN DBMS_STATS.GATHER_TABLE_STATS (         08-FEB-13 15:15:00,000000 EUROPE/ROME

ownname => “USER_BIG”,

tabname => “BIGRAS”,

degree  => 4,

cascade => TRUE);

END;                                                    

These jobs are scheduled to run several times throughout the day and this is a window was created ad hoc:

SCHEDULE_NAME                  REPEAT_INTERVAL

—————————— ————————————————————

STATS_USER_BIG           FREQ=DAILY; BYHOUR=7,9,13,15,17,19; BYMINUTE=15; BYSECOND=0;

 

The run is then fixed for all seven days of the week (MON-SUN) at 7:15, 9:15, 13:15, 15:15, 17:15, 19:15. After 19.15 there are no executions to avoid any kind of overlap with backup jobs.

OWNER           LOG_DATE                              ERROR# STATUS

————— ———————————– ——– ——————–

USER_BIG      08-FEB-13 17:19:43,132000 +01:00           0 SUCCEEDED

The query verification is as follows:

 

alter session set nls_date_format=’dd-mm-yyyy hh24:mi:ss’;

select owner, table_name, tablespace_name, last_analyzed from all_tables where owner=’USER_BIG’ and table_name in (‘BIGRAS’,’BIG_TAB’,’BIGTXT’);

 

The results presented after the insertion of the jobs show significant reductions in reported performance problems so the problem can be considered returned.
ANALYSIS AND OPTIMIZATION OF MEMORY
To try to optimize performance were also monitored the memory. Currently the allocation is managed automatically:


Automatic memory management  Enabled

Total size of memory 6560 Mb

Maximum memory size 6560 Mb

and the total of the allocated memory is equal to 6560Mb as can be seen even during the start of the instance (expressed in bytes):

SQL> startup

Istanza ORACLE avviata.

Total System Global Area 6847938560 bytes

Fixed Size                  2268224 bytes

Variable Size            3590325184 bytes

Database Buffers         3238002688 bytes

Redo Buffers               17342464 bytes

You can check the memory allocation for the individual components by querying the dynamic view V $ MEMORY_DYNAMIC_COMPONENTS:

COMPONENT                      Current size (MB) Minimun Size (MB) Maximum Size (MB)

—————————— —————– —————– —————–

shared pool                                 1088              1088              1088

large pool                                    16                16                16

java pool                                     16                16                16

streams pool                                  16                16                16

SGA Target                                  4272              4272              4272

DEFAULT buffer cache                        3088              3088              3088

PGA Target                                  2288              2288              2288

NOTE: The values ​​indicated are relative to the time in which the view has been interrogated.

There is also another dynamic view called V $ MEMORY_TARGET_ADVICE that indicates what are the optimal targets considering all the information contained in the AWR snapshots. The basis of the analyzed information can be gleaned by the following information:
Questioning the view we get:

 

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR

———– —————— ———— ——————-

       6560                  1          706                   1

       7380              1,125          706                   1

       8200               1,25          706                   1

       9020              1,375          706                   1

       9840                1,5          706                   1

      10660              1,625          706                   1

      11480               1,75          706                   1

      12300              1,875          706                   1

      13120                  2          706                   1

on the basis of these data it is possible to see clearly how the increase of the memory (MEMORY_SIZE_FACTOR) not bring any type of improvement (ESTD_DB_TIME).

Potrebbero interessarti anche...