tkprof review fetch/rows

[kburrows]

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

[bazza]

Are you sure you have the right set of stats here? I can't quite see 45 minutes being taken for this particular query.

[kburrows]

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)

[marist89]

I'd trace the whole session with waits (level 8). You might be waiting on the network or Access/ODBC.

[kburrows]

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

[kburrows]

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?

[marist89]

exec sys.dbms_System.set_ev(sid_from_v$session, serial_from_v$session, 10046, 8, '');

[kburrows]

Jeff,

Ok, I'll give this a try.

Thanks,
Kathy

[marist89]

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.