深入理解PL/SQL：技术与实践

需积分: 10 114 浏览量更新于2024-07-29 收藏 6.54MB PDF 举报

"本书主要探讨了PL/SQL的各种技术和最佳实践，旨在帮助读者深入理解和有效利用这一强大的数据库编程语言。作者Riyaj Shamsudeen提醒读者，虽然PL/SQL非常强大，但并非所有场景都适合使用。" 在《PL/SQL技术篇》这本书中，作者深入讲解了多个关键知识点，涵盖了从基础到高级的应用技巧，对于任何想要提升PL/SQL编程能力的IT专业人员来说，都是一份宝贵的资源。以下是对各章节内容的详细概述： 1. **Chapter 1: Do Not Use** - 这一章强调了在某些情况下不应使用PL/SQL，比如当简单SQL语句就能解决问题时，过度使用PL/SQL可能会引入不必要的复杂性和性能问题。 2. **Chapter 2: Dynamic SQL: Handling the Unknown** - 动态SQL是处理不确定数据查询的关键，本章介绍了如何在PL/SQL中安全有效地使用动态SQL，以及避免SQL注入等安全风险。 3. **Chapter 3: PL/SQL and Parallel Processing** - 并行处理在大数据交互中扮演重要角色，本章讲解了如何利用PL/SQL进行并行处理，提高处理效率。 4. **Chapter 4: Warnings and Conditional Compilation** - 条件编译和警告管理是优化代码质量和可维护性的关键，本章介绍如何使用这些工具来改进代码。 5. **Chapter 5: PL/SQL Unit Testing** - 单元测试是确保代码质量的重要手段，本章教导读者如何为PL/SQL编写和执行单元测试。 6. **Chapter 6: Bulk SQL Operations** - 批量SQL操作可以显著提高数据处理速度，本章详细讨论了如何高效地使用PL/SQL处理大量数据。 7. **Chapter 7: Know Your Code** - 了解代码结构和设计是优化性能的基础，本章提供了分析和理解PL/SQL代码的技巧。 8. **Chapter 8: Contract-Oriented Programming** - 合同导向编程强调接口和约定，本章讲解了如何在PL/SQL中实现这一编程范式，提高代码的可复用性和可维护性。 9. **Chapter 9: PL/SQL from SQL** - 从SQL调用PL/SQL函数和过程是常见的交互方式，本章介绍了如何在SQL语句中嵌入PL/SQL逻辑。 10. **Chapter 10: Choosing the Right Cursor** - 游标选择对性能影响很大，本章指导读者根据需求选择合适的游标类型。 11. **Chapter 11: PL/SQL Programming in the Large** - 大型PL/SQL项目管理涉及模块化、错误处理和性能监控，本章提供了在大规模项目中使用PL/SQL的最佳实践。 12. **Chapter 12: Evolutionary Data Modeling** - 进化式数据建模允许随着业务需求的变化逐步调整模型，本章讲述了如何在PL/SQL中实现这种灵活的数据建模方法。 13. **Chapter 13: Profiling for Performance** - 性能剖析是找出瓶颈的关键，本章介绍了如何对PL/SQL代码进行性能分析。 14. **Chapter 14: Coding Conventions and Error Handling** - 编程规范和错误处理对于代码可读性和健壮性至关重要，本章探讨了建立良好编码习惯和处理异常的方法。 15. **Chapter 15: Dependencies and Invalidations** - 依赖管理和失效机制是数据库管理的关键部分，本章讲解了如何处理PL/SQL对象间的依赖关系以及如何应对对象失效。《PL/SQL技术篇》是一本全面覆盖PL/SQL编程技巧和最佳实践的指南，无论你是初学者还是经验丰富的开发者，都能从中受益匪浅，提升你的数据库编程能力。通过深入学习和应用书中的知识点，你将能够更好地应对各种数据交互挑战，编写出更高效、更可靠的PL/SQL代码。

CHAPTER 1 ■

DO NOT USE

BEGIN

FOR c1 IN (

SELECT s.prod_id, s.cust_id,s.time_id,

c.cust_first_name, c.cust_last_name,

s.amount_sold

FROM sales s,

customers c

WHERE s.cust_id = c.cust_id and

s.amount_sold> 100)

LOOP

$IF $$debug_on $THEN

IF c1.cust_first_name IS NOT NULL THEN

r1 := log_entry ('first_name is not null ', l_debug );

END IF;

IF c1.cust_last_name IS NOT NULL THEN

r1 := log_entry ('Last_name is not null ', l_debug);

END IF;

$END

null;

END LOOP;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34

The problem of invoking functions unnecessarily tends to occur frequently in programs copied from

another template program and then modified. Watch for this problem. If a function doesn’t need to be

called, avoid calling it.

INTERPRETED VS. NATIVE COMPILATION

PL/SQL code, by default, executes as an interpreted code. During PL/SQL compilation, code is converted to

an intermediate format and stored in the data dictionary. At execution time, that intermediate code is

executed by the engine.

Oracle Database version 9i introduces a new feature known as native compilation. PL/SQL code is

compiled into machine instruction and stored as a shared library. Excessive function execution might have

less impact with native compilation as modern compilers can inline the subroutine and avoid the

instruction jump.

Costly Function Calls

If the execution of a function consumes a few seconds of elapsed time, then calling that function in a

loop will result in poorly performing code. You should optimize frequently executed functions to run as

efficiently as possible.

CHAPTER 1 ■

DO NOT USE

You should also understand that function-based indexes have a cost. INSERT statements and UPDATE

statements (that update the time_id column) will incur the cost of calling the function and maintaining

the index. Carefully weigh the cost of function execution in DML operations against the cost of function

execution in SELECT statement to choose the cheaper option.

■ Note From Oracle Database version 11g onwards, you can create a virtual column, and then create an index

on that virtual column. The effect of an indexed virtual column is the same as that of a function-based index. An

advantage of virtual columns over function-based index is that you can partition the table using a virtual column,

which is not possible with the use of just function based indexes.

The function result_cache, available from Oracle Database version 11g, is another option to tune

the execution of costly PL/SQL functions. Results from function execution are remembered in the result

cache allocated in the Shared Global Area (SGA) of an instance. Repeated execution of a function with

the same parameter will fetch the results from the function cache without repeatedly executing the

function. Listing 1-13 shows an example of functions utilizing result_cache to improve performance: the

SQL statement completes in 0.81 seconds.

Listing 1-13. Functions with Result_cache

DROP INDEX compute_epoch_fbi;

CREATE OR REPLACE FUNCTION calculate_epoch (d in date)

RETURN NUMBER DETERMINISTIC RESULT_CACHE IS

l_epoch number;

BEGIN

l_epoch := (d - TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))

* 24 *60 *60 ;

RETURN l_epoch;

END calculate_epoch;

SELECT /*+ cardinality (10) */ max( calculate_epoch (s.time_id)) epoch

FROM sales s

WHERE s.amount_sold> 100 and

calculate_epoch (s.time_id) between 1000000000 and 1100000000;

EPOCH

----------

1009756800

Elapsed: 00:00:00.81

In summary, excessive function execution leads to performance issues. If you can’t reduce or

eliminate function execution, you may be able to employ function-based indexes or result_cache as a

short term fix in order to minimize the impact from function invocation.

CHAPTER 1 ■ DO NOT USE

Database Link Calls

Excessive database link-based calls can affect application performance. Accessing a remote table or

modifying a remote table over a database link within a loop is not a scalable approach. For each access

to a remote table, several SQL*Net packets are exchanged between the databases involved in the

database link. If the databases are located in geographically separated datacenters or, worse, across the

globe, then the waits for SQL*Net traffic will result in program performance issues.

In Listing 1-14, for every row returned from the cursor, the customer table in the remote database is

accessed. Let’s assume that the round trip network call takes 100ms, so 1 million round trip calls will

take 27 hours approximately to complete. A response time of 100ms between the databases located in

different parts of the country is not uncommon.

Listing 1-14. Excessive Database Link Calls

DECLARE

V_customer_name VARCHAR2(32);

BEGIN

...

FOR c1 IN (SELECT …)

LOOP

...

SELECT customer_name

INTO v_customer_name

FROM customers@remotedb

WHERE account_id = c1.account_id;

...

END LOOP;

END;

Judicial use of materialized views can be used to reduce network round trip calls during program

execution. In the case of Listing 1-14, the customer table can be created as a materialized view. Refresh

the materialized view before program execution and access that materialized view in the program.

Materializing the table locally reduces the number of SQL*Net round trip calls. Of course, as an

Application Designer, you need to compare the cost of materializing the whole table versus the cost of

accessing a remote table in a loop, and choose an optimal solution.

Rewriting the program as a SQL statement with a join to a remote table is another option. The query

optimizer in Oracle Database can optimize such statements so as to reduce the SQL*Net trip overhead.

For this technique to work, you should rewrite the program so that SQL statement is executed once and

probably not in a loop.

Materializing the data locally or rewriting the code as a SQL statement with a remote join are the

initial steps to tune the program in Listing 1-14. However, if you are unable to do even these things, there

is a workaround. As an interim measure, you can convert the program to use a multi-process

architecture. For example, process #1 will process the customers in the range of 1 to 100,000, process #2

will process the customers in the range of 100,001 to 200,000, and so on. Apply this logic to the example

program by creating 10 processes, and you can reduce the total run time of the program to 2.7 hours

approximately. Use of DBMS_PARALLEL_EXECUTE is another option to consider for splitting the code in to

parallel processing.

CHAPTER 1 ■

DO NOT USE

Excessive Use of Triggers

Triggers are usually written in PL/SQL, although you can write trigger code in Java as well. Excessive

triggers are not ideal for performance reasons. Row changes are performed in the SQL engine and

triggers are executed in the PL/SQL engine. Once again, you encounter the dreaded context-switch

problem.

In some cases, triggers are unavoidable. For example, complex business validation in a trigger can’t

be avoided. In those scenarios, you should write that type of complex validation in PL/SQL code. You

should avoid overusing triggers for simple validation. For example, use check constraints rather than a

trigger to check the list of valid values for a column.

Further, avoid using multiple triggers for the same trigger action. Instead of writing two different

triggers for the same action, you should combine them into one so as to minimize the number of context

switches.

Excessive Commits

It is a not uncommon to see commits after every row inserted or modified (or deleted) in a PL/SQL loop.

The coding practice of committing after every row will lead to slower program execution. Frequent

commits generate more redo, require Log Writer to flush the contents of log buffer to log file frequently,

can lead to data integrity issues, and consume more resources. The PL/SQL engine is optimized to

reduce the effect of frequent commits, but there is no substitute for a well written code when it comes to

reducing commits.

You should commit only at the completion of a business transaction. If you commit earlier than

your business transaction boundary, you can encounter data integrity issues. If you must commit to

improve restartability, consider batch commits. For example, rather than commit after each row, it’s

better to do batch commit every 1000 or 5000 rows (the choice of batch size depends upon your

application). Fewer commits will reduce the elapsed time of the program. Furthermore, fewer commits

from the application will also improve the performance of the database.

Excessive Parsing

Don’t use dynamic SQL statements in a PL/SQL loop as doing so will induce excessive parsing issues.

Instead, reduce amount of hard parsing through the use of bind variables.

In Listing 1-15, the customers table is accessed to retrieve customer details, passing cust_id from

cursor c1. A SQL statement with literal values is constructed and then executed using the native dynamic

SQL EXECUTE IMMEDIATE construct. The problem is that for every unique row retrieved from cursor c1, a

new SQL statement is constructed and sent to the SQL engine for execution.

Statements that don’t exist in the shared pool when you execute them will incur a hard parse.

Excessive hard parsing stresses the library cache, thereby reducing the application’s scalability and

concurrency. As the number of rows returned from cursor c1 increases, the number of hard parses will

increase linearly. This program might work in a development database with a small number of rows to

process, but the approach could very well become a problem in a production environment.

剩余507页未读，继续阅读

add166

粉丝: 2

深入理解PL/SQL：技术与实践

Oracle PL/SQL best practice

PL/SQL Developer 8.0.4.1514注册码

PL/SQL编程技术详解

数据库资料，PL/SQL

PL/SQL学习笔记5

Oracle PL/SQL的大量操作

精通Oracle 10g Pl/SQL编程

Oracle PL/SQL分区技术详解

Oracle PL/SQL性能调优技术详解

Oracle基础与SQL/PL/SQL详解

最新资源