OraSRP v1.9

I released OraSRP v1.9

A little changes in outputs (only get rid of irrelevant numbers in Summary section).

Added three new flags:
--stdin tells orasrp to read data from stdin. By using this flag its possible to analyze gzipped/compressed trace files on the fly. Like this:
$ gzip -cd trace_file.trc.gz | orasrp.py -s --stdin > trace_profile.html

--pattern flag is used in http server mode. It tells OraSRP to show/process only those files which names contain specified pattern.

--htpasswd flag is also used in http server mode. It instructs OraSRP to use .htpasswd file for client (more...)

what to do with hanging database

Here's the citation from article 'Oracle Database 10g: Top Features for DBAs: Part 3—Performance Features' by Arup Nanda:
Let's assume that you use Oracle Enterprise Manager to diagnose and solve performance issues. One day, a nasty issue arises: a badly designed application is causing serious library-cache lock issues and the database appears to be hung. You have to quickly identify the culprit sessions and kill them quickly.

You could bring up Oracle Enterprise Manager to diagnose this issue. But, wait! If the entire database is saturated with hung sessions, wouldn't the query from Oracle Enterprise Manager hang as well?

With (more...)

Pl/sql timer

A simple package to output the elapsed time between two points. Supports versions from 8i onwards. July 2005

another way to generate systemstate/hanganalyze events

It's a known fact that we can generate systemstate dump by directly attaching to oracle process. Like this:

$ cd $ORACLE_HOME/bin
$ gdb oracle <oracle_pid_to_attach>
(gdb) call ksudss(10) # at this point attached process should do smth, i.e. it shouldn't sleep

Search for 'ksudss' in Metalink for more detailed info.

But if, instead of ksudss() we will call ksdhng() then hanganalyze trace (which is way more readable than systemstate dump and often is enough for analyzing 'hang database' issue) will be generated.

[below the same in Russian]


Известно, что мы можем сгенерить systemstate dump с помощью gdb, приаттачившись к (more...)

Array-based advanced queuing in 10g

Uncategorized
| Jul 16, 2005
Oracle 10g enables us to enqueue and dequeue in bulk. July 2005

new fields in v$session

While one known 'balabol' blows at every corner about the fact that 'The 10046 wait interface becomes obsolete ', Oracle in its new version 10.2 added three new fields in view v$session, intended only for the work with 10046 wait interface and wrote three new procedures in package dbms_monitor, utilized for the same.

Fields named SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS make it possible to see whether session is traced and if yes, then with what parameters. However, finesse in the fact that the correct values in these fields are advanced only during use dbms_monitor, i.e., if we establish tracing (more...)

новый формат extended sql trace файла в 10.2

На протяжении многих лет Oracle не менял формат extended sql trace файла. К примеру, версия 7.2, вышедшая в 1994 году генерит практически такой же trace файл, что и версия 10.1

Однако, с выходом версии 10.2 инженеры корпорации Oracle решились на небольшое изменение формата trace файла. Если ранее 'WAIT'-строки выглядели примерно так:

WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0

то в 10.2 они выглядят так:

WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=43442 tim=1094547545117701

Т.е. названия параметров p1/p2/p3 тут же расшифровываются исходя из значения nam.
Для каждого (more...)

Introduction to advanced queuing

Uncategorized
| Jul 8, 2005
A high-level tutorial on Oracle's Advanced Queuing. July 2005

Pl/sql timer

Uncategorized
| Jul 8, 2005
A simple package to output the elapsed time between two points. Supports versions from 8i onwards. July 2005

fixed view definitions in Oracle Database 10.2

Для любопытных персон, которые время от времени разглядывают определения fixed views (находящихся в v$fixed_view_definition, но в неотформатированном виде) я выложил определения fixed views из Oracle Database 10G Release 2 в человеческом виде.