11gOCM考試大綱

哎呀我的天吶發表於2014-11-25
Go

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

點選(此處)摺疊或開啟

  1. sys@TESTDB12>alter session set parallel_degree_policy=auto;

  3. Session altered.   --自動實現並行，那麼就設定成auto的。

  5. sys@TESTDB12>alter session set parallel_min_time_threshold=20;

  7. Session altered.   --oracle估計，如果超過20s那麼就開啟並行。

啟動並行DML

點選(此處)摺疊或開啟

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

  8. Table created.

點選(此處)摺疊或開啟

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

  6. 4999 rows created.
v$pq_sesstat
這個檢視可以看到上一個的操作是否用到並行。

點選(此處)摺疊或開啟

  1. sh@TESTDB12>select * from v$pq_sesstat;

  3. STATISTIC                 LAST_QUERY SESSION_TOTAL
  4. ------------------------------ ---------- -------------
  5. Queries Parallelized               0     1
  6. DML Parallelized                   0     0
  7. DDL Parallelized                   0     0
  8. DFO Trees                          0     1
  9. Server Threads                     0     0
  10. Allocation Height                  0     0
  11. Allocation Width                   0     0
  12. Local Msgs Sent                    0     66
  13. Distr Msgs Sent                    0     0
  14. Local Msgs Recv\'d                 0     66
  15. Distr Msgs Recv\'d                 0     0

  17. 11 rows selected.
沒有用到並行，因為我們沒有在回話上啟用dml並行操作

啟動dml並行                  

點選(此處)摺疊或開啟

  1. sh@TESTDB12>alter session enable parallel dml;


點選(此處)摺疊或開啟

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

  4. 918843 rows created.

  6. sh@TESTDB12>select * from v$pq_sesstat;

  8. STATISTIC                  LAST_QUERY SESSION_TOTAL
  9. ------------------------------ ---------- -------------
  10. Queries Parallelized                0     1
  11. DML Parallelized                    1     1
  12. DDL Parallelized                    0     0
  13. DFO Trees                           1     2
  14. Server Threads                      2     0        --這裡說的是總共的並行程式數
  15. Allocation Height                   2     0        --這裡說明每個表上有2個程式在做並行                               
  16. Allocation Width                    1     0
  17. Local Msgs Sent                  2105     66
  18. Distr Msgs Sent                     0     0
  19. Local Msgs Recv\'d                  8     66
  20. Distr Msgs Recv\'d                  0     0

  22. 11 rows selected.


這裡並行最大的 parallel_max_servers是20                 


點選(此處)摺疊或開啟

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

點選(此處)摺疊或開啟

  1. sys@TESTDB12>alter system set parallel_max_servers=5;              --最大並行度設定成5

  3. System altered.

  5. sys@TESTDB12>alter system set parallel_adaptive_multi_user = false;

  7. System altered.

點選(此處)摺疊或開啟

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

點選(此處)摺疊或開啟

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

  4.   COUNT(*)
  5. ----------
  6.   798353


點選(此處)摺疊或開啟

  1. sh@TESTDB12>select * from v$pq_sesstat;

  3. STATISTIC                      LAST_QUERY SESSION_TOTAL
  4. ------------------------------ ---------- -------------
  5. Queries Parallelized                    1     3
  6. DML Parallelized                        0     0
  7. DDL Parallelized                        0     3
  8. DFO Trees                               1     6
  9. Server Threads                          4     0             --4個並行
  10. Allocation Height                       2     0             --兩張表
  11. Allocation Width                        1     0
  12. Local Msgs Sent                        88     611
  13. Distr Msgs Sent                         0     0
  14. Local Msgs Recv\'d                     88     611
  15. Distr Msgs Recv\'d                      0     0

點選(此處)摺疊或開啟

  1. alter system set parallel_max_servers = 20;



點選(此處)摺疊或開啟

  1. sh@TESTDB12>select * from v$pq_sesstat;

  3. STATISTIC                               LAST_QUERY SESSION_TOTAL
  4. ------------------------------ ---------- -------------
  5. Queries Parallelized                    1     4
  6. DML Parallelized                        0     0
  7. DDL Parallelized                        0     3
  8. DFO Trees                               1     7
  9. Server Threads                         10     0       --2張表總共啟動並行數    
  10. Allocation Height                       5     0       --1張表的並行數
  11. Allocation Width                        1     0
  12. Local Msgs Sent                       223     834
  13. Distr Msgs Sent                         0     0
  14. Local Msgs Recv\'d                    223     834
  15. Distr Msgs Recv\'d                      0     0
資料倉儲


效能調優


 使用結果池

結果集（result cache）：只是把計算後的結果，放在shared pool中。         

點選(此處)摺疊或開啟

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

  5. User created.

  7. idle>grant connect , resource ,dba to grc;

  9. Grant succeeded.

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

  15. PL/SQL procedure successfully completed.

  17. grc@TESTDB12>create table cachejfv (c varchar2(500)) tablespace users;

  19. Table created.

  21. grc@TESTDB12>insert into cachejfv values(\'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\');

  23. grc@TESTDB12>insert into cachejfv select * from cachejfv;

  25. 2097152 rows created.                                  --插入4194304條資料。

  27. grc@TESTDB12>insert into cachejfv values ('b');
  28. grc@TESSDB12>commit;

  30. grc@TESTDB12>alter system flush buffer_cache;

點選(此處)摺疊或開啟

  1. grc@TESTDB12>show parameter result

  3. NAME                                        TYPE     VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. client_result_cache_lag              big integer      3000
  6. client_result_cache_size             big integer      0
  7. result_cache_max_result                  integer      5
  8. result_cache_max_size                big integer      2080K
  9. result_cache_mode                         string      MANUAL
  10. result_cache_remote_expiration           integer      0

點選(此處)摺疊或開啟

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

  5.   COUNT(*)
  6. ----------
  7.      1


  10. Execution Plan
  11. ----------------------------------------------------------
  12. Plan hash value: 2522916280

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

  35. Predicate Information (identified by operation id):
  36. ---------------------------------------------------

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

  44. Result Cache Information (identified by operation id):
  45. ------------------------------------------------------

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


  52. Note
  53. -----
  54.    - dynamic sampling used for this statement (level=2)


  57. Statistics
  58. ----------------------------------------------------------
  59.      21 recursive calls
  60.      10 db block gets
  61.      358165 consistent gets
  62.      182445 physical reads
  63.      0 redo size
  64.     526 bytes sent via SQL*Net to client
  65.     523 bytes received via SQL*Net from client
  66.      2 SQL*Net roundtrips to/from client
  67.      4 sorts (memory)
  68.      0 sorts (disk)

點選(此處)摺疊或開啟

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

  4. TYPE       STATUS NAME                               OBJECT_NO    ROW_COUNT  ROW_SIZE_AVG
  5. ---------- --------- ------------------------------ ---------- ------------ -------------
  6. Dependency Published GRC.CACHEJFV                        76977            0             0
  7. Result     Published select /*+ result_cache q_name          0            1             5
  8.            (Q1) */ count(*)
  9.            from cachejfv c1 ,cachejfv c2，cachejfv c3
  10.            ,cachejfv c4,cachejfv c5 where c1.C =
當表中有資料改變oracle會將那個結果集標記為invalid


點選(此處)摺疊或開啟

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

  27. PL/SQL procedure successfully completed.
如果表的資料一直放生變化那麼可以把手動設定成自動。




DG

備份可以在備庫上備份就可以啦

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1346325/，如需轉載，請註明出處，否則將追究法律責任。

相關文章