Hi
Already knew the fact that ... Java 5 and 1.5 are the same thing but the following article at suns site clearly spotlights the similarity in exact technical jargon ....
Thought it would be useful for my friends also
cheerz !!
Saturday, March 31, 2007
Monday, March 26, 2007
Oracle : EXPLAIN PLAN
I happen to go through useful tutorials on oracle's 'Explain plan' utility ... but it was a bit cranky(at first) ... so thought would share answers to some questions that would " probably " come in mind of anyone who starts with it
What is it ??
A statement's execution plan is the sequence of operations Oracle performs to run the statement.
Basically EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements
When do i use use ??
To tune SQL statements.. Tuning problematic SQL statements is no magic. Oracle generates an execution plan for the submitted SQL. This execution plan then tells it how to retrieve the data, or, how to perform the data-related tasks. The better one understand how to interpret these explain plans, the better one can resolve possible performance issues with the SQL.
Any Pre-Requisites ??
Yes .... Before issuing an EXPLAIN PLAN statement, you must have a table to hold its output called plan_table. Use the SQL script
How to get the execution plan ??
In sql prompt simply type
EXPLAIN PLAN
FOR SQL_Statement
You can get the execution plan from the EXPLAIN PLAN SQL statement, from querying V$SQL_PLAN, or from SQL trace.
It can also be invoked in a button-click fashion in a variety of GUI tools such as Oracle Enterprise Manager (OEM), TOAD, SQL Navigator, and Oracle SQL Developer. But in opinion of some SQL gurus... single-click Explain Plan tools, frequently show the wrong plan; ie. not the plan that is used by the live production code. The Cost Based Optimizer (which generates the SQL execution plan )is sensitive to a number of session-level database parameters, any of which may be overridden either by the production code or the GUI tool.
OTHER ... synatx(es) for explain plan
EXPLAIN PLAN
SET STATEMENT_ID = identifier
FOR SQL_Statement
EXPLAIN PLAN
INTO different_table
FOR SQL_Statement
How can execution plan change for same SQL ??
Execution plans can differ due to the following:
* Different Schemas
* Different Costs
Different Schemas
* The execution and explain plan happen on different databases.
* The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.
* Schema changes (usually changes in indexes) between the two operations.
Different Costs
Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:
* Data volume and statistics
* Bind variable types
* Initialization parameters (set globally or at session level)
Some examples ??
EXPLAIN PLAN Example 1
EXPLAIN PLAN SET statement_id = 'example1' FOR
SELECT full_name FROM per_all_people_f
WHERE UPPER(full_name) LIKE 'Pe%' ;
Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL PER_ALL_PEOPLE_F
This plan shows execution of a SELECT statement. The table PER_ALL_PEOPLE_F is accessed using a full table scan.
* Every row in the table PER_ALL_PEOPLE_F is accessed, and the WHERE clause criteria is evaluated for every row.
* The SELECT statement returns the rows meeting the WHERE clause criteria.
EXPLAIN PLAN Example 2
EXPLAIN PLAN SET statement_id = 'example2' FOR
SELECT full_name FROM per_all_people_f
WHERE full_name LIKE 'Pe%' ;
Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
INDEX RANGE SCAN PER_PEOPLE_F_N54
This plan shows execution of a SELECT statement.
* Index PER_PEOPLE_F_N54 is used in a range scan operation.
* The table PER_ALL_PEOPLE_F is accessed through ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
EXPLAIN PLAN Example 3
EXPLAIN PLAN SET statement_id = 'example3' FOR
SELECT segment1, segment2, description, inventory_item_id
FROM mtl_system_items msi
WHERE segment1 = :b1
AND segment2 LIKE '%-BOM'
AND NVL(end_date_active,sysdate+1) > SYSDATE ;
Plan
--------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS
INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N8
This plan shows execution of a SELECT statement.
* Index MTL_SYSTEM_ITEMS_N8 is used in a range scan operation. This is an index on (SEGMENT1, SEGMENT2, SEGMENT3). The range scan happens using the following condition:
segment1 = :b1
The rows that come out of this step satisfy all the WHERE clause criteria that can be evaluated with the index columns. Therefore, the following condition is also evaluated at this stage:
segment2 LIKE '%-BOM'
* The table PER_ALL_PEOPLE_F is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
NVL(end_date_active,sysdate+1) > SYSDATE
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
EXPLAIN PLAN Example 4
EXPLAIN PLAN SET statement_id = 'example4' FOR
SELECT h.order_number, l.revenue_amount, l.ordered_quantity
FROM so_headers_all h, so_lines_all l
WHERE h.customer_id = :b1
AND h.date_ordered > SYSDATE-30
AND l.header_id = h.header_id ;
Plan
--------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N1
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N1
This plan shows execution of a SELECT statement.
* Index so_headers_n1 is used in a range scan operation. This is an index on customer_id. The range scan happens using the following condition:
customer_id = :b1
* The table so_headers_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
h.date_ordered > sysdate-30
* For every row from so_headers_all satisfying the WHERE clause conditions, a range scan is run on so_lines_n1 using the following condition:
l.header_id = h.header_id
* The table so_lines_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. There are no additional conditions to evaluate here.
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
Found the same on other sites too ... didnt understand them.. completely
In examining examples , it is easy to follow the explain plan indentations. Basically, Oracle works from the most indented item outwards, so Oracle will work on the most indented item first.
Its tough to inter prate which one is the most indented one ... ??
Well the answer is one may use Dan Hotka's query to see the corresponding child id and parent id with indentedation
rem
rem Dan Hotka Pinnacle Professional article
rem Show_Plan.sql - used to print contents from Plan_Table
rem
rem Install utlxplan.sql for each user, then put this line in front of sql statement
rem --> explain plan set statement_id = '' for
rem --> run this script --> sqlplus userid/pwd @show_plan
rem
set pagesize 20
set linesize 80
column id format 999 heading 'ID'
column parent_id format 999 heading 'P_ID'
column cost format 999 heading 'Cost'
column access_plan format a30 heading 'Access|Plan'
column access_path format a15 heading 'Access|Path'
column object_name format a15 heading 'Object|Name'
select cost,id, parent_id, lpad (' ', 2 * level) || operation Access_Plan, options Access_Path,
object_name
from plan_table
where statement_id = '&1'
connect by prior id = parent_id
start with id = 0;
delete from plan_table where statement_id = '&1';
save the above as Show_Plan.sql and use it
Explain plan descriptions ??
FILTER
FILTERs apply 'other criteria' in the query to further qualify the matching rows such as correlated subqueries.
FULL Table Scan
Table being accessed from beginning to end, not using an Index.HAVING clause.
INDEX (UNIQUE)
SQL statement utilized a unique index to search for a specific value.
INDEX (RANGE SCAN)
SQL statement contains a non-equality or BETWEEN condition.
HASH JOIN
SQL statement initiated a hash-join operation, tables are read and put into a memory structure accessed via a mathematical calcuation (known as a HASH key).
MERGE JOIN
A join method used when more than one table appears in the FROM clause. Oracle will sort the two result sets being joined over the join columns and then merging the results via the join columns.
NESTED LOOPS
This operation is another form of joining tables. One row is retrieved from the row source identified by the first statement ID under the NESTED LOOP, and then joined to all matching rows in the other table referenced by the NESTED LOOP.
Each of the join conditions — NESTED LOOP, MERGE JOIN, and HASH JOINS — makes an intermediate result set that is passed to the parent ID.
Plan Steps ??
You may also divide the plan steps in
i) Plan steps with no children
ii) Plan steps with 1 child
iii) Plan steps with 2 children
Plan steps with no children
A step in the plan with no dependents is a leaf of the tree. A leaf step will be either a Table Access or an Index Scan; the Rows (or Cardinality) column tells us how many rows the scan should return. Simple? Well, not quite; there is a vital piece of information missing: How many times will the step be executed? An Index Range Scan that returns 500 rows is hardly cause for alarm; but if it is going to be exectued 2 million times then we have a problem.
Looking at a step in isolation (and this applies to branch steps as well as leaf steps), you cannot tell how many times it will be executed; you need to look at its ancestors in the tree.
Watch for:
1. INDEX RANGE SCAN. This is probably the most insidious performance hole in Oracle. A Range Scan can return any number of rows; 1, 100, 100 million - the Rows column in Explain Plan often gets it wrong.
2. TABLE ACCESS FULL. Full table scans (with high row counts) when you are performing low-volume transactional SQL. Full table scans are OK for high-volume batch processes and reports.
Plan steps with 1 child
Plan steps with one child fall into three main classes:
1. Passive Operations
Operations such as VIEW and PX SEND simply pass data through unaltered to a parent step. They may be ignored.
2. Iterative Operations
INLIST ITERATOR, PARTITION INLIST, PARTITION ALL, PARTITION ITERATOR, and PX ITERATOR all execute the child step many times.
Even though we cannot tell from the plan how many times the child steps will be executed, the Rows column displays the expected number of rows for all iterations, not the average per iteration. Note that this is in contrast to plan steps with 2 children (see below).
3. Active Operations
All other operations with a single child are active; they receive the row set from the child, do something to it, then pass it on to the parent.
Note: the terms Passive, Iterative, and Active are just a learning-aid; they are not used by Oracle. If you use them, don't expect anyone to understand what you are talking about.
Watch for:
1. SORT operations with high row counts. If a result set is small enough then Oracle will perform a very efficient in-memory sort. Beyond a certain size (depending on the setup of your database and session) the sort will need to page to disk; this can double the sort time or much worse. This means that execution times for small volumes will not scale proportionally to larger volumes.
2. FILTER is an unusual step in its single-child form. Look at the Filter condition in the Predicate Information section of the plan. If the condition references any table columns from subordinate steps, then the filter is applied after the child step, filtering non-matching rows as they are returned. If the condition references only bind variables and constants, then it is evaluated before the child step; if the expression evaluates False, the the child step is not executed at all.
3. PARTITION ALL and any operation containing the word ITERATOR are iterative; they execute the child step many times. Note that the Rows column shows the total number of rows expected for all iterations; not per iteration.
4. A VIEW operation is often encountered when selecting from a database view, an inline view, or simply when joining a large number of tables. It is a popular misconception that a VIEW operation will cause the result set to be materialised in TEMP space before proceeding with parent steps. This is not true; the VIEW operation appears to have no effect on the plan at all.
Plan steps with 2 children
There are two ways to interpret steps with two children:
1. Active: Do A, then do B.
2. Iterative: For each A, do B.
The difference is one of the most critical aspects of performance tuning. NESTED LOOPS, FILTER, and MERGE JOIN CARTESIAN are the only iterative operations; all others are active. Unlike the single-child iterative operations described above, the Rows measure is the expected number of rows for a single iteration of step 2.
Watch for:
1. NESTED LOOPS and FILTER operations with a large number of rows in the first child step, especially when the second child step returns more than one row or has subordinate steps; the cost of repeating the second child step so many times can be prohibitive. Exception: if the second child step is a unique index scan without a TABLE ACCESS, it can be very efficient in a NESTED LOOPS or FILTER operation.
2. MERGE JOIN CARTESIAN has a bad reputation from the days of the Rule Based Optimizer because it usually signalled a programming error, and was the cause of a performance problem. Under the Cost Based Optimizer, MERGE JOIN CARTESIAN is often used to join two unrelated tables where one table will return just a single row (or no rows). A cartesian join is only a problem if both row sources in the join have a large number of rows.
3. HASH JOIN is especially efficient when one of the sources is small (say, <10000 href="http://www.dbazine.com/oracle/or-articles/hotka1">DonHotka
rleishman
Oracle
What is it ??
A statement's execution plan is the sequence of operations Oracle performs to run the statement.
Basically EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements
When do i use use ??
To tune SQL statements.. Tuning problematic SQL statements is no magic. Oracle generates an execution plan for the submitted SQL. This execution plan then tells it how to retrieve the data, or, how to perform the data-related tasks. The better one understand how to interpret these explain plans, the better one can resolve possible performance issues with the SQL.
Any Pre-Requisites ??
Yes .... Before issuing an EXPLAIN PLAN statement, you must have a table to hold its output called plan_table. Use the SQL script
UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. For description of the plan_table click here...How to get the execution plan ??
In sql prompt simply type
EXPLAIN PLAN
FOR SQL_Statement
You can get the execution plan from the EXPLAIN PLAN SQL statement, from querying V$SQL_PLAN, or from SQL trace.
It can also be invoked in a button-click fashion in a variety of GUI tools such as Oracle Enterprise Manager (OEM), TOAD, SQL Navigator, and Oracle SQL Developer. But in opinion of some SQL gurus... single-click Explain Plan tools, frequently show the wrong plan; ie. not the plan that is used by the live production code. The Cost Based Optimizer (which generates the SQL execution plan )is sensitive to a number of session-level database parameters, any of which may be overridden either by the production code or the GUI tool.
OTHER ... synatx(es) for explain plan
EXPLAIN PLAN
SET STATEMENT_ID = identifier
FOR SQL_Statement
EXPLAIN PLAN
INTO different_table
FOR SQL_Statement
How can execution plan change for same SQL ??
Execution plans can differ due to the following:
* Different Schemas
* Different Costs
Different Schemas
* The execution and explain plan happen on different databases.
* The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.
* Schema changes (usually changes in indexes) between the two operations.
Different Costs
Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:
* Data volume and statistics
* Bind variable types
* Initialization parameters (set globally or at session level)
Some examples ??
EXPLAIN PLAN Example 1
EXPLAIN PLAN SET statement_id = 'example1' FOR
SELECT full_name FROM per_all_people_f
WHERE UPPER(full_name) LIKE 'Pe%' ;
Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS FULL PER_ALL_PEOPLE_F
This plan shows execution of a SELECT statement. The table PER_ALL_PEOPLE_F is accessed using a full table scan.
* Every row in the table PER_ALL_PEOPLE_F is accessed, and the WHERE clause criteria is evaluated for every row.
* The SELECT statement returns the rows meeting the WHERE clause criteria.
EXPLAIN PLAN Example 2
EXPLAIN PLAN SET statement_id = 'example2' FOR
SELECT full_name FROM per_all_people_f
WHERE full_name LIKE 'Pe%' ;
Plan
---------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F
INDEX RANGE SCAN PER_PEOPLE_F_N54
This plan shows execution of a SELECT statement.
* Index PER_PEOPLE_F_N54 is used in a range scan operation.
* The table PER_ALL_PEOPLE_F is accessed through ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
EXPLAIN PLAN Example 3
EXPLAIN PLAN SET statement_id = 'example3' FOR
SELECT segment1, segment2, description, inventory_item_id
FROM mtl_system_items msi
WHERE segment1 = :b1
AND segment2 LIKE '%-BOM'
AND NVL(end_date_active,sysdate+1) > SYSDATE ;
Plan
--------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS
INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N8
This plan shows execution of a SELECT statement.
* Index MTL_SYSTEM_ITEMS_N8 is used in a range scan operation. This is an index on (SEGMENT1, SEGMENT2, SEGMENT3). The range scan happens using the following condition:
segment1 = :b1
The rows that come out of this step satisfy all the WHERE clause criteria that can be evaluated with the index columns. Therefore, the following condition is also evaluated at this stage:
segment2 LIKE '%-BOM'
* The table PER_ALL_PEOPLE_F is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
NVL(end_date_active,sysdate+1) > SYSDATE
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
EXPLAIN PLAN Example 4
EXPLAIN PLAN SET statement_id = 'example4' FOR
SELECT h.order_number, l.revenue_amount, l.ordered_quantity
FROM so_headers_all h, so_lines_all l
WHERE h.customer_id = :b1
AND h.date_ordered > SYSDATE-30
AND l.header_id = h.header_id ;
Plan
--------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL
INDEX RANGE SCAN SO_HEADERS_N1
TABLE ACCESS BY INDEX ROWID SO_LINES_ALL
INDEX RANGE SCAN SO_LINES_N1
This plan shows execution of a SELECT statement.
* Index so_headers_n1 is used in a range scan operation. This is an index on customer_id. The range scan happens using the following condition:
customer_id = :b1
* The table so_headers_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:
h.date_ordered > sysdate-30
* For every row from so_headers_all satisfying the WHERE clause conditions, a range scan is run on so_lines_n1 using the following condition:
l.header_id = h.header_id
* The table so_lines_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. There are no additional conditions to evaluate here.
* The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).
Found the same on other sites too ... didnt understand them.. completely
In examining examples , it is easy to follow the explain plan indentations. Basically, Oracle works from the most indented item outwards, so Oracle will work on the most indented item first.
Its tough to inter prate which one is the most indented one ... ??
Well the answer is one may use Dan Hotka's query to see the corresponding child id and parent id with indentedation
rem
rem Dan Hotka Pinnacle Professional article
rem Show_Plan.sql - used to print contents from Plan_Table
rem
rem Install utlxplan.sql for each user, then put this line in front of sql statement
rem --> explain plan set statement_id = '
rem --> run this script --> sqlplus userid/pwd @show_plan
rem
set pagesize 20
set linesize 80
column id format 999 heading 'ID'
column parent_id format 999 heading 'P_ID'
column cost format 999 heading 'Cost'
column access_plan format a30 heading 'Access|Plan'
column access_path format a15 heading 'Access|Path'
column object_name format a15 heading 'Object|Name'
select cost,id, parent_id, lpad (' ', 2 * level) || operation Access_Plan, options Access_Path,
object_name
from plan_table
where statement_id = '&1'
connect by prior id = parent_id
start with id = 0;
delete from plan_table where statement_id = '&1';
save the above as Show_Plan.sql and use it
Explain plan descriptions ??
FILTER
FILTERs apply 'other criteria' in the query to further qualify the matching rows such as correlated subqueries.
FULL Table Scan
Table being accessed from beginning to end, not using an Index.HAVING clause.
INDEX (UNIQUE)
SQL statement utilized a unique index to search for a specific value.
INDEX (RANGE SCAN)
SQL statement contains a non-equality or BETWEEN condition.
HASH JOIN
SQL statement initiated a hash-join operation, tables are read and put into a memory structure accessed via a mathematical calcuation (known as a HASH key).
MERGE JOIN
A join method used when more than one table appears in the FROM clause. Oracle will sort the two result sets being joined over the join columns and then merging the results via the join columns.
NESTED LOOPS
This operation is another form of joining tables. One row is retrieved from the row source identified by the first statement ID under the NESTED LOOP, and then joined to all matching rows in the other table referenced by the NESTED LOOP.
Each of the join conditions — NESTED LOOP, MERGE JOIN, and HASH JOINS — makes an intermediate result set that is passed to the parent ID.
Plan Steps ??
You may also divide the plan steps in
i) Plan steps with no children
ii) Plan steps with 1 child
iii) Plan steps with 2 children
Plan steps with no children
A step in the plan with no dependents is a leaf of the tree. A leaf step will be either a Table Access or an Index Scan; the Rows (or Cardinality) column tells us how many rows the scan should return. Simple? Well, not quite; there is a vital piece of information missing: How many times will the step be executed? An Index Range Scan that returns 500 rows is hardly cause for alarm; but if it is going to be exectued 2 million times then we have a problem.
Looking at a step in isolation (and this applies to branch steps as well as leaf steps), you cannot tell how many times it will be executed; you need to look at its ancestors in the tree.
Watch for:
1. INDEX RANGE SCAN. This is probably the most insidious performance hole in Oracle. A Range Scan can return any number of rows; 1, 100, 100 million - the Rows column in Explain Plan often gets it wrong.
2. TABLE ACCESS FULL. Full table scans (with high row counts) when you are performing low-volume transactional SQL. Full table scans are OK for high-volume batch processes and reports.
Plan steps with 1 child
Plan steps with one child fall into three main classes:
1. Passive Operations
Operations such as VIEW and PX SEND simply pass data through unaltered to a parent step. They may be ignored.
2. Iterative Operations
INLIST ITERATOR, PARTITION INLIST, PARTITION ALL, PARTITION ITERATOR, and PX ITERATOR all execute the child step many times.
Even though we cannot tell from the plan how many times the child steps will be executed, the Rows column displays the expected number of rows for all iterations, not the average per iteration. Note that this is in contrast to plan steps with 2 children (see below).
3. Active Operations
All other operations with a single child are active; they receive the row set from the child, do something to it, then pass it on to the parent.
Note: the terms Passive, Iterative, and Active are just a learning-aid; they are not used by Oracle. If you use them, don't expect anyone to understand what you are talking about.
Watch for:
1. SORT operations with high row counts. If a result set is small enough then Oracle will perform a very efficient in-memory sort. Beyond a certain size (depending on the setup of your database and session) the sort will need to page to disk; this can double the sort time or much worse. This means that execution times for small volumes will not scale proportionally to larger volumes.
2. FILTER is an unusual step in its single-child form. Look at the Filter condition in the Predicate Information section of the plan. If the condition references any table columns from subordinate steps, then the filter is applied after the child step, filtering non-matching rows as they are returned. If the condition references only bind variables and constants, then it is evaluated before the child step; if the expression evaluates False, the the child step is not executed at all.
3. PARTITION ALL and any operation containing the word ITERATOR are iterative; they execute the child step many times. Note that the Rows column shows the total number of rows expected for all iterations; not per iteration.
4. A VIEW operation is often encountered when selecting from a database view, an inline view, or simply when joining a large number of tables. It is a popular misconception that a VIEW operation will cause the result set to be materialised in TEMP space before proceeding with parent steps. This is not true; the VIEW operation appears to have no effect on the plan at all.
Plan steps with 2 children
There are two ways to interpret steps with two children:
1. Active: Do A, then do B.
2. Iterative: For each A, do B.
The difference is one of the most critical aspects of performance tuning. NESTED LOOPS, FILTER, and MERGE JOIN CARTESIAN are the only iterative operations; all others are active. Unlike the single-child iterative operations described above, the Rows measure is the expected number of rows for a single iteration of step 2.
Watch for:
1. NESTED LOOPS and FILTER operations with a large number of rows in the first child step, especially when the second child step returns more than one row or has subordinate steps; the cost of repeating the second child step so many times can be prohibitive. Exception: if the second child step is a unique index scan without a TABLE ACCESS, it can be very efficient in a NESTED LOOPS or FILTER operation.
2. MERGE JOIN CARTESIAN has a bad reputation from the days of the Rule Based Optimizer because it usually signalled a programming error, and was the cause of a performance problem. Under the Cost Based Optimizer, MERGE JOIN CARTESIAN is often used to join two unrelated tables where one table will return just a single row (or no rows). A cartesian join is only a problem if both row sources in the join have a large number of rows.
3. HASH JOIN is especially efficient when one of the sources is small (say, <10000 href="http://www.dbazine.com/oracle/or-articles/hotka1">DonHotka
rleishman
Oracle
Thursday, March 22, 2007
boolean and JVM
Booelan one of little cryptic primitive types in Java
Here are a few things i noticed about the same
1) depth of a boolean, is virtual-machine dependent
2) JVM spec syas that ....
Although the Java virtual machine defines a boolean type, it only provides very limited support for it. There are no Java virtual machine instructions solely dedicated to operations on boolean values. Instead, expressions in the Java programming language that operate on boolean values are compiled to use values of the Java virtual machine int data typ
3) When the Java source is compiled into bytecode, then in the bytecode booleans are treated the same way as ints. For the Java programmer this isn't relevant, because when you're programming in Java you are only working on the level of the Java language, and not on the bytecode level.
' There's a difference between the Java programming language and the bytecode - don't confuse them '
Here are a few things i noticed about the same
1) depth of a boolean, is virtual-machine dependent
2) JVM spec syas that ....
Although the Java virtual machine defines a boolean type, it only provides very limited support for it. There are no Java virtual machine instructions solely dedicated to operations on boolean values. Instead, expressions in the Java programming language that operate on boolean values are compiled to use values of the Java virtual machine int data typ
3) When the Java source is compiled into bytecode, then in the bytecode booleans are treated the same way as ints. For the Java programmer this isn't relevant, because when you're programming in Java you are only working on the level of the Java language, and not on the bytecode level.
' There's a difference between the Java programming language and the bytecode - don't confuse them '
Thursday, March 1, 2007
Seriosity ... Serious problem .... Email Overload
Talking of coming back to work from a long leave ... It has mixed feelings both good and bad ... good ?? i must be joking ... but no ... thats the best thing .. enjoying ones work ...
bad .... i bet you have experienced that
well its a good solution to the problem of overly crowded emails ....
forget about vacations on daily basis we all receive so many un-wanted mails .. what was the last time when you missed out an important email .... coz it was hidden in a big pile of mails .. not spam exactly ?? i guess not too long ...
but now we have a solution.... read what cnet has to say about it ...
just a thought ... cant we include the solution to such problem in at protocol level itself... ??
instead of letting proprietary technology step in ... ??
bad .... i bet you have experienced that
- Vacations over :(
- PENDING emails .... phew !!
well its a good solution to the problem of overly crowded emails ....
forget about vacations on daily basis we all receive so many un-wanted mails .. what was the last time when you missed out an important email .... coz it was hidden in a big pile of mails .. not spam exactly ?? i guess not too long ...
but now we have a solution.... read what cnet has to say about it ...
just a thought ... cant we include the solution to such problem in at protocol level itself... ??
instead of letting proprietary technology step in ... ??
Subscribe to:
Posts (Atom)