Re: Performance Tuning

On Feb 20, 6:30 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 20, 5:05 pm, "James" <jwilk..._at_gmail.com> wrote:
>
>
>
>
>
> > On Feb 20, 3:48 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > > On Feb 20, 3:34 pm, "James" <jwilk..._at_gmail.com> wrote:
>
> > > > Hi,
>
> > > > I work on a data warehouse and hit the brick wall on this one. Here
> > > > is the problem:
>
> > > > The source data we are trying to load looks like this (Table1)
> > > > ID Code Quantity
> > > > 1 'A' 2
> > > > 1 'A' 3
> > > > 1 'B' 2
> > > > 2 'A' 1
> > > > 2 'C' 2
> > > > 3 'A' 4
> > > > ...
>
> > > > Our user wants to see one row per ID, like this (Table2)
> > > > ID A_Quantity B_Quantity C_Quanty
> > > > 1 5 2 null
> > > > 2 1 null null
> > > > 3 4 null null
>
> > > > There are a few challenges we face:
> > > > 1. We have three different codes now but new ones can be added anytime
> > > > without us knowing. So we don't want to design a table 2 because it'll
> > > > become a nightmare to maintain.
> > > > 2. The amount of data is very large (40 million rows).
> > > > 3. We have limited system resources to work with.
>
> > > > So I loaded the data just like table1 and created a view look like
> > > > this:
>
> > > > create view myview as
> > > > select a.a_q, a.b_q, a.c_q, b.id, b.other_things
> > > > from
> > > > (Select max(a_quantity) a_q, max(b_quantity) b_q, max(c_quantity)
> > > > c_q
> > > > from
> > > > (Select
> > > > sum(decode (quantity, code, 'A' ,quantity, null)) as
> > > > A_quantity,
> > > > sum(decode (quantity, code, 'B', quantity, null)) as
> > > > B_quantity,
> > > > sum(decode (quantity, code, 'C', quantity, null)) as
> > > > C_quantity
> > > > from table1
> > > > group by ID, Code )
> > > > group by ID) a, some_other_large_table b
> > > > where a.id = b.id)
>
> > > > The problem we are running into is when querying this view, a very
> > > > very large amount of temp space is used. Beause there are other
> > > > schemas using the same temp space, we are blowing everybody else out
> > > > of the water. What other options do I have?
>
> > > > Thanks!
>
> > > > James.
>
> > > Do you have a DBMS XPLAN? Maybe try to simplify:
>
> > > The set up:
> > > CREATE TABLE T1 (
> > > ID NUMBER(10),
> > > CODE VARCHAR2(4),
> > > QUANTITY NUMBER(10));
>
> > > INSERT INTO T1 VALUES (1,'A',2);
> > > INSERT INTO T1 VALUES (1,'A',3);
> > > INSERT INTO T1 VALUES (1,'B',2);
> > > INSERT INTO T1 VALUES (2,'A',1);
> > > INSERT INTO T1 VALUES (2,'C',2);
> > > INSERT INTO T1 VALUES (3,'A',4);
>
> > > COMMIT;
>
> > > Starting point:
> > > SELECT
> > > ID,
> > > CODE,
> > > SUM(QUANTITY) QTY
> > > FROM
> > > T1
> > > GROUP BY
> > > ID,
> > > CODE;
>
> > > ID CODE QTY
> > > ---------- ---- ----------
> > > 1 A 5
> > > 1 B 2
> > > 2 A 1
> > > 2 C 2
> > > 3 A 4
>
> > > Next step:
> > > SELECT
> > > ID,
> > > MAX(DECODE(CODE,'A',QTY,NULL)) A_QUANTITY,
> > > MAX(DECODE(CODE,'B',QTY,NULL)) B_QUANTITY,
> > > MAX(DECODE(CODE,'C',QTY,NULL)) C_QUANTITY
> > > FROM
> > > (SELECT
> > > ID,
> > > CODE,
> > > SUM(QUANTITY) QTY
> > > FROM
> > > T1
> > > GROUP BY
> > > ID,
> > > CODE)
> > > GROUP BY
> > > ID;
>
> > > ID A_QUANTITY B_QUANTITY C_QUANTITY
> > > ---------- ---------- ---------- ----------
> > > 1 5 2
> > > 2 1 2
> > > 3 4
>
> > > DBMS XPLAN:
> > > ---------------------------------------------------------------------------ญญญ---------------------------------------
> > > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > > Time | Buffers | OMem | 1Mem | O/1/M |
> > > ---------------------------------------------------------------------------ญญญ---------------------------------------
> > > | 1 | SORT GROUP BY NOSORT| | 1 | 6 | 3 |
> > > 00:00:00.01 | 7 | | | |
> > > | 2 | VIEW | | 1 | 6 | 5 |
> > > 00:00:00.01 | 7 | | | |
> > > | 3 | SORT GROUP BY | | 1 | 6 | 5 |
> > > 00:00:00.01 | 7 | 9216 | 9216 | 1/0/0|
> > > | 4 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 |
> > > 00:00:00.01 | 7 | | | |
> > > ---------------------------------------------------------------------------ญญญ---------------------------------------
>
> > > Try #2:
> > > SELECT
> > > ID,
> > > SUM(DECODE(CODE,'A',QUANTITY,NULL)) A_QUANTITY,
> > > SUM(DECODE(CODE,'B',QUANTITY,NULL)) B_QUANTITY,
> > > SUM(DECODE(CODE,'C',QUANTITY,NULL)) C_QUANTITY
> > > FROM
> > > T1
> > > GROUP BY
> > > ID;
>
> > > ID A_QUANTITY B_QUANTITY C_QUANTITY
> > > ---------- ---------- ---------- ----------
> > > 1 5 2
> > > 2 1 2
> > > 3 4
>
> > > DBMS XPLAN:
> > > ---------------------------------------------------------------------------ญญญ-------------------------------------
> > > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > > Time | Buffers | OMem | 1Mem | O/1/M |
> > > ---------------------------------------------------------------------------ญญญ-------------------------------------
> > > | 1 | SORT GROUP BY | | 1 | 6 | 3 |
> > > 00:00:00.01 | 7 | 9216 | 9216 | 1/0/0|
> > > | 2 | TABLE ACCESS FULL| T1 | 1 | 6 | 6 |
> > > 00:00:00.01 | 7 | | | |
> > > ---------------------------------------------------------------------------ญญญ-------------------------------------
>
> > > Your SQL statement?
>
> > > What version of Oracle? What is the SORT_AREA_SIZE set to?
>
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > We have 9i. sort_area_size = 4194304
> > The amount of data we have is huge. We might have hit a hard limit
> > because the amount of data it has to process.- Hide quoted text -
>
> A 10046 trace may reveal what is causing the problem. It is also
> important to look at the explain plan (DBMS XPLAN) to see what is
> happening - is Oracle joining the tables in a different order than
> expected, is Oracle predicting that a join will produce 1 row, when it
> really produces 17,000,000 rows, etc.
>
> A couple days ago I worked through a similar problem, where neither a
> 10046 trace nor a 10053 trace revealed much information related to why
> a query was taking in excessive of 33 minutes to execute. A DBMS
> XPLAN showed that Oracle was a bit more creative than I expected when
> generating the execution plan. When I finished helping Oracle with a
> couple hints, the same query executed in roughly 2.5 seconds. In my
> case, Oracle did not want to allow in inline view drive a table -
> instead it decided to use a Cartesian join to drive into the inline
> view.
>
> Also, keep in mind that placing the SQL code into a view will, as a
> general rule, never improve performance. There is a greater chance
> that placing the SQL code into a view will hurt performance, as the
> views may hide some optimization methods.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

I will try that, thanks!

James Received on Wed Feb 21 2007 - 10:05:32 CST