Installing dbms_profiler

I used the following to install dbms_profiler, using a central set of system owned tables rather than each user having its own tables:

conn / as sysdba
@?/rdbms/admin/profload.sql
conn system/password
alter user system default tablespace users ; [or any other reasonable tablespace]
@?/rdbms/admin/proftab.sql
GRANT all ON plsql_profiler_runnumber TO PUBLIC;
GRANT all ON plsql_profiler_data TO PUBLIC;
GRANT all ON plsql_profiler_units TO PUBLIC;
GRANT all ON plsql_profiler_runs TO PUBLIC;
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_data FOR plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR plsql_profiler_runnumber;
alter user system default tablespace system ;

Based on Tim Hall’s post.

What SQL and sessions are running?

GUIs like TOAD and Enterprise Manager can show this better, but they’re not always to hand. Every session active now or within the last minute is shown.

set pages 9999 lines 132
col username form a20
col osuser form a20
col program form a50
col minutes form 999
alter session set nls_date_format = 'Dy DD-Mon-YYYY HH24:MI:SS'
/
select distinct
s.sid, s.username, s.osuser, s.program||' '||s.module program,
s.status, s.last_call_Et/60 minutes, s.logon_time, w.state, w.event, w.seconds_in_wait ,
q.sql_text
from v$session s, v$sql q, v$session_wait w
where ( s.status = 'ACTIVE' or last_call_et < 60 )
and s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.sid = w.sid
order by s.sid
/
set pages 9999 lines 80 head on feed 6

Bug? with wrong results from all_objects in stored plsql procedures

Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used.

Run this code to see the anomaly for yourself:

set serverout on
declare
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
create or replace procedure af_temp as
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
exec af_temp ;
drop procedure af_temp ;

It’s the exact same plsql each time, but very different results reported. For example:

all_objects: 13501

PL/SQL procedure successfully completed.

Procedure created.

all_objects: 4929

PL/SQL procedure successfully completed.

Procedure dropped.

This behaviour appears to occur on all versions - I tested from 7.3 through 10gR2. The biggest discrepancies in results seem to be with DBA users other than SYS, but all users show some discrepancy.

This issue doesn’t occur with the dba_ views, so best to use them instead in stored plsql objects. That does mean explicitly granting select privileges on the dba_ views being used in stored procedures, which isn’t necessary for the all_ views.

So, is this a bug? I can’t see any mention of it all in metalink.

It’s not just an academic issue either: it caused this code of mine to fail to find and drop the objects it was meant to drop.

10046 tracing in another session

To switch 10046 tracing on in another session (first setting on timed statistics and making max_dump_file_size very large):

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_bool_param_in_session(&1,&serial,'timed_statistics',true)
exec sys.dbms_system.set_int_param_in_session(&1,&serial,'max_dump_file_size',999999999)
exec sys.dbms_system.set_ev(&1,&serial,10046,8,'')

And to switch it off:

set pages 9999 verify off
col serial new_value serial noprint
select serial# serial from v$session where sid = &1 ;
exec sys.dbms_system.set_ev(&1,&serial,10046,0,'')

Note that you can run new (9i+) tkprof against old (8i and below) 10046 trace files in order to report the wait information. It is also possible to cat multiple trace files together before tkprof’ing.

Based on Alexander Bubernak’s post at http://www.dbasupport.com/oracle/ora10g/10046event.shtml

Clone a database

The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file and directory names, and change cp to rcp or scp if cloning to another server.

Database clones can also be done with rman, which has the advantage of avoiding the performance draining hot backup mode.

set pages 9999 lines 132 serverout on size 99999
BEGIN
  FOR t IN ( select distinct tablespace_name from dba_data_files )
  LOOP
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' begin backup ;') ;
    FOR f IN ( select file_name from dba_data_files where tablespace_name = t.tablespace_name )
    LOOP
      dbms_output.put_line('host cp '||f.file_name||' '||f.file_name ) ;
    END LOOP ;
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' end backup ;') ;
  END LOOP ;
END ;
/
set lines 80
select 'alter tablespace '||tablespace_name||' add tempfile '||file_name||' size '||bytes/1024/1024||' m ;'
from dba_temp_files
/
prompt alter system switch logfile ;;
prompt alter system backup controlfile to trace ;;

Slow SQL report

Lists slow SQL in library cache for tuning investigations:

set pages 9999
spool c.lst
select elapsed_time/1000000 secs, executions,
  elapsed_time/1000000/greatest(executions,1) secs_per_exec,
sql_text from v$sql
where executions > 50
and elapsed_time/1000000/greatest(executions,1) > 1
order by 3 desc
/
spool off
ed c.lst

For older versions of oracle, use buffer_gets or disk_reads in place of elapsed_time.

Using 10g datapump and scheduler to copy schemas

Update 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below.

Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in this post.

For a nightly process to copy small schemas from one database to another, the normal, or old style, way to do that is with export/import in a cron controlled shell script, which would include a sql-from-sql script to drop all the target schema objects before doing the import.

But that presents problems in RAC, which is what I was working on - which node does the cron script reside on? What happens if that node is down?

An option would be to have the script run on another machine, one unrelated to the RAC cluster, but that introduces more points of failure, and means sending data back and forward over sql*net.

So instead I used the oracle’s 10g scheduler and datapump, as below. This data pumps straight over a database link, without having to write to a dump file in between, which is nice. And because it is all in the database, it ought to be unaffected by particular RAC nodes being down.

Identify what auditing is on

Suspect too much auditing is on on a database? This script will show you what auditing is on:

set pages 9999
select * from DBA_STMT_AUDIT_OPTS ;
select * from DBA_PRIV_AUDIT_OPTS ;
set termout off lines 192
spool 1.tmp
select * from DBA_OBJ_AUDIT_OPTS ;
spool off
set termout on lines 80
host egrep '(S/|A/|/S|/A)' 1.tmp

Note that the unix command egrep is used to filter the dba_obj_audit_opts rows of interest.

Ref: Oracle Database Security Guide, Chapter 12

Pre 9202/9014: Advanced queuing runs away with server memory.

Using the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had high memory usage (over 200mb each). Which pointed to bug 2227360 “QMN process leaking memory”

It is fixed in 9.0.1.4/9.2.0.2 and above, but I was on 9.2.0.1. So the workaround is to kill the QMN (=AQ) sessions. Sounds drastic, but oracle restarts the processes apparently cleanly, with just a message in the alert log like:

Restarting dead background process QMN0
QMN0 started with pid=9

And that is the memory then freed up - for a while at least, you have to be prepared to kill those sessions every so often as the memory leaks build up, if, that is, you can’t patch up.

I used the below script to kill the sessions. I didn’t need to use the drastic “kill -9” on this occasion, but sometimes sessions take forever to go without that.

select 'alter system kill session '''||s.sid||','||s.serial#||''' ;',
'kill -9 '||p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.program like '%QMN%'
/

Reference: Metalink Note 233101.1.

Script run against every running database, listing total oracle memory (sga+pga)

This shell script runs against every database that is up and running (has an entry in the ps list). Which I think is better than searching through oratab (since not all of those may be up and running, esp. on dev machines).

You can run any SQL within it, but this particular version prints out the total oracle memory (sga and pga) use. That is useful because there is often a risk of more memory being allocated to oracle than actual physical memory on the server. That is especially true of dev servers with many databases running.

EDIT - wordpress.com just will not properly format the script code, so its uploaded in this .doc file.

The results are best pasted into a spreadsheet where there are many databases.

On 9i on Solaris, sga_max_size preallocates the memory at OS leve, making it largely useless, so there would be worth also checking that, with e.g. the commented out line above.

One thing to watch is that to query a v$ or sm$ view direct from a shell script, you have to escape out the $, hence the “v\$” above rather than "v$".

Array use in this script (rather than clumsier writing to temporary files) came from Daniel D’Souza.