Sometimes we need to do a trace analysis of the specific execution of SQL execution, especially when application performance is optimized. Oracle two tools can help us do performance analysis, one is Sql_trace, the other is session_event. Sql_trace tracks SQL execution, such as parsing time, execution time, query time, and so on, session_event to track wait events during execution, such as waiting for client response time, waiting for local disk IO events, and so on. You must use DBA authority to turn on Oracle auto-tracking. To open sql_trace using jdbc:Conn.preparestatement ("Alter SESSION SET sql_trace=true"); PS. Execute ();To open session_event using jdbc: Open: PS=conn.preparestatement ( "ALTER SESSION SET EVENTS ' 10046 trace Name" context Forever, level "); PS. Execute ();Shut down:Ps=conn.preparestatement ("ALTER SESSION SET EVENTS ' 10046 trace name context off '"); (Perform a close trace before closing the connection) PS. Execute ();
Here is the trace file that turns on these two traces:
Oracle Execution
Process analysis:
- During the execution of SQL, the main execution process is spent in the fetch phase of Oracle
- The number of calls to the OCI for fetch is 146 times, time is 50.49 seconds, and the number of query bars is 145,259.
Wait event analysis during Oracle execution:
- Oracle Server waits 0 when sending to client, indicating server-side network transmission is not a problem
- Oracle server waits 5.92 seconds for client response, which means it takes time for the client to process data and network transmission.
- Oracle Server disk IO time is 48.37 seconds, indicating that the primary time is spent on Oracle reading data blocks from disk
- It takes 0.12 seconds for the data to be sent to the client in batches, and this time is acceptable.
