Oracle-L: RE: RE: Cary's Book

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Wed, 15 Oct 2003 10:39:38 -0800
Message-ID: <F001.005D3409.20031015103938@fatcity.com>

Ron,

It is really simple - Just recreate the *larger* TRCA tables to use GTT as shown below:

CREATE GLOBAL TEMPORARY TABLE trca$trace (

  trace_id                   NUMBER,
  trace_filename             VARCHAR2(64),
  trace_size                 NUMBER,
  trca_date                  DATE,
  parsed_percent             NUMBER,
  host_name                  VARCHAR2(64),
  platform                   VARCHAR2(40),
  rdbms_release              VARCHAR2(17),
  instance_name              VARCHAR2(16),
  same_instance              VARCHAR2(13),
  tim_factor                 NUMBER,
  start_date                 VARCHAR2(23),
  trace_date                 DATE,
  start_tim                  NUMBER,
  completion_tim             NUMBER,
  duration_secs              NUMBER,
  total_gaps                 NUMBER,
  total_c                    NUMBER,
  total_e                    NUMBER,
  wait_non_idle              NUMBER,
  wait_idle                  NUMBER,
  truncated                  VARCHAR2(9),
  num_lines                  NUMBER,
  cursors_sys                NUMBER,
  cursors_user               NUMBER,
  unique_sql_sys             NUMBER,
  unique_sql_user            NUMBER

)
ON COMMIT PRESERVE ROWS; CREATE GLOBAL TEMPORARY TABLE trca$parsing_in_cursor (

  trace_id                   NUMBER,
  line_id                    NUMBER,
  cursor_#                   NUMBER,
  cursor_id                  NUMBER,
  len                        NUMBER,
  dep                        NUMBER,
  uid$                       NUMBER,
  oct                        NUMBER,
  lid                        NUMBER,
  tim                        NUMBER,
  hv                         NUMBER,
  ad                         VARCHAR2(32),
  err                        NUMBER

)
ON COMMIT PRESERVE ROWS; Etc...

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Ron Thomas [mailto:rthomas_at_hypercom.com]
>Sent: Wednesday, October 15, 2003 11:19 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: RE: Cary's Book - new topic
>
>
>
>John-
>
>Just got back from a vacation and saw this... Our jr DBA is
>in the process of doing this. Care to
>share your code???
>
>Thanks,
>Ron Thomas
>Hypercom, Inc
>rthomas_at_hypercom.com
>Each new user of a new system uncovers a new class of bugs. --
>Kernighan
>
>
>
>
>
> john.kanagaraj_at_hd
>
>
> s.com To:
>ORACLE-L_at_fatcity.com
>
> Sent by: cc:
>
>
> ml-errors_at_fatcity Subject: RE:
>RE: Cary's Book - new topic
>
> .com
>
>
>
>
>
>
>
>
> 10/08/2003 02:04
>
>
> PM
>
>
> Please respond to
>
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
>Raj (and all who use Oracle's Trace analyzer,
>
>I 'converted' the trace analzyer tables to GTTs, and no longer
>had the space
>issues with large trace files. This is because the data is stored
>'temporarily' and is used for reporting in a subsequent SQL in the same
>session stream, and not reused elsewhere. Haven't really measured
>performance improvement, but this should ride on all the
>advantages that GTT
>provides.
>
>FWIW!
>John Kanagaraj
>DB Soft Inc
>Phone: 408-970-7002 (W)
>
>Disappointment is inevitable, but Discouragement is optional!
>
>** The opinions and facts contained in this message are
>entirely mine and do
>not reflect those of my employer or customers **
>
>-----Original Message-----
>Sent: Wednesday, October 08, 2003 7:19 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>Thanks,
>
>I have been using that tool for a long time now, it needs a
>big tablespace
>(cause everything is loaded in tables) and puts a load on the
>server. It is
>good for smaller files, but takes too long on larger files.
>
>Nevertheless it is a great utility.
>Raj
>---------------------------------------------------------------
>-------------
>----
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-----Original Message-----
>Sent: Wednesday, October 08, 2003 9:50 AM
>To: Multiple recipients of list ORACLE-L
>
>
>go to metalink and check out trace analyzer. ITs a new tool
>for analyzing
>10046 traces. Has ALOT more detail than tkprof. Major
>improvement. Its on
>metalink.
>
>
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Ron Thomas
> INET: rthomas_at_hypercom.com
>
>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).

Received on Wed Oct 15 2003 - 13:39:38 CDT