tune sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: tune sql

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I have a small SQL, was wondering if anyone´s got any suggestion to improve it

    cheers


    SELECT A.*, B.OFICINAS, C.FOTOS
    FROM
    ICDBA.IC_PROMOCIONES A,
    (select count(COD_OFI) AS OFICINAS, COD_AGE, COD_RED, COD_PRM
    FROM ICDBA.PR_PROMOCIONOFI
    GROUP BY COD_AGE, COD_RED, COD_PRM) B,
    (SELECT COUNT(COD_TIP_IMG) as fotos, COD_RED, COD_AGE, COD_PRM
    FROM ICDBA.IC_FICH_GRAFICOS
    GROUP BY COD_AGE, COD_RED, COD_PRM) C
    WHERE
    A.COD_AGE = B.COD_AGE AND
    A.COD_RED = B.COD_RED AND
    A.COD_PRM = B.COD_PRM AND
    c.COD_AGE (+)= a.COD_AGE AND
    c.COD_RED (+)= a.COD_RED AND
    c.COD_PRM (+)= a.COD_PRM
    AND A.COD_RED=2
    AND A.COD_AGE=596
    AND A.COD_PRM=3331
    /

    Query Plan
    --------------------------------------------------------------------------------
    SELECT STATEMENT Cost= 5
    NESTED LOOPS OUTER
    NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID IC_PROMOCIONES
    INDEX RANGE SCAN IC_PROMOCIONES_PK
    VIEW
    SORT GROUP BY
    INDEX RANGE SCAN PR_PROMOCIONOFI_PK
    VIEW
    SORT GROUP BY
    TABLE ACCESS FULL IC_FICH_GRAFICOS

  2. #2
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    Phew! What a query.
    By the way what is table size of each table in the query and how much time is this particular query taking.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well the tables arent big since this is from our development database, there are only a couple of hundreds rows in each table

    table ic_promociones is the parent table and the other two are the child table so those two would be pretty big once we go to production.

    The timing is 00:00:00:70

    Excution plan using autotrace on (shows more details)

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=445)
    1 0 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=445)
    2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
    t=1 Card=1 Bytes=341)

    4 3 INDEX (RANGE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE) (
    Cost=1 Card=1)

    5 2 VIEW
    6 5 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
    7 6 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
    ) (Cost=2 Card=1 Bytes=39)

    8 1 VIEW
    9 8 SORT (GROUP BY) (Cost=3 Card=82 Bytes=3198)
    10 9 TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Ca
    rd=82 Bytes=3198)

    Statistics
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    5 consistent gets
    0 physical reads
    0 redo size
    918 bytes sent via SQL*Net to client
    315 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    1 rows processed



    What it bothers me is

    TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Card=82 Bytes=3198)

    Since this table could be pretty big, meanwhile for table PR_PROMOCIONOFI does goes by index range scan and both these tables have similar number of rows probably only 5 or 6 rows difference :o

  4. #4
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    I think you cannot avoid the full table scan because you have used group by on 'IC_FICH_GRAFICOS.
    Anyway I am thinking abt it.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    However I do the same with PR_PROMOCIONOFI
    using a group by it goes

    SORT GROUP BY
    INDEX RANGE SCAN PR_PROMOCIONOFI_PK

    tricky!

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Basically, I did not read all the detail in the thread. However, there were some obvious issues with the query as posted, so I just went right to it. Try:
    SELECT
    ---A.*,
    ---B.OFICINAS,
    ---C.FOTOS
    FROM
    ---ICDBA.IC_PROMOCIONES A,
    ---(
    ------SELECT
    ---------COUNT(*) AS OFICINAS,
    ---------COD_AGE,
    ---------COD_RED,
    ---------COD_PRM
    ------FROM
    ---------ICDBA.PR_PROMOCIONOFI
    ------WHERE
    ---------COD_RED---=---2---------AND
    ---------COD_AGE---=---596------AND
    ---------COD_PRM---=---3331 ------
    ------GROUP BY
    ---------COD_AGE,
    ---------COD_RED,
    ---------COD_PRM
    ---) ---------------------B,
    ---(
    ------SELECT
    ---------COUNT(*) AS FOTOS,
    ---------COD_RED,
    ---------COD_AGE,
    ---------COD_PRM
    ------FROM
    ---------ICDBA.IC_FICH_GRAFICOS
    ------WHERE
    ---------COD_RED---=---2---------AND
    ---------COD_AGE---=---596------AND
    ---------COD_PRM---=---3331 ------
    ------GROUP BY
    ---------COD_AGE,
    ---------COD_RED,
    ---------COD_PRM
    ---) ---------------------C
    WHERE
    ---A.COD_RED---=---2---------AND
    ---A.COD_AGE---=---596------AND
    ---A.COD_PRM---=---3331 ------AND
    ---B.COD_AGE---= A.COD_AGE---AND
    ---B.COD_RED---= A.COD_RED---AND
    ---B.COD_PRM---= A.COD_PRM---AND
    ---C.COD_AGE(+)= A.COD_AGE---AND
    ---C.COD_RED(+)= A.COD_RED---AND
    ---C.COD_PRM(+)= A.COD_PRM---AND


    - You are not restricting on B until after you grouped the entire table. You should restrict first, then group and save a lot of time. Same with B.
    - Never count a specific column, it *won't* change your answer but can change your plan. You were counting using a column that you did not reference anywhere else in the query, meaning that the optimizer probably had to go back to the table just to get that value. And again, it *will not* change your answer from the COUNT() function [COUNT (DISTINCT <column>) matters, but not COUNT()]. That probably explains your table scan.
    - Finally, I'll assume that there is an index on each table that contains COD_AGE, COD_RED and COD_PRM as the left, leading columns.


    Anyway, try this and let me know what you find.

    - Chris

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    The plan changed now to

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=445)
    1 0 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=445)
    2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
    3 2 VIEW (Cost=1 Card=1 Bytes=52)
    4 3 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
    5 4 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
    ) (Cost=2 Card=1 Bytes=39)

    6 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
    t=1 Card=246 Bytes=83886)

    7 6 INDEX (UNIQUE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE)
    8 1 VIEW
    9 8 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
    10 9 INDEX (RANGE SCAN) OF 'IC_FIC_GRAFICOS_PK' (UNIQUE)
    (Cost=2 Card=1 Bytes=39)



    the other way was



    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=445)
    1 0 NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=445)
    2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=393)
    3 2 TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
    t=1 Card=1 Bytes=341)

    4 3 INDEX (RANGE SCAN) OF 'IC_PROMOCIONES_PK' (UNIQUE) (
    Cost=1 Card=1)

    5 2 VIEW
    6 5 SORT (GROUP BY) (Cost=1 Card=1 Bytes=39)
    7 6 INDEX (RANGE SCAN) OF 'PR_PROMOCIONOFI_PK' (UNIQUE
    ) (Cost=2 Card=1 Bytes=39)

    8 1 VIEW
    9 8 SORT (GROUP BY) (Cost=3 Card=82 Bytes=3198)
    10 9 TABLE ACCESS (FULL) OF 'IC_FICH_GRAFICOS' (Cost=1 Ca
    rd=82 Bytes=3198)



    The difference is that

    TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
    t=1 Card=1 Bytes=341)

    changed to

    TABLE ACCESS (BY INDEX ROWID) OF 'IC_PROMOCIONES' (Cos
    t=1 Card=246 Bytes=83886)


    and now IC_FIC_GRAFICOS is accessed with index.


  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    But did you actually run it and compare the logical reads (V$SQL_AREA.BUFFER_GETS) and timings of the 2 statements? Since you are now restricting the lists before the grouping, you will be saving an awful lot of effort. Unfortunately, the plans don't tell you everything :). Of course, the logical reads count will also be affected by the switch from a table scan to and index usage. Your best bet might be to run each statement twice to remove any caching issues and check the timing of the second runs for each.

    - Chris

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I will do these timing today busy yesterday coz solaris died :(

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Your SQL statement:
    (SELECT COUNT(COD_TIP_IMG) as fotos, COD_RED, COD_AGE, COD_PRM
    FROM ICDBA.IC_FICH_GRAFICOS
    GROUP BY COD_AGE, COD_RED, COD_PRM) C

    Pando, Create a composite index on COD_AGE, COD_RED,COD_PRM columns, because if the index is available and the columns are mentioned in the Group By clause, the index will be used even though they are not mentioned in the WHERE clause.

    With few hundred rows, it is very difficult to predict the performance. Populate 100,000 rows in all tables using LOAD RUNNER, and then do the tuning.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width