Performance still bad.

From: abaumhau@xxxxxxxxxx (Andy Baumhauer)
Date: Sat, 15 Sep 2007 11:53:07 -0400

Attached is output from a utility called snapper, being run against the Session that is basically at a complete standstill. Any ideas from this report where the problem lies?

SQL> @snapper.sql out 1 30 202

-- Session Snapper v1.03 by Tanel Poder ( http://www.tanelpoder.com )

--------------------------------------------------------------------------------------------------------------------------------------------
-- SID, SNAPSHOT START , SECONDS , TYPE, STATISTIC , DELTA, D/SEC, HDELTA, HD/SEC
--------------------------------------------------------------------------------------------------------------------------------------------
DATA, 202, 20070915 05:42:23, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 1
DATA, 202, 20070915 05:42:26, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 4
DATA, 202, 20070915 05:42:29, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 7
DATA, 202, 20070915 05:42:32, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 10
DATA, 202, 20070915 05:42:35, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 13
DATA, 202, 20070915 05:42:36, 1, STAT, opened cursors cumulative , 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, user calls , 18, 18, 18 18
DATA, 202, 20070915 05:42:36, 1, STAT, session logical reads , 164, 164, 164 164
DATA, 202, 20070915 05:42:36, 1, STAT, enqueue requests , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, enqueue releases , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, db block gets , 153, 153, 153 153
DATA, 202, 20070915 05:42:36, 1, STAT, db block gets from cache , 153, 153, 153 153
DATA, 202, 20070915 05:42:36, 1, STAT, consistent gets , 11, 11, 11 11
DATA, 202, 20070915 05:42:36, 1, STAT, consistent gets from cache , 11, 11, 11 11
DATA, 202, 20070915 05:42:36, 1, STAT, consistent gets - examination , 4, 4, 4 4
DATA, 202, 20070915 05:42:36, 1, STAT, db block changes , 119, 119, 119 119
DATA, 202, 20070915 05:42:36, 1, STAT, free buffer requested , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, commit cleanouts , 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, commit cleanouts successfully completed , 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, calls to kcmgcs , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, calls to kcmgas , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, calls to get snapshot scn: kcmgss , 8, 8, 8 8
DATA, 202, 20070915 05:42:36, 1, STAT, redo entries , 61, 61, 61 61
DATA, 202, 20070915 05:42:36, 1, STAT, redo size , 36012, 36012, 36.01k 36.01k
DATA, 202, 20070915 05:42:36, 1, STAT, redo ordering marks , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, undo change vector size , 13720, 13720, 13.72k 13.72k
DATA, 202, 20070915 05:42:36, 1, STAT, immediate (CURRENT) block cleanout appli, 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, deferred (CURRENT) block cleanout applic, 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, commit txn count during cleanout , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, active txn count during cleanout , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, cleanout - number of ktugct calls , 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, leaf node splits , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, heap block compress , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, session cursor cache hits , 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, parse count (total) , 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, execute count , 12, 12, 12 12
DATA, 202, 20070915 05:42:36, 1, STAT, bytes sent via SQL*Net to client , 10438540, 10438540, 10.44M 10.44M
DATA, 202, 20070915 05:42:36, 1, STAT, bytes received via SQL*Net from client , 20823, 20823, 20.82k 20.82k
DATA, 202, 20070915 05:42:36, 1, STAT, SQL*Net roundtrips to/from client , 26, 26, 26 26
DATA, 202, 20070915 05:42:36, 1, TIME, repeated bind elapsed time , 30, 30, 30us 30us
DATA, 202, 20070915 05:42:36, 1, TIME, parse time elapsed , 87, 87, 87us 87us
DATA, 202, 20070915 05:42:36, 1, TIME, DB CPU , 2086, 2086, 2.09ms 2.09ms
DATA, 202, 20070915 05:42:36, 1, TIME, sql execute elapsed time , 1794, 1794, 1.79ms 1.79ms
DATA, 202, 20070915 05:42:36, 1, TIME, DB time , 2086, 2086, 2.09ms 2.09ms
DATA, 202, 20070915 05:42:36, 1, WAIT, virtual circuit status , -707825, -707825, -707.83ms -707.83ms
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net message to client , 26, 26, 26us 26us
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net message from client , 16, 16, 16us 16us
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net more data from client , 29292216, 29292216, 29.29s 29.29s
-- End of snap 14
DATA, 202, 20070915 05:42:38, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 16
DATA, 202, 20070915 05:42:41, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 19
DATA, 202, 20070915 05:42:44, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 22
DATA, 202, 20070915 05:42:47, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 25
DATA, 202, 20070915 05:42:50, 1, WAIT, virtual circuit status , 3000000, 3000000, 3s 3s
-- End of snap 28

PL/SQL procedure successfully completed.

.

Prev by Date: Re: Extremely poor execute performance
Next by Date: Re: DBI::ProfileDumper::Apache and mod_perl 2
Previous by thread: Extremely poor execute performance
Next by thread: Re: Oracle Performance still bad.
Index(es):
- Date
- Thread

Relevant Pages

Re: What did Perierra say on NFL Network?
... A play from Atlanta/Houstone ... I hadn't seen it before this session, ... Only if they snap it before the players stop and reset. ...
(alt.sports.football.pro.ne-patriots)
Whoever reach crudely if Najems ballot isnt decent.
... You won't snap me coming except your alleged treasury. ... Do not root the baskets thoughtfully, report them here. ...
(sci.crypt)
Re: NoData Event on Report - evaluating
... varying parameters (using the Where Clause in the code). ... user can go to the server to see the snap view). ... but .the report still tries to run for the snapshot version. ... Case 1 ' POs for One Day - One Merchant ...
(comp.databases.ms-access)
Re: can anyone help me tune the database
... Snap Id Snap Time Sessions Curs/Sess Comment ... -> ordered by wait time desc, waits desc (idle events last) ... The report covers 20.58 minutes. ... seconds waiting for the client to send another request to the server. ...
(comp.databases.oracle.server)
Re: NoData Event on Report - evaluating
... varying parameters (using the Where Clause in the code). ... I preview the report, send it to snap, then close the preview (the ...
(comp.databases.ms-access)