DBA Blogs

Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer

Inside the Oracle Optimizer - Tue, 2008-02-26 15:34
One of the most daunting activities a DBA can undertake is upgrading the database to a new version. Having to comprehend all of the new features and to deal with potential plan changes can be overwhelming. In order to help DBA's upgrade from Oracle Database 9i to 10g a new whitepaper called "Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer" has recently been posted on Oracle Technology Network (OTN). This paper aims to explain in detail what to expect from the CBO when you upgrade from Oracle database 9i to 10g and describes what steps you should take before and after the upgrade to minimize any potential SQL regressions. This is a must read for any DBA planning on upgrading from 9i to 10g in the near future!
Categories: DBA Blogs, Development

Reactive performance management By Craig Shallahamer

Virag Sharma - Sun, 2008-02-17 00:00

Last week Oracle University arrange Seminar by Performance Management guru Craig Shallahamer in India. Craig brings his unique experiences to many as a keynote speaker, a sought after teacher, a researcher and publisher for improving Oracle performance management, and also the architect of Horizone, OraPub's service level management solution.<?xml:namespace prefix = o />


His way of teaching is very very good , also contents of seminar is different and very effective. He focused on accurate performance diagnosis of even the most complex Oracle systems and how oracle interact with OS and application’s. I have been reading Craig’s papers/articles for long time , but never got full/big picture. After attending seminar his papers/articles look easy to understand.

Focus point of seminar was how to pin point issue area and different method to resolve it. He also cover some area of Mutex, IMU ( In memory Undo ) and performance forecasting etc. The way he teach latch Internals, anybody can understand it, even small kids J, he Create an honest and understandable story explaining the problem, the solutions, and how to best implement your solutions in a complex and highly available Oracle environment.

Second day of training , we had little tough time , as next to our conference hall in Oberoi Hotel, New Delhi , Shahrukh Khan( One of India Famous actor) was coming to inugrate/launch "Kya Aap Paanchvi Paas Se Tez Hain" new serial on Star Plus channel. But Craig enjoy music and continue with training

Categories: DBA Blogs

The “log file sync” wait event is not always spent waiting for an I/O

Christian Bilien - Tue, 2008-02-12 10:41
I went through a rather common problem some times ago, which shows how one can get easily fooled by the “log file sync” wait event. Here is a log file sync wait histogram which was interpreted as a poor storage array response time by the DBAs, often a good way to get off the hook […]
Categories: DBA Blogs

Displaying and reading the execution plans for a SQL statement

Inside the Oracle Optimizer - Thu, 2008-02-07 14:37
Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.

While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. For example, consider the following query based on the SH schema (Sales History):



select prod_category, avg(amount_sold)
from sales s, products p
where p.prod_id = s.prod_id
group by prod_category;


The tabular representation of this query's plan is:



------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------

While the tree-shaped representation of the plan is:

GROUP BY
|
JOIN
_____|_______
| |
ACCESS ACCESS
(PRODUCTS) (SALES)




When you read a plan tree you should start from the bottom up. In the above example begin by looking at the access operators (or the leaves of the tree). In this case the access operators are implemented using full table scans. The rows produced by these tables scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-opertor.

The execution plan generated for a SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance, the lower is the cost the better is the performance. The cost model used by the query optimizer accounts for the IO, CPU, and network usage in the query.

There are two different methods you can use to look at the execution plan of a SQL statement:


  1. EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.

  2. V$SQL_PLAN - A dictionary view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache.


Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.

Displaying an execution plan has been made easier after the introduction of the dbms_xplan package in Oracle 9i and by the enhancements made to it in subsequent releases. This packages provides several PL/SQL procedures to display the plan from different sources:


  1. EXPLAIN PLAN command

  2. V$SQL_PLAN

  3. Automatic Workload Repository (AWR)

  4. SQL Tuning Set (STS)

  5. SQL Plan Baseline (SPM)


The following examples illustrate how to generate and display an execution plan for our original SQL statement using the different functions provided in the dbms_xplan package.

Example 1 Uses the EXPLAIN PLAN command and the dbms_xplan.display function.


SQL> EXPLAIN PLAN FOR
2 select prod_category, avg(amount_sold)
3 from sales s, products p
4 where p.prod_id = s.prod_id
5 group by prod_category;

Explained.



SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'basic'));

------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------


The arguments are for dbms_xplan.display are:


  • plan table name (default 'PLAN_TABLE'),

  • statement_id (default null),

  • format (default 'TYPICAL')


More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

Example 2 Generating and displaying the execution plan for the last SQL statement executed in a session:



SQL> select prod_category, avg(amount_sold)
2 from sales s, products p
3 where p.prod_id = s.prod_id
4 group by prod_category;

no rows selected


SQL> select plan_table_output
2 from table(dbms_xplan.display_cursor(null,null,'basic'));

------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------


The arguments used by dbms_xplay.dispay_cursor are:


  • SQL ID (default null, null means the last SQL statement executed in this session),

  • child number (default 0),

  • format (default 'TYPICAL')


The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.


Example 3 Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:

  1. Directly:

    SQL> select plan_table_output from
    2 table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));


  2. Indirectly:

    SQL> select plan_table_output
    2 from v$sql s,
    3 table(dbms_xplan.display_cursor(s.sql_id,
    4 s.child_number, 'basic')) t
    5 where s.sql_text like 'select PROD_CATEGORY%';


Example 4 - Displaying an execution plan corresponding to a SQL Plan Baseline. SQL Plan Baselines have been introduced in Oracle 11g to support the SQL Plan Management feature (SPM). In order to illustrate such a case we need to create a SQL Plan Baseline first.


SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> select prod_category, avg(amount_sold)
2 from sales s, products p
3 where p.prod_id = s.prod_id
4 group by prod_category;

no rows selected

If the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verified this using the follows query:


SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
2 from dba_sql_plan_baselines
3 where sql_text like 'select prod_category%';

SQL_HANDLE PLAN_NAME ACC
------------------------------ ------------------------------ ---
SYS_SQL_1899bb9331ed7772 SYS_SQL_PLAN_31ed7772f2c7a4c2 YES


The execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:

  1. Directly
    select t.* from
    table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
    format => 'basic')) t


  2. Indirectly
    select t.*
    from (select distinct sql_handle
    from dba_sql_plan_baselines
    where sql_text like 'select prod_category%') pb,
    table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
    null,'basic')) t;



The output of either of these two statements is:



----------------------------------------------------------------------------
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
where p.prod_id = s.prod_id group by prod_category
----------------------------------------------------------------------------

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

Plan hash value: 4073170114

---------------------------------------------------------
Id Operation Name
---------------------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 VIEW index$_join$_002
4 HASH JOIN
5 INDEX FAST FULL SCAN PRODUCTS_PK
6 INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
7 PARTITION RANGE ALL
8 TABLE ACCESS FULL SALES
---------------------------------------------------------


Formatting


The format argument is highly customizable and allows you to see as little (high-level) or as much (low-level) details as you need / want in the plan output. The high-level options are:

  1. Basic
    The plan includes the operation, options, and the object name (table, index, MV, etc)
  2. Typical
    It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.
  3. All
    It includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs. The last two pieces of information can be used as arguments to add hints to the statement.
The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.
For example,


select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

-------------------------------------------------------
Id Operation Name Cost (%CPU)
-------------------------------------------------------
0 SELECT STATEMENT 17 (18)
1 HASH GROUP BY 17 (18)
* 2 HASH JOIN 15 (7)
3 TABLE ACCESS FULL PRODUCTS 9 (0)
4 PARTITION RANGE ALL 5 (0)
5 TABLE ACCESS FULL SALES 5 (0)
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")



select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

----------------------------------------------------------------------------
Id Operation Name Rows Time Pstart Pstop
----------------------------------------------------------------------------
0 SELECT STATEMENT 4 00:00:01
1 HASH GROUP BY 4 00:00:01
* 2 HASH JOIN 960 00:00:01
3 TABLE ACCESS FULL PRODUCTS 766 00:00:01
4 PARTITION RANGE ALL 960 00:00:01 1 16
5 TABLE ACCESS FULL SALES 960 00:00:01 1 16
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

Note Section


In addition to the plan, the package displays notes in the NOTE section, such as that dynamic sampling was used during query optimization or that star transformation was applied to the query.
For example, if the table SALES did not have statistics then the optimizer will use dynamic sampling and the plan display will report it as follows (see '+note' detail in the query):


select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +note'));

------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------

Note
-----
- dynamic sampling used for this statement


Bind peeking



The query optimizer takes into account the values of bind variable values when generation an execution plan. It does what is generally called bind peeking. See the first post in this blog about the concept of bind peeking and its impact on the plans and the performance of SQL statements.
As stated earlier the plan shown in V$SQL_PLAN takes into account the values of bind variables while the one shown from using EXPLAIN PLAN does not. Starting with 10gR2, the dbms_xplan package allows the display of the bind variable values used to generate a particular cursor/plan. This is done by adding '+peeked_binds' to the format argument when using display_cursor().
This is illustrated with the following example:



variable pcat varchar2(50)
exec :pcat := 'Women'

select PROD_CATEGORY, avg(amount_sold)
from sales s, products p
where p.PROD_ID = s.PROD_ID
and prod_category != :pcat
group by PROD_CATEGORY;

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));

------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'
Categories: DBA Blogs, Development

What CPU Jan 2008 Patch will not tell you

Virag Sharma - Tue, 2008-02-05 09:55

What CPU Jan 2008 Patch will not tell you

File system space :- opatch suppose to check files system space as a part of Running prerequisite checks... and start apply patch when enough space is there.

Patching component oracle.rdbms, 11.1.0.6.0...

Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzsr.o"
Updating archive file "/u01/app/oracle/product/11.1.0.6/lib/libserver11.a" with "lib/libserver11.a/kzu.o"

The following actions have failed:

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzsr.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Archive not applied /home/oracle/patch/CPU2008/6646866/6650135/files/lib/libserver11.a/kzu.o to /u01/app/oracle/product/11.1.0.6/lib/libserver11.a... ''

Do you want to proceed? [yn]

Well I came out with N option. When we started opatch again , it gave error some thing like spacecheck fail.

So make sure you have enough space on file system.

In case of RAC database, CPU patch readme.txt will not tell you that one need to set cluster_database = false , before starting database in upgrade mode(“startup upgrade”). For compiling views and objects.

So make sure, in RAC environment you have set cluster_database = false before starting database in upgrade mode

<?xml:namespace prefix = o />

Categories: DBA Blogs

Secrets of Happiness

Virag Sharma - Sun, 2008-02-03 10:26
Secrets of Happiness

I was traveling from my from Agra (= City of Taj Mahal, 30 Miles away from my home town ) to Hyderabad. My Train AP Express was late so thought to pick some book. As usual I picked some my favorites books/magazines like Reader Digest , Champak ( Famous Kids book in India ). While purchasing books/magazines, saw book with title “Secrets of Happiness - Tanushree Podder”. Title looks very odd to me because , I feel how one can define happiness. Well , Just picked the book and browse some page , it look good. It is not different from other book like “Mega Living- Robin Sharma” , “Who will Cry when you die Robin Sharma”. English look typical Indian English. Some of the stories we already heard in our childhood from Grand Mother , Mom , aunty etc. But it is really nice to re-visit those stories. Writer presentation look good and that make book more interesting. I started reading book from Agra and keep reading book till Gwalior, Everybody in train want to sleep , since I was reading book light was on and everybody eye brow in train getting tight. Finally switched off light , but finished book before reaching Hyderabad. I feel book is worth to read , that’s why thought to write about this book.

Check book excerpt [click Here]

Summary
  1. What you put in life , you get back
  2. No situation is good / bad / ugly , it is our believe that colored our perception about situation and we feel accordingly(good / bad / ugly) about situation. So change our believe , thought things will improve/change, otherwise same thought/believe same result
  3. Keep It Simple and Straight ( KISS) .....................................

Apart from this there are two more book, that really worth to read

Monk Who Sold His Ferrari – Robin Sharma

Follow Your Heart
- Andrew Matthews

I read above book frequently , and feel , If I would have got these book 6 Year Back ……. :-)

><?xml:namespace prefix = o />


Categories: DBA Blogs

Detect numbers with TRANSLATE() - Take two

Jared Still - Thu, 2008-01-31 04:03
Last week I wrote about using TRANSLATE to detect numbers in data Using The TRANSLATE() function...

Andrew Clarke at Radio Free Tooting pointed out the shortcomings of using TRANSLATE() to detect numbers.

As I said earlier, all I needed to do was detect if the characters in a string were all digits or not, and I wanted it to be very fast.

But Andrew's remarks got me thinking - could translate be used to detect more complex numbers?

Here's the short list of requirements:

* Detect integers
* Detect numbers with decimal point ( 4.6, 0.2, .7)
* Detect negative and positive ( leading + or - )
* Reject text with more than 1 '.', such as an IP address ( 127.0.0.1 )
* Reject anything with alpha text

And comma's are considered as text. 99,324.1 would be alpha.

If you need to do this on 10g, no problem, as a regular expression can handle it.

Fist create some test data:

drop table number_test;

create table number_test( alphacol varchar2(20));

insert into number_test values('.5');
insert into number_test values('1');
insert into number_test values('2');
insert into number_test values(' 3');
insert into number_test values('4 ');
insert into number_test values('3.14159');
insert into number_test values('127.0.0.1');
insert into number_test values('+34.45');
insert into number_test values('-54.43');
insert into number_test values('this is a test');
insert into number_test values('th1s is 4 t3st');
insert into number_test values('.');
commit;

Now select only columns where the value is a number:

select alphacol
from number_test
where regexp_instr(trim(alphacol),'^[-+]?[0-9]*(\.?[0-9]+)?$') > 0
order by 1

SQL> /

ALPHACOL
--------------------
3
+34.45
-54.43
1
2
3.14159
4

7 rows selected.

That seems to work.

But what if you're stuck doing this on 9i? REGEXP_INSTR is not available.

You can use the user defined function IS_NUMBER(), which works well, but is very slow if used on large amounts of data.

Might we be able to use and abuse the TRANSLATE() function to speed this up? Here's a bit of convoluted SQL that works well on the limited test data:

select alphacol, alpha2
from
(
select alphacol,
-- is there a sign +- ? - remove it
decode(substr(alpha2,1,1),
'-',substr(alpha2,2),
'+',substr(alpha2,2),
alpha2
) alpha2
from (
select
alphacol,
-- remove a single '.' if it/they exists
replace(substr(alphacol,1,instr(alphacol,'.')),'.') || substr(alphacol,instr(alphacol,'.')+1) alpha2
from (
select trim(alphacol) alphacol
from number_test
)
)
)
where substr('||||||||||||||||||||||||||||||||',1,length(alpha2)) = translate(alpha2,'0123456789','||||||||||')
/


(Sorry about formatting - I seem to lose all formatting when I paste SQL)

Output from this nasty bit of SQL is identical to that when using REGEXP_INSTR:

ALPHACOL ALPHA2
-------------------- ----------------------------------------
.5 5
1 1
2 2
3 3
4 4
3.14159 314159
+34.45 3445
-54.43 5443

8 rows selected.

To make the TRANLATE() function do what is needed, a lot of data manipulation had to be done in the SQL. There is so much work being done now that it now takes nearly as long to run as does the IS_NUMBER() function, so there isn't much point in using TRANSLATE().

Runstats results:

SQL> @th5
.047739 secs
.037447 secs
PL/SQL procedure successfully completed.

If nothing else, this was an interesting exercise.
Categories: DBA Blogs

An upper bound of the transactions throughputs

Christian Bilien - Mon, 2008-01-28 15:46
Capacity planning fundamental laws are seldom used to identify benchmark flaws, although some of these laws are almost trivial. Worse, some performance assessments provide performance outputs which are individually commented without even realizing that physical laws bind them together. Perhaps the simplest of them all is the Utilization law, which states that the utilization of […]
Categories: DBA Blogs

Small change make difference

Virag Sharma - Sun, 2008-01-27 08:41

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a

SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise

MYSUN:oracle> (10.1.0.4) /usr/proc/bin

$ ps -aef grep pmon

oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test
oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon

mysun:oracle> (10.1.0.4) /usr/proc/bin

$ pwdx 2424
2424: /u01/app/oracle/product/10.1.0.4/dbs

<

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

<?xml:namespace prefix = o /?>

[root@apps001 proc]# ps -aef grep pmon

oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon

[root@apps001 proc]# ls -l /proc/8268

total 0
dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr
-r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs
-r-------- 1 oracle oinstall 0 Jan 27 23:20 environ
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle
dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd
-rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid
-r-------- 1 oracle oinstall 0 Jan 27 23:20 maps
-rw------- 1 oracle oinstall 0 Jan 27 23:20 mem
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts
lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> /
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status
dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task
-r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

>

<

[oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ ls
hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~

"11g.env" 7L, 136C written

[oracle@apps001 ~]$chmod 755 11g.env
[oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.

Ctl-d

[oracle@apps001 ~]$ ps -aef grep pmon
oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE
oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g
oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon

[oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs

[oracle@apps001 dbs]$ /sbin/fuser *

hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372

lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

# It means database is up (may be nomount , mount or open mode )
#

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set editor vi
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/tnslsnr/apps001/listener
2: diag/rdbms/orcl2/orcl2
3: diag/rdbms/stdorcl2/stdorcl2
4: diag/rdbms/orcl11g/orcl11g
Q: to quit

Please select option:4

space available in the underlying filesystem or ASM diskgroup.
2008-01-27 21:22:07.366000 +05:30
Completed: ALTER DATABASE OPEN

2008-01-27 21:22:26.499000 +05:30
Starting background process CJQ0
CJQ0 started with pid=26, OS id=8394
2008-01-27 21:23:24.231000 +05:3
Thread 1 advanced to log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log
2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0/
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written


#
# I made little change in script i.e. removed last “/” from ORACLE_HOME
#


vi 11g.env
ORACLE_HOME=/u01/app/oracle/11.1.0
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
ORACLE_SID=orcl11g
export ORACLE_SID

~
~
"11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env

[oracle@apps001 ~]$ sys

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

So small change make difference.

Categories: DBA Blogs

SQL Injection for parents

Andrew Fraser - Fri, 2008-01-25 18:46

This page has been moved to http://andrewfraserdba.com/?p=51


Categories: DBA Blogs

DR-Setup....

Ayyu's Blog - Thu, 2008-01-24 01:25
Categories: DBA Blogs

LOG_ARCHIVE_DEST_n

Ayyu's Blog - Tue, 2008-01-22 21:28
Categories: DBA Blogs

Data Guard 9i Log Transportation on RAC

Ayyu's Blog - Tue, 2008-01-22 21:23
Categories: DBA Blogs

Creating a Data Guard Configuration

Ayyu's Blog - Tue, 2008-01-22 21:14
Categories: DBA Blogs

Improvement of AUTO sampling statistics gathering feature in Oracle 11g

Inside the Oracle Optimizer - Tue, 2008-01-22 17:34
Optimizer statistics in Oracle are managed via a pl/sql package, dbms_stats. It provides several pl/sql procedures to gather statistics for a table, schema, or a database. For example, gather_table_statistics is used to gather statistics on a table. This procedure has an estimate_percent parameter, which specifies the sampling percentage of the statistics gathering. The users can specify any number between 0 ~ 100 for this parameter. For example, suppose you have a table BIGT, you can specify a 1% sampling percentage as follows:

exec dbms_stats.gather_table_stats(null, 'BIGT', 
estimate_percent => 1);


It is not always easy for users to pick the appropriate sampling percentage. If the specified sampling percentage is too high, it can take longer to gather statistics. On the contray, if the data is skewed and the specified sampling percentage is too low, the resulting statistics can be inaccurate.

For this reason, Oracle introduced the AUTO value for the estimate_percent parameter. For example, you can gather statistics on BIGT as follows:

exec dbms_stats.gather_table_stats(null, 'BIGT', 
estimate_percent => dbms_stats.auto_sample_size);


The advantage of using AUTO sample size over a fixed number is two-folds. First, when AUTO sample size is specified, the system automatically determines the appropriate sampling percentage. Second, AUTO sample size is more flexible than a fixed sampling percentage. A fixed sampling percentage size that was good at some point in time may not be appropriate after the data distribution in the table has changed. On the other hand when AUTO value is used Oracle will adjust the sample size when the data distribution changes.

When AUTO is used Oracle picks a sample size where the statistics quality is good enough. However, it does not work very well under cases of extreme skew in the data. In Oracle 11g, we improved the behavior when the AUTO value is used. First, AUTO sampling now generates deterministic statistics. Second, and more importantly, AUTO sampling generates statistics that are almost as accurate as 100% sampling but takes much less time than 100% sampling. To illustrate these merits, we compare the performance of using a fixed sampling percentage, AUTO sample size in Oracle 10g and AUTO sample size in Oracle 11g.

We used the standard TPC-D data generator to generate a Lineitem table. The Lineitem table is about 230G and contains 1.8 million rows with 16 columns. The schema of the lineitem table is as follows:

column namecolumn typel_shipdatedatel_orderkeynumberl_discountnumberl_extendedpricenumberl_suppkeynumberl_quantitynumberl_returnflagvarchar2l_partkeynumberl_linestatusvarchar2l_taxnumberl_commitdatedatel_receiptdatedatel_shipmodevarchar2l_linenumbernumberl_shipinstructvarchar2l_commentvarchar2
Table 1 gives the elapsed time of gathering statistics on the Lineitem table by different sampling percentages.

Sampling PercentageElapsed Time (sec) 1% sampling 797100% sampling (Compute)18772Auto sampling in Oracle 10g 2935Auto sampling in Oracle 11g 1908

Table 1: Statistics gathering time on 230G TPC-D Lineitem Table Using Different Estimate Percentages

We also compare the quality of the statistics gathered using different estimate percentages. Among all the statistics of a column, number of distinct values (NDV) is the one whose accuracy used to be an issue. We define the accuracy rate of NDV of a column as follows:


accuracy rate = 1 - (|estimated NDV - actual NDV|)/actual NDV.


The accuracy rate ranges from 0% to 100%. The higher the accuracy rate is, the more accurate the gathered statistics are. Since 100% sampling always lead to an accuracy rate of 100%, we do not report it. We focus on the columns which has at least one statistics accuracy rate below 99.9% when using different estimate percentages. Table 2 illustrates the accurate rates of the columns.

Column NameActual NDV Auto Sampling in Oracle 11g 1% Sampling orderkey 450,000,000 98.0% 50% comment 181,122,127 98.60% 4.60% partkey 60,000,000 99.20% 98.20% suppkey 3,000,000 99.60%
99.90% extendedprice 3,791,320 99.60% 94.30%

Table 2: Accuracy Rate of Gathering NDV LineItem Using Different Estimate Percentages

In short, the elapsed time of ''AUTO sampling in Oracle 11g'' is 10 times faster than 100% sampling but yields the statistics of similar quality (accuracy rate close to 100%).


Categories: DBA Blogs, Development

Regular Tasks Of a DBA....

Ayyu's Blog - Sun, 2008-01-20 20:34
Categories: DBA Blogs

Regular Taska Of a DBA

Ayyu's Blog - Sun, 2008-01-20 20:34
Categories: DBA Blogs

Oracle’s clusterware real time priority oddity

Christian Bilien - Sat, 2008-01-19 12:16
The CSS processes use both the interconnect and the voting disks to monitor remote node. A node must be able to access strictly more than half of the voting disks at any time (this is the reason for the odd number of voting disks), which prevents split brain. Let’s just recall that split brains are […]
Categories: DBA Blogs

Oracle 11g Security Enhancements Part – 1

Virag Sharma - Thu, 2008-01-17 10:02
Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

  • SEC_RETURN_SERVER_RELEASE<?xml:namespace prefix = o />
  • SEC_PROTOCOL_ERROR_FURTHER_ACTION
  • SEC_PROTOCOL_ERROR_TRACE_ACTION
  • SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
  • SEC_DISABLE_OLDER_ORACLE_RPCS*

These parameters are system wide and static.

  • Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.

SQL> show parameter SEC_RETURN_SERVER_RELEASE_BANNER

NAME TYPE VALUE

----------------------------- -------------------- ---------------------
sec_return_server_release_banner boolean FALSE

When set to true the full banner is displayed. When the value is set to FALSE, a limited generic banner is displayed.

  • Protect against denial of Service (DoS) attacks

The two parameters shown specify the actions to be taken when the database receives bad packets from a client. The assumption is that the bad packets are from a possible malicious client. The SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter specifies what action is to be taken with the client connection: Continue, drop the connection, or delay accepting requests.

# Continue connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue

#Delay 4 Sec before sever accept next connection

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4

# Drop connection after 10 bad packet

SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10

The other parameter SEC_PROTOCOL_ERROR_TRACE_ACTION specifies a monitoring action: NONE, TRACE(Default) , LOG, or ALERT.

# DEFAULT Creates the trace files, but it is useful for debugging purposes,

SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace

# Writes a short, one-line error message to the server trace file and alert log.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert

# Writes a short, one-line message to the server trace file.

SEC_PROTOCOL_ERROR_TRACE_ACTION = Log

# Configures the server to ignore the bad packets and does not generate any trace files or

SEC_PROTOCOL_ERROR_TRACE_ACTION = None

>

SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTION

NAME TYPE VALUE
------------------------------- ------------------- ------------------------
sec_protocol_error_further_action string CONTINUE

SQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTION

NAME TYPE VALUE
---------------------- ------------------------- -----------------------
sec_protocol_error_trace_action
string TRACE

  • Protect against intruder

If profile is not enabled then intruder can try unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

A new initialization parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS that has a default setting of 10 causes a connection to be automatically dropped after the specified number of attempts. This parameter is enforced even when the password profile is not enabled.

SQL> show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS

NAME TYPE VALUE
-------------------------- --------------------------------------------------
sec_max_failed_login_attempts integer 10

>

The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

>

Recently come across new parameter SEC_DISABLE_OLDER_ORACLE_RPCS on Database Error Messages 11g Release 1

>
ORA-03139: This OCI call has been disabled by the DBA
Cause: The SEC_DISABLE_OLDER_ORACLE_RPCS initialization parameter was enabled.
Action: Contact the Database Administrator

But when searched oracle documentation not found any detail about it , also not found any detail about it on Database

SQL> show parameter SEC_DISABLE_OLDER_ORACLE_RPCS

SQL>

Categories: DBA Blogs

Tag flood

Christian Bilien - Fri, 2008-01-11 10:08
  Amid the tag furry, I was tagged some days ago by Jeff Moss so I’ll have to give some pieces of information about myself presumably of low interest to most. I had not put any personal information on my blog, so here are 8 of them which I’ll keep short anyway: I come from […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs