Enable session trace

[vnktummala]

Hi,

Is there any thing wrong with the below syntax?

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 667, serial_num => 53739, waits => TRUE, binds => TRUE);

I have used this many times earlier but in the current site it is not generating any trace files in udump.

Please let me know if i am doing any thing wrong.

Thanks,

[LKBrwn_DBA]

Did you check the AUDIT_TRAIL parameter?

[vnktummala]

Hi LKB -

AUDIT_TRAIL parameter is set to DB.

Could you please explain how does it related to session trace?

Thanks,

[LKBrwn_DBA]

Ooops, yes. I was thinking about auditing, not tracing...Need coffee.


PS: Are you logon as DBA?
"DBMS_MONITOR package can only be invoked by a user with the DBA role..."

[vnktummala]

Hi LKB,

hope you had a good coffee ...

Yes, I logged in DBA. However, the trace files started generating after half an hour. Looks like this site had severe I/O issue. Why I am thinking in that front is ...

1. OS level file copy from one FS to other FS is very slow
2. at least two db writer processes are in the top list (when using unix top)
3. at least one archiver processes is in the top list
4. often check point will be first in the top consuming 80% cpu
5. log writer always complaints that can not allocate new log sequence
6. sequential reads are damn slow ... and so on

This Oracle 10.2.0.4 db is on HP RX 8640 with 24 cores and 48 GB memory. Storage is Hitachi FC SAN.

I am working on to prove with evidence that there an I/O issue.

Any suggestions from your side is much appretiated.

P.S - due to license issues I can not use AWR & ADDM here. I can only work on Statspack.

Thanks,

[malay_biswal]

Vijay,
Why don't you execute sar/vmstat whaetever and check whether I/O is really issue?

[vnktummala]

Hi Malay,

Sure. At the moment no load in the DB. I will do that during next batch load time.

By the way, is there any recommended switch to get more information about disk I/O in vmsat or sar?

I usually use "vmstat 2 100" or "vmstat -du 2 100" or "sar 2 100" like that. Any suggestion will be much appreciated?

Thanks,

[malay_biswal]

sar or vmstat whatedver u mentioned should be good enough for initial I/O investigation. Then you may get into details using iostat or similar command and at same time get the pid track it what it's doing.