![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416885878Xz1E.jpg?x-oss-process=style/bb)
session 4 5 6 占的比例最大
11gServer Configuration
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416886065Xbf6.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_14168862298g6c.jpg?x-oss-process=style/bb)
将数据文件创建到NFS上。
用opath打补丁
Direct NFS数据库拓扑结构
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416890109387P.jpg?x-oss-process=style/bb)
Oracle直接客户端扫描顺序
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_141689044099JK.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416890646QSCh.jpg?x-oss-process=style/bb)
local:本地的网段
path:nfs服务器的ip
最多支持将数据文件放到四个nfs服务器中。
最后编译一下
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416890816f36R.jpg?x-oss-process=style/bb)
Enterprise Manager Grid Control
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_14168931451HLH.jpg?x-oss-process=style/bb)
Management Database Available
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416893415YeqQ.jpg?x-oss-process=style/bb)
Data Management
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_14168939180zC9.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416894147FRy4.jpg?x-oss-process=style/bb)
并行
配置 和管理并行查询:
![](http://img.blog.itpub.net/blog/attachment/201411/25/29990276_1416894388quuk.jpg?x-oss-process=style/bb)
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那么就开启并行。
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.
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.
2 select *
3 from sales
4 where rownum < 5000;
4999 rows created.
这个视图可以看到上一个的操作是否用到并行。
点击(此处)折叠或打开
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.
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并行
点击(此处)折叠或打开
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.
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.
![](http://img.blog.itpub.net/blog/attachment/201411/27/29990276_14170807999p09.jpg?x-oss-process=style/bb)
这里并行最大的 parallel_max_servers是20
点击(此处)折叠或打开
create table temp_channels
parallel 5 as
select * from sh.channels
where channel_id in ('2','3','4');
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.
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 * 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
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
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
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
![](http://img.blog.itpub.net/blog/attachment/201411/27/29990276_14170929474hhf.jpg?x-oss-process=style/bb)
性能调优
![](http://img.blog.itpub.net/blog/attachment/201411/27/29990276_1417093047dzWU.jpg?x-oss-process=style/bb)
使用结果池
结果集(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;
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
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)
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 =
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 =
点击(此处)折叠或打开
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.
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.
![](http://img.blog.itpub.net/blog/attachment/201411/28/29990276_1417145316mHR0.png?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201411/28/29990276_14171455007pug.png?x-oss-process=style/bb)
DG
![](http://img.blog.itpub.net/blog/attachment/201411/28/29990276_14171456990oa8.png?x-oss-process=style/bb)
备份可以在备库上备份就可以啦
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1346325/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1346325/