RE: INSERT Performance in Benchmark ? ... Basic Qs

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_at_infosys.com>:

> Folks
>
> Why the 13 RE-parses, when using Bind Variables?
>
> Cheers
>
> ________________________________
> Sent: Thursday, June 12, 2008 4:57 PM
> Folks
>
> Benchmarking a Banking Java Application using Oracle thin driver to
> connect to the Database. OLTP Nature of Transactions
> NOTE - 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
> 13
>
> Misses 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 solely
> for 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-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>
>

--
http://www.freelists.org/webpage/oracle-l