Database Reference
In-Depth Information
Again, the contribution of the wait event latch: cache buffers chains is negligible. The problem
is elsewhere: 51% of the response time is due to SQL*Net message from client , the most signifi-
cant contributor to overall response time. Look at the columns “Count” and “Rows” of the
statistics for the INSERT statement. The figures are identical. Furthermore the number of execu-
tions is almost identical to the number of network round-trips ( SQL*Net message from client ).
This means that the application does single row inserts, i.e., one network round-trip per INSERT
statement executed. This observation is confirmed by looking at the row count r of EXEC entries
in the trace file. These consistently have the value 1.
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=347983945867
EXEC #3:c=0,e=79,p=0,cr=0,cu=2,mis=0,r=1,dep=0,og=1,tim=347983945892
WAIT #3: nam='SQL*Net message from client' ela= 109 driver id=1413697536
#bytes=1 p3=0 obj#=-1 tim=347983946105
A major reduction in response time could be achieved by recoding the application to use
array inserts. I caution you against following advice on the Internet to search for latch conten-
tion by accessing X$ tables. Such advice may lead you on the road to compulsive tuning disorder,
rather than paving the way to a solution for performance problems. Always use a response time-
based approach. Never drill down to some perceived anomalies or ratios that you deem to be
too high unless you have convincing evidence that these contribute significantly to response
time. Several years ago, I had the opportunity to talk to the renowned neuroscientist Jaak Pank-
sepp. To this date, I remember him asking, “What data do you have in support of this claim?”
More often than not, unsubstantiated assertions may be dismissed by asking this question.
Instead of prematurely attributing symptoms to causes and haphazardly implementing hypo-
thetical solutions, we as DBAs would be better advised to adopt scientific approaches
resembling those used by the medical community.
Source Code Depot
Table 10-2 lists this chapter's source files and their functionality.
Table 10-2. X$BH Source Code Depot
File Name
Functionality
latch_vs_blocks.sql
Retrieves database objects protected by a child latch.
 
Search WWH ::




Custom Search