| | SQL to view OS process and Oracle session info
Use this commands and sql query to show information about OS process and Oracle user session. Useful if for example you notice a single process is chewing up CPU on the server.
At the Unix prompt (replace 22910 with the actual process id):
$ ps -ef | grep 22910
oracle 22910 ... | |  
More...
08/02/06 | | | | |
|
| | Find the haviest queries in multiple trace files
After processing multiple trace files with tkprof you can sort them by number of seconds it took to execute queries, the potential problematic queries will appear at the bottom:
grep ^total *.trc.txt | sort -k 4 -n
| | More...
07/06/06 | | | | |
|
| | Using Oracle statspack
STATSPACK has two adjustable parameters: level and threshold. Level is the type of data collected and threshold acts as a filter for collecting SQL statements. You can use this query to view all levels:
SQL> SELECT * FROM stats$level_description ORDER BY snap_level;
Level 0 This level captures general statistics, including rollback segment, row ... | | More...
04/26/06 | | | | |
|
| | Installing Oracle STATSPACK
STATSPACK is a performance diagnosis tool available since Oracle8i. STATSPACK is the successor of BSTAT and ESTAT utilities. It is recommended to set timed_statistics to true. To install STATSPACK follow the steps below:
1. Create PERFSTAT Tablespace:
SQL> CREATE TABLESPACE statspack
DATAFILE '/path_to_file.dbf' SIZE 200M REUSE
... | |
More...
04/26/06 | | | | |
|
| | How to enable trace in Oracle
1. Enable trace at instance level
Put the following line in init.ora. It will enable trace for all sessions and the background processes
sql_trace = TRUE
to disable trace:
sql_trace = FALSE
- or -
to enable tracing without restarting database run the following command in sqlplus
SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;
to stop trace run:
SQLPLUS> ... | |
More...
11/03/05 | | | | |
|
| | Show current transactions per user session
--
-- SQL script to show transactions for each user session
--
SELECT s.logon_time, t.start_time, oc.sql_text, s.username "User Name",
s.osuser "OS User", s.status "Status", lockwait "Lock Wait",
s.program "Program", s.logon_time "Connect Time", p.program "P Program",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets", si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, s.serial#, ... | | More...
11/03/05 | | | | |
|
| | Show locks per user session
--
-- View locked objects, object types and user info
--
SELECT d.owner, d.object_id, d.object_name, d.object_type, l.type, s.username, s.sid, s.serial#
FROM dba_objects d, v$lock l, v$session s
WHERE d.object_id = l.id1
AND l.sid = s.sid
AND owner 'SYS'
ORDER BY d.object_name
/
| | 
More...
11/03/05 | | | | |
|
| | Explain plan for an SQL query
1. Create the plan table
SQLPLUS> @${ORACLE_HOME}/rdbms/admin/utlxplan.sql
2. Delete old plan from the table and run explain plan.
Replace 'select * from v$database' with the actuall sql statement you want explain plan for.
set lin 180
set pages 0
set feedback on
set echo off
delete from plan_table
where STATEMENT_ID = 'p1'
/
commit
/
EXPLAIN PLAN SET STATEMENT_ID = 'p1' FOR ... | | More...
10/13/05 | | | | |
|
| | Generate an analyze script for all tables in schema
-- Replace USERNAME with the schema owner who's tables are to be analyzed.
set head off pagesize echo off verify off feedback off linesize 200
spool analyze.sql
select 'set head on echo on verify on feedback on'
from dual
/
select 'spool analyze.log'
from dual
/
select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>' || '''' || owner ||
'''' || ', ' || ... | | More...
10/03/05 | | | | |
|
| | Example of how to trace a session and use tkprof
Enable trace for a user session:
SQLPLUS> exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL, true);
Use tkprof to analyze the trace file:
tkprof [TRACE FILE] [OUTPUT FILE] explain=/ sort=prsela,exeela,fchela table=ops\$oracle.plan_table
| |
More...
10/03/05 | | | | |
|
<< 1 | Page 2 | 3 >> |
