Oracle-L: Re: 2G trace files

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Wed, 29 Oct 2003 11:44:24 -0800
Message-ID: <F001.005D4F59.20031029114424@fatcity.com>

Richard,

Are you sure that you are targeting your diagnostic efforts appropriately?

If all users are using dedicated servers, then each trace file should only have the info for one session. If your interval for a statspack report is an entire week, its going to be pretty tough to find the particular query that you're looking for. Are you tracing all sessions, all the time?

The feds don't put up routine checkpoints on federal interstates just to check for expired registration. It would bottleneck the entire system and generate too much info to be processed effectively.

>From the Heisenburg angle, I would think that generating such a large amount of trace would clearly be impacting the server's I/O subsystem in a big way.

"Quintin, Richard" <rquintin_at_vt.edu> wrote: I'm tracing a session with 10046 event level 8. Here's the method I use:

sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE ); sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#, 'timed_statistics', true);
/* Max dump file size is 2G */
sys.dbms_system.set_int_param_in_session(p_sid, p_serial#, 'max_dump_file_size', 2147483647);
sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');

If I set max_dump_file_size greater than 2G I get an error. But with trace level 8, I'm easily overrunning this limit. How do you guys get around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't have to work so much I would have read it through by now. Maybe I'll take tomorrow off.

Richard Quintin, DBA
Information Systems & Computing, DBMS
Virginia Tech
--

"When the character of a man is not clear to you, look at his friends."
-- Japanese Proverb
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Quintin, Richard
INET: rquintin_at_vt.edu

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Paul Drake
INET: discgolfdba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------