[20180316]共享服務模式和直接路徑讀.txt
[20180316]共享服務模式和直接路徑讀.txt
--//在共享伺服器模式下,執行計劃不會選擇直接路徑讀,透過例子證明.
1.環境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
-------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> alter system set filesystemio_options=asynch scope=spfile;
System altered.
--//重啟資料庫.
SCOTT@book> show sga
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
SCOTT@book> create table t as select rownum id from dual connect by level<=2;
Table created.
SCOTT@book> ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
Table altered.
--//這樣可以實現每塊2條記錄.
SCOTT@book> insert into t select rownum+2 from dual connect by level <=8e4-2;
79998 rows created.
SCOTT@book> commit ;
Commit complete.
--//分析表略.
SCOTT@book> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS from dba_segments where owner=user and segment_name='T';
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS
------ -------------------- ------------------ ----------- ------------ ---------- ----------
SCOTT T TABLE 4 546 333447168 40704
--//佔用 333447168/1024/1024 = 318M
SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
90467 90467
SCOTT@book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
36020
2.測試分析:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
274 11 46995 DEDICATED 46996 21 6 alter system kill session '274,11' immediate;
--//server=DEDICATED採用專用模式.
SCOTT@book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
0
SCOTT@book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
------------------------------------------ ---------- ---------- ----------
physical reads direct 97 0 274
physical reads direct temporary tablespace 110 0 274
physical reads direct (lob) 176 0 274
SCOTT@book> select count(*) from t;
COUNT(*)
----------
80000
SCOTT@book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
------------------------------------------ ---------- ---------- ----------
physical reads direct 97 40217 274
physical reads direct temporary tablespace 110 0 274
physical reads direct (lob) 176 0 274
--//可以發現執行計劃走的physical reads direct.physical reads direct=40217.透過執行後資料快取數量也可以推定:
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
0
SCOTT@book> select count(*) from t;
COUNT(*)
----------
80000
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10963 (100)| | 1 |00:00:00.17 | 40227 | 40222 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.17 | 40227 | 40222 |
| 2 | TABLE ACCESS FULL| T | 1 | 89876 | 10963 (1)| 00:02:12 | 80000 |00:00:00.17 | 40227 | 40222 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Note
-----
- dynamic sampling used for this statement (level=2)
SCOTT@book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
5
--//僅僅快取5個資料塊.
3.測試分析:
$ rlsql scott/book@127.0.0.1:1521/book
--//我以前多次提到dispatchers包括服務.ezconenct優先使用共享伺服器模式:
SCOTT@127.0.0.1:1521/book> show parameter dispatchers
NAME TYPE VALUE
--------------- -------- -------------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=book,bookXDB)
max_dispatchers integer
SCOTT@127.0.0.1:1521/book> alter session set statistics_level=all;
Session altered.
SCOTT@127.0.0.1:1521/book> alter system flush buffer_cache;
System altered.
SCOTT@127.0.0.1:1521/book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
261 1 47005 SHARED 46300 20 1 alter system kill session '261,1' immediate;
--//server=SHARED,採用共享伺服器模式.
SCOTT@127.0.0.1:1521/book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
------------------------------------------ ---------- ---------- ----------
physical reads direct 97 0 261
physical reads direct temporary tablespace 110 0 261
physical reads direct (lob) 176 0 261
SCOTT@127.0.0.1:1521/book> select count(*) from t;
COUNT(*)
----------
80000
SCOTT@127.0.0.1:1521/book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ---------- ----------
physical reads direct 97 0 261
physical reads direct temporary tablespace 110 0 261
physical reads direct (lob) 176 0 261
--//可以發現在共享伺服器模式下執行計劃不選擇直接路徑讀.透過快取數量也可以證明:
SCOTT@127.0.0.1:1521/book> alter system flush buffer_cache;
System altered.
SCOTT@127.0.0.1:1521/book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
0
SCOTT@127.0.0.1:1521/book> select count(*) from t;
COUNT(*)
----------
80000
SCOTT@127.0.0.1:1521/book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cyzznbykb509s, child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10963 (100)| | 1 |00:00:00.34 | 40236 | 40222 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.34 | 40236 | 40222 |
| 2 | TABLE ACCESS FULL| T | 1 | 89876 | 10963 (1)| 00:02:12 | 80000 |00:00:00.34 | 40236 | 40222 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Note
-----
- dynamic sampling used for this statement (level=2)
SCOTT@127.0.0.1:1521/book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
40222
--//全表掃描後資料塊大量進入資料快取.
3.但是在並行的情況下共享模式情況就不同:
SCOTT@127.0.0.1:1521/book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
261 1 47005 SHARED 46300 20 1 alter system kill session '261,1' immediate;
SCOTT@127.0.0.1:1521/book> alter system flush buffer_cache;
System altered.
SCOTT@127.0.0.1:1521/book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
0
SCOTT@127.0.0.1:1521/book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ---------- ----------
physical reads direct 97 0 261
physical reads direct temporary tablespace 110 0 261
physical reads direct (lob) 176 0 261
SCOTT@127.0.0.1:1521/book> select /*+ parallel(t 8) */ count(*) from t;
COUNT(*)
----------
80000
SCOTT@127.0.0.1:1521/book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 965gv5rh77t0c, child number 0
-------------------------------------
select /*+ parallel(t 8) */ count(*) from t
Plan hash value: 3126468333
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1522 (100)| | | | | 1 |00:00:00.22 | 15 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | | 1 |00:00:00.22 | 15 |
| 2 | PX COORDINATOR | | 1 | | | | | | | 8 |00:00:00.22 | 15 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 | 89876 | 1522 (0)| 00:00:19 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL| T | 0 | 89876 | 1522 (0)| 00:00:19 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic sampling used for this statement (level=2)
SCOTT@127.0.0.1:1521/book> @ &r/viewsess "physical reads direct"
NAME STATISTIC# VALUE SID
------------------------------------------ ---------- ---------- ----------
physical reads direct 97 40217 261
physical reads direct temporary tablespace 110 0 261
physical reads direct (lob) 176 0 261
--//可以發現physical reads direct上升.
SCOTT@127.0.0.1:1521/book> select count(*) from v$bh where OBJD=90467 and STATUS<>'free';
COUNT(*)
----------
314
--//從資料快取的數量也可以看出.
4.附上viewsess.sql指令碼:
set verify off
column name format a70
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
FROM v$mystat a, v$statname b
WHERE lower(b.NAME) like lower('%&1%') AND a.statistic# = b.statistic# ;
--and a.value>0;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151913/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180316]非同步IO和共享服務模式.txt非同步模式
- [20160830]使用共享服務模式的弊端.txt模式
- [20161212]ezconnect與共享服務模式.txt模式
- [20190306]共享服務模式與SDU.txt模式
- 直接路徑插入模式的一些討論模式
- 網路檔案共享服務
- [20190115]關於共享服務與專用模式.txt模式
- Oracle直接路徑載入Oracle
- 並行建立索引會不會進行直接路徑讀取？並行索引
- [20190115]共享服務模式與啟動到mount狀態.txt模式
- 10月14日 網路檔案共享服務 2 NFS服務和SAMBA服務NFSSamba
- insert /*+ append */直接路徑插入APP
- NFS網路檔案共享服務的配置和排錯總結NFS
- 直接路徑插入 -- insert /*+append*/ into [zt]APP
- 關於直接路徑插入的工作原理
- Linux 訪問網路檔案共享服務Linux
- oracle常規與直接路徑插入區別Oracle
- 索引對直接路徑載入的影響索引
- FTP檔案共享服務FTP
- 路徑中./和../和/
- python怎麼寫txt檔案路徑Python
- 共享服務-FTP基礎（一）FTP
- win10如何關閉共享服務_win10共享服務怎麼開啟Win10
- 讀《我和Labview》3.5-3.6路徑和資料平化View
- 檔案絕對路徑和相對路徑
- Jsp相對路徑和絕對路徑JS
- html中的路徑的介紹：絕對路徑和相對路徑HTML
- Linux檔案的路徑定位-相對路徑和絕對路徑Linux
- nodejs路徑處理方法和絕對路徑NodeJS
- 絕對路徑和相對路徑的區別，
- 絕對路徑和相對路徑的區別
- 檔案的相對路徑和絕對路徑以及根相對路徑
- 更改oracle10g的歸檔模式和歸檔路徑Oracle模式
- 賽可達推病毒攻擊檢測和情報分享服務
- SSON：2024年共享服務和外包行業報告行業
- Javascript寫入txt和讀取txt檔案示例JavaScript
- 05_Linux相對路徑和絕對路徑Linux
- 【Direct-Path】直接路徑載入提升插入效率及其自身限制