Performance issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance issue

  1. #1
    Join Date
    Sep 2005
    Posts
    11

    Performance issue

    Here is my query:
    SELECT a, b
    FROM (
    SELECT tab1.a, tab1.b,
    RANK() OVER (PARTITION BY tab1.d
    ORDER BY tab1.a DESC NULLS LAST) rnk
    FROM tab1, tab2
    WHERE tab2.x = xpto
    AND tab1.d = xyz
    AND tab1.c = tab2.a
    ) WHERE rnk < 7

    I have a primary key on tab1, that includes column c, d and others (the order is c,d,e,f).
    Have a primary key in tab2.
    The response time is about 0.6s to 1.3s. The point is that I have to make this query for 40000 times, if
    it takes 1s the procedure time is about 11hours!!!

    Explain plan
    SELECT STATEMENT Optimizer Mode=CHOOSE 1 96
    VIEW 1 39 96
    WINDOW SORT PUSHED RANK 1 85 96
    TABLE ACCESS BY LOCAL INDEX ROWID TAB1 3 117 2
    NESTED LOOPS 1 85 94
    TABLE ACCESS FULL TAB2 1 46 92
    PARTITION RANGE ITERATOR KEY KEY
    INDEX RANGE SCAN PK_TAB1 3 1 KEY KEY


    Table tab1 has 115200000 records and 70800.

    I try to run statistics, but without success in performance.

    What can I do?

    Thanks in advance,
    Carlos Dias

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool


    What do you mean by "I have to make this query for 40000 times,"?
    Is it: I have to execute this query 40,000 times? -- If this is the case, please provide the requirements and/or reason.

    PS: Do you have indexes on tab2 that include columns x, c and d?

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    WHERE tab2.x = xpto
    AND tab1.d = xyz
    AND tab1.c = tab2.a
    I would like to try with an index on tab1 (d,c) or tab2(x,a).

    Why don't you post the trace output.

    Tamil

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Since you'll be executing it 40K times and that sums up your problem. There is one thing missing bind variable as you are using hard coded variable. That will save the parsing time. You may need to look at your buffer cahe size and if it's sort may think of increasing it.
    http://www.perf-engg.com
    A performance engineering forum

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