How to Make Things Faster

Chapter 20. The Profile

A profile is a table that summarizes a trace stream by grouping on one or more of its attributes. It was a profile that helped us solve the Payroll problem:

	Duration
Subroutine	seconds	%	Count	Mean
SQL*Net message from client	984.010	50.3%	95,161	0.010 340
SQL*Net more data from client	418.820	21.4%	3,345	0.125 208
db file sequential read	279.340	14.3%	45,084	0.006 196
12 other subroutines	275.300	14.1%	281,727	0.000 977
Total (15)	1,957.470	100.0%	425,317	0.004 602

In this profile, the grouping key is the subroutine name. Since most Oracle subroutine calls are associated with a “cursor” that has a SQL or PL/SQL statement attribute, you can also group Oracle trace data by statement:

	Duration
Statement	seconds	%	Count	Mean
select ASSBAL.defined_bala…	627.900	32.1%	12,794	0.049 078
select ITEM_NAME,ITEM_…	278.500	14.2%	31,779	0.008 764
select fi.formula_name,fi.stic…	128.500	6.6%	12,621	0.010 181
270 other statements	922.570	47.1%	368,123	0.002 506
Total (273)	1,957.470	100.0%	425,317	0.004 602

In the Payroll story, the profile by subroutine was more useful to us than the profile by statement, but in other stories (see Richard’s story later in the book for an example), the profile by statement is more useful. The important thing is that, no matter what you group by, the bottom line total duration—in this case, the 1,957.470 seconds for which we had traced PYUGEN—remains the same.

Of course, you can drill into just ...