1 / 70

您可能不了解的 SQL 和 PL/SQL 的 5 个方面

您可能不了解的 SQL 和 PL/SQL 的 5 个方面. Mick Xu Oracle 大学首席讲师. 以下内容旨在概述产品的总体发展方向 。 该内容仅供参考，不可纳入任何合同 。 其内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的依据 。 此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定 。. 议题. 更好的可绑定性 隐式结果集 警告 更详细的统计信息 优化器优化. 更好的可绑定性. 更好的可绑定性. 目前仅有 PL/SQL 类型现在可在原生动态 SQL 中绑定

july
Download Presentation

您可能不了解的 SQL 和 PL/SQL 的 5 个方面

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 您可能不了解的 SQL 和 PL/SQL 的 5 个方面 Mick XuOracle 大学首席讲师

  2. 以下内容旨在概述产品的总体发展方向。该内容仅供参考，不可纳入任何合同。其内容不构成提供任何材料、代码或功能的承诺，并且不应该作为制定购买决策的依据。此处所述有关 Oracle 产品的任何特性或功能的开发、发布以及相应的日程安排均由 Oracle 自行决定。

  3. 议题 • 更好的可绑定性 • 隐式结果集 • 警告 • 更详细的统计信息 • 优化器优化

  4. 更好的可绑定性

  5. 更好的可绑定性 • 目前仅有 PL/SQL 类型现在可在原生动态 SQL 中绑定 • 记录 • 布尔值 • 集合

  6. 历史问题…… ops$tkyte%ORA11GR2> begin 2 execute immediate q'| 3 if :x then null; end if; 4 |' using TRUE; 5 end; 6 / |' using TRUE; * ERROR at line 4: ORA-06550: line 4, column 11: PLS-00457: expressions have to be of SQL types ORA-06550: line 2, column 2: PL/SQL: Statement ignored

  7. 绑定记录阵列 c##tkyte%CDB1> create or replace package my_pkg 2 as 3 type array 4 is table of emp%rowtype 5 index by binary_integer; 6 7 procedure p( p_data in array ); 8 end; 9 / Package created.

  8. 绑定记录阵列 c##tkyte%CDB1> create or replace package body my_pkg … 4 procedure p( p_data in array ) 5 is 6 l_cursorsys_refcursor; 7 l_recemp%rowtype; 8 begin 9 open l_cursor for 10 q'|select * 11 from table(:x) 12 where ename like '%A%' 13 order by empno|' 14 using p_data; 15 16 loop 17 fetch l_cursor into l_rec; 18 exit when l_cursor%notfound; 19 dbms_output.put_line 20 ( l_rec.empno || ', ' || l_rec.ename ); 21 end loop; 22 close l_cursor; 23 end; …

  9. 绑定布尔值 c##tkyte%CDB1> create or replace 2 procedure print_bool( p_bool in boolean ) 3 as 4 begin 5 execute immediate q'| 6 begin 7 if :x 8 then 9 dbms_output.put_line( 'true' ); 10 elsif NOT :x 11 then 12 dbms_output.put_line( 'false' ); 13 else 14 dbms_output.put_line( 'I do not know' ); 15 end if; 16 end;|' 17 using p_bool; 18 end; 19 / Procedure created.

  10. 绑定布尔值 c##tkyte%CDB1> begin 2 print_bool( true ); 3 print_bool( false ); 4 print_bool( null ); 5 end; 6 / true false I do not know PL/SQL procedure successfully completed.

  11. 隐式结果集

  12. 隐式结果集 • 游标变量从 7.2 开始使用 • 用于显式返回结果集 • 某些其他数据库隐式返回结果集 • 导致迁移问题

  13. 隐式结果集 • 两个新 API 调用： • PROCEDURE RETURN_RESULT (rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE); • PROCEDURE RETURN_RESULT (rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE); • TO_CLIENT => true, return to client layer • TO_CLIENT=> false, return to invoker, immediate caller

  14. 隐式结果集 c##tkyte%CDB1> declare 2 c sys_refcursor; 3 begin 4 open c for 5 select * 6 from dept; 7 8 dbms_sql.return_result(c); 9 end; 10 / PL/SQL procedure successfully completed. ResultSet #1 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

  15. 隐式结果集 • 两个新 API 调用： • PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT SYS_REFCURSOR); • PROCEDURE GET_NEXT_RESULT (c IN INTEGER, rc OUT INTEGER); • For PL/SQL processing

  16. 隐式结果集 Connection conn = DriverManager.getConnection(jdbcURL, user, password); try { Statement stmt = conn.createStatement (); stmt.executeQuery ( “begin foo; end;” ); while (stmt.getMoreResults()) { ResultSet rs = stmt.getResultSet(); System.out.println("ResultSet"); while (rs.next()) { /* get results */ } } }

  17. 警告

  18. 警告 • 自 10.1 (2004!) 开始，PL/SQL 编译器始终可以发出警告 • 未广泛采用 • 可以是警告或编译错误

  19. 警告 • 严重：代码可能导致意外操作或错误结果 • 性能：情况可能导致性能问题 • 信息：编写的代码不会出错或不慢，但不是好代码

  20. 严重 c##tkyte%CDB1> alter session set plsql_warnings='enable:severe'; Session altered. c##tkyte%CDB1> create or replace procedure p 2 as 3 procedure substr 4 is 5 begin 6 null; 7 end; 8 begin 9 null; 10 end; 11 / SP2-0804: Procedure created with compilation warnings c##tkyte%CDB1> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 1/1 PLW-05018: unit P omitted optional AUTHID clause; default value DEFINER used 3/12 PLW-05004: identifier SUBSTR is also declared in STANDARD or is a SQL builtin

  21. 性能 c##tkyte%CDB1> alter session set plsql_warnings='enable:performance'; Session altered. c##tkyte%CDB1> create or replace procedure p 2 as 3 l_string varchar2(5); 4 begin 5 for x in (select * from emp where empno = l_string) 6 loop 7 null; 8 end loop; 9 end; 10 / SP2-0804: Procedure created with compilation warnings c##tkyte%CDB1> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/44 PLW-07204: conversion away from column type may result in sub-optimal query plan

  22. 信息 c##tkyte%CDB1> alter session set plsql_warnings='enable:informational'; Session altered. c##tkyte%CDB1> create or replace procedure p 2 as 3 begin 4 if (null is not null) 5 then 6 dbms_output.put_line( 'hello world' ); 7 end if; 8 end; 9 / SP2-0804: Procedure created with compilation warnings c##tkyte%CDB1> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/3 PLW-06002: Unreachable code

  23. 我当前的偏好…… c##tkyte%CDB1> alter session set plsql_warnings='enable:all,disable:5018,error:6009,error:7204'; Session altered. c##tkyte%CDB1> create or replace procedure p 2 as 3 begin 4 dbms_output.put_line( 'hello world' ); 5 exception 6 when others 7 then null; 8 end; 9 / Warning: Procedure created with compilation errors. c##tkyte%CDB1> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/6 PLS-06009: procedure "P" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

  24. 警告 • 可采用以下任一种方法设置 • 系统级别ALTER SYSTEM • 会话级别ALTER SESSION • 逐个单元ALTER PROCEDURE P COMPILE PLSQL_WARNINGS=‘…’ REUSE SETTINGS;

  25. 更详细的统计信息

  26. 更详细的统计信息 • 计划错误 => 基数错误 • 管道功能 => 默认错误的基数 • 我们可以做得更好 — 5 种可以更好地统计管道功能的方法 • http://www.oracle-developer.net/display.php?id=427最好写入Adrian Billington提供的部分内容

  27. #1 基数提示（9i 及以上版本，未存档） c##tkyte%CDB1> create or replace type str2tblType as table of varchar2(30) 2 / Type created. c##tkyte%CDB1> create or replace 2 function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) 3 return str2tblType 4 PIPELINED 5 as 6 l_str long default p_str || p_delim; 7 l_n number; 8 begin 9 loop 10 l_n := instr( l_str, p_delim ); 11 exit when (nvl(l_n,0) = 0); 12 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) ); 13 l_str := substr( l_str, l_n+1 ); 14 end loop; 15 end; 16 / Function created.

  28. #1 基数提示（9i 及以上版本，未存档） c##tkyte%CDB1> variable x varchar2(15) c##tkyte%CDB1> exec :x := '1,2,3,a,b,c' PL/SQL procedure successfully completed. c##tkyte%CDB1> select * from table(str2tbl(:x)); COLUMN_VALUE ------------------------------ 1 2 3 a b c 6 rows selected.

  29. #1 基数提示（9i 及以上版本，未存档） c##tkyte%CDB1> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------ SQL_ID ddk1tv9s5pzq5, child number 0 ------------------------------------- select * from table(str2tbl(:x)) Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------

  30. #1 基数提示（9i 及以上版本，未存档） c##tkyte%CDB1> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID bd2f8rh30z3ww, child number 0 ------------------------------------- select /*+ cardinality(sq 10) */ * from table(str2tbl(:x)) sq Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 10 | 20 | 29 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- 13 rows selected.

  31. #1 基数提示（9i 及以上版本，未存档） select * from t where object_name in (select * from table(str2tbl(:x))) Plan hash value: 1957688699 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 431 (100)| | |* 1 | HASH JOIN RIGHT SEMI | | 2 | 232 | 431 (1)| 00:00:01 | | 2 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T | 87322 | 9721K| 401 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"=VALUE(KOKBF$))

  32. #1 基数提示（9i 及以上版本，未存档） select * from t where object_name in (select /*+ cardinality(sq 10) */ * from table(str2tbl(:x)) sq) Plan hash value: 3519658119 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 44 (100)| | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 17 | 1972 | 44 (0)| 00:00:01 | | 3 | SORT UNIQUE | | 10 | 20 | 29 (0)| 00:00:01 | | 4 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 10 | 20 | 29 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T_IDX | 2 | | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID | T | 2 | 228 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("OBJECT_NAME"=VALUE(KOKBF$))

  33. #2 OPT_ESTIMATE 提示（10g 及以上版本，未存档，由 SQL 配置文件使用） c##tkyte%CDB1> select 10/8168 from dual; 10/8168 ---------- .00122429 select /*+ opt_estimate(table, sq, scale_rows=0.00122429) */ * from table(str2tbl(:x)) sq Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 10 | 20 | 29 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------

  34. #3 可扩展的优化器（10g 及以上版本） c##tkyte%CDB1> CREATE OR REPLACE TYPE str2tbl_stats 2 AS OBJECT 3 ( 4 x NUMBER, 5 6 STATIC FUNCTION ODCIGetInterfaces 7 ( p_interfaces OUT SYS.ODCIObjectList 8 ) RETURN NUMBER, 9 10 STATIC FUNCTION ODCIStatsTableFunction 11 ( p_function IN SYS.ODCIFuncInfo, 12 p_stats OUT SYS.ODCITabFuncStats, 13 p_args IN SYS.ODCIArgDescList, 14 p_str IN varchar2 default NULL, 15 p_delim IN varchar2 default ',' 16 ) RETURN NUMBER 17 ); 18 / Type created.

  35. #3 可扩展的优化器（10g 及以上版本） c##tkyte%CDB1> CREATE or replace TYPE BODY str2tbl_stats 2 AS 3 4 STATIC FUNCTION ODCIGetInterfaces ( 5 p_interfaces OUT SYS.ODCIObjectList 6 ) RETURN NUMBER IS 7 BEGIN 8 p_interfaces := 9 SYS.ODCIObjectList( SYS.ODCIObject ('SYS', 'ODCISTATS2')); 10 RETURN ODCIConst.success; 11 END ODCIGetInterfaces; 12 13 STATIC FUNCTION ODCIStatsTableFunction ( 14 p_function IN SYS.ODCIFuncInfo, 15 p_stats OUT SYS.ODCITabFuncStats, 16 p_args IN SYS.ODCIArgDescList, 17 p_str IN varchar2 default NULL, 18 p_delim IN varchar2 default ',' 19 ) RETURN NUMBER IS 20 BEGIN 21 p_stats := SYS.ODCITabFuncStats 22 ( nvl( length(p_str)-length(replace(p_str,p_delim,''))+1, 10) ); 23 RETURN ODCIConst.success; 24 END ODCIStatsTableFunction; 25 END; 26 /

  36. #3 可扩展的优化器（10g 及以上版本） c##tkyte%CDB1> associate statistics with functions str2tbl using str2tbl_stats; Statistics associated.

  37. #3 可扩展的优化器（10g 及以上版本） Select * from table ( str2tbl( :x||'',',') ) Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 10 | 20 | 29 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------

  38. #3 可扩展的优化器（10g 及以上版本） Select * from table ( str2tbl( 'a,b,c',',') ) Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 29 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 3 | 6 | 29 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------

  39. #4 动态采样（11gR1 及以上版本） select /*+ dynamic_sampling( sq, 2 ) */ * from table( str2tbl(:x,',') ) sq Plan hash value: 2407808827 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 11 (100)| | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 6 | 12 | 11 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) (must be hinted)

  40. #5 基数反馈（11gR2 及以上版本） with sq as (select /*+ materialize */ * from table( str2tbl( :x ) ) ) select * from sq Plan hash value: 630596523 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 32 (100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | | 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0)| 00:00:01 | | 4 | VIEW | | 8168 | 135K| 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6652_20F4CB | 8168 | 16336 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------- 18 rows selected.

  41. #5 基数反馈（11gR2 及以上版本） with sq as (select /*+ materialize */ * from table( str2tbl( :x ) ) ) select * from sq Plan hash value: 630596523 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |™ ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 32 (100)| | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | | 3 | COLLECTION ITERATOR PICKLER FETCH| STR2TBL | 8168 | 16336 | 29 (0)| 00:00:01 | | 4 | VIEW | | 6 | 102 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6653_20F4CB | 6 | 12 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------- Note ----- - cardinality feedback used for this statement 22 rows selected.

  42. 优化器优化

  43. 优化器优化 • 您会认为这是关于 SQL 的，但实际上不是 • 自 10.1 起，PL/SQL 已采用优化编译器 • “在较少情况下，PL/SQL 可能比预期更早生成异常，或根本不生成”（PL/SQL 语言首选项）

  44. 优化器优化 • 三级 — PLSQL_OPTIMIZE_LEVEL 1: 没有重新安排代码，代码“保持原样” 2: 可能重新安排代码，添加了隐式阵列提取等许多优化 3: 重新安排激进式代码

  45. 优化器优化 c##tkyte%CDB1> alter session set plsql_optimize_level=1; Session altered. c##tkyte%CDB1> create or replace procedure p 2 as 3 begin 4 for x in ( select * from t ) 5 loop 6 null; 7 end loop; 8 end; 9 / Procedure created. c##tkyte%CDB1> exec dbms_monitor.session_trace_enable; PL/SQL procedure successfully completed. c##tkyte%CDB1> exec p PL/SQL procedure successfully completed.

  46. 优化器优化 SELECT * FROM T call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 87323 0.81 0.85 1438 87325 0 87322 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 87325 0.81 0.85 1438 87325 0 87322

  47. 优化器优化 c##tkyte%CDB1> alter procedure p compile plsql_optimize_level=2; Procedure altered. c##tkyte%CDB1> exec dbms_monitor.session_trace_enable; PL/SQL procedure successfully completed. c##tkyte%CDB1> exec p PL/SQL procedure successfully completed. SELECT * FROM T call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 874 0.25 0.28 1438 2303 0 87322 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 876 0.25 0.28 1438 2303 0 87322

  48. 优化器优化，代码重新安排 c##tkyte%CDB1> alter session set Plsql_Warnings = 'Enable:All' 2 / Session altered. c##tkyte%CDB1> alter session set Plsql_Optimize_Level = 2 2 / Session altered.

  49. 优化器优化 c##tkyte%CDB1> create or replace procedure p 2 authid definer 3 as 4 function Is_Number(x in varchar2) return varchar2 5 is 6 n number; 7 begin 8 n := To_Number(x); 9 return 'Y'; 10 exception 11 when value_error then 12 return 'N'; 13 end Is_Number; 14 15 begin 16 DBMS_Output.Put_Line(Is_Number('1')); 17 DBMS_Output.Put_Line(Is_Number('a')); 18 end p; 19 / Procedure created.

More Related