You are using an outdated browser. Please upgrade to the latest version for the best experience.
How do I narrow my search results?

After you submit your search query:

  1. On the Refine Search results page, select one or more categories of products or services from the left sidebar.
  2. Then for a category with search results, click Select to choose your product and release filters, and then click OK.

For search queries that contain multiple words, surround the query with quotes, and then resubmit your query. Example: "database cloud service"

How do I find the documentation for my product or service?

From the home page, click the technology categories for your products or services. If you do not know which category to pick, try the following features:

  • Click the Find a product tab and search for your product or service.
  • Click Browse All Products & Services at the bottom of the home page to view an alphabetical listing of products and services.
  • Apple Safari: Version 6
  • Google Chrome: Version 29 and later
  • Mozilla Firefox: Version 24 and later
  • Microsoft Internet Explorer: Version 9 and later
Results for tuning


1 to 20 of 2307 results. Previous page Prev  Next next page
Results per page:   20  | 40 | 60


Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Candidates for TuningThe following kinds of PL/SQL code are very likely to benefit from tuning: Older code that does not … , see \"What's New in PL/SQL?\". Tip: Before tuning older code, benchmark the current system and … \" ), you might see performance improvements before doing any tuning. Older dynamic SQL statements

Database Performance Tuning Guide, 11g Release 2 (11.2)

Database Performance Tuning Guide - Contents

ContentsConventions What's New in Oracle Database Performance Tuning Guide? Oracle Database 11 g Release 2 … Database Performance Part I Performance Tuning 1 Performance Tuning Overview 1.1 Introduction to … Performance Tuning 1.1.1 Performance Planning 1.1.2 Instance Tuning 1.1.2.1 Performance Principles

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

12 PL/SQL Optimization and Tuningcode and improve existing PL/SQL code. Topics PL/SQL Optimizer Candidates for Tuning Minimizing CPU

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

PL/SQL OptimizerPrior to Oracle Database 10 g, the PL/SQL compiler translated your source text to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance. The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Minimizing CPU OverheadTopics Tune SQL Statements Tune Function Invocations in Queries Tune Subprogram Invocations Tune Loops Tune Computation-Intensive PL/SQL Code Use SQL Character Functions Put Least Expensive Conditional Tests First

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Setting Up a New Database for PL/SQL Native CompilationIf you have DBA privileges, you can set up a new database for PL/SQL native compilation by setting the compilation parameter PLSQL_CODE_TYPE to NATIVE. The performance benefits apply to the PL/SQL packages that Oracle Database supplies, which are used for many database operations. Note: If you compile the whole database as NATIVE, Oracle recommends that you set PLSQL_CODE_TYPE at the system level.

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Tune LoopsBecause PL/SQL applications are often built around loops, it is important to optimize both the loops themselves and the code inside them. If you must loop through a result set more than once, or issue other queries as you loop through a result set, you might be able to change the original query to give you exactly the results you want. Explore the SQL set operators that let you combine multiple queries,

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Pipelined Table Functions as Transformation FunctionsA pipelined table function with a cursor variable parameter can serve as a transformation function. Using the cursor variable, the function fetches an input row. Using the PIPE ROW statement, the function pipes the transformed row or rows to the invoker. If the FETCH and PIPE ROW statements are inside a LOOP statement, the function can transform multiple input rows. In Example 12-30, the pipelined

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Passing CURSOR Expressions to Pipelined Table FunctionsAs Example 12-31 shows, the actual parameter for the cursor variable parameter of a pipelined table function can be either a cursor variable or a CURSOR expression, and the latter is more efficient. Note: When a SQL SELECT statement passes a CURSOR expression to a function, the referenced cursor opens when the function begins to run and closes when the function completes. See Also: \"CURSOR Expressions\"

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Updating Large Tables in ParallelThe DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps: Group sets of rows in the table into smaller chunks. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. This technique is recommended whenever you are updating a lot of data. Its advantages are: You

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Subprogram InliningOne optimization that the compiler can perform is subprogram inlining. Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit). To allow subprogram inlining, either accept the default value of the PLSQL_OPTIMIZE_LEVEL compilation parameter (which is 2) or set it to 3. With PLSQL_OPTIMIZE_LEVEL=2,

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Overview of Table FunctionsA table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example: SELECT * FROM TABLE(table_function_name(parameter_list)) (For more information about the TABLE clause of the SELECT statement, see

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Chaining Pipelined Table FunctionsTo chain pipelined table functions tf1 and tf2 is to make the output of tf1 the input of tf2. For example: SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1())))); The rows that tf1 pipes out must be compatible actual parameters for the formal input parameters of tf2. If chained pipelined table functions are enabled for parallel execution, then each function runs in a different process (or set

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

NO_DATA_NEEDED ExceptionYou must understand the predefined exception NO_DATA_NEEDED in two cases: You include an OTHERS exception handler in a block that includes a PIPE ROW statement Your code that feeds a PIPE ROW statement must be followed by a clean-up procedure Typically, the clean-up procedure releases resources that the code no longer needs. When the invoker of a pipelined table function needs no more rows from the

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Determining Whether to Use PL/SQL Native CompilationWhether to compile a PL/SQL unit for native or interpreted mode depends on where you are in the development cycle and on what the program unit does. While you are debugging program units and recompiling them frequently, interpreted mode has these advantages: You can use PL/SQL debugging tools on program units compiled for interpreted mode (but not for those compiled for native mode). Compiling for

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Dependencies, Invalidation, and RevalidationRecompilation is automatic with invalidated PL/SQL modules. For example, if an object on which a natively compiled PL/SQL subprogram depends changes, the subprogram is invalidated. The next time the same subprogram is called, the database recompiles the subprogram automatically. Because the PLSQL_CODE_TYPE setting is stored inside the library unit for each subprogram, the automatic recompilation uses

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Compiling the Entire Database for PL/SQL Native or Interpreted CompilationIf you have DBA privileges, you can recompile all PL/SQL modules in an existing database to NATIVE or INTERPRETED, using the dbmsupgnv. sql and dbmsupgin. sql scripts respectively during the process explained in this section. Before making the conversion, review \"Determining Whether to Use PL/SQL Native Compilation\". Note: If you compile the whole database as NATIVE, Oracle recommends that you set

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Using FORALL Statement and BULK COLLECT Clause TogetherIn a FORALL statement, the DML statement can have a RETURNING BULK COLLECT INTO clause. For each iteration of the FORALL statement, the DML statement stores the specified values in the specified collections—without overwriting the previous values, as the same DML statement would do in a FOR LOOP statement. In Example 12-26, the FORALL statement runs a DELETE statement that has a RETURNING BULK COLLECT

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Fetching from Results of Pipelined Table FunctionsYou can associate a named cursor with a query that invokes a pipelined table function. Such a cursor has no special fetch semantics, and such a cursor variable has no special assignment semantics. However, the SQL optimizer does not optimize across PL/SQL statements. Therefore, in Example 12-31, the first PL/SQL statement is slower than the second—despite the overhead of running two SQL statements

Database PL/SQL Language Reference, 11g Release 2 (11.2)

PL/SQL Optimization and Tuning

Collecting Data About User-Defined IdentifiersPL/Scope extracts, organizes, and stores data about user-defined identifiers from PL/SQL source text. You can retrieve source text identifier data with the static data dictionary views *_IDENTIFIERS. For more information, see Oracle Database Advanced Application Developer's Guide.