Category Archives: Performance tuning

Installing dbms_profiler

Posted on 22 June 2007 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

plan_table changes

Posted on 23 May 2007 by Andrew Fraser

In versions 10g, plan_table exists as a public synonym of the global temporary table sys.plan_table$. That is all created automatically at database create time and when a database is upgraded to 10g (creation is in catplan.sql, which is called from … Continue reading →

Posted in Performance tuning | 4 Comments

Fix for TOAD error ORA-02404: specified plan table not found

Posted on 23 May 2007 by Andrew Fraser

TOAD can report error “ORA-02404: specified plan table not found” when trying to display execution plans: Two options to fix: Run script toadprep.sql. This creates the table toad_plan_table, among other things, and is meant to be run by a user … Continue reading →

Posted in Performance tuning | 49 Comments

What SQL and sessions are running?

Posted on 20 April 2007 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, Scripts | 3 Comments

10046 tracing in another session

Posted on 14 February 2007 by Andrew Fraser

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) … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

Slow SQL report

Posted on 7 February 2007 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, Scripts | Leave a comment

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

Posted on 11 January 2007 by Andrew Fraser

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 … Continue reading →

Posted in Performance tuning, Scripts | 1 Comment