Re: long runtimes cured by cutting hash_area_size by 90% - ???

From: Layzee DBA <layzeedba2_at_yahoo.com>
Date: Tue, 08 Apr 2003 19:33:37 -0800
Message-ID: <F001.0057D9B8.20030408193337@fatcity.com>

Take a good look at what the guru has to say:

http://asktom.oracle.com/pls/ask/f?p=4950:8:17804323752346946::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:449420120409,

May be that you did not have much redosize / generated OR for that matter recursive calls (related to space mgmt). What might have been actually happening is that the values would have been got from the temp tbs because it was already avbl. during the "first run". (You had mentioned about alter session after first run).

Try taking the db down or making the temp tbs permanent and bringing it back as temp as shown in the example and rerun with hash area size (smaller). Set trace and post back the results.

First do a trace, then go for tkprof blah blah.

HTH

---

Not so Lazy DBA

---

• Chris Barr <cbarr_at_hotpop.com> wrote:
  > Hash_Area_Size runtime
  > 10,000,000 6:16 ( 6+ mins )
  > 1,000,000 0:15 ( 16 secs)
  >
  > Why?
  > Isn't bigger always better for hash_area_size?
  > Is another param affected?
  >
  > That's the only param that changed, via alter
  > session.
  >
  > Oracle 8.1.7, Sun/Solaris, 8 cpus, lots & lots of
  > memory,
  > no other queries, or 3 concurrent: same runtime.
  >
  > The query joins 9 tables, one has 1.8M rows, the
  > others under 60k rows.
  >
  > Explain Plan shows 5 nested loops, 3 hash joins, and
  > no diff between
  > plans with the 2 values.
  >
  > Chris Barr
  >
  >
  >
  

  ---

Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Layzee DBA
  INET: layzeedba2_at_yahoo.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 Tue Apr 08 2003 - 22:33:37 CDT