Re: Stats on partitioned table

From: rpr <p_r_renu_at_yahoo.com>
Date: Mon, 24 Nov 2003 09:29:26 -0800
Message-ID: <F001.005D79FC.20031124092926@fatcity.com>

Hi,

1. Did you look at the explain plans in both places?
2. What the difference in size of data?
3. How many times did you run the test?
4. Did you try giving the *RULE* hint and see the timing?

"Bala, Prakash" <prakash.bala_at_hp.com> wrote:

st1\:* {	BEHAVIOR: url(#default#ieooui)}@font-face {	font-family: Tahoma;}@font-face {	font-family: Arial \(Arabic\);}@page Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; mso-header-margin: .5in; mso-footer-margin: .5in; mso-paper-source: 0; }P.MsoNormal {	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: ""; mso-pagination: widow-orphan; mso-fareast-font-family: "Times New Roman"}LI.MsoNormal {	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: ""; mso-pagination: widow-orphan; mso-fareast-font-family: "Times New Roman"}DIV.MsoNormal {	FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-style-parent: ""; mso-pagination: widow-orphan; mso-fareast-font-family: "Times New Roman"}A:link {	COLOR: blue; TEXT-DECORATION: underline; text-underline: single}SPAN.MsoHyperlink {	COLOR: blue; TEXT-DECORATION: underline; text-underline: single}A:visited {	COLOR: blue;
 TEXT-DECORATION: underline; text-underline: single}SPAN.MsoHyperlinkFollowed {	COLOR: blue; TEXT-DECORATION: underline; text-underline: single}SPAN.EmailStyle17 {	COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply; mso-style-noshow: yes; mso-ansi-font-size: 10.0pt; mso-bidi-font-size: 10.0pt; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial; mso-bidi-font-family: Arial}SPAN.SpellE {	mso-style-name: ""; mso-spl-e: yes}DIV.Section1 {	page: Section1}

Hello,

Data-warehouse application using Oracle 8.1.7.4 on HP-UX 11i

Since our ETL process is running for a long time, have been given the task of partitioning some of huge tables and check the results. So picked the top 5 sessions (in terms of running time) and got the sql used by Informatica.

Test results were excellent: 48 minutes against the original table vs 15 seconds on the partitioned table to select about 4000 rows from a 5 million row table. The test was done using a separate schema on the same production database.

However, after implementing the same in the production schema, the same query is taking about 100 seconds.

Automatic_parallel_tuning is set to TRUE. Parallelism is set to 5 at the table level.

The only diference that I can see is that the table in production schema is analyzed (using analyze table ... estimate statistics 20 percent) whereas my test table was not analyzed.

Would appeciate any input.

TIA Prakash

---

Do you Yahoo!?
Free Pop-Up Blocker - Get it now
--

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

Author: rpr
  INET: p_r_renu_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 Mon Nov 24 2003 - 11:29:26 CST