【原創】Oracle9i和10g中plan_table表的差異
實驗環境:WinXP SP2
資料庫版本:10.2.0.1
今天在做實驗的時候發現,plan_table在不用的Oracle版本間是有一定的差異的。10g相對9i,在plan_table中新增了一些列(下面綠色的部分)
Oracle 9i的utlxplan.sql內容:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
);
Oracle 10g的utlxplan.sql內容:
create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30),
other_xml clob
);
刪除原有10g的plan_table表
sys@TEST>drop table plan_table;
Table dropped.
建立9i下的plan_talbe表
sys@TEST>create table PLAN_TABLE (
2 statement_id varchar2(30),
3 timestamp date,
4 remarks varchar2(80),
5 operation varchar2(30),
6 options varchar2(255),
7 object_node varchar2(128),
8 object_owner varchar2(30),
9 object_name varchar2(30),
10 object_instance numeric,
11 object_type varchar2(30),
12 optimizer varchar2(255),
13 search_columns number,
14 id numeric,
15 parent_id numeric,
16 position numeric,
17 cost numeric,
18 cardinality numeric,
19 bytes numeric,
20 other_tag varchar2(255),
21 partition_start varchar2(255),
22 partition_stop varchar2(255),
23 partition_id numeric,
24 other long,
25 distribution varchar2(30),
26 cpu_cost numeric,
27 io_cost numeric,
28 temp_space numeric,
29 access_predicates varchar2(4000),
30 filter_predicates varchar2(4000));
Table created.
可以看出執行計劃的顯示沒有問題,只是在Note中會提示使用的是舊版的plan_table(綠色的部分)
sys@TEST>select * from dual;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)|
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)|
---------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
重新使用10g的plan_table表
sys@TEST>drop table plan_table;
Table dropped.
sys@TEST>@?/rdbms/admin/utlxplan.sql
Table created.
會發現比使用9i的plan_table多顯示了每步操作的執行時間
sys@TEST>select * from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/498744/viewspace-264994/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Explain Plan】10g中的PLAN_TABLE$臨時表AI
- 【原創】不同RTOS中POSIX介面的實現差異
- 兩表中某列的資料差異
- SQL server差異備份和還原SQLServer
- 建表時的約束的語法在informix和oracle中的差異ORMOracle
- 10g和9i執行計劃差異
- [轉載] SQL server 差異備份和還原SQLServer
- jquery版本中的差異jQuery
- UDP和TCP的差異UDPTCP
- vue和react的差異VueReact
- Firefox與IE在CSS樣式表中的差異FirefoxCSS
- ERP差異來源和差異處理
- 【原創】比較資料泵和exp/imp對相同資料匯出/匯入的效能差異
- 【原創】比較使用sql*loader的直接載入方式和傳統載入方式的效能差異SQL
- MySQL中myisam和innodb有什麼差異?MySql
- Oracle --- PLAN_TABLE$和PLAN_TABLE區別Oracle
- 【原創】ORA-00054問題的解決(plan_table在autotrace過程中會被鎖定)
- “=》”關聯運算子在Oracle 10g和11g的變化差異Oracle 10g
- Linux系統中Ubuntu和Redhat的差異有哪些?LinuxUbuntuRedhat
- 比較兩個的表結構差異
- “=》”關聯操作符在Oracle 10g和11g的變化差異Oracle 10g
- Gradle中的差異化構建Gradle
- 並行和非並行在不通場景中的效能差異並行
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五:函式的差異(五)筆記SQLOracle函式
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五:函式的差異(三)筆記SQLOracle函式
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五:函式的差異(一)筆記SQLOracle函式
- Bootstrap和Tailwind CSS之間的差異?bootAICSS
- Java和C++的基本差異(轉)JavaC++
- TrustStrategy——Java中httpclient.jar和httpcore.jar版本差異帶來的異常RustJavaHTTPclientJAR
- 關於在SQL語句中ON和WHERE中條件使用的差異SQL
- delphi中回撥函式差異函式
- 工作流和BPM之間的差異
- 談談 mysql和oracle的使用感受 -- 差異MySqlOracle
- 聊聊t-io和netty的差異Netty
- mac和windows執行maven命令的差異MacWindowsMaven
- 空字串和空格字串在informix和oralce 的差異字串ORM
- 深度解析dba_segments和sys.seg$中的細節差異(上)
- 深度解析dba_segments和sys.seg$中的細節差異(下)