DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Fetch Time !!

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Fetch Time !!

    I am running same query through siebel apps on production and test and both have same amount of data. On the prod it is taking much longer than on test. Also on production the timing is changing considerable based on the time of the day I run the query. During the busiest time it takes almost twice as much then during off hours. I want to pin point what is the bottle neck. I put a trace on both session and it show most time is during the Fetch.

    Can someone tell me what could be the bottel neck from the reading and how should I go about digging more.

    call Count cpu elapsed disk query curr rows
    ----- ----- --- ------- ------ ----- ---- ----
    Parse 10 0.04 0.02 0 0 0 0
    Execute 6 0.00 0.00 0 0 0 0
    Fetch 35 11.87 250.73 56808 447290 18 35
    ------- ---- ---- ------- ------ ------- ---- ---
    total 51 11.91 250.75 56808 447290 18 35

    ======================================================

    call count cpu elapsed disk query curr rows
    ------ ----- ----- ------- ----- ----- ---- ----------
    Parse 4 0.03 0.03 0 0 0 0
    Execute 2 0.00 0.01 0 0 0 0
    Fetch 14 2.46 8.21 24801 25372 8 14
    ------- ---- ----- ------- ----- ----- --- ----------
    total 20 2.49 8.25 24801 25372 8 14

  2. #2
    Join Date
    Nov 2002
    Posts
    170
    Also the explain plan for both the query are exactly the same. The time for the fetch is more on the same prod database at different times. AT the first guess it looks like it is the IO but how do I track more information on both the sessions.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd run a level 8 or level 12 trace on the session and look at the waits. If most of your waits are for reads/writes, I would look at the I/O subsystem for contention.
    Jeff Hunter

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    I have a level 12 trace and it doesn't give me much other than the explain plan and the following chart.

    I don't see any output regarding the i/o contention etc....Any syntax I am missing ?

    Trace Level
    exec sys.dbms_system.set_ev(&SID,&SERIAL,10046,12,'');

    tkprof
    tkprod infile outfile sys=no explain=user/pass

    trace out
    Explain plan output and
    call Count cpu elapsed disk query curr rows
    ----- ----- --- ------- ------ ----- ---- ----
    Parse 10 0.04 0.02 0 0 0 0
    Execute 6 0.00 0.00 0 0 0 0
    Fetch 35 11.87 250.73 56808 447290 18 35
    ------- ---- ---- ------- ------ ------- ---- ---
    total 51 11.91 250.75 56808 447290 18 35

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    look at the raw trace file for WAIT...
    Jeff Hunter

  6. #6
    Join Date
    Nov 2002
    Posts
    170
    I see a lot of wait for the "db sequential file read" and "sql*Net" but that doesn't really tell me what the exact problem is. Does that mean that the bottleneck is IO. Could it not be that it is doing more disk read than it should have ?? IS there any other way I can get more detail ?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, it tells you a lot.

    Look at the p1 for the sequential waits. That is file# from v$datafile. See if you these files are located on the same physical device.

    A sequential wait is probably an index hit and consequently an LIO. Are the number of LIO's reasonable for this query? (12,779 LIOs/row sounds pretty high for my systems). Does this index need to be rebuilt maybe? Maybe spread on multiple devices?

    Is the optimizer choosing this index incorrectly, perhaps? Maybe your init.ora parameters need to be adjusted?
    Jeff Hunter

  8. #8
    Join Date
    Nov 2002
    Posts
    170
    Look at the p1 for the sequential waits. That is file# from v$datafile. See if you these files are located on the same physical device.
    We are using SAN raw device.

    Is the optimizer choosing this index incorrectly, perhaps?
    The optimizer is picking the correct index everytime. I was infact thinking to rebuild the index before I read all these article which says if you are using LMT then you don't have to ever rebuild indexes or do any fragmentation.


    Sample WAIT's from the RAW trc file
    --------------------------------

    WAIT #2: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
    WAIT #2: nam='SQL*Net more data to client' ela= 0 p1=1413697536 p2=2017 p3=0
    WAIT #2: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
    WAIT #2: nam='file open' ela= 0 p1=0 p2=0 p3=0
    WAIT #2: nam='db file sequential read' ela= 1 p1=53 p2=59549 p3=1
    WAIT #2: nam='db file sequential read' ela= 0 p1=77 p2=198018 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=88 p2=296067 p3=1
    WAIT #2: nam='db file sequential read' ela= 0 p1=15 p2=386946 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=69 p2=273113 p3=1
    WAIT #2: nam='db file sequential read' ela= 0 p1=10 p2=199347 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=5 p2=290216 p3=1
    WAIT #2: nam='db file sequential read' ela= 0 p1=16 p2=183614 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=53 p2=59561 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=84 p2=223476 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=11 p2=341199 p3=1
    WAIT #2: nam='db file sequential read' ela= 1 p1=60 p2=117954 p3=1
    WAIT #2: nam='db file sequential read' ela= 0 p1=111 p2=234460 p3=1

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by dbasupuser
    We are using SAN raw device.
    so? Are files #53,77,88,... on the same stripe?


    The optimizer is picking the correct index everytime. I was infact thinking to rebuild the index before I read all these article which says if you are using LMT then you don't have to ever rebuild indexes or do any fragmentation.
    Depends. Does the index have a lot of holes? Does the index receive a lot of deletes and subsequent inserts?

    To me, this looks either like an inappropriate index being used or an index that needs to be rebuilt.
    Jeff Hunter

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Since, you use Siebel, finally you come and tell us SQL cannot be tuned b/c it is being generated from the appln.

    Post the row source from the tkprof output here.

    Tamil

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