本文详细探讨了Oracle数据库的SQL执行计划，包括Userenv系统函数、ROWID与ROWNUM、索引扫描类型、DML语句如CALL与MERGE、账号与日期命令、表锁模式、EXPLAIN PLAN的使用及优化器评估。通过深入研究执行计划的稳定性与四大优化途径，提供了丰富的SQL优化实例。
-
目录
- 系统函数Userenv
- ROWID、ROWNUM
- 分析Oracle索引扫描四大类
- DML语句-MERGE
- DML语句-CALL
- 账号与日期的命令
- oracle table-lock的5种模式
- EXPLAIN PLAN查看相关
- EXPLAIN PLAN总结
- SQL优化器如何进行评估优化
- 执行计划稳定性深入研究
- Oracle查询优化4大方面的主要途径
- SQL优化例子
系统函数Userenv
Oracle中USERENV和SYS_CONTEXT用来返回当前session的信息，其中，userenv是为了保持向下兼容的遗留函数，推荐使用sys_context函数调用userenv命名空间来获取相关信息。
1、 USERENV(OPTION)
返回当前的会话信息.
OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
OPTION='LANGUAGE'返回数据库的字符集.
OPTION='SESSIONID'为当前会话标识符.
OPTION='ENTRYID'返回可审计的会话标识符.
OPTION='LANG'返回会话语言名称的ISO简记.
OPTION='INSTANCE'返回当前的实例.
OPTION='terminal'返回当前计算机名
SELECT USERENV('LANGUAGE') FROM DUAL;
2、sys_context
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
ROWID、ROWNUM
认识ROWID
SQL> select rowid from dept where rownum < 2;
ROWID各列信息格式如下
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
简单查看ROWID信息
select rowid ,
substr(rowid,1,6) "OBJECT",
substr(rowid,7,3) "FILE",
substr(rowid,10,6) "BLOCK",
substr(rowid,16,3) "ROW"
from dept
/
ROWNUM是oracle系统顺序分配为从查询返回的行的编号。
返回的第一行分配的是1，第二行是2，依此类推。
这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
分析Oracle索引扫描四大类
学习Oracle时，你可能会遇到Oracle索引扫描问题，这里将介绍Oracle索引扫描问题的解决方法，在这里拿出来和大家分享一下。根据索引的类型与where限制条件的不同，有4种类型的Oracle索引扫描：
◆索引唯一扫描(index unique scan)
◆索引范围扫描(index range scan)
◆索引全扫描(index full scan)
◆索引快速扫描(index fast full scan)
(1) 索引唯一扫描(index unique scan)
通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。
使用唯一性约束的例子：
SQL> explain plan for
select empno,ename from emp where empno=10;
Query Plan
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
(2) 索引范围扫描(index range scan)
使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)
使用索引范围扫描的例子：
SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;
Query Plan
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行
(c) 对非唯一索引列上进行的任何查询。
(3) 索引全扫描(index full scan)
与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
全Oracle索引扫描的例子：
An Index full scan will not perform single block i/o's and so it may prove to be inefficient.
e.g.
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno,ename;
Query Plan
SELECT STATEMENT [CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]
(4) 索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。
索引快速扫描的例子：
SQL> explain plan for select empno,ename from big_emp;
Query Plan
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
SQL> explain plan for select ename from big_emp;
Query Plan
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
DML语句-CALL
測試oracle直接call java程式
當初oracle提供這個功能是為了讓java的程式師可方便寫的一些功能在oracle中使用，這樣子也就可不必學pl/sql了
但我暫時還沒有找到效能上的差別是多少的官方資料。
不過有做過就記錄一下，以免以後真的遇到還要上網找資料
1、先寫一個很簡單的java程式 (檔名叫 test.java，請注意大小寫)
public class test
{
public static void main(String[] a) {
System.out.println("this is java");
}
}
2、編譯成class
D:\>javac test.java
3、查看一下是否ok
D:\>dir test.*
磁碟區 D 中的磁碟是 本機磁碟
磁碟區序號: 7098-7819
目錄: D:\
2007-03-14 11:38a 414 test.class
2007-03-14 11:37a 120 test.java
2 個檔案 534 位元組
0 個目錄 9,290,588,160 位元組可用
4、進sqlplus了
D:\>sqlplus sys/binhu as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 14 11:41:20 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線至閒置的執行處理.
SQL> startup
ORACLE 執行處理已啟動.
Total System Global Area 201326592 bytes
Fixed Size 1248092 bytes
Variable Size 92275876 bytes
Database Buffers 104857600 bytes
Redo Buffers 2945024 bytes
資料庫已掛載.
資料庫已開啟.
5、建立相關的別名跟設定
SQL> create or replace directory java_dir as 'd:\';
已建立目錄.
SQL> create or replace java class using bfile(java_dir,'test.class');
2 /
已建立 Java.
SQL> create or replace procedure test_java as language java name 'test.main(java.lang.String[])';
2 /
已建立程序.
6、準備測試了
SQL> set serveroutput on size 2000
SQL> call dbms_java.set_output(2000);
已完成呼叫.
SQL> execute test_java;
this is java
已順利完成 PL/SQL 程序.
SQL> call test_java();
this is java
已完成呼叫.
心得:
使用上還算簡單，但真正上線的db會有多少人讓java在上面跑呢，因為這又多了一個變數
到時db掛了，還要多花心力去查看是否是由java引起的問題=.="
除非oracle有大力推行跟公司有規定使用的話，不然我還是不會讓java跑在db上的，呵呵
-bash-3.00$ pwd
/export/home/oracle
-bash-3.00$ vi test.java
"test.java"［新文件］
public class test
{
public static void main(String[] a) {
System.out.println("this is java");
}
}
-bash-3.00$ javac test.java
-bash-3.00$ sqlplus / as sysdba
SQL> create or replace directory java_dir as '/export/home/oracle';
Directory created.
SQL> create or replace java class using bfile(java_dir,'test.class');
Java created.
SQL> create or replace procedure test_java as language java name 'test.main(java.lang.String[])';
Procedure created.
SQL> set serveroutput on size 2000
SQL> call dbms_java.set_output(2000);
已完成呼叫.
SQL> execute test_java;
this is java
已順利完成 PL/SQL 程序.
SQL> call test_java();
this is java
ERROR at line 1:
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:317
Uncaught exception System error: java/lang/UnsupportedClassVersionError
根据http://edu.136z.com/DataBase/27839.html的解决方法
设置了LD_LIBRARY_PATH，
SQL> CALL SGRADE(:sgrade) INTO :output
SQL> print output
调用完全正确
DML语句-MERGE
把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。
在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。
现在ORACLE9i专为这种情况提供了MERGE语句，使这一工作变得异常轻松，Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.
Oracle 10g中MERGE有如下一些改进：
1、UPDATE或INSERT子句是可选的
2、UPDATE和INSERT子句可以加WHERE子句
3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行
首先创建示例表：
create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
1、可省略的UPDATE或INSERT子句
在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,
p.category = np.category
/
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
---------- -------------------- ----------
1501 VIVITAR 35MM ELECTRNCS
1502 OLYMPUS CAMERA ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER TOYS
SQL>
SQL> ROLLBACK;
Rollback complete.
SQL>
在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.
SQL> MERGE INTO products p
2 USING newproducts np
3 ON (p.product_id = np.product_id)
4 WHEN NOT MATCHED THEN
5 INSERT
6 VALUES (np.product_id, np.product_name,
7 np.category);
1 row merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
---------- -------------------- ----------
1501 VIVITAR 35MM ELECTRNCS
1502 OLYMPUS IS50 ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER DVD
1700 WAIT INTERFACE BOOKS
2、带条件的Updates和Inserts子句
你能够添加WHERE子句到UPDATE或INSERT子句中去,
最低0.47元/天 解锁文章
扫一扫
890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
