oracle课堂笔记----第三十天
自动化维护
准备工作
字典表：
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 值为空
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 更新
SQL> insert into t1 values (2);
SQL> commit;
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 过时
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where table_name='T1'; 更新
v$表：
SQL> select name, value from v$sysstat where name like '%sort%';
SQL> select * from hr.employees order by salary;
SQL> select name, value from v$sysstat where name like '%sort%'; 增加
SQL> shutdown immediate
SQL> startup
SQL> select name, value from v$sysstat where name like '%sort%'; 归零
参数：
SQL> show parameter statistics_level 不能是basic
AWR
em中查看基本设置
em中生成和查看awr报表
$ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awr*.sql
server alert
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
em中修改警告50%，严重80%。
SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0;
SQL> insert into t1 select * from dba_objects where rownum<=10000;
SQL> commit; 超过50%
SQL> insert into t1 select * from dba_objects where rownum<=20000;
SQL> commit; 超过80%
em中查看警告信息。
ADDM
session 1:
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11;
session 2:
SQL> update t1 set x=22;
ASH
session 1:
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11;
session 2:
SQL> update t1 set x=22;
em中寻找问题的根源
em中做ash报表
$ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ash*.sql
AMM
initorcl.ora
spfileorcl.ora
ASMM
AMM
SQL> select bytes/1024/1024 from v$sgainfo where name='Granule Size';
SQL> show parameter memory
SQL> select COMPONENT, CURRENT_SIZE/1024/1024 from v$memory_dynamic_components;
em中的内存指导
$ strings $ORACLE_HOME/dbs/spfileorcl.ora __开头的隐含参数保留优化设置
java pool的调整：
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='java pool';
DECLARE
i NUMBER;
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..200 LOOP
-- Build up a dynamic statement to create a uniquely named java stored proc.
-- The "chr(10)" is there to put a CR/LF in the source code.
v_sql := 'create or replace and compile' || chr(10) ||
'java source named "SmallJavaProc' || i || '"' || chr(10) ||
'as' || chr(10) ||
'import java.lang.*;' || chr(10) ||
'public class Util' || i || ' extends Object' || chr(10) ||
'{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
SQL> select CURRENT_SIZE from v$memory_dynamic_components where COMPONENT='java pool'; java pool改变
java pool扩展、buffer cache收缩
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='java pool';
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='DEFAULT buffer cache';
large pool的调整：
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool';
SQL> create table t1 as select rownum x from dual connect by level<=100000;
SQL> alter table t1 parallel 64; 也可以在查询时指定并行度
SQL> select /*+ parallel(t1 24) */ count(*) from (select /*+ parallel(t1 24)*/ * from t1 group by x);
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'; large pool改变
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='large pool';
备份spfile
$ cp $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
AMMàASMM
amm下，sga和pga不需要设置
SQL> show parameter sga_max_size
SQL> show parameter sga_target
SQL> show parameter pga_aggregate_target
SQL> alter system set memory_target=0;
SQL> show parameter sga_target amm和asmm都有一对参数
SQL> show parameter sga_max_size
SQL> show parameter pga_aggregate_target
SQL> alter system set sga_target=300M; 手动修改
ASMMàmanual
SQL> show parameter shared_pool_size 值为0
SQL> alter system set sga_target=0;
SQL> show parameter shared_pool_size 固定
manualàasmmàamm
修改sga_target或memory_target，清空所有遗留参数
内存大小的建议：
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE ,SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED from V$SHARED_POOL_ADVICE;
SQL> select SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PHYSICAL_READ_FACTOR from V$DB_CACHE_ADVICE;
SQL> select * from V$SGA_TARGET_ADVICE;
SQL> select PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR, ESTD_PGA_CACHE_HIT_PERCENTAGE from V$PGA_TARGET_ADVICE;
SQL> select * from V$MEMORY_TARGET_ADVICE;
SAA
SQL> alter system flush shared_pool;
SQL> grant dba to hr;
SQL> conn hr/hr
SQL> set autot on
SQL> select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id;
em中执行saa，过滤条件为表：hr.employees, hr.departments
STA
SQL> alter system flush shared_pool;
SQL> conn hr/hr
SQL> set autot on
SQL> select /*+ full(employees) */ * from employees where employee_id=100;
em中创建tuning set，调用sta分析
