How to identify CPU hogging Oracle sessions on a Windows server

Posted by David Yahalom under Oracle

The Oracle Server process model is different between Windows and Linux/Unix. While in Linux the Oracle instance uses a dedicated process model on Windows server, the instance is composed from one oracle.exe process and many different threads. Each thread represent either a background “process” (PMON, SMON…) or a foreground user session.

So, when you are running Oracle server on Windows and encounter a situation where the server CPUs are nearing 100% utilization, a quick glimpse in Task Manager only reveals that oracle.exe hogging the CPU. Nothing regarding which specific thread or session is responsible for the high load.

For us to identify the taxing session we will need to do some basic digging.

First, we’ll need to know which thread within the oracle.exe process is hogging the processor. In order to do so we’ll need a process explorer that supports displaying threads within processes. A good free choice is QSlice, a free program that comes as part of the Windows2000 resource kit. You can get it here.

When running QSlice you’ll see a list of process IDs, names and CPU utilization for each process. Locate oracle.exe and double click it.

Now you’ll see a list composed of TID (Thread IDs), time / CS and % of CPU utilized within the oracle.exe process. Identify the thread which is taxing the CPU (for example, thread 6b88) by looking at the TID (Thread ID) column with the highest user or system CPU time and convert the value in TID column from HEX to DEC.

Open SQL*PLUS and logon as sysdba.

Use the following query to get details about the specific session

SQL> select proc.spid ThreadNO,
sess.username Username,
sess.osuser OSUser,
sess.status Status,
sess.sid SessionID,
sess.program Program
from v$process proc, v$session sess, v$bgprocess bg
where sess.paddr = proc.addr
and bg.paddr(+) = proc.addr
and proc.spid in (TID_VALUE_CONVERTED_TO_DEC)

(you can modify the query to include more columns to be displayed from v$session, v$process and v$bgprocess depending on your specific needs).

You can also retrive the specific SQL the CPU hogging session is running by running the following SQL:

SQL> select sqlarea.sql_text
from v$process proc,
v$session sess,
v$sqlarea sqlarea
where proc.addr = sess.paddr
and sess.sql_hash_value = sqlarea.hash_value
and proc.spid in (TID_VALUE_CONVERETED_TO_DEC)

You can now use the ALTER SYSTEM KILL SESSION command to kill the taxing session if needed.

Search for a given string in all fields of an entire schema

Posted by David Yahalom under Oracle

A developer friend of mine requested my help in writing a stored procedure that will allow him to search an entire schema for a certain “string”.

To help him I’ve written this small and dirty PL/SQL procedure. Keep in mind that while it will most likely work out of the box, it will probably require some debugging on your parts as it was written for a small dev project and isn’t fully tested. Feel free to leave a comment if you find anything borken with the code.

Otherwise, enjoy.

CREATE OR REPLACE procedure search_db (p_search varchar, p_type varchar)
/*
* 	This procedure will search a user's schema (all tables) for columns matching the user's input.
*
*	####### Please create the following table before you run this procedure:
*	create table search_db_results(result varchar2(256));
*
*
*	This table will contain the result of the procedure run so that you can view intermediate search results while the procedure is running.
*
*	You pass two parameters to this procedure:
*
*
*	1) Search string / number / date 			(REQUIRED)
*	2) Search datatype 					(REQUIRED)
*
*	Example:
*
*	exec search_db('hello','VARCHAR2') 		-- will search for rows in all tables that have a VARCHAR2 column with "hello" as the data.
*	exec search_db('01-JAN-2008','DATE') 	-- will search for all rows in all tables that have a DATE column with the data '01-JAN-2008' in it.
*	exec search_db(1000,'NUMBER') 			-- will search for all rows in all tables that have a NUMBER column with the data 1000 in it.
*
*
*	Allowed data types: VARCHAR2, CHAR, DATE, NUMBER, FLOAT.
*
*
*
*	**********************************************************************************************************************************************
*	WARNING!!!!! if you have a large schema be advised that the search can take anywhere from minutes to hours!

*	**********************************************************************************************************************************************
*/

IS
TYPE tab_name_arr IS VARRAY(10000) of varchar2(256);

v_tab_arr1 tab_name_arr;	 		/* ARRAY TO HOLD ALL TABLES IN THE USER SCHEMA */
v_col_arr1	tab_name_arr;		/* ARRAY TO HOLD ALL COLUMNS IN EACH TABLE */

v_amount_of_tables number(10);		/* this holds the amount of tables in the current user schema so that the for loop will know how many times to run */
v_amount_of_cols	number(10);		/* when searching in a table, this holds the amount of columns in that table so that the for loop searching the table will know how many iterations it needs */
v_search_result	number(10);		/* when searching the table, this holds the amount of results found. We use this is that if the amount of result found is greated than 0 we will print the name of the table and the column */
v_result_string	varchar2(254);
BEGIN

v_tab_arr1 := tab_name_arr();	/*INITIALIZE THE ARRAY*/
v_col_arr1 := tab_name_arr();	/*INITIALIZE THE ARRAY*/

v_col_arr1.EXTEND(1000);		/* INITIALIZE THE ARRAY to the maximum amount of columns allowed in a table */

/* This will return the amount of tables in the user schema so that we know how many times we need to invoke the for loop */
select count(table_name)
into v_amount_of_tables
from user_tables;

v_tab_arr1.EXTEND(v_amount_of_tables);	/*INITIALIZE THE ARRAY to the number of tables found in the user's schema */

FOR i in 1..v_amount_of_tables LOOP		/*LOOP until we reach the maximum amount of tables in the user schema */

/* start populating the tables array with table names. The data is read fomr the data dictionary */

select table_name
into v_tab_arr1(i)
from
(
select rownum a, table_name
from user_tables
order by table_name
)
where a = i;

END LOOP;

/* now, after we have an array with all the names of the tables in the user's schmea, we'll start going
over each table and get all of its columns so that we can search every column */

FOR i in 1..v_amount_of_tables LOOP

/*select the amount of columns in the table where the data_type matches the data type the user passed as a parameter to the procedure */

select count(*)
into  v_amount_of_cols
from user_tab_columns
where table_name = v_tab_arr1(i)
and data_type = p_type;

/* start searching the clumns ONLY IF there is at least one column with the requested data type in the table */

if v_amount_of_cols <> 0 then

/* do the search for every column in the table */

FOR j in 1..v_amount_of_cols LOOP

select column_name
into v_col_arr1(j)
from
(
select rownum a, column_name
from user_tab_columns
where table_name = v_tab_arr1(i)
and data_type = p_type
)
where a = j;

/* each type of data_type has its own SQL query used to search. Here we execute different queries based on the user passed parameter of requested data type */

IF p_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || lower(v_col_arr1(j)) || ' like ' || '''' || '%' || lower(p_search) || '%' || '''' into v_search_result;
end if;

if p_type in ('DATE') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' || '''' || p_search || '''' into v_search_result;
end if;

if p_type in ('NUMBER', 'FLOAT') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' ||  p_search into v_search_result;
end if;

/* if there is at least one row in the table which contains data, return the table name and column name */

if v_search_result > 0 then
v_result_string :=  v_tab_arr1(i) || '.' || v_col_arr1(j);
execute immediate 'insert into search_db_results values (' || '''' || v_result_string || '''' || ')';
commit;
end if;

END LOOP;

end if;

end loop;

END;
/

Of course in case you don’t mind loosing flexibility a bit you can also use this, much simpler, dynamically generated SQL code:

select 'select '||column_name||' from '||table_name||' where '||column_name||' > sysdate;'
from user_tab_columns
where data_type='DATE'

ORA-01031: insufficient privileges upon instance startup

Posted by David Yahalom under Windows, Oracle

A friend of mine who is a junior DBA approached me today with a strange problem that was bugging him for hours. He installed a new Windows Oracle 9i server and wanted to create the databases manually (something I always recommend over DBCA, as you get finger grain control over your database creation process and you feel more “in control” of whats going on inside your DB).

He created an init.ora file for his Instance and since he wanted to use OS authentication for Oracle, he left the parameter remote_login_passwordfile to its default value (”none”).

He then proceeded to creating his instance using the following command:

oradim -new -sid InstanceSID -startmode m -pfile C:location_to_pfileinit.ora -intpwd password

After which, the instance was created. He proceeded to creating the database itself. Since he was all keen about doing this manually he opened up a Windows CMD and did the following steps:

SET ORACLE_SID=InstanceSID
C:> sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 13 10:18:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/password as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=C:location_to_fileinit.ora;
ORA-01031: insufficient privileges
SQL>

As you see, he got an ORA-01031. He didn’t understand what is causing this. His O/S user was a member of the ORA_DBA group. He tried logging off windows and then logging backup in. Nothing.

The solution is actually quite simple. By default, if you install Oracle and don’t use the network configuration assistance you get a ORA_HOME without an SQLNET.ORA file. Without it, Oracle will not be able to use O/S authentication.

To solve this problem, simply create a file named sqlnet.ora in your %ORA_HOME%\network\network\admin directory and add to it the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Save the file.

After this, try starting the instance using the same procedure described above and your O/S based logon should work just fine!

Misc tip: search for all unread mail in Gmail

Posted by David Yahalom under General IT

For ages I’ve looked for a way to quick search for all unread mail in Gmail.

I need this because most of the time I let a few unimportant (read: advertisements) emails slip by me without reading them or marking them as read. After time, this list grows and I find it annoying to always receive notifications as if I have unread mail in my inbox (I have unread mail, it is just unimportant mail I forgot to mark as read).

Well, there doesn’t seem to be a way to search for only unread mail using Gmail’s interface but there is quick way around this.

Just type:

is:unread

Into the search box!

Materialized view will become invalid after a refresh - documented Oracle bug in older versions+ workaround.

Posted by David Yahalom under Oracle

Today I stumbled across a very annoying Oracle bug regarding Materialized Views becoming invalid after a refresh. I’m talking about Oracle Bug 2639679 (QUERY_REWRITE flag changes for MVIEW with DATE RANGE in WHERE) which affects Oracle 8i and even some versions of 9i (can’t be sure which versions exactly as Metalink says this is resolved in 9i but clearly this is not the case).

When you create a materialized view with DISABLE QUERY REWRITE option, when you refresh the materialized view (using DBMS_SNAPSHOT.REFRESH, DBMS_REFRESH.REFRESH or any other method), the QUERY REWRITE flag will automatically be turned back on. This is a problem if your materialized view has a where clause in it as QUERY REWRITE + a where clause is a no-no.

This bug will cause your materialize view to become invalid every time you refresh it. Very annoying.

The solution I found was to add a disable query rewrite command before and after the refresh of the materialized view.

EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');
DBMS_SNAPSHOT.REFRESH( 'MV_NAME','C');
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');

You can put this in a database job to have the materialized view refresh automatically.

Hope this helps.

Insufficient privileges error while installing Oracle DBCA under Linux

Posted by David Yahalom under Oracle

When installing Oracle 9 on Linux, you may receive the following error during installation:

“you do not have sufficient privileges to write to the specified path Database Configuration Assistant 9.2.0.1.0…”

The problem usually happens when you forget to set the $ORACLE_BASE (not to be confused with $ORACLE_HOME) environment variable prior to initiating the OUI installation.

You want to place such variables in the .bash_profile file located under the oracle user home directory (usually /home/oracle).

If you still get this error even after you have set $ORACLE_BASE (you did remember to you a “-” when SUing to the oracle user, right? su - oracle) then make sure you have proper permissions set.

ORA-600 [16201] when dropping a procedure

Posted by David Yahalom under Oracle

In certain older versions of Oracle database (such as 8.1.7.4 like this case) you may receive an ORA-600 error when trying to drop or recompile a database PL/SQL package or procedure. Usually when the source is wrapped.

SQL> drop procedure schema.proc_name;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

SQL> create or replace procedure schema.proc_name
as
begin
end;
/

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

This is a documented Oracle bug (No. 2422726). It affects Oracle versions 8.1.7.4, 9.0.1.4, 9.2.0.1 and fixed in Oracle 9.2.0.2 and 10g. However, even if you are running an older version of Oracle there’s a possible workaround available to solve this issue. Note, this is an ugly hack, but it works, and sometimes your only solution for this annoying problem.

The solution is as follows:

1) Connect as SYS to the problematic db.

2) Run the following query to identify the object# of the INVALID object you can’t drop.

SQL> select obj#,owner#,type# from sys.obj$ where name = 'PROC_NAME';

OBJ#     OWNER#      TYPE#
---------- ---------- ----------
1396504          5          7

3) Now try and select the procedure from v$procedure using the OBJ# from the above query.

SQL> select * from procedure$ where obj# in (1396504);

no rows selected

4) You can’t. That’s because of the above mentioned bug.

5) The only way to fix this is to insert a fake row into the procedure$ view to fool Oracle to allow you to successfully drop the procedure.

SQL> insert into procedure$ values (1396504, '-----------------------',NULL,2);

1 row created.

SQL> commit;

Commit complete.

6) Now you can successfully drop the INVALID procedure.

SQL> drop procedure schema.proc_name;

Procedure dropped.

As I said, it’s a hack, so you are better of upgrading your Oracle installation to a version not effected by this bug. But as we all know, many times this is simply not an option. And for those occasions the solution I’ve written here seems to be the only one.

Good luck!

Archiving not possible: No primary destinations errors after fixing a space issue on an archive destination

Posted by David Yahalom under Oracle

If space ran out in an archive destination, after you fix the problem, you may still recieve the following error in your alert_log:

ARC1: Archiving not possible: No primary destinations

A quick way to solve this is to modify your archive destination with the reopen parameter. This parameter specifies the minimum number of seconds before redo transport services should try to reopen a failed destination.

SQL> show parameter log_archive_dest_1

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
log_archive_dest_1                   string  location=/db/orcl/archive

SQL> alter system set log_archive_dest_1='location=/db/orcl/archive reopen=5' scope=both;

System altered.

Following that modification you should start seeing successful archive messages in your alert_log.

ARC1: Beginning to archive log# 1 seq# 115127
...
ARC0: Completed archiving log# 1 seq# 115127
...

UltraSparc Vs. x86 servers. Which one runs Oracle faster?

Posted by David Yahalom under Hardware, Solaris, Linux, Oracle

I was doing research for a client that is trying to replace old Sun UltraSparc Solaris servers with new, cheaper (and hopefully better performing), Linux x86 machines. I want to see if I can find any benchmarks that will help me compare the performance of SPARC servers to x86 machines when running Oracle Database.

First I started by looking in TPC’s OLTP benchmark results, which are considered somewhat of an industry standard. I found several interesting things.

Let’s look at two low end HP machines, the ProLiant ML350G5 and the ML370G5. These are both low-end Dual-CPU socket x86 servers from HP starting at a price lower then $2,500 for both servers.

low-end HP ProLiant ML350G5 (1 X Intel X5355 Quad-Core) - 100,926 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107061101

low-end ProLiant ML370 G5 (2 X Intel X5460 Quad-Core) - 273,666 TPC-C/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107111201

While I was unable to fund recent benchmarks of Sparc machines in TPC, I was able to find benchmark that while conducted several years ago might still be useful.

OLDish (2003) Fujitsu PRIMEPOWER 2500 (64 X SPARC64 - 1.3 GHz ) - 595,702 TPC/Sec
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=103103101

This is a high-end server with 64 SPARC processors. In its time it was quite the beast and can still be found in many data centers worldwide crunching very important numbers.

As you can see it scored twice the number of points compared to the ProLiant ML370G5. Impressive. Sure. Just keep in mind that we are talking about a really expensive high-end 64CPU machine against a very low-end 2 X QuadCore (8 cores total) Intel-based commodity server. So eight times the CPUs for roughly twice the performance? 83.2 total SPARC Ghz vs. 25.28 total x86 Ghz ? Still confused? According to TPC the Fujitsu machine is almost X20 more expensive compared to the x86 HP server. Twice the performance for 20 times the price?

So yes, the Fujitsu PRIMEPOWER is an older server with older CPU models but it is also a much more expensive server and we can still use this data when considering replacing old SUN servers with newer X86 alternatives. These results can help us better understand what type of x86 hardware we need in order to replace our old SPARC servers. This isn’t %100 scientific comparison but it can still give us a ballpark estimate.

We’ll need to do some more digging to find more current results using newer SPARC processors (such as the Sun T1). What I was able to find were some MySQL benchmarks done by tweakers.net for their own web application. While these are very tailored benchmarks done for a specific web site application it does help contribute to the general picture here.

http://tweakers.net/reviews/646/14/server-duel-xeon-woodcrest-vs-punt-opteron-socket-f-pagina-14.html

http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html

You can see that the Xeons fares much better compared to the UltraSparcs.

Another very interesting benchmark I found was a blog entry quoting official SAP benchmarks between x86 and SPARC hardware. The SAP benchmark shows the x86 servers to be faster compared to the Sun T2000 SPARC server with some limitations.

2 X Quad-Core Intel Xeon X5355 2.66 GHz - 9330 SAPS
www.sap.com/solutions/benchmark/pdf/cert4207.pdf

2 X Dual-Core Intel Xeon 5160 3 GHz - 5120 SAPS
www.sap.com/solutions/benchmark/pdf/cert3107.pdf

Sun Fire Model T2000 - 1 processor / 8 cores / 32 threads - 4780 SAPS
www.sap.com/solutions/benchmark/pdf/cert4705.pdf

You should go ahead and read the reservations made by the author of the above post. They are all valid. Just keep in mind that the Sparc system is a $33,000 server compared to a much cheaper HP.

Of course I’m only talking about raw performance here. The x86/Linux hardware has several other advantages such as being more open, more future-proof and arguably easier and cheaper to service and maintain.

This is all very interesting stuff. I’ll continue to do more digging and update this post as necessary.

How to perform a manual uninstallion of an $ORACLE_HOME on a Linux server

Posted by David Yahalom under Oracle

While the safest way to remove an $ORACLE_HOME installation from a server is by using OUI, sometimes it might not be possible (for example, if you are not running X).

In case you ever need to perform a manual uninstall of an $ORACLE_HOME installation from a Linux server, you can do so by following these steps:

1.Stop (or kill) all Oracle processes running on the machine.
2. Remove the oraInventory directory (check /etc/oraIns.loc if unsure about location).
3. Remove (using rm -rf) the $ORACLE_HOME(s) you have on your system.
4. Remove all other traces of Oracle from your server:

/usr/local/bin/oraenv
/usr/local/bin/coraenv
/usr/local/bin/dbhome
/etc/oraInst.loc
/etc/oratab
/var/tmp/.oracle

That’s it. Following this you won’t have any trace of Oracle software on your server.