Thursday May 12, 2011

How do I get an application to use the same execution plan I get in SQL*Plus?

Let me provide a little background to this question before answering it.

Background
This application is a home-grown Java based app and the SQL statement in questions looks like this:

INSERT /*+APPEND*/ INTO t1
(ROW_ID, MODIFICATION_NUM, OPERATION, LAST_UPD)
SELECT ROW_ID ,1 ,'I' ,LAST_UPD
FROM t2
WHERE t2.LAST_UPD > :1;

The table T2 has approximately 1.4 million rows and one index IND_T2 on the LAST_UPD and ROW_ID columns. The execution plans are quite simple and involve just the IND_T2 index. The paln in SQL* Plus does an index range scan on the IND_T2 index.


[Read More]

Monday May 09, 2011

Explain the Explain Plan white paper finally published!

New Oracle Optimizer white paper, Explain the Explain Plan, published on OTN.

[Read More]

Thursday Feb 07, 2008

How do I display and read the execution plans for a SQL statement

Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.[Read More]
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today