Slow performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Slow performance

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    Slow performance

    We have a new application in a fairly empty database and there have been complaints of slow performance. Looking at the statspack report for those time windows, this is what I see as the most expensive query. But the explain plan for that SQL looks good. When I run this SQL directly on the database, it comes back under 2 msecs. So where else should I look ?

    Code:
        CPU                  CPU per             Elapsd                     Old
      Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
    ---------- ------------ ---------- ------ ---------- --------------- ----------
         38.39           12       3.20   59.0     320.31      14,569,520 3959463376
    Module: JDBC Thin Client
    select level, seq_order, standard_id, title, internal_grade_leve
    ls as grade           from standard          where standard_type
    _id = :1            and state_code = :2         connect by prior
     standard_id = parent_id           start with parent_id = :3
    
    
    Plan
    SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 555 Cardinality: 3 
     	8 FILTER 
     	 	7 CONNECT BY WITH FILTERING 
     	 	 	2 TABLE ACCESS BY INDEX ROWID TABLE SBO.STANDARD Cost: 1 Bytes: 555 Cardinality: 3 
     	 	 	 	1 INDEX RANGE SCAN INDEX SBO.STANDARD_PARENTID_IX Cost: 1 Cardinality: 3 
     	 	 	6 NESTED LOOPS 
     	 	 	 	3 CONNECT BY PUMP 
     	 	 	 	5 TABLE ACCESS BY INDEX ROWID TABLE SBO.STANDARD Cost: 1 Bytes: 555 Cardinality: 3 
     	 	 	 	 	4 INDEX RANGE SCAN INDEX SBO.STANDARD_PARENTID_IX Cost: 1 Cardinality: 3

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I understand some user is complaining about bad performance -that's a surprise, users never complain about performance LOL

    Okay... you found a query in statspack.

    Question, is that the particular query user is complaining about?

    Can you define good performance for the affected query? do you have a baseline? what's the SLA? Did you compare SLA vs. actual response time?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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