精通PL/SQL实践指南

5星 · 超过95%的资源需积分: 11 51 浏览量更新于2024-07-28 收藏 5.17MB PDF 举报

"Expert PL SQL Practices" 是一本关于高级PL/SQL编程实践的专业书籍，旨在帮助读者提升在Oracle数据库环境中使用PL/SQL的技巧和策略。这本书由Riyaj Shamsudeen撰写，并包含多个章节，涵盖了从避免使用某些功能到性能优化、错误处理等多个重要主题。作者首先提醒读者，虽然PL/SQL是一个强大的工具，但必须明智地选择使用哪些特性。第1章"不要使用"（Do Not Use）可能探讨了某些可能导致代码效率低下或难以维护的PL/SQL特性，鼓励读者避免这些陷阱。第2章"动态SQL：处理未知"（Dynamic SQL: Handling the Unknown）深入介绍了如何在不知道具体SQL语句结构时进行编程，这对于处理灵活的数据查询和报告生成至关重要。动态SQL是PL/SQL中的一个强大工具，但也需要谨慎使用，以防止SQL注入等安全问题。第3章"PL/SQL与并行处理"（PL/SQL and Parallel Processing）讨论了如何利用Oracle数据库的并行执行能力来加速PL/SQL程序，提高大数据量处理的效率。第4章"警告与条件编译"（Warnings and Conditional Compilation）讲解了如何利用编译警告和条件编译指令来增强代码的健壮性和适应性。第5章"PL/SQL单元测试"（PL/SQL Unit Testing）介绍了编写和执行单元测试的重要性，以及如何有效地对PL/SQL代码进行测试，确保其质量和稳定性。第6章"批量SQL操作"（Bulk SQLOperations）阐述了如何利用BULK COLLECT和FORALL等特性进行高效的批量数据处理，减少数据库交互次数，提高性能。第7章"了解你的代码"（Know Your Code）强调了代码审查和理解代码结构对于维护和优化的重要性。第8章"面向契约的编程"（Contract-Oriented Programming）可能涉及了如何通过接口和规范确保代码的互操作性和可维护性。第9章"从SQL调用PL/SQL"（PL/SQL from SQL）讲解了如何在SQL查询和存储过程中嵌入PL/SQL代码，以实现更复杂的业务逻辑。第10章"选择正确的游标"（Choosing the Right Cursor）讨论了何时使用哪种类型的游标，以及如何优化游标处理以提高性能。第11章"大型PL/SQL编程"（PL/SQL Programming in the Large）探讨了在大型项目中组织和管理PL/SQL代码的最佳实践。第12章"进化式数据建模"（Evolutionary Data Modeling）可能介绍了如何随着业务需求的变化逐步调整数据库模型。第13章"性能剖析"（Profiling for Performance）提供了分析和优化PL/SQL代码性能的方法。第14章"编码规范和错误处理"（Coding Conventions and Error Handling）强调了遵循一致的编码标准和有效的错误处理策略对于代码质量和可读性的重要性。第15章"依赖和失效"（Dependencies and Invalidations）讲解了如何管理对象间的依赖关系，以及当依赖的对象发生变化时，如何处理由此引发的失效问题。最后，书后附有索引，方便读者查找和参考特定主题。这本书是一本全面的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页未读，继续阅读

ZZABCDEF007

粉丝: 0
资源: 11

精通PL/SQL实践指南

Expert PL SQL Practices for Oracle Developers and DBAs 无水印pdf

Oracle PLSQL实战(Expert PLSQL Practices for Oracle Developers and DBAs)ch01

Oracle PL SQL Best Practices(2nd) 无水印pdf

《Expert PL/SQL Practices》PDF英文原版免费下载

Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码

精通PL-SQL实践指南

专家级PL/SQL实践指南

Quest Toad for Oracle 中文手册：提升 PL/SQL 开发与数据库管理效率

ExpertPLSQLPracticesFreePdfBook.pdf 英文原版

ta-lib-0.5.1-cp312-cp312-win32.whl

最新资源