Jobs Automatic For The calculation of statistics
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).