| | View wait events for a session
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser, b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 283
order by time_waited; | | More...
01/13/09 | | | | |
|
| | Get buffer cache hit ratio / miss ratio
SQL> SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets )) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
/
NAME HIT_RATIO
-------------------- ----------
DEFAULT .817011081
| | More...
06/24/08 | | | | |
|
| | View what sql user is running
-- View SQL text for user session
-- Replace DB_USER with the actuall database username and os_user with the OS username.
select a.sql_text, b.username, b.osuser, b.logon_time, b.program,
b.machine, b.sid, b.serial#
from v$sql a, v$session b
where a.address = b.sql_address
and b.username = 'DB_USER'
and b.osuser = 'os_user'; | | More...
11/01/07 | | | | |
|
| | How to save (export) table stats
Before analyzing a table you may want to save the old stats (i.e. if the table is very large and analyzing takes a while just to have a backup of previous stats). Here's the syntax:
SQL> exec dbms_stats.export_table_stats('SCHEMA_OWNER', 'TABLE_NAME', NULL, 'STATS_TABLE_NAME', 'STATID', TRUE, 'SCHEMA_OF_STATS_TABLE'); | |  
More...
06/27/07 | | | | |
|
| | Script to analyze table subpartitions
This script will analyze specified subpartitions in a loop, useful when some partitions / subpartitions in the table have been modified or re-built.
BEGIN
FOR rec IN
(SELECT subpartition_name
FROM all_tab_subpartitions
WHERE table_owner = 'SCHEMA_OWNER'
... | | More...
06/27/07 | | | | |
|
| | How to ANALYZE partitioned tables with VALIDATE STRUCTURE
If you got the error:
ORA-14508: specified VALIDATE INTO table not found
This may be because you are trying to analyze a partitioned table and INVALID_ROWS table is not found in the current schema.
To validate structure (checks objects for logical corruption) for partitioned tables you first need to ensure that you’ve got ... | |
More...
01/24/07 | | | | |
|
| | 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 | | | | |
|
| | 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 | | | | |
|
Page 1 | 2 >> |
