tkprof review fetch/rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: tkprof review fetch/rows

  1. #1
    Join Date
    Nov 2000
    Posts
    173

    tkprof review fetch/rows

    Hi,

    I have a user complaining about queries slowing down. This is an Access front-end and Oracle back-end. So, I ran tkprof and don't see anything that would cause drastic query to run 45 min.

    I did notice that the fetch seems to return about 11 rows per trip for a lot of the queries. Do you think this is could be a problem?

    Here is a few of my ouput.

    call count cpu elapsed disk query current rows
    ---- --- ----- ----- ------ -------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 919 0.05 0.04 19 940 4 10108
    ----- ------ -------- ------ ---------- ---------- ----------
    Total 921 0.05 0.04 19 940 4 10108

    Misses in library cache during parse:1
    Optimizer goal: CHOOSE
    Rows Row Source Operation
    ------- ---------------------------------------------------
    10108 INDEX FAST FULL SCAN (object id 4097)

    Thanks,
    Kathy

  2. #2
    Join Date
    Jul 2002
    Posts
    335
    Are you sure you have the right set of stats here? I can't quite see 45 minutes being taken for this particular query.

  3. #3
    Join Date
    Nov 2000
    Posts
    173
    Well, I traced what the user was doing (filling in a form). It returned about 57 queries total. This was just an example. Most are very similar to this one. I don't really see anything that stand out. I noticed the fetch returns 11 rows per fetch and I was wondering is this a normal amount. Why shouldn't you fetch the entire result in one trip?

    The only other one I have questions about is this one. I noticed parse,execute,fetch are all the same. Plus, this is only a select stmt why the execute, should it just be a fetch for a select?

    SELECT "QCFLOWHEADERID" ,"QCFLOWDESCRIP"
    FROM
    ("OWNER"."TBL_QCFLOWHEADER"."QCFLOWHEADERID" = :1 ) )

    call count cpu elapsed disk query current rows
    ---- --- ------ ----- ---------- ---------- ---------- ----------
    Parse 4 0.00 0.00 0 0 0 0
    Execute 4 0.00 0.00 0 0 0 0
    Fetch 4 0.00 0.00 0 4 0 8
    ------- ------ ---- ------ ----- ---------- ---------- ---------
    total 12 0.00 0.00 0 4 0 8

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 NESTED LOOPS
    1 INDEX UNIQUE SCAN (object id 4086)
    0 TABLE ACCESS BY INDEX ROWID TBL_QCFLOWDETAIL
    0 INDEX RANGE SCAN (object id 4081)

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd trace the whole session with waits (level 8). You might be waiting on the network or Access/ODBC.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Posts
    173
    Below is the total of my tkprof for all the queries.

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ ---- ------- ------ ------- --------- ----------
    Parse 57 0.04 0.07 0 0 0 0
    Execute 537 0.01 0.01 0 0 0 0
    Fetch 2707 0.34 0.31 110 2601 144 24056
    ------- ------ ---- ------- ------ ------- ---------- ----------
    total 3301 0.39 0.39 110 2601 144 24056

    Misses in library cache during parse: 38

  6. #6
    Join Date
    Nov 2000
    Posts
    173
    How can I do that?

    I normally put a trigger, set event 10046, but I can't with this one. All the users login as the same userid. Thus, I cannot start the trace until he logs in so I can get the sid, serial#. I have been using sys.dbms_system.set_sql_trace_in_session. I also cannot have the user alter the session because he is using an application.

    I was kind of leaning towards a networking issue. But how to prove that?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    exec sys.dbms_System.set_ev(sid_from_v$session, serial_from_v$session, 10046, 8, '');
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Nov 2000
    Posts
    173
    Jeff,

    Ok, I'll give this a try.

    Thanks,
    Kathy

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The waits will especially be interesting. If you are waiting on SQL*Net from/to client, then that means you are waiting on the network. Also, if you are on 9.2, your tkprof will list the top wait events and how much time you waited on them.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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