Re: Performance Tuning

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. Received on Tue Feb 20 2007 - 18:30:24 CST