OCP042 12th lecture Proactive Maintenance

The content of this lecture includes:
Use optimizer statistics
Manage automatic workload Archives
Automatic database performance diagnosis and monitoring (ADDM)
Terms
Automatic Workload Repository (AWR): Infrastructure for data gathering, analysis, and solutions recommendations
Baseline: Data gathered of a "normal running database" for performance comparison
Metric: Rate of change in a cumulative statistic
Statistics: Data collections used for optimizing internal operations, such as execution of a SQL statement
Threshold: A boundary value against which metric values are compared
I. optimizer statistics
Features:
Non-real-time (the default time window is from Monday to Friday to 6 days in the early morning of the next day, all day over the weekend) can be restarted across the database instance and automatically collected. The optimizer statistics include tables, column indexes and system statistics are stored in the data dictionary.
Statistics Collection mainly includes:
Size of the table or index in database blocks
Number of rows
Average row size and chain count (tables only)
Height and number of deleted leaf rows (indexes only)

 
[Oracle @ rhel6 ~] $ Sqlplus/nolog
SQL * Plus: Release 10.2.0.1.0-Production on Fri Jul 22 16:34:00 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
 
SQL> conn/as sysdba
Connected.
SQL> create table hr. t04212_big as select * from dba_source;
Table created.
 
SQL> conn hr/hr
Connected.
SQL> set autot on
SQL> select count (*) from t04212_big;
 
COUNT (*)
----------
323065
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1778284531
 
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------------
| 0 | select statement | 1 | 1463 (1) | 00:00:18 |
| 1 | sort aggregate | 1 |
| 2 | table access full | T04212_BIG | 294K | 1463 (1) | 00:00:18 |
-------------------------------------------------------------------------
 
Note
-----
-Dynamic sampling used for this statement
// We can see that the optimizer performs dynamic sampling before collecting statistics.
 
Statistics
----------------------------------------------------------
28 recursive cballs
0 db block gets
6599 consistent gets
6520 physical reads
0 redo size
517 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select num_rows from dba_tables t where t. owner = 'hr' and t. table_name = upper ('t04212 _ Day ');
NUM_ROWS
----------

SQL> exec dbms_stats.gather_table_stats ('hr', 't04212 _ Day ');
PL/SQL procedure successfully completed.
 
SQL> select num_rows from dba_tables t where t. owner = 'hr' and t. table_name = upper ('t04212 _ Day ');
 
NUM_ROWS
----------
323445
----------
 
SQL> set autot on
SQL> select count (*) from hr. t04212_big;
 
COUNT (*)
----------
323065
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1778284531
 
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------------
| 0 | select statement | 1 | 1463 (1) | 00:00:18 |
| 1 | sort aggregate | 1 |
| 2 | table access full | T04212_BIG | 323K | 1463 (1) | 00:00:18 |
-------------------------------------------------------------------------
 
 
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
6529 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select num_rows from user_tables where table_name = 'hr _ TUNDO ';
 
NUM_ROWS
----------
 
SQL> analyze table hr_tundo compute statistics;
Table analyzed
 
SQL> select num_rows from user_tables where table_name = 'hr _ TUNDO ';
 
NUM_ROWS
----------
100000
 
There are three levels of statistics: basic, typical, and all.
Basic indicates that statistics are disabled, typical indicates that only important statistics are collected, and all indicates that all statistics are collected.
SQL> show parameter statistics;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Statistics_level string TYPICAL
Ii. Automatic workload data archives
Features: provides a built-in data archiving database for performance. snapshots are taken once an hour to retain 7-day snapshot data, which is the basis of all automatic management functions. The MMON process is responsible for collecting information.
 

Create a baseline, retain the snapshot set, and run ADDM.

 

 

 
SQL> select SQL _text from v $ SQL where
2 plan_hash_value = 292991709 and rownum <= 5;
 
SQL _TEXT
---------------------------------------------------------
Update hr. hr_tundo set uvalue = 79320 where uname = 'a79319'
Update hr. hr_tundo set uvalue = 79721 where uname = 'a79720'
Update hr. hr_tundo set uvalue = 79415 where uname = 'a79414'
Update hr. hr_tundo set uvalue = 83617 where uname = 'a83616'
Update hr. hr_tundo set uvalue = 83684 where uname = 'a83683'
Iii. Suggestion center, provided by the dbms_advisor package
SQL> begin
2 dbms_advisor.quick_tune (
3 advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
4 task_name => 'task2 ',
5 attr1 => 'select * from hr. hr_tundo where uname like ''% 123% ''',
6 template => 'sqlaccess _ OLTP ');
7 * end;
8
9 PL/SQL procedure successfully completed.

 

 

 
This article is from the "yueda tianchong" blog