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分析