oraclesql语句暗示hints资源-CSDN下载

5星 · 超过95%的资源需积分: 50 58 浏览量 2008-11-26 22:02:48 上传评论 1 收藏 166KB PDF 举报

### Oracle SQL 语句暗示 (Hints) 全面解析 #### 一、引言在当前企业级应用环境中，为了实现成本控制与性能优化的目标，高效编写应用程序变得尤为重要。这不仅涉及程序的设计与实现，还包括运行时资源的有效利用。Oracle SQL 作为业界广泛使用的数据库查询语言之一，在这一过程中扮演着关键角色。为了提升SQL语句的执行效率，Oracle 提供了一系列称为“暗示”（Hints）的功能。本文将深入探讨 Oracle SQL 语句暗示的相关知识点，帮助开发人员更好地理解和运用这些强大工具。 #### 二、Oracle SQL 暗示概述 **Oracle SQL 暗示** 是一种特殊语法，用于向数据库优化器提供有关如何处理特定 SQL 语句的指导性建议。这些暗示可以用来控制执行计划，从而改善查询性能或解决优化器选择错误的问题。暗示分为多种类型，包括但不限于访问方法暗示、连接方法暗示等。 ##### 2.1 访问方法暗示访问方法暗示主要用于指定如何访问表或索引数据。常见的访问方法暗示包括： - **`/*+ INDEX (表名索引名) */`**：强制使用指定的索引来访问表。 - **`/*+ FULL (表名) */`**：强制全表扫描。 - **`/*+ USE_NL (表名) */`**：强制使用嵌套循环连接方法。 - **`/*+ NO_INDEX (表名) */`**：禁止使用任何索引，只能进行全表扫描。 ##### 2.2 连接方法暗示连接方法暗示用于控制不同表之间的连接方式，常用的连接方法暗示有： - **`/*+ HASH_JOIN (表1 表2) */`**：使用哈希连接方法。 - **`/*+ MERGE_JOIN (表1 表2) */`**：使用合并排序连接方法。 - **`/*+ NESTED_LOOPS (表1 表2) */`**：使用嵌套循环连接方法。 - **`/*+ NO_PARALLEL (表1 表2) */`**：禁止并行执行。 ##### 2.3 并行执行暗示并行执行暗示用于控制查询的并行程度，有助于提高大数据量处理时的性能。常用的并行执行暗示包括： - **`/*+ PARALLEL (表名线程数) */`**：指定并行执行的线程数量。 - **`/*+ NO_PARALLEL (表名) */`**：禁止并行执行。 ##### 2.4 其他暗示此外，Oracle SQL 还支持其他类型的暗示，如分区访问暗示、统计信息暗示等。这些暗示有助于进一步细化对执行计划的控制，提高查询效率。 #### 三、实际应用场景 ##### 3.1 强制使用特定索引当优化器未能正确识别应使用的最佳索引时，可以通过添加 **`/*+ INDEX (表名索引名) */`** 来强制使用特定索引，从而优化查询性能。 ##### 3.2 控制连接顺序在复杂的多表连接查询中，使用 **`/*+ ORDERED */`** 可以控制连接顺序，确保按照指定的顺序执行连接操作，避免不必要的数据重新排序。 ##### 3.3 提升并行处理能力对于大量数据处理任务，通过设置 **`/*+ PARALLEL (表名线程数) */`**，可以在多线程环境下并行处理数据，显著提高查询速度。 #### 四、监控与调优 PL/SQL 在 PL/SQL 程序开发中，除了合理运用 SQL 暗示外，还需要关注程序本身的监控与调优。例如，可以利用 Oracle 提供的 DBMS_PROFILER 包来监控 PL/SQL 的执行情况，通过分析输出结果来定位性能瓶颈；使用 DBMS_SQL_TRACE 包来跟踪 SQL 执行过程中的详细信息，进而优化 SQL 查询。 #### 五、总结 Oracle SQL 暗示为开发人员提供了强大的工具，能够直接影响到 SQL 语句的执行效率。通过对这些暗示的合理使用，不仅可以显著提升应用程序的性能，还能有效地降低系统资源消耗。然而，需要注意的是，虽然暗示功能非常有用，但在实际应用中仍需谨慎对待，避免过度依赖而忽视了优化器本身的智能性。最终目的是结合优化器的选择与暗示的辅助，共同达到最佳的查询执行效果。

资源推荐

资源详情

资源评论

www.odtug.com ODTUG 2002

ONITORING AND

UNING

PL/SQL

Michael R. Ault, TUSC

Introduction

With the requirements for cost reduction all companies are facing in todays business environment it is critical that

applications be written as efficiently as possible. This requirement for application efficiency means that all parts of an

application, the design, implementation and operation, must be optimized for minimum consumption of available resources.

In PL/SQL the developer must be careful to use optimized SQL statements, proper coding techniques and the proper

specialized features for the particular package, procedure or function they are building. To often the PL/SQL developer may

not be aware of the latest features, the optimal technique or best method for SQL optimization.

In this presentation I will provide information on methods to monitor and tune PL/SQL programs. If the techniques are

utilized properly the PL/SQL developer should see dramatic performance improvements in their code that translates to more

efficiency in the overall application and lower costs.

Monitoring PL/SQL

PL/SQL can be problematic to monitor. As a procedural language it lacks interactive capabilities present in 3GL languages.

Components of PL/SQL such as the DBMS_OUTPUT and UTL_FILE packages provided limited IO capabilities with

DBMS_OUTPUT only delivering messages to the users screen after the completion of PL/SQL processing and UTL_FILE

providing text output to files that then must be read or otherwise displayed via a secondary process. More complex

instrumenting of PL/SQL can also be accomplished using DBMS_DEBUG.

More complex monitoring can be built in with the DBMS_PIPE and DBMS_ALERT type packages but again only by

creating secondary processes that can intercept and process the resulting pipe messages and alerts.

Programs can also be written such that debug code is built into all packages and procedures allowing the developers to switch

on and off the debug capabilities built into Oracle, limited as those may be, however, these debug options may involve

recompilation of the code using the debug qualifier.

Third party programs provide the capability to execute PL/SQL in a stepwise manner and provide syntactical analysis to help

ensure proper coding techniques are used, but the quality of these programs ranges from excellent and expensive to poor and

cheap, but not all expensive programs are excellent and not all cheap programs are poor.

Oracle has also provided the internal views and tables that can be utilized to see the statistics involved with package and SQL

execution, every PL/SQL developer needs to be aware of the tables and views to use to see PL/SQL related statistics.

Using V$ Tables and DBA Views

Generally speaking the PL/SQL developer may not have access to all of the internal tables and views required to monitor

PL/SQL performance. As a minimum the developer needs SELECT access on:

•

V$DB_OBJECT_CACHE

•

V$SQLAREA

•

V$SQLTEXT

•

DBA_SOURCE, ALL_SOURCE or USER_SOURCE

V$DB_OBJECT_CACHE

This view provides execution information on PL/SQL objects that are currently in use, or have recently been used. The

developer can use this view to determine the usage characteristics of the PL/SQL program units for an application. The

V$DB_OBJECT_CACHE view has the structure shown in Figure 1 on Oracle9i.

Name Null? Type

----------------------------------------- -------- --------------

OWNER VARCHAR2(64)

NAME VARCHAR2(1000)

DB_LINK VARCHAR2(64)

NAMESPACE VARCHAR2(28)

Monitoring and Tuning PL/SQL

Ault

www.odtug.com ODTUG 2002

TYPE VARCHAR2(28)

SHARABLE_MEM NUMBER

LOADS NUMBER

EXECUTIONS NUMBER

LOCKS NUMBER

PINS NUMBER

KEPT VARCHAR2(3)

Figure 1: Contents of the V$DB_OBJECT_CACHE View

The important columns that a developer should pay attention to in the V$DB_OBJECT_CACHE internal view are:

sharable_mem, loads, executions locks and pins. The sharable_mem column tells the developer the amount of sharable

memory the current PL/SQL object occupies. Ideally this should be as small a value as possible. The loads column tells how

many times this particular PL/SQL object has been reloaded from disk storage. The executions column tells the developer

how many times this PL/SQL object has been executed. The locks column tells the developer how users are currently locking

the PL/SQL object. The pins column tells you how many users are currently accessing the object.

If the DBA is doing their job properly any object that shows a large number of executions and has a large memory footprint

will be kept. If an object is kept that means it is not subject to the LRU aging algorithm and cannot be removed from memory

with normal operation of the database. Keeping an object prevents reloads and performance hits due to the required reloading

and reparsing. Objects are kept using the DBMS_SHARED_POOL package.

V$SQLAREA

The V$SQLAREA view provides information about the SQL that is being currently executed or has been executed for the

database. The V$SQLAREA view shows all SQL, even if it has been embedded into PL/SQL code or is sent to the database

via an external program.

The V$SQLAREA and its companion view V$SQLTEXT provide the text and statistics for each piece of SQL code. The

contents of the V$SQLAREA view are shown in Figure 2.

Name Null? Type

----------------------------------------- -------- --------------

SQL_TEXT VARCHAR2(1000)

SHARABLE_MEM NUMBER

PERSISTENT_MEM NUMBER

RUNTIME_MEM NUMBER

SORTS NUMBER

VERSION_COUNT NUMBER

LOADED_VERSIONS NUMBER

OPEN_VERSIONS NUMBER

USERS_OPENING NUMBER

EXECUTIONS NUMBER

USERS_EXECUTING NUMBER

LOADS NUMBER

FIRST_LOAD_TIME VARCHAR2(19)

INVALIDATIONS NUMBER

PARSE_CALLS NUMBER

DISK_READS NUMBER

BUFFER_GETS NUMBER

ROWS_PROCESSED NUMBER

COMMAND_TYPE NUMBER

OPTIMIZER_MODE VARCHAR2(25)

PARSING_USER_ID NUMBER

PARSING_SCHEMA_ID NUMBER

KEPT_VERSIONS NUMBER

ADDRESS RAW(4)

HASH_VALUE NUMBER

MODULE VARCHAR2(64)

MODULE_HASH NUMBER

ACTION VARCHAR2(64)

ACTION_HASH NUMBER

SERIALIZABLE_ABORTS NUMBER

CPU_TIME NUMBER

ELAPSED_TIME NUMBER

IS_OBSOLETE VARCHAR2(1)

Figure 2: Contents of the V$SQLAREA View

Monitoring and Tuning PL/SQL

Ault

www.odtug.com ODTUG 2002

Notice that the V$SQLAREA only shows the first 1000 bytes of any SQL. If the SQL is longer than 1000 bytes the

V$SQLTEXT view should be used to see the entire text. The critical statistics in this view for tuning code are

•

SHARABLE_MEM -- Amount o sharable memory used by the SQL statement

•

PERSISTENT_MEM -- Amount of persistent memory used by the statement

•

RUNTIME_MEM -- Amount of runtime memory used by the statement

•

SORTS -- Total sorts across all executions

•

EXECUTIONS -- Total executions

•

PARSE_CALLS -- Total parse calls across all executions

•

DISK_READS -- Total disk reads across all executions

•

BUFFER_GETS -- Total buffer gets across all executions

•

ROWS_PROCESSED -- Total rows processed over all executions

•

OPTIMIZER_MODE -- Optimizer mode used by statement

•

SERIALIZABLE_ABORTS -- Number of serializable aborts over all executions

•

CPU_TIME -- Total CPU time used for all executions

•

ELAPSED_TIME -- Total elapsed time for all executions

You should notice that most of the statistics are based on total number of executions. It will probably be more meaningful for

you to generate a report based on average values for these statistics over all executions as shown in the code section in Figure

SELECT * FROM(

SELECT

sql_text,

ceil(cpu_time/greatest(executions,1)) ave_cpu_time,

ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,

ceil(disk_reads/greatest(executions,1)) ave_disk_reads,

persistent_mem per_mem, runtime_mem run_mem,

ceil(sorts/greatest(executions,1)) ave_sorts,

ceil(parse_calls/greatest(executions,1)) ave_parse_calls,

ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,

ceil(rows_processed/greatest(executions,1)) ave_row_proc,

ceil(Serializable_aborts/greatest(executions,1)) ave_ser_aborts

FROM

v$sqlarea

WHERE

Disk_reads/greatest(executions,1)>&&disk_reads

Cpu_time/greatest(executions,1)>&&cpu_time

Elapsed_time/greatest(executions,1)>&&elapsed_time

order by elapsed_time, cpu_time, disk_reads)

where rownum<11

Figure 3: Example Select Against the V$SQLAREA View

Of course if you aren't interested in all of the stats the list of columns can be pared down to only those with which you have a

concern. One thing you will note about the above code is that it returns the top 10 statements (WHERE rownum<11) and

provides for setting various thresholds. You may want to place identifying tags using comments inside your SQL statements

in your PL/SQL so that you can just look at statements that apply to a specific package. For example:

SELECT /* DBA_UTIL.get_time */ startup_time INTO jdate FROM v$instance;

This will allow you to separate out your PL/SQL program SQL from other SQL that may be in the shared pool with a simple

select against the SQL_TEXT column as shown in Figure 4.

SELECT

sql_text, executions,

ceil(cpu_time/greatest(executions,1)) ave_cpu_time,

ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,

Monitoring and Tuning PL/SQL

Ault

www.odtug.com ODTUG 2002

ceil(disk_reads/greatest(executions,1)) ave_disk_reads,

persistent_mem per_mem, runtime_mem run_mem,

ceil(sorts/greatest(executions,1)) ave_sorts,

ceil(parse_calls/greatest(executions,1)) ave_parse_calls,

ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,

ceil(rows_processed/greatest(executions,1)) ave_row_proc

FROM

v$sqlarea

WHERE

Sql_text like '%&sub_str%';

Figure 4: Example Select against the SQL_TEXT column using a Sub-String

For example, in my test instance there were 265 total SQL areas, but only 25 had the DBA_UTIL comment line.

DBA_SOURCE

The DBA_SOURCE view is based on the SOURCE$ table that holds all of the source code for the stored objects (other than

triggers and views) in the database. The DBA_SOURCE view should be used to view specific lines of source code based on

output from the DBMS_PROFILER package and other monitoring tools.

The DBA_SOURCE view and underlying table can also be used to document existing stored objects. The DBA_SOURCE

view has the following structure which mimics the structure of the SOURCE$ table as shown in Figure 4.

Name Null? Type

--------------------------------------------------- -------- ---------------

OWNER VARCHAR2(30)

NAME VARCHAR2(30)

TYPE VARCHAR2(12)

LINE NUMBER

TEXT VARCHAR2(4000)

Figure 5: Contents of the DBA_SOURCE View

Notice that the TEXT column is a VARCHAR2(4000) data type. This 4000 length indicates that to properly view any text

lines you may need to set the LONG variable in SQL*Plus to 4000. A simple select will return all of the lines for a given

stored object in order as shown in Figure 6.

select 'create or replace '||text,line

from

dba_source

where

owner = upper('&&owner') and

type = upper('&&obj_type') and

name = upper('&&obj_name') and

line = 1

UNION

select text,line

from

dba_source s2

where

s2.name = upper('&&3') and

s2.owner = upper('&&1') and

s2.type = upper('&&2') and

s2.line > 1

order by 2;

Figure 6: Example Select Against the DBA_SOURCE View

We will see in the section on DBMS_PROFILER how the LINE column is used to retrieve a problem line.

Using DBMS_PROFILER

The DBMS_PROFILER package is similar to a UTLESTAT/UTLBSTAT for PL/SQL. Just as UTLESTAT and

UTLEBSTAT collect database level statistics for later analysis the DBMS_PROFILER package does the same for PL/SQL.

The package allows you to start, stop and flush statistics to persistent storage. The DBMS_PROFILER package is created

using the PROFLOAD.SQL script located in the $ORACLE_HOME/rdbms/admin directory on UNIX and

ORACLE_HOME\rdbms\admin directory on NT. The required storage tables for persistent data retention are created using

the PROFTAB.SQL script co-located with the PROFLOAD.SQL script. If the data collected by DBMS_PROFILER

Monitoring and Tuning PL/SQL

Ault

www.odtug.com ODTUG 2002

packages is not flushed to the persistent tables created with PROFTAB.SQL the data is erased at the end of the monitoring

session.

Data is only flushed when the explicit call to the flush_data procedure is made or when the stop_profiler function is executed.

To ensure collection of proper statistics put the application though a complete cycle before invoking the start_profiler

function, otherwise startup noise of the PL/SQL portions of the application may mask or indicate non-existent problems.

The profiler gathers the following information about each line of PL/SQL code:

•

Total elapsed time in execution

•

Number of times line has been executed

•

Minimum and maximum times spent executing line

Based on the above information the DBA and developers can concentrate their efforts on the PL/SQL statements that are the

most costly.

DBMS_PROFILER must be installed as the SYS user.

The functions in the DBMS_PROFILER package have the following return codes:

•

ERROR_PARAM which shows an incorrect parameter was sent to the function it corresponds to a one (1).

•

ERROR_IO which shows that a data flush operation failed, usually due to permission or tables not being present it

corresponds to a two (2).

•

ERROR_VERSION which shows there is a mismatch between the version of profiler and the database it

corresponds to a negative one (-1).

START_PROFILER

The start_profiler function has a single input variable: run_comment VARCHAR2 which defaults to the value of SYSDATE.

The function starts collection of PL/SQL statistics and should not be invoked until the application has been run through once

to avoid startup noise. The function returns a BINARY_INTEGER which will be zero (0) if the call was successful,

otherwise it will relate to one of the standard DBMS_PROFILER error codes.

DBMS_PROFILE.START_PROFILER(run_comment IN VARCHAR2:=SYSDATE)

RETURN BINARY_INTEGER;

STOP_PROFILER

The stop_profiler function has no input variables and returns a BINARY_INTEGER which will be zero (0) if the call was

successful, otherwise it will relate to one of the standard DBMS_PROFILER error codes. The stop_profiler flushes statistics

to the pre-defined flush tables and stops PL/SQL statistics gathering.

DBMS_PROFILER.STOP_PROFILER RETURN BINARY_INTEGER;

FLUSH_DATA

The flush_data function has no input variables and returns a BINARY_INTEGER that will be zero (0) if the call was

successful, otherwise it will relate to one of the standard DBMS_PROFILER error codes. The flush_data function flushes

statistics to the pre-defined flush tables. The flush_data function can be used at anytime during statistics gathering to provide

intermediate looks at PL/SQL statistics. To use FLUSH_DATA the PROFTAB.SQL script must have been run and the tables

available to the executing user.

DBMS_PROFILE.FLUSH_DATA RETURN BINARY_INTEGER;

GET_VERSION

The get_version procedure gets the version of the DBMS_PROFILER API to ensure that there isn't a version mismatch. The

procedure GET_VERSION has the following output variables:

•

Major BINARY_INTEGER

•

Minor BINARY_INTEGER

DBMS_PROFILER.GET_VERSION(major VARCHAR2, minor VARCHAR2);

剩余51页未读，继续阅读

评论收藏

内容反馈

apn

2012-11-13

非常好的文档，对初学者很有帮助

arthurnew

粉丝: 0

oracle sql 语句暗示 hints

最新资源

oracle sql 语句暗示 hints

Oracle SQL Hints

oracle hints详细介绍

通过分析SQL语句的执行计划优化SQL

oracle sql语句优化

Oracle SQL语句方法

ORACLE SQL语句执行效率研究.pdf

oracle性能优化之SQL语句优化

ORACLE数据库SQL语句编写优化总结

SQL语句性能调整 ORACLE的执行计划.

ORACLE数据库SQL语句编写优化总结.zip

ORACLE并行SQL指南

ORACLE SQL性能优化系列(全)

oracle-hints.rar_oracle

Oracle和SqlServer语法区别

oracle HINTS用法

Oracle SQL 优化与调优技术详解-附录：SQL提示.pdf

ORACLE SQL性能优化文档大全(包括所有sql索引方面知识)

ORACLE SQL性能优化

ORACLE和SQL Server的语法区别

SQL语句优化过程策略，帮助您优化Oracle查询语句

ORACLESQL性能优化.ppt

Oracle Sql 优化

ORACLE_SQL性能优化

Top-SQL Tuning SQL语句调整

oracle SQL优化

oracle中sql语句的数据优化[参考].pdf

Oracle SQL最佳实践

Oracle sql 性能优化调整

oracle sql调优新思维

WiFi模块相关参数详解

BIWM综合物资管理系统 v3.09

最新资源