One of the pitfalls to TKPROF is that none of the "sort" options (yet) include wait event timings, except perhaps "elapsed", but still not perfectly.
Please be sure to specify "sort=prsela,exeela,fchela" on the TKPROF command-line for the best results.
But even sorting by "prsela,exeela,fchela" misses a huge chunk of time, usually accounted to "log file sync", that occurs when the transaction commits. Just an anomaly of the aged and band-aided TKPROF program...
I would suggest searching for the phrase "log file sync" in your TKPROF report and finding where your transaction is committing, and seeing if you have huge waits there. Just bear in mind how redo is generated by a session and how it is flushed to the online redo log files and when. Reviewing most of the excellent articles on Steve Adams' website at http://www.ixora.com.au on LGWR and "log file sync" would be useful. In particular, his script "lgwr_waits.sql" (I think that's what it's called) and the "sync wait ratio" might be illuminating...
Quoting VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>:
Folks Why the 13 RE-parses, when using Bind Variables? Cheers ________________________________ Sent: Thursday, June 12, 2008 4:57 PM FolksBenchmarking a Banking Java Application using Oracle thin driver to connect to the Database. OLTP Nature of TransactionsNOTE - Dedicated Server process connections to Database. INSERT INTO SaleBackEnd(SaleBackEnd.ATMPinStatus, ... VALUES ( :1, :2, ... ) Below is the SQL Trace taken under Tran. Load (10046 at Level 12). Qs Is the INSERT facing performance issues? CONFIG:- HP-UX Oracle 10.2 Will share Detailed SQL Trace file, as needed. Cheers & Thanks indeed Vivek P.S. SQL Trace under Load :-call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 13 0.00 0.00 0 0 0 0 Execute 13 0.20 0.57 0 29 184 13 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 26 0.20 0.58 0 29 184 13Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 26 (CRMUSER) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 13 0.00 0.00 SQL*Net message from client 13 0.30 0.35 **************** CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solelyfor the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mailaddress. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS***
-- //www.freelists.org/webpage/oracle-l
