Tuning Oracle Net - Expert Oracle RAC Performance Diagnostics and Tuning - page 343

Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: PRDDB_1_ora_24536_MVEGCDEV_1_Mar30_0321.trc
Analyzing PRDDB_1_ora_24536_MVEGCDEV_1_Mar30_0321.trc
To monitor progress, login as TRCANLZR into another session and execute:
SQL> SELECT * FROM trca$_log_v;
... analyzing trace(s) ...
Trace Analyzer completed.
Review first trcanlzr_error.log file for possible fatal errors.
Review next trca_e77025.log for parsing messages and totals.
Copying now generated files into local directory
TKPROF: Release 11.2.0.2.0 - Development on Thu Mar 31 11:42:14 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
adding: trca_e77025.html
File trca_e77025.zip has been created
TRCANLZR completed.
Step 3(d)
From the zip file generated in the previous step, extract the contents and review the trace analyzer report using a
browser. Figure 11-3 illustrates a snapshot of the report and the high-level details from the query execution with the
array size of 15 (default).
In Figure 11-3 , the query takes a significant amount of time to complete the request, the total response time
(Response Time Summary section) is 17,308 seconds, and the number of calls to the database to physically get the
rows indicated by the “Fetch, call count” is also high. The database experienced high waits on SQL Net message from
and to client, indicating that although the database itself was idle, it could not transmit the data to the client due to
limitations in the client configuration to receive these data transfers.
Step 3(e)
To check and validate the appropriate array size that would help improve the overall response time, test with various
array sizes. Check the network traffic during this analysis and ensure setting the array size to a higher number, for
example, 200:
SET ARRAYSIZE 200
SHOW ARRAYSIZE
arraysize 200