DBA Blogs

any-longer-getting-ddl

Ayyu's Blog - Sun, 2007-12-23 04:06
Categories: DBA Blogs

Typical-index-maintenance-tasks-9i10g

Ayyu's Blog - Sun, 2007-12-23 04:03
Categories: DBA Blogs

keeping-eye-on-sessions

Ayyu's Blog - Sun, 2007-12-23 04:01
Categories: DBA Blogs

moving-tablespartitions-9i-onwards

Ayyu's Blog - Sun, 2007-12-23 04:00
Categories: DBA Blogs

Outerjoins in Oracle

Inside the Oracle Optimizer - Tue, 2007-12-11 15:00
Since release 6, Oracle has supported a restricted form of left outerjoin, which uses Oracle-specific syntax. In 9i, Oracle introduced support for ANSI SQL 92/99 syntax for inner joins and various types of outerjoin. The Oracle syntax for left outerjoin and that of ANSI SQL 92/99 are not equivalent, as the latter is more expressive.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

Oracle-Specific Syntax

Consider query A, which expresses a left outerjoin in the Oracle syntax. Here T1 is the left table whose non-joining rows will be retained and all non-joining rows of T2 will be null appended.


A.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);


ANSI Left Outerjoin

In the ANSI outer join syntax, query A can be expressed as query B.


B.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);


Equivalence

Consider the following queries. In the Oracle semantics, the presence of (+) on the filter predicate (e.g., T2.y (+) > 5 in query C) indicates that this filter must be applied to the table T2 before the outer join takes place.


C.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;


The ANSI left outer join query D is equivalent to C. Applying the filter on the right table in the left outer join is the same as combining the filter with the join condition.


D.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);


Similarly, the presence of (+) on the filter predicate, T2.y (+) IS NULL, in query E indicates that this filter must be applied to the table T2 before the outer join takes place.


E.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;


The ANSI left outer join query F is equivalent to E.


F.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);


Consider query G. Oracle will apply the filter, T2.y IS NULL, in query G after the outer join has taken place. G will return only those rows of T2 that failed to join with T1 or those whose T2.y values happen to be null.


G.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;


The ANSI left outer join query H is equivalent to G, as the WHERE clause in H is applied after the left outer join is performed based on the condition specified in the ON clause.


H.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;


Consider query I, where the filter on the left table is applied before or after the outer join takes place.


I.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;


The ANSI left outer join query J is equivalent to query I.


J.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;


Lateral Views

In Oracle, ANSI left and right outerjoins are internally expressed in terms of left outerjoined lateral views. In many cases, a left outerjoined lateral view can be merged and the ANSI left (or right) outerjoin can be expressed entirely in terms of Oracle native left outerjoin operator. (A lateral view is an inline view that contains correlation referring to other tables that precede it in the FROM clause.)

Consider the ANSI left outer join query K, which is first represented internally as L.


K.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);

L.
SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;


The lateral view in query L is merged to yield query M.


M.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;


Consider query N, which expresses a left outerjoin in the ANSI join syntax. Currently query N cannot be expressed using the Oracle native left outer join operator.


N.
SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;


The query N is converted into query O with a left outer-joined lateral view. The lateral view in O cannot be merged, since the filter on the left table specified in the ON clause must be part of the left outerjoin condition.


O.
SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;


Consider query P with a disjunction in the outer join condition. Currently N cannot be expressed using the Oracle native left outer join operator.


P.
SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);


The query P will be converted into Q with a left outer-joined lateral view containing the disjunctive join condition.


Q.
SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;


ANSI Full Outerjoin

Before Oracle 11gR1 all ANSI full outerjoins were converted into a UNION ALL query with two branches, where one branch contained a left outerjoined lateral view and the other branch contained a NOT EXISTS subquery. A native support for hash full outerjoin was introduced in 11gR1 to overcome this problem. When the native full outerjoin, cannot be used, Oracle reverts to the pre-11gR1 strategy.

Consider query R, which specifies an ANSI full outerjoin.


R.
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;


Before 11gR1, Oracle would internally convert query R into S.


S.
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);


With the native support of hash full outerjoin, R will be simply expressed as query T, where the view, VFOJ, is considered unmergeable.


T.
SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;


Conversion of Outerjoin into Inner Join

Consider query U. Here the filter predicate on the outer-joined table T2 does not contain the outerjoin operator (+); thus it will be applied after the left outerjoin has taken place. This will result in the elimination of all null appended rows of T2. Hence, Oracle converts the outer join into an inner join.


U.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;


The ANSI left outer join query V is equivalent to query U, as the WHERE clause in V is applied after the left outer join is performed based on the condition specified in the ON clause.


V.
SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;


Oracle converts the queries U and V into query W with an inner join.


W.
SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;



Q&A

Q1: I do not understand the queries N and O. What is the difference between
a filter appearing in the ON Clause or a filter appearing in the WHERE
clause?

A1: Consider two tables T11 and T22.

T11:
A | B
1 | 2
2 | 3
3 | 5

T22:
X | Y
7 | 2
8 | 4
9 | 4

The following ANSI left outer join query N’ involving
the tables T11 and T22 will return three rows, since
the filter, which always fails, is part of the join
condition. Although this join condition, which
comprises both the predicates in the ON clause,
always evaluates to FALSE, all the rows of the left
table T11 are retained in the result.

N’.
SELECT *
FROM T11 LEFT OUTER JOIN T22
ON (T11.A > 9 and T11.B = T22.Y);

A B X Y
------ ---------- ---------- ---------
1 2
2 3
3 5

However, if the filter, T11.A > 9, is moved to the WHERE clause,
the query will return zero rows.


Q2: Is the outer to inner join conversion a new feature?

A2: No. This feature has been avaliable since Release 7.


Q3: Has native full outer join been made available in
versions prior to 11gR1?

A3: Yes. It is available in 10.2.0.3 and 10.2.0.4, but not by
default.
Categories: DBA Blogs, Development

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

Oracle VM

Renaps' Blog - Wed, 2007-11-14 15:09

Oracle VM is now available for download.

You can read the news here.

Oracle VM Homepage.


Categories: DBA Blogs

11g on Windows (32-bit) is out!

Renaps' Blog - Mon, 2007-10-22 19:42

Thanks to OTN Headlines. I’m downloading Oracle 11g for windows at this moment.

But only the client version, since all Oracle servers I managed runs on Linux.

With the Windows client, I will be able to use cool new features like errorlogging.


Categories: DBA Blogs

11g on Linux x86_64 is out!

Renaps' Blog - Fri, 2007-10-12 19:15

This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

It is now available here: Oracle 11g on Linux x86_64.

I have been waiting for this release for a while since two of my databases are stuck to 32 bits as they need to send and receive data using “hsodbc” (only available for 32bit) for an ERP software.

Heterogeneous systems are quite common, and I am pretty sure that many of the enhancement requests for Generic Connectivity were focused on making them available for 64-bit platforms.
The
Metalink Note:361676.1, Is Generic Connectivity Available On The LINUX Or Windows 64-bit Platforms? , explains that Generic Connectivity will be available on Linux Red Hat 64-bit and SUSE 64-bit with 11gR1.

Another reason that makes me appreciate this new release is that I ran through several issues while trying to run Oracle with larger SGA’s on 32 bits platforms. I used RAMFS and HugePages to enable the use of 8 to 10GB of SGAs. The following links helped me in this process:

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Metalink Note: 317141.1, How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages

I am hoping that “hsodbc” will work fine on 11g x86_64; I’ll keep you posted on my findings…


Categories: DBA Blogs

Oracle Validated Configuration RPM for OEL5

Renaps' Blog - Wed, 2007-10-03 21:30

In the Oracle® Database Installation Guide for 11g there was a mention of Oracle Validated Configuration RPM.

When I first installed OEL5, the RPM’s for that o/s version were not yet released.

But since Oracle has now released the oracle-validated RPM’s for OEL5 on Unbreakable Linux Network. I decided to give it a try.

I installed OEL5 with the default installation. I then registered my machine with the up2date utility.

Do not forget to register to the proper channel on Unbreakable Linux Network or the following error will happen:

[root@oracletest ~]# up2date oracle-validated

Fetching Obsoletes list for channel: el5_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-

The following packages you requested were not found:
oracle-validated

Once registered, I validated the channel I was registered to:

[root@oracletest db_1]# up2date –nox –show-channels
el5_i386_oracle
el5_i386_latest

Here is my installation output:

[root@oracletest ~]# up2date oracle-validated

Fetching Obsoletes list for channel: el5_i386_oracle…
########################################

Fetching Obsoletes list for channel: el5_i386_latest…

Fetching rpm headers…
########################################

Name Version Rel
———————————————————-
oracle-validated 1.0.0 3.el5 i386

Testing package set / solving RPM inter-dependencies…
########################################
compat-db-4.2.52-5.1.i386.r ########################## Done.
compat-gcc-34-3.4.6-4.i386. ########################## Done.
compat-gcc-34-c++-3.4.6-4.i ########################## Done.
compat-libstdc++-33-3.2.3-6 ########################## Done.
elfutils-libelf-devel-0.125 ########################## Done.
gcc-4.1.1-52.el5.2.i386.rpm ########################## Done.
gcc-c++-4.1.1-52.el5.2.i386 ########################## Done.
glibc-devel-2.5-12.i386.rpm ########################## Done.
glibc-headers-2.5-12.i386.r ########################## Done.
libXp-1.0.0-8.i386.rpm: ########################## Done.
libaio-devel-0.3.106-3.2.i3 ########################## Done.
libstdc++-devel-4.1.1-52.el ########################## Done.
oracle-validated-1.0.0-3.el ########################## Done.
sysstat-7.0.0-3.el5.i386.rp ########################## Done.
unixODBC-2.2.11-7.1.i386.rp ########################## Done.
unixODBC-devel-2.2.11-7.1.i ########################## Done.
cpp-4.1.1-52.el5.2.i386.rpm ########################## Done.
elfutils-libelf-devel-stati ########################## Done.
libgcc-4.1.1-52.el5.2.i386. ########################## Done.
libgomp-4.1.1-52.el5.2.i386 ########################## Done.
libstdc++-4.1.1-52.el5.2.i3 ########################## Done.
Preparing ########################################### [100%]

Installing…
1:libgcc ########################################### [100%]
2:libstdc++ ########################################### [100%]
3:libstdc++-devel ########################################### [100%]
4:unixODBC ########################################### [100%]
5:glibc-headers ########################################### [100%]
6:glibc-devel ########################################### [100%]
7:compat-gcc-34 ########################################### [100%]
8:compat-gcc-34-c++ ########################################### [100%]
9:unixODBC-devel ########################################### [100%]
10:compat-db ########################################### [100%]
11:compat-libstdc++-33 ########################################### [100%]
12:libgomp ########################################### [100%]
13:cpp ########################################### [100%]
14:gcc ########################################### [100%]
15:gcc-c++ ########################################### [100%]
16:sysstat ########################################### [100%]
17:libaio-devel ########################################### [100%]
18:libXp ########################################### [100%]
19:elfutils-libelf-devel ########################################### [100%]
20:oracle-validated ########################################### [100%]
21:elfutils-libelf-devel-s########################################### [100%]
The following packages were added to your selection to satisfy dependencies:

Name Version Release
————————————————————–
compat-db 4.2.52 5.1
compat-gcc-34 3.4.6 4
compat-gcc-34-c++ 3.4.6 4
compat-libstdc++-33 3.2.3 61
elfutils-libelf-devel 0.125 3.el5
gcc 4.1.1 52.el5.2
gcc-c++ 4.1.1 52.el5.2
glibc-devel 2.5 12
glibc-headers 2.5 12
libXp 1.0.0 8
libaio-devel 0.3.106 3.2
libstdc++-devel 4.1.1 52.el5.2
sysstat 7.0.0 3.el5
unixODBC 2.2.11 7.1
unixODBC-devel 2.2.11 7.1
cpp 4.1.1 52.el5.2
elfutils-libelf-devel-static 0.125 3.el5
libgcc 4.1.1 52.el5.2
libgomp 4.1.1 52.el5.2
libstdc++ 4.1.1 52.el5.2

Oracle Validated Configuration did not only install the necessary RPM’s, it also created the oracle user/group’s, along with a couple of Oracle installation’s Requirements file modifications.

I was able to see my system configuration changes with this log file:

/etc/sysconfig/oracle-validated/results/orakernel.log

Creating oracle user passed

Verifying kernel parameters as per Oracle recommendations…
fs.file-max 327679
kernel.msgmni 2878
kernel.msgmax 65536 8192
kernel.sem 250 32000 100 142
kernel.shmmni 4096
kernel.shmall 268435456 3279547
kernel.sysrq 0 1
net.core.rmem_default 262144
net.core.rmem_max 2097152
net.core.wmem_default 262144
net.core.wmem_max 262144
fs.aio-max-nr 3145728
net.ipv4.ip_local_port_range 1024 65000
Setting kernel parameters as per oracle recommendations…
Altered file /etc/sysctl.conf
Original file backed up at /etc/sysctl.conf.orabackup
Verifying & setting of kernel parameters passed

Verifying oracle user OS limits as per Oracle recommendations…
oracle soft nofile 131072
oracle hard nofile 131072
oracle soft nproc 131072
oracle hard nproc 131072
oracle soft core unlimited
oracle hard core unlimited
oracle soft memlock 3500000
oracle hard memlock 3500000
Setting oracle user OS limits as per Oracle recommendations…
Altered file /etc/security/limits.conf
Original file backed up at /etc/security/limits.conf.orabackup
Verifying & setting of user limits passed

Verifying kernel boot parameters as per Oracle recommendations…
Setting kernel boot parameters as per Oracle recommendations…
Boot parameters will be effected on next reboot
Altered file /boot/grub/grub.conf
Original file backed up at /boot/grub/grub.conf.orabackup
Verifying & setting of boot parameters passed

Verifying module parameters as per Oracle recommendations…
Setting module parameters as per Oracle recommendations…
Altered file /etc/modprobe.conf
Original file backed up at /etc/modprobe.conf.orabackup
e1000 settings modified to include FlowControl=1
insmod /lib/modules/2.6.18-8.el5/kernel/drivers/char/hangcheck-timer.ko hangcheck_reboot=1
hangcheck-timer module settings modified to include hangcheck_reboot=1
Oct 2 12:01:35 oracletest ntpd[3529]: time reset -0.771217 s
Oct 2 12:05:38 oracletest ntpd[3529]: synchronized to LOCAL(0), stratum 10

Note that every modified files are backup up by the installation.

All this automation is very neat and save some time. However, some manual work still needs to be done afterwards.
The following files were not modified by the oracle-validated-1.0.0-3.el5.i386 RPM:

–/etc/pam.d/login

–/etc/profile

connected as user oracle:

–.bash_profile

Setting Shell Limits for the oracle User and Configuring the oracle User’s Environment section in the Oracle® Database Installation Guide recommend to configure those files above.

Also, Oracle 11g installer failed the Prerequisites Checks for 2 kernel parameters.

Checking operating system requirements …
Checking kernel parameters
Checking for rmem_default=4194304; rmem_default=262144. Failed <<<<
Checking for rmem_max=4194304; rmem_max=2097152. Failed <<<<

I had to manually change those 2 parameters in the /etc/sysctl.conf file.

References:

Wim Coekaerts Blog

Kevin Closson’s Oracle Blog

Oracle and Linux on OTN


Categories: DBA Blogs

Oracle 11g New Features DBMS_ADDM for RAC

Virag Sharma - Thu, 2007-09-20 08:37
Virag Sharma virag123@gmail.com

In OCP Oracle Database 10g Exam Guidechapter 3 “Automatic Database Management” there is question


5. To retrieve the ADDM reports using SQL, what do you need to do?

A. Run the addmrpt.sql SQL script
B. Use the DBA_ADDM view
C. Use the DBA_ADVISOR view
D. Use the DBMS_ADDM package

Answer “A” is correct and D is wrong because there is no PL/SQL package named DBMS_ADDM.
But in 11g , it is not true , i.e. Package DBMS_ADDM is there in 11g. In case of RAC script
addmrpt.sql
run give report for single instance, not report of all instance in RAC. But using DBMS_ADDM , we can generate report for all instance of RAC.

Different mode of DBMS_ADDM

  1. Database Mode Instance Mode
  2. Partial Mode

Database Mode of DBMS_ADDM


In database mode DBMS_ADDM , analyze all instance in RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'DB_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);

END;

Instance Mode of DBMS_ADDM
In Instance mode DBMS_ADDM , analyze one particular instance

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_MODE_TEST_RAG';
:start_snap_id := 100 ;
:end_snap_id := 200 ;
:INST_NUM := 2;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;

/

Partial mode of DBMS_ADDM

In partial mode DBMS_ADDM analyze subset of instances. for example we want to analyze instance 2 and 4 out of four node RAC

VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'PART_MODE_TEST_RAG';
:start_snap_id :=100;
:end_snap_id := 200;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'2,4', :start_snap_id, :end_snap_id);

END;

/

Displaying an ADDM Report
  SET LONG 1000000 PAGESIZE 0;
   SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

or

SELECT DBMS_ADDM.GET_REPORT('DB_MODE_TEST_RAG') FROM DUAL;


Categories: DBA Blogs

Oracle 11g Documentation

Renaps' Blog - Fri, 2007-09-14 09:02

Since reading the documentation is always a good idea when a new Oracle release is out, I decided to read Oracle® Database Upgrade Guide and the Oracle® Database New Features Guide before getting into the 11g installation guide.

It is funny how I haven’t yet downloaded the new Oracle 11g database and I am already using one of its new features!

Oracle 11g now enables users to send out comments, making it faster and easier to everyone to enhance the documentation.
This new feature have also been discussed here:
IT-eye Weblog
OracleAppsLab
OTN TechBlog

While reading through the 2 guides, I used this new feature to report a couple of errors:
Oracle has responded to my user comments pretty fast:

Thank you for sending us this correction to the Oracle Database Upgrade Guide. The problem has been fixed and the correction should appear in the next revision of the book (probably in part number B28300-02).

Submitter: my_mail
Book title: Oracle Database Upgrade Guide
Part number: b28300
Release: 11g Release 1 (11.1)
Topic title: Compatibility and Interoperability
URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/compat.htm
Status: Forwarded
Submitted on: 28-AUG-07

In the Automatic Maintenance Tasks Management section, the “See Also” link was pointing to the wrong URL: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/repair.htm#ADMIN022

This page should have been pointing to a subsection of the Automatic Maintenance Tasks Management feature…


Categories: DBA Blogs

Oracle 11g Database New Features: Data Guard Enhancements

Virag Sharma - Wed, 2007-09-12 07:59

Why We need Data Guard ?? for

  • Data protection
  • Data Availability

11g Increase ROI from standby systems and enhance manageability, New feature like Active Data Guard , Snapshot standby make better ROI.

Here is some data guard category and there enhancement

1) Data Protection
  • Advanced Compression
  • Lost-write protection
  • Fast-Start Failover
2) Increase ROI
  • Active Data Guard
  • Snapshot Standby
3) High Availability
  • Faster Redo Apply
  • Faster failover & switchover
  • Automatic Failover using ASYNC
4) Manageability
  • Mixed Windows/Linux

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option
Enables read-only access to a physical standby database while Redo Apply is active
It is now possible to query a physical standby database while Redo Apply is active.This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection.
( We can do same with logical standby , what is diffrent here , well redo apply method is faster in physical stand by ie redo apply methode is diffrent in physical standby)


  • Stop log apply
Alter database recover managed standby database cancel;
  • Open database for read-only access
alter database open
  • Once database open start redo apply
alter database recover managed standby database
using current logfile disconnect from session;


Snapshot Standby


This enhancement is good example of ROI. This feature allow us to better utilization of standby. This feature Truly leverages DR hardware for multiple purposes.For example convert physical database to snapshot standby database do testing/ application patching etc. After testing convert back snapshot database back to physical standby.













  • -- Convert physical database to snapshot
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
  • --
  • -- Do testing on database
  • --
  • -- Convert snapshot Database to physical database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


This Similar to storage snapshot , but it useage same space , not additional space is needed ( I know this possibly can achieve in 10g R2 too , using flashback feature , But for sure it is not easy as it is in 11g )


Redo compression

To transport redo data in compressed form to destination use COMPRESSION attribute.
By default compression is disabled.


  • Alter system set log_archive_dest_1 = 'SERVICE=standby1 compression=ENABLE';

  • select dest_name , compression from v$archive_dest;

    SQL>
    DEST_NAME COMPRES
    --------------------------------- -------
    LOG_ARCHIVE_DEST_1 DISABLE
    LOG_ARCHIVE_DEST_2 ENABLE
    LOG_ARCHIVE_DEST_3 DISABLE
    LOG_ARCHIVE_DEST_4 DISABLE
    LOG_ARCHIVE_DEST_5 DISABLE
    LOG_ARCHIVE_DEST_6 DISABLE
    LOG_ARCHIVE_DEST_7 DISABLE
    LOG_ARCHIVE_DEST_8 DISABLE
    LOG_ARCHIVE_DEST_9 DISABLE
    LOG_ARCHIVE_DEST_10 DISABLE

    10 rows selected.

Data Protection

New Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo stream
Version discrepancy implies lost write on either primary or standby database

db_lost_write_protect


Managibilty Enhancement

SYS user and password files no longer required for redo transmission authentication. Non-SYS user can be specified through the parameter, This user must have the SYSOPER privileges, Requires password for this user to be the same at primary and all standbys
Upon SYSDBA / SYSOPER changes, password file must be copied from the primary to all physical standby databases

redo_transport_user

Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (- Since 11g Linux installer only available, so not able to test this feature)

Fast-Start Failover

Immediate automatic failover for user-configurable health conditions



DGMGRL> show FAST_START FAILOVER
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION ];

Condition examples:
  • Datafile Offline
  • Corrupted Controlfile
  • Corrupted Dictionary
  • Inaccessible Logfile
  • Stuck Archiver
  • Any explicit ORA-xyz error


Application can also request for failover using package DBMS_DG

DBMS_DG.INITIATE_FS_FAILOVER



Categories: DBA Blogs

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

Virag Sharma - Sat, 2007-09-08 21:56

I have rated 5 *(star) for some sites , which I like Most

( these links collected from google alert )

  1. Oracle Database Online Documentation 11g Release 1 (11.1) *****
  2. ORACLE-BASE - Articles on Oracle 11g new features *****
  3. Oracle Database 11g on OTN
  4. Oracle Database 11g: The Top Features for DBAs and Developers
  5. Pythian Group Blog » Tuning Pack 11g : Real-Time SQL Monitoring
  6. PSOUG - New in 11gR1*****
  7. Robert G. Freeman’s Blog: 11g Security New Feature… A short one…
  8. Oracle 11g new Features Summary by Burleson
  9. My top 10 Oracle 11g New Features Part 3 - 5 nice optimizer statistics
  10. Oracle 11g Top New Features for DBA Virag Sharma
  11. Oracle Magazine: PL/SQL Practices: On the PL/SQL Function Result Cache
  12. Changes in Oracle 11.1 - Julian Dyke *****
  13. Blogging about 11g - Part 7 - Function Result Cache
  14. Oracle 11g New Features SQL plan management (SPM)
  15. Oracle 11g New feaures : Case Sensitive Password
  16. SQL Performance Analyzer (SPA) Part - 2
  17. Oracle 11g ADR Automatic Diagnostic Repository
  18. New Parameters in 11g (Part 2) | Dizwell Informatics
  19. Oracle 11g DRCP: Database Resident Connection Pooling - second attempt
  20. Oracle 11g internals part 1: Automatic Memory Management
  21. My top 10 Oracle 11g New Features Part 4 - the SQL Query Result Cache
  22. Oracle 11g Tips by Burleson Consulting

You can have a look on following forum as well. Good thing about this forum is that , it update time to time , with latest 11g Links. Thanks to TongucY

http://forums.oracle.com/forums/thread.jspa?threadID=542281&tstart=90

- Vi
Categories: DBA Blogs

Oracle 11g New Features SQL plan management (SPM)

Virag Sharma - Fri, 2007-09-07 07:54
You might have noticed that execution plan changed in CBO , specially in following case

  • Database Upgrade
  • Database / Schema Stats collection
  • Change in environment ( LinkUnix to Linux Migration )
  • Change in data














Case
:
You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

Solution: Oracle 11g new features SQL plan management (SPM) records and evaluates the execution plans of SQL Statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Note:- Before upgrading database to 11g (11.1.0.6) , i have collect SQL Tuning set (STS) for 7 Day using "capture_cursor_cache_sqlset". By assuming that in 7 day all SQL stas will collected in STS. After that upgraded database to 11g ( from 10g 10.2.0.3) , i have used this STS to compare execution plan from 11g. Please check "SQL Performance Analyzer Part - 2"
for steps to transfer STS one database to other database


1) Capturing SQL Plan Baselines

· Automatic Plan Capture ( 11g Only )

· Manual Plan Capture ( 10g and 11g )

2) Make Changes Upgrade Database / collect stats / Migrate Database to Linux

3) Upload SQL Plan Baseline

4) Enable the use of SQL plan baselines

5) Evolving SQL Plan Baselines

1) Capturing SQL Plan Baselines in oracle 10g before upgrading to 11g

a)
Automatic Plan Capture(11g only)

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES set true , then database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer.

>

In init.ora file

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true

OR

SQL>Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Note : - Above automatic plan capture only work on 11g , if you want to capture plan in 10g , then check section “Manual Plan Capture”

b) Manual Plan Capture ( 10g and 11g )

Following script will capture sql plan in SQL tuning set for 7 Day ( this script will run for 7 day , you can change time according to your need )

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';
BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,
604800,
1,
'MERGE',
dbms_sqltune.MODE_ACCUMULATE_STATS
);

END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

  • time_limit : 604800 ( The total amount of time, in seconds, to execute , 7 day = 7 * 24 * 60 * 60 = 604800 )
  • repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
  • capture_option During capture, either insert new statements, update existing ones, or both.
    • 'INSERT'
    • 'UPDATE',
    • 'MERGE'
  • capture_mode capture Option (UPDATE and MERGE capture options).
    • MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions
      seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL statements or the LOAD_SQLSET procedure, which accesses the data source only once.

2) Make Changes e.g. Upgrade Database / collect stats / Migrate Database to Linux Collect stats

exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 254');

exec dbms_stats.Gather_Database_Stats;

3) Upload SQL Plan Baseline

If you have collected stats manually in SQL tuning sets ( STS) , then you need to
upload baseline from STS

-- Upload plan manually using dbms_spm
--

variable pls number;

exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS_RAG_CMUL',-
basic_filter=>'parsing_schema_name like ''APPS'' and plan_hash_value!=0', -
fixed=>'NO',commit_rows=>1000);

4) Enable the use of SQL plan baselines

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter to TRUE. By default, this parameter is set to TRUE.

--In init.ora file
--

OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE

OR

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES= true;

5) Evolving SQL Plan Baselines

I have captured 10g plans in STS and after upgrading database to 11g uploaded those plans ,using dbms_spm.load_plans_from_sqlset. After uploading plans manually , I have set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.

--Check not accepted Plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed
from dba_sql_plan_baselines
Where ACCEPTED='NO' and
PARSING_SCHEMA_NAME like 'APPS';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
--------------------- ------------------------- ------- --------- -------
SYS_SQL_9295397103ae5ebe SYS_SQL_PLAN_74720f16b0fcefa7 YES NO NO
SYS_SQL_9295397103ad3eba SYS_SQL_PLAN_03ad3ebaa086802f YES NO NO
SYS_SQL_434ef30d9da6a29b SYS_SQL_PLAN_9da6a29b1f6e321d YES NO NO
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES NO NO
.......................................... ....................................

40 rows selected.

-- Displaying SQL Plan Baselines
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902
-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------

42 rows selected.

SQL>


Evolving Plans with pls/sql function DBMS_SPM.EVOLVE_SQL_PLAN_BASELIN

--Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
--

SQL> DECLARE
report clob;
BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_13836d6b3da62bbb');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =SYS_SQL_13836d6b3da62bbb
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES

Plan: SYS_SQL_PLAN_3da62bbbe5990995
-----------------------------------
Plan was verified: Time used .06 seconds.
Failed performance criterion: Compound improvement ratio <= .3.

Baseline Plan Test Plan Improv. Ratio

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

Execution Status: COMPLETE COMPLETE
Rows Processed: 1460 1460
Elapsed Time(ms): 10 10 1
CPU Time(ms): 9 9 1
Buffer Gets: 84 286 .29
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------

Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

PL/SQL procedure successfully completed.

SQL>

Just for demo purpose , lets accept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995')

-- Acccept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995') and Fixed it
--

variable cnt number;
exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

PL/SQL procedure successfully completed.
SQL>

-- Same pl/sql function used to fix plans
-- Optimizer always picked FIXED plans

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'FIXED', attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL>

- - Check status of plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines
2 Where FIXED='YES' and PARSING_SCHEMA_NAME like '';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
----------------------------- ---------------------------- -------------- --------
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES YES YES

SQL>

>

>

-- Displaying SQL Plan Baselines after changes
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------
42 rows selected.


ReferenceSQL Plan Management Chapter 15
Categories: DBA Blogs

Configure SSO(single sign-on) with Apex

Pankaj Chandiramani - Fri, 2007-08-31 01:25

I am loving the experience with Apex , we plan to have couple of new application using APEX , for that i neeed to configure SSO with apex .

It can be done in 3 steps :

  1. Creating partner app
  2. Installing sdk (You need to get sso sdk from midtier)
  3. Configuring apex for sso(You need to login a zip file from here)
For step 1 , you need below :
HOME URL : http: // apex_server_name/pls/apex
Success URL : http: // apex_server_name/pls/apex/wwv_flow_custom_auth_sso.process_success
Log Out URL : http: // apex_server_name/pls/apex/apex

Note : not covering the actual creation process , pls look at "Creating Partner App in AS admin guide"

For Step 2:
First unlock the flows schema according to the apex version you have .
SQL> alter user flows_030100 account unlock; (If your Apex version is 2.0.0.0.0 change acccordingly)

Now you need to copy sso sdk from the midtier , Login to Middle tier $ORACLE_HOME/sso/lib
copy ssosdk902.zip , unzip it . It creates some sql scripts & packages .

Login to db as user flows_030100 & run loadsdk.sql
SQL>@loadsdk.sql (Make sure you are executing under flows_XXXX)

Above will load the sso schema into flows account.

For Step 3:
Now we need to wire sso & apex schema(flows_xx)

SQL> @regapp.sql
It will prompt you for
Enter value for listener_token: HTML_DB:Hostname:7777
Enter value for site_id: enter_value_returned_while_registering_sso
Enter value for site_token: enter_value_returned_while_registering_sso
Enter value for login_url: http: // apex_url_here/pls/orasso/orasso.wwsso_app_admin.ls_login
Enter value for encryption_key: enter_value_returned_while_registering_sso
Enter value for ip_check: N

These above value you will get from Registring as partner app in step 1

Now configure authentication packages under flows_XXX schema
You need to login a zip file from here:
Unzip & get 2 files , execute as below

SQL> @custom_auth_sso.sql
SQL> @custom_auth_sso.plb

Finally you should lock flows_XXXX schema & grant execute permission to public user
SQL> alter user flows_020200 account unlock;
SQL> grant execute on wwv_flow_custom_auth_sso to APEX_PUBLIC_USER;

All set , once you use the authentication schema as sso , you can see the login page .

Categories: DBA Blogs

Recovering Corrupted Data Blocks

Renaps' Blog - Mon, 2007-08-27 10:46

Last Week, I experienced a kernel crash using Oracle 10.2.0.3 on my Oracle Enterprise Linux 4 sandbox server. There was not a single reaction from the server, I had to reboot the box by pressing the reset button.
I happened to have a backup of this sandbox database, which gave me the opportunity to perform a media recovery.

The situation was that the undo Tablespace had a corrupt block.

Here is the alert log content:

Mon Aug 20 08:54:44 2007
SMON: enabling cache recovery
Mon Aug 20 08:54:45 2007
Errors in file /opt/oracle/app/oracle/admin/orcl/udump/orcl_ora_6733.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 9)
ORA-01110: data file 2: ‘/OraData2/oradata/orcl/undotbs01.dbf’
Mon Aug 20 08:54:45 2007
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Instance terminated by USER, pid = 6733
ORA-1092 signalled during: ALTER DATABASE OPEN…

Just when I was about to restore the whole datafile, I remembered a nice Oracle 10g feature called Block-level media recovery.

Note that this is a Enterprise Edition feature.

I had a huge load of tasks to do that particular morning and I was very happy with the time saved recovering that datafile.

Here is the RMAN output session:

RMAN> blockrecover datafile 2 block 9;
Starting blockrecover at 20-AUG-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp tag=BACKUP_ORCL.TO_081907110003
channel ORA_DISK_1: block restore complete, elapsed time: 00:03:56
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 20-AUG-07
RMAN> backup validate datafile 2;
Starting backup at 20-AUG-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/OraData2/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 20-AUG-07

It was as easy as this ! The database is now up & running, with no further issue in this matter.


Categories: DBA Blogs

Installing Apex 3.0.1

Pankaj Chandiramani - Mon, 2007-08-20 20:33

I have been using Apex extensively now , have created 3-4 application for production usage & i can finally say that its easy to use for us non developers also ;)

I will be covering the installation of Apex 3.0.1 in this post & will cover the SSO part in next .

Software Req:

  1. Download Apex 3.0.1 from here
  2. 10g Db
  3. Http server
In short , the process of Installing & configuring apex goes like this :
Install 10g Db --> Install Http server (i used 10g AS) -->Install Apex

Starting the Apex Installation
I assume you have 10g Db installed , else read this post for details .

You need to create 2 table space , i created  apex1 & apex2 to seed the apex data :

create tablespace apex1 datafile '/apexdb/apex1.dbf' size 50M autoextend on next 10M maxsize 300M;

create tablespace apex2 datafile '/apexdb/apex2.dbf' size 50M autoextend on next 10M maxsize 300M;

Now we will seed the db with Apex configs .
Download Apex 3.0.1 from here & unzip it .

This will craete a subdirectory with name apex , cd into it & start sqlplus as sysdba & run apexins.sql as shown below.
cd apex

sqlplus system@tns_alias as sysdba
@apexins.sql {pass} {ts for user} {ts for files} {ts for temp} /i/

Where

pass - Password for Apex Super User
ts for user - Tablespace for User data (Created above)
ts for files - Tablespace for Apex files (Created above)
ts for temp - temporary tablespace (Usually Temp)
i - is the alias for image directory configuration in Application Server (Apache httpd.conf)

According to our config above , i used :

Sqlplus > @apexins.sql welcome apex1 apex2 temp /i/

It Takes ard 1 hr to get the seed data .

After this is complete , we go to the HTTP server location to configure Apex with it . AS we have the Oracle 10g AS installed we go to $ORACLE_HOME/Apache/modplsql/conf directory .

There in dads.conf we add the below lines :

#######
Alias /i/ "/apex/images/"  (replace with your image location)

AddType text/xml xbl
AddType text/x-component htc
&lt;Location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString host:port:service_name ServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apex_public_user_password
Allow from all
&lt;/Location>
###################

For above replace according to your server & install locations , we did below :
PlsqlDatabaseConnectString xyz.com:1521:apexdb apexdb

Post Install
After this you need to restart the Appache server as shown below :

dcmctl updateconfig -ct ohs
opmnctl restartproc ias-component=HTTP_Server (Restart HTTP Server to check it)
ou are all set to use the Apex , try http://hostname.domainname:port/pls/apex

I will be writing on how to configure sso in next post

Categories: DBA Blogs

11g , get -set - go !!!!

Pankaj Chandiramani - Fri, 2007-08-10 01:55

11g for linux is available for download @OTN from here.
Read all abt the new features for HA , Db Replay   etc from http://www.oracle.com/technology/products/database/oracle11g/index.html


Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs