Feed aggregator

The Fire, part II

Moans Nogood - Sun, 2007-12-09 18:20
Turns out there are many more good things to be said about this incident:

a. The entire house will be cleaned for Christmas.
b. All windows will be cleaned.
c. The entire first floor will be re-painted.
d. We'll get a complete list of our posessions.
e. Since the entire first floor has been emtied, we can now do all the things we always dreamed of doing up there.
f. My hand-made Italian shoes will be replaced, which is good, since little Viktor removed one of my shoe laces some months ago, and we haven't been able to locate it since.
g. We now live (with the consent of my wife Anette) on top of a bar, and the owner - Jytte - is one of the most heart-warming people I've ever met. Her magnificient helper Linda immediately moved out of her apartment so that we could stay there - just to mention one detail out of many.
h. Christmas will be special this year, no matter what happens. Just like in the movies, where the Christmas peace is secured in the last minute.

I cannot even begin to see the downsides of this unfortunate incident :-))).

Mogens

Sharing is Caring 3

Fadi Hasweh - Fri, 2007-12-07 05:48
Oracle wiki
is the new site from oracle where members of the Oracle community (employees and non-employees) collaboratively create and share content about Oracle-related subjects they're passionate about. http://wiki.oracle.com/

See you there
Fadi

Recap Post

Marcos Campos - Thu, 2007-12-06 12:11
For the past couple of months the blog took a back seat. Basically, since KDD, I have had very little time to write. I have been on the road quite a bit and my trip to KDD unleashed a number of research ideas that I have been following up. I will post on the latter over time as the results mature.I have also dropped the ball answering many of the emails and comments I have received. I have caughtMarcoshttp://www.blogger.com/profile/14756167848125664628noreply@blogger.com7
Categories: BI & Warehousing

Oracle BI Applications and Embedded BI, Part II

Dylan Wan - Wed, 2007-12-05 13:04

This is a topic I wrote in six month ago. In the Part I of this series ofarticles, I mentioned that a warehouse like architecture is required ina heterogeneous environment. I want to elaborate more about this. Inthe future posts, I will also describe the integration technology Ilearned for supporting the embedded BI.

Read the rest of this entry >>

Categories: BI & Warehousing

Do not trust Google Maps

Pawel Barut - Wed, 2007-12-05 11:23
Ok, Might be title is too strong, but you should not trust Google Maps when searching for streets in Krosno, Poland (city where I was born). Here is sample, try to search for "Lwowska, 38-400 Krosno, Poland" and you get:
Search is working correctly, but names on street do not match reality. Instead of "Lwowska" street is "Jana Lenarta". Another example is "Platynowa" instead of "Podkarpacka". It seems that all street names are messed up on map. Hope they fix it soon.

Cheers, Paweł
Categories: Development

adpcpcmp.pls - Takes an hour while applying any patch after upgrading to 11.5.10.2

Madhu Thatamsetty - Wed, 2007-12-05 00:32
Any simple problem after the upgrade that is not resolved will have long term maintenance related issues in production.It is very important to watch the alert log while applying Oracle Applications patch as it gives wealth of information. As a matter of practice I watch the alert log while applying a patch and noticed that Invalid's Compilation stage of adpatch performed by "adpcpcmp.pls" was Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com5

Why are there more cursors in 11g for my query containing bind variables?

Oracle Optimizer Team - Mon, 2007-12-03 17:05
Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We'll explain why in this article. Before we get into the details, let's review a little history.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let's look at an example to see exactly how this works.

Assume I have simple table emp which has 100,000 rows and has one index called emp_i1 on deptno column.

SQL> desc emp

Name Null? Type
---------------------- -------- ----------------------------------
ENAME VARCHAR2(20)
EMPNO NUMBER
PHONE VARCHAR2(20)
DEPTNO NUMBER

There is a data skew in the deptno column, so when I gathered statistics on the emp table, Oracle automatically created a histogram on the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME COLUMN_NAME HISTOGRAM
------------------ ------------------ ---------------
EMP DEPTNO HEIGHT BALANCED
EMP EMPNO NONE
EMP ENAME NONE
EMP PHONE NONE


Now I will execute a simple select on my emp table, which has a single WHERE
clause predicate on the deptno column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;


COUNT(*) MAX(EMPNO)
---------- ----------
10 99
Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------


So we got the index range scan that we expected. Now let's look at the execution statistics for this statement

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 1 53 Y N

You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Now let's change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.

SQL>  exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000

We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let's check:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N

You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let's re-execute the statement using the same popular value, 10.

SQL> exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- -----------
99900 100000

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10.

Let's check what the new plan is.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 1
--------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 2083865914
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 240 (100)|
| 1 | SORT AGGREGATE | | 1 | 16 | |
|* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)|
--------------------------------------------------------------------


Given how unselective the value 10 is in the table, it's not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.

Looking at the execution statistics:

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N
1 1 821 Y Y

we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
10 99

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 2
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The proper plan was chosen, based on the selectivity produced by the current bind value.

There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:


SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware, is_shareable
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR
------------ ---------- ----------- --------- --------- ----------
0 2 957 Y N N
1 1 765 Y Y Y
2 2 6 Y Y Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9"). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

Q & A
Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.

Q: Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?
A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.

Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.
A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.

Q: What triggers a cursor to be marked "bind sensitive"?
A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes.
In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Q: Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan...
A: I am not going to go into the details of the "special sauce" for how we decide to mark a cursor bind-aware. The number of rows processed is one input.

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

Why are there more cursors in 11g for my query containing bind variables?

Inside the Oracle Optimizer - Mon, 2007-12-03 17:05
Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We'll explain why in this article. Before we get into the details, let's review a little history.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let's look at an example to see exactly how this works.

Assume I have simple table emp which has 100,000 rows and has one index called emp_i1 on deptno column.

SQL> desc emp

Name Null? Type
---------------------- -------- ----------------------------------
ENAME VARCHAR2(20)
EMPNO NUMBER
PHONE VARCHAR2(20)
DEPTNO NUMBER

There is a data skew in the deptno column, so when I gathered statistics on the emp table, Oracle automatically created a histogram on the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

TABLE_NAME COLUMN_NAME HISTOGRAM
------------------ ------------------ ---------------
EMP DEPTNO HEIGHT BALANCED
EMP EMPNO NONE
EMP ENAME NONE
EMP PHONE NONE


Now I will execute a simple select on my emp table, which has a single WHERE
clause predicate on the deptno column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;


COUNT(*) MAX(EMPNO)
---------- ----------
10 99
Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------


So we got the index range scan that we expected. Now let's look at the execution statistics for this statement

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 1 53 Y N

You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the deptno column was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Now let's change the value of the bind variable to 10, which is the most popular value for the deptno column. It occurs 99900 times in the table, i.e in 99.9% of the rows.

SQL>  exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
99900 100000

We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let's check:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N

You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let's re-execute the statement using the same popular value, 10.

SQL> exec :deptno := 10

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- -----------
99900 100000

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10.

Let's check what the new plan is.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 1
--------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 2083865914
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 240 (100)|
| 1 | SORT AGGREGATE | | 1 | 16 | |
|* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)|
--------------------------------------------------------------------


Given how unselective the value 10 is in the table, it's not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are.

Looking at the execution statistics:

SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
0 2 1007 Y N
1 1 821 Y Y

we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
2 from emp
3 where deptno = :deptno;

COUNT(*) MAX(EMPNO)
---------- ----------
10 99

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 2
------------------------------------------------------------------------
select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------

The proper plan was chosen, based on the selectivity produced by the current bind value.

There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:


SQL> select child_number, executions, buffer_gets,
2 is_bind_sensitive, is_bind_aware, is_shareable
3 from v$sql
4 where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR
------------ ---------- ----------- --------- --------- ----------
0 2 957 Y N N
1 1 765 Y Y Y
2 2 6 Y Y Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9"). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

Q & A
Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.

Q: Is this behavior managed by 11g optimizer automatically and we don't need cursor_sharing anymore?
A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.

Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.
A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.

Q: What triggers a cursor to be marked "bind sensitive"?
A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes.
In this first version of the feature, we only handle equality predicates where a histogram exists on the column and range predicates (with or without histogram). We do not currently consider LIKE predicates, but it is on the top of our list for future work.

Q: Also it sounds like the optimizer is using the number of rows returned to decided that it's time for a new plan...
A: I am not going to go into the details of the "special sauce" for how we decide to mark a cursor bind-aware. The number of rows processed is one input.

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.
Categories: DBA Blogs, Development

Sharing is Caring 2

Fadi Hasweh - Mon, 2007-12-03 13:55
Oracle mix
Is it a new product no its not, it the new site from oracle for social networking, http://mix.oracle.com to make oracle community even smaller. It’s a grate site but still has some small bugs. I encourage you to register there so you will be closer to other oracle guys around the world

See you there
Fadi

SQL Techniques Tutorials: Rows to Columns (Updated SQL Snippets Topic)

Joe Fuda - Sun, 2007-12-02 15:01
The SQL Snippets "Rows to Columns" tutorial has been updated to include solutions that use the new 11g PIVOT clause of the SELECT command.
...

Nulls: Nulls and Equality (Updated SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-12-02 15:00
The SQL Snippets "Nulls and Equality" tutorial has been updated with new solutions and a discussion about the undocumented SYS_OP_MAP_NONNULL function.
...

Rant: Great fun with Wii

Pawel Barut - Sun, 2007-12-02 04:48
I'm not a computer game player, but Nintendo Wii really impressed me. I was with a visit to my friend and he showed me his new gadget. We started to play for a while, and time passed by. It's really addictive. The best thing is that you do not seat at computer and use joystick or keyboard, but you stand up and move whole body to play. It really gives pleasure, and we have good time. If you do not know Wii yet check this Wii commercial.

Have fun
Paweł
Categories: Development

Problem with 'Not Null' validations

Anthony Rayner - Sun, 2007-12-02 04:31
Introduction...
A question popped up on the forum this week about the ability to bypass not null validations. The problem is if you define a 'Not null' validation on a page item, the user can enter a blank space and the validation will allow it. But there is a simple workaround and it requires no changes to any of your existing validations.

Note this will only work in APEX version 3.0 or higher. If you are looking to implement this on 2.2 or 2.2.1, then please refer to the link above to the forum post where this was discussed, where I posted a solution for 2.2 (the views were changed slightly). Unfortunately, pre 2.2 this method is not possible, as I'm referencing the APEX dictionary views that were introduced in 2.2.

How...
To get around this you can use an APEX application process to trim all the items for the current page that have associated not null validations. Create the following application process:

Sequence: 1
Process Point: On Submit: After Page Submission - Before Computations and Validations
Name: TRIM_NOT_NULL_ITEMS
Type: PL/SQL Anonymous Block

Process Text:
BEGIN
FOR cur IN
( SELECT items.item_name
FROM apex_application_page_items items,
apex_application_page_val vals
WHERE items.application_id = TO_NUMBER(:APP_ID)
AND items.page_id = TO_NUMBER(:APP_PAGE_ID)
AND items.item_name = vals.associated_item
AND vals.validation_type
= 'Item specified is NOT NULL'
)
LOOP
apex_util.set_session_state( cur.item_name
, TRIM(v(cur.item_name)));
END LOOP;
END;

I added this to a form on EMP and setup a not null validation on the job item. Then loaded the page, keyed a space for the job field and on inspection of the debug, you can see that before the process fires, session state shows a " " for P12_JOB, which would have passed the not null validation.



And then after the process has fired, it has been trimmed and set in session to "", which causes the not null validation to correctly fail.



Hope it helps,
Anthony

Categories: Development

Key Roles involved in a BI Data Warehouse Project

Dylan Wan - Fri, 2007-11-30 14:10

To develop ordeploy a BI solution for your organizations, you need to have the rightpeople involved in the time time. Here are typical roles involved in aBI data warehouse project.

  • Project Sponsor
  • Project Manager
  • Functional Analyst
  • SME
  • BI Architect
  • ETL Developers
  • DBA

The job description and responsibilities are listed in this table: Read the rest of this entry >>

Categories: BI & Warehousing

DSS and BI

Dylan Wan - Fri, 2007-11-30 14:09

I found a very old book, called Decision Support Systems: An Organizational Perspective, in a library last weekend. It was written by Peter Keen,an author of several popular books, which help many business managersand users understand the value of information technology. His DSS bookdraw my attention because he is also the author of my textbook Network in Actions.

More...The DSS book uses a very typical and conventional categorization system which puts the IT systems into three types:

Transactional System, Structure Decision system, and Decision Support System.

These categories are created based on the classification ofdecisions into structured, unstructured, and partially structureddecision. His focus is the 3rd category, DSS. Peter believes that a DSSshould assist in solving the semi-structured problems. A DSS shouldsupport, not replace, the managers.

I feel that the above is a very good framework to view the role ofan analytics apps. A BI analytics application should be a DSS solution.However, BI analytics apps can do much more then just a decisionsupport system. BI may help the structured decision making.

BI is not just a collection of reports. The design of a BI analyticsapps needs to consider what are the business decision need to make andwhat kind of information is helpful for making the decision.

Categories: BI & Warehousing

DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01

Madhu Thatamsetty - Thu, 2007-11-29 21:10
--Clip--BEGIN2 dbms_prvtaqis.upgrade_rulesub_msgs;3 END;4 /BEGIN*ERROR at line 1:ORA-01562: failed to extend rollback segment number 12ORA-01650: unable to extend rollback segment ODMA_RBS01 by 384 in tablespace ODMA_RBSORA-06512: at "SYS.DBMS_PRVTAQIS", line 3507ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3515ORA-06512: at line 2--End Clip--If the Oracle Applications used database is having Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Boring life.

Moans Nogood - Thu, 2007-11-29 16:57
As my 18-year old daughter Christine said yesterday when she arrived at the scene: "There's ALWAYS something going on in this house."

Here's what happened:

We have insufficient pressure on the cold water in our house these days. It means that something must be done, so CarpenterTorben called on Grethe's brother, who can fix water things.

It required that we broke up a bit of the floor in our entre/foyer/whatever it's called in our house and dig a big hole on the outside in order to get to the water pipes. Torben and I joked about an idea: Why not tear down the whole house and build a new one where there's plenty of space for pipes, wires, and such?

Well, one should be careful about what one wishes for.

Grethe's brother arrived around 1400 hours yesterday and within minutes I had complaints from my wife via text messages and phone calls that he was leaving dust everywhere, and how on Earth were we supposed to get it cleaned in time for the weekend?

So when Anette called the third time I was tempted not to answer, but I did.

This time she wasn't worried about the dust. She was in our bedroom upstairs with Viktor and Melina (2 and 9 years old) and the staircase was blocked due to fire and thick smoke downstairs.

Grethe's brother had been cutting a water pipe when a spark ignited the styrofoam used for insulation under the floor. Since he had cut the water supply he acted quickly and took a towel, dipped in the toilet, and tried to put out the fire that way. Didn't work. It kept creeping further and further under the floor through the styrofoam.

So he called for fire figthers while Anette shut doors upstairs and opened the windows in the bedroom where she was, ready to throw the kids out of the window into either blankets or arms of the people gathered under the window. She even had the wherewithal to look for tape that could seal the door from the smoke. She then called me, CarpenterTorben and others. Then she made sure the kids were dressed warmly. Cool lady under pressure!

Turns out I can drive 180 km/h on a bike path and cross lots of red lights if I have to (due to the rush hour traffic blocking the roads). I found out later that Torben had done the same in an attempt to get quickly from our new office to Kratvej.

When I arrived there were already seven fire trucks, ambulances and police cars on site. Anette and the kids had been saved out of the window by a big, strong fire fighter, and the fire had pretty much been put out.

Anette, Viktor and Melina were taken to ER, had oxygene and came home again in a taxi, still with no shoes. All shoes are kind of rubbish. They slept in NabooPeter's house, Christine slept in the Garage and I slept (of course!) on the couch in the living room, just in case somebody wanted to sneak in and steal my laptop or other important things.

So nobody got hurt, and we didn't lose any dear possessions (bar all our shoes), which is very nice.

Oh, the Emergency Service company that took over when the fire fighters left wouldn't listen to CarpenterTorben, who kept saying that it was still burning somewhere underneath the floor. But suddenly they were convinced, too, and the fire fighters had to show up a second time. This time Torben had to break down the floor in the entre/foyer with a huge drill hammer (or whatever it's called) so they could get down to the styrofoam. Otherwise, the fire would have spread underneath the floors to the kitchen and living room (and possibly to the oak table!). Good man, this Torben. I think he saved my house yesterday.

We can't live in the house for some days - it has to be cleaned due to the smoke, particles and such. But Jytte, who runs the local restaurant, has made an apartment upstairs ready for us, and all the neightbours are ready to house us, too.

So we wished for a lot of cold water with high pressure. We got that, courtesy of the Ballerup fire brigade.

We also wished for easy access to the water pipes. We got that.

Anette wanted new shoes. Check.

I never liked the white colour of the entre/foyer walls. Man, it's black now.

Viktor always loved fire trucks. Check.

Women dream about being rescued out of a burning building through the window by a big, strong fire fighter. Check.

Tomorrow we'll have the traditional, Danish Christmas lunch in Miracle. We have things to talk about now.

Mogens

PS: And I forgot to mention that it was CarpenterTorben's birthday that day!

After 9.2.0.8 patchset adgrants.sql fails "O/S Message: No such file or directory"

Madhu Thatamsetty - Wed, 2007-11-28 21:40
SYMPTOM:sqlplus "/ as sysdba" @adgrants.sqlSQL*Plus: Release 9.2.0.8.0 - Production on Sun Nov 18 16:14:16 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.8.0 - ProductionO/S Message: No such file or directorySQL*PLUS Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Converting MySQL "on update current_timestamp" to Oracle

Hampus Linden - Wed, 2007-11-28 15:20
Another short simple SQL for all out there in the process of converting old MySQL schemas to Oracle.

MySQL has got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestamp" feature can be quite handy if you have lazy developers who can't be bothered writing full insert statements. :)

The MySQL create table statement would be something like this:
create table p (
id int,
a varchar(10),
d timestamp DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
constraint p_pk primary key (id)
);

Not difficult to do in Oracle either, but we need a trigger to assist.
Example:
SQL> alter session set nls_Date_format='HH24:MI:SS';

Session altered.

SQL> create table p (id number, a varchar2(10), d date default sysdate,
constraint p_pk primary key (id));

Table created.

SQL> insert into p(id,a) values(1,'test');

1 row created.

SQL> host cat p_test.sql
CREATE OR REPLACE TRIGGER p_d_trig
BEFORE UPDATE ON p
FOR EACH ROW
BEGIN
select sysdate into :new.d from dual;
END p_d_trig;
/

SQL> @p_test

Trigger created.

SQL> select * from p;

ID A D
---------- ---------- --------
1 test 21:15:05

SQL> update p set a='foo' where id=1;

1 row updated.

SQL> select * from p;

ID A D
---------- ---------- --------
1 foo 21:16:44

SQL>

Pages

Subscribe to Oracle FAQ aggregator