11gOCM考试大纲

session 4 5 6 占的比例最大

11gServer Configuration
 

将数据文件创建到NFS上。
用opath打补丁

Direct NFS数据库拓扑结构


Oracle直接客户端扫描顺序


local：本地的网段
path：nfs服务器的ip
最多支持将数据文件放到四个nfs服务器中。

最后编译一下

Enterprise Manager Grid Control
 
Management Database Available

Data Management


并行

配置 和管理并行查询：

10g没有这个参数parallel_degree_policy

点击(此处)折叠或打开

sys@TESTDB12>alter session set parallel_degree_policy=auto;

Session altered.   --自动实现并行，那么就设置成auto的。

sys@TESTDB12>alter session set parallel_min_time_threshold=20;

Session altered.   --oracle估计，如果超过20s那么就开启并行。

启动并行DML

点击(此处)折叠或打开

sh@TESTDB12>create table little_sales
  2 partition by hash (time_id)
  3 (partition ls1,partition ls2)
  4 parallel
  5 as
  6 select * from sales where 1=2;

Table created.

点击(此处)折叠或打开

sh@TESTDB12>insert into little_sales
  2 select *
  3 from sales
  4 where rownum < 5000;

4999 rows created.

v$pq_sesstat
这个视图可以看到上一个的操作是否用到并行。

点击(此处)折叠或打开

sh@TESTDB12>select * from v$pq_sesstat;

STATISTIC                 LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized               0     1
DML Parallelized                   0     0
DDL Parallelized                   0     0
DFO Trees                          0     1
Server Threads                     0     0
Allocation Height                  0     0
Allocation Width                   0     0
Local Msgs Sent                    0     66
Distr Msgs Sent                    0     0
Local Msgs Recv\'d                 0     66
Distr Msgs Recv\'d                 0     0

11 rows selected.

没有用到并行，因为我们没有在回话上启用dml并行操作

启动dml并行                  

点击(此处)折叠或打开

sh@TESTDB12>alter session enable parallel dml;

点击(此处)折叠或打开

sh@TESTDB12>insert into little_sales
  2 select * from sales;

918843 rows created.

sh@TESTDB12>select * from v$pq_sesstat;

STATISTIC                  LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                0     1
DML Parallelized                    1     1
DDL Parallelized                    0     0
DFO Trees                           1     2
Server Threads                      2     0        --这里说的是总共的并行进程数
Allocation Height                   2     0        --这里说明每个表上有2个进程在做并行                               
Allocation Width                    1     0
Local Msgs Sent                  2105     66
Distr Msgs Sent                     0     0
Local Msgs Recv\'d                  8     66
Distr Msgs Recv\'d                  0     0

11 rows selected.

这里并行最大的 parallel_max_servers是20                 

点击(此处)折叠或打开

create table temp_channels
parallel 5 as
select * from sh.channels
where channel_id in ('2','3','4');

点击(此处)折叠或打开

sys@TESTDB12>alter system set parallel_max_servers=5;              --最大并行度设置成5

System altered.

sys@TESTDB12>alter system set parallel_adaptive_multi_user = false;

System altered.

点击(此处)折叠或打开

create table temp_sales nologging parallel 5 as
select * from sh.sales
where channel_id in ('2','3');

点击(此处)折叠或打开

select count(*) from temp_sales s ,temp_channels c
where (s.channel_id) = (c.channel_id);

  COUNT(*)
----------
  798353

点击(此处)折叠或打开

sh@TESTDB12>select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1     3
DML Parallelized                        0     0
DDL Parallelized                        0     3
DFO Trees                               1     6
Server Threads                          4     0             --4个并行
Allocation Height                       2     0             --两张表
Allocation Width                        1     0
Local Msgs Sent                        88     611
Distr Msgs Sent                         0     0
Local Msgs Recv\'d                     88     611
Distr Msgs Recv\'d                      0     0

点击(此处)折叠或打开

alter system set parallel_max_servers = 20;

点击(此处)折叠或打开

sh@TESTDB12>select * from v$pq_sesstat;

STATISTIC                               LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1     4
DML Parallelized                        0     0
DDL Parallelized                        0     3
DFO Trees                               1     7
Server Threads                         10     0       --2张表总共启动并行数    
Allocation Height                       5     0       --1张表的并行数
Allocation Width                        1     0
Local Msgs Sent                       223     834
Distr Msgs Sent                         0     0
Local Msgs Recv\'d                    223     834
Distr Msgs Recv\'d                      0     0

数据仓库


性能调优


 使用结果池

结果集（result cache）：只是把计算后的结果，放在shared pool中。         

点击(此处)折叠或打开

idle>create user grc identified by grc
  2 default tablespace users
  3 temporary tablespace temp;

User created.

idle>grant connect , resource ,dba to grc;

Grant succeeded.

idle>conn grc/grc
Connected.
grc@TESTDB12>exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.
grc@TESTDB12>create table cachejfv (c varchar2(500)) tablespace users;

Table created.
grc@TESTDB12>insert into cachejfv values(\'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\');
grc@TESTDB12>insert into cachejfv select * from cachejfv;
2097152 rows created.                                  --插入4194304条数据。

grc@TESTDB12>insert into cachejfv values ('b'); grc@TESSDB12>commit;
grc@TESTDB12>alter system flush buffer_cache;

点击(此处)折叠或打开

grc@TESTDB12>show parameter result

NAME                                        TYPE     VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer      3000
client_result_cache_size             big integer      0
result_cache_max_result                  integer      5
result_cache_max_size                big integer      2080K
result_cache_mode                         string      MANUAL
result_cache_remote_expiration           integer      0

点击(此处)折叠或打开

grc@TESTDB12>select /*+ result_cache q_name(Q1) */ count(*)                --不使用hint就不会用到结果集
  2 from cachejfv c1 ,cachejfv c2 ,cachejfv c3 ,cachejfv c4,cachejfv c5
  3 where c1.c ='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';

  COUNT(*)
----------
     1


Execution Plan
----------------------------------------------------------
Plan hash value: 2522916280

--------------------------------------------------------------------------------------------------------
|  Id | Operation                 | Name                       | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                            | 1    | 1260  | 286T  (1)   |999:59:59 |
|   1 |  RESULT CACHE             | 2czdyj48767d90j12kd0bvvbrd |      |       |             |          |
|   2 |   SORT AGGREGATE          |                            | 1    | 1260  |             |          |
|   3 |    MERGE JOIN CARTESIAN   |                            | 11T  | 12P   | 286T  (1)   |999:59:59 |
|   4 |     MERGE JOIN CARTESIAN  |                            | 28G  | 25T   | 700G  (1)   |999:59:59 |
|   5 |      MERGE JOIN CARTESIAN |                            | 68M  | 48G   | 1708M (1)   |999:59:59 |
|   6 |       MERGE JOIN CARTESIAN|                            | 167K | 80M   | 4169K (1)   | 13:53:51 |
|*  7 |        TABLE ACCESS FULL  | CACHEJFV                   | 410  | 100K  | 10146 (1)   | 00:02:02 |
|   8 |        BUFFER SORT        |                            | 410  | 100K  | 4159K (1)   | 13:51:49 |
|*  9 |         TABLE ACCESS FULL | CACHEJFV                   | 410  | 100K  | 10144 (1)   | 00:02:02 |
|  10 |       BUFFER SORT         |                            | 410  | 100K  | 1708M (1)   |999:59:59 |
|* 11 |        TABLE ACCESS FULL  | CACHEJFV                   | 410  | 100K  | 10144 (1)   | 00:02:02 |
|  12 |      BUFFER SORT          |                            | 410  | 100K  | 700G  (1)   |999:59:59 |
|* 13 |       TABLE ACCESS FULL   | CACHEJFV                   | 410  | 100K  | 10144 (1)   | 00:02:02 |
|  14 |     BUFFER SORT           |                            | 410  | 100K  | 286T  (1)   |999:59:59 |
|* 15 |      TABLE ACCESS FULL    | CACHEJFV                   | 410  | 100K  | 10144 (1)   | 00:02:02 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter(\"C1\".\"C\"=\'b\')
   9 - filter(\"C2\".\"C\"=\'b\')
  11 - filter(\"C3\".\"C\"=\'b\')
  13 - filter(\"C4\".\"C\"=\'b\')
  15 - filter(\"C5\".\"C\"=\'b\')

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=1; dependencies=(GRC.CACHEJFV); attributes=(single-row); parameters=(nls); name=\"select /*+ result_cache q_name(Q1) */ count(*)
from cachejfv c1 ,cachejfv c2 ,cachejfv c3 ,cachejfv c4,cachejfv c5
where c1.c =\'\"


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
     21 recursive calls
     10 db block gets
     358165 consistent gets
     182445 physical reads
     0 redo size
    526 bytes sent via SQL*Net to client
    523 bytes received via SQL*Net from client
     2 SQL*Net roundtrips to/from client
     4 sorts (memory)
     0 sorts (disk)

点击(此处)折叠或打开

1 select type,status,name,object_no,row_count,row_size_avg
  2* from v$result_cache_objects order by 1

TYPE       STATUS NAME                               OBJECT_NO    ROW_COUNT  ROW_SIZE_AVG
---------- --------- ------------------------------ ---------- ------------ -------------
Dependency Published GRC.CACHEJFV                        76977            0             0
Result     Published select /*+ result_cache q_name          0            1             5
           (Q1) */ count(*)
           from cachejfv c1 ,cachejfv c2，cachejfv c3
           ,cachejfv c4,cachejfv c5 where c1.C =

当表中有数据改变oracle会将那个结果集标记为invalid

点击(此处)折叠或打开

grc@TESTDB12>exec dbms_result_cache.memory_report(detailed=>true);
R e s u l t C a c h e M e m o r y    R e p o r t
[Parameters]
Block Size     = 1K bytes
Maximum Cache Size = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 168920 bytes [0.064% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Cache Mgr = 208 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 163568 bytes [0.062% of the Shared Pool]
....... Overhead = 130800 bytes
........... Hash Table     = 64K bytes (4K buckets)
........... Chunk Ptrs     = 24K bytes (3K slots)
........... Chunk Maps     = 12K bytes
........... Miscellaneous = 28400 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)

PL/SQL procedure successfully completed.

如果表的数据一直放生变化那么可以把手动设置成自动。




DG

备份可以在备库上备份就可以啦

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29990276/viewspace-1346325/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29990276/viewspace-1346325/