Re: Question if you know ora_debug?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/15
Message-ID: <34cf2265.216471028@inet16>#1/1

I use the following sqlplus script to see what sql is actively being processed in the database (works in 7.1 on up). You of course need access to the v$ tables for this to work:

column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

begin

    for x in
    ( select username||'('||sid||','||serial#||') ospid = ' || process ||

                         ' program = ' || program username, 
             sql_address
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null ) loop
        for y in ( select sql_text 
                     from v$sqlarea 
                    where address = x.sql_address ) loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;

    end loop;
end;
/

---

It'll print out whos logged in and, if they are active, the sql statement they are currently executing.

On 14 Jan 1998 23:27:54 GMT, leidad_at_aol.com (LeidaD) wrote:

>Does anyone know how to use oradebug?
>I need to have the capability to take a look at a Multi threaded server process
>and determine what sql code is running at the time. I have gone to the oracle
>web site and cannot find anything pretaining to debugging on the server.
>Does anyone have any ideas?
>We have ORACLE 7.1.6 on an HP-UX v 10.01. Other then upgrade to ORACLE 7.3.3 I
>have tried everything. I've used HP tools: glance and top. I've been running
>oracle scripts to see who is logged into the database and possibly see who is a
>heavy user when glance tells me the CPU is at 100% utilization. Also, I've
>been running scripts to check the highwater mark. I've been looking a sar
>files, on the HP to get a trend of high CPU usage. This is fine, but it does
>not tell me anything specific. It does show the server processes are extremely
>busy on s000 and s001,but the other servers a total of 20 are barely touched.
>What is happening in those processes when glance tells me s000 is using 165
>IO's / second and the CPU is 100% utilization. What type of sql code is
>occuring then.
>I am going to stop here.. I would appreciate any opinions from this.
>
>Regards,
>Leida W. Dougherty
>Database admin.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 15 1998 - 00:00:00 CST