Building
Support Software
Benchmark
Concepts and Design
his chapter covers some of the more critical reasons for benchmarking and highlights why this type of testing is so important. Some ideas are then presented as to how you can go about developing a benchmark suite for your own application, including the provision of some useful software tools for speeding up the process.
3.1 Why Develop a Benchmark?
First of all, we should get some definitions straight. This section discusses
benchmarks, but this term is used in a very generic way. There are many reasons for running benchmarks, such as:
· Platform selection/platform changes
Only the last item in this list, and the performance part of capacity planning, could be classed as true benchmarks, because the others do not compare system performance. In order to keep things clear, the term
benchmark will be used when referring to the testing, whereas
simulator and
simulation will be used when referring to the actual test software.
The importance of running benchmarks cannot be overstated. Running Oracle on a UNIX server offers many advantages over mainframe systems, and it is frequently a cost-centric argument that is used to sell the systems. However, the real benefit of this combination comes in the form of rapid evolution. Major releases emerge at intervals of 6 months to 12 months, and the processor and interconnect architectures leapfrog each other every 6 months. With the Oracle tools protecting the application developer from most of the porting effort associated with changing platforms, the price/performance ratio can be progressively lowered during a system's life span. Of course, this actually happens very rarely, because new functionality is always "needed" just as extra headroom is obtained. Either way, the rapid development of software and hardware provides tangible business advantages.
Unfortunately, the rapid change element does not come without occasional problems. Owing to the flexibility of the programming model and the rapid rate of change in the hardware and software, any single system can be exposed to literally millions of different states. Combined with the financial pressures that the vendors are under to get new releases to market, it is not unusual for unexpected problems to slip through their testing programs.
In smaller systems, most software and hardware changes can occur transparently because the demands that the application makes on the system are minimal. Large-scale systems that push the hardware and software in unique and extreme dimensions cannot rely on this kind of plug-and-play approach. In fact, in order to implement any significant changes at all, the target system should be tested as extensively as possible with the real application. This is the only way to eliminate major problems before they occur in a production environment.
Capacity planning is a very ambiguous term and yet actually suits this category quite well. Capacity testing should be considered in the following situations:
· Initial platform sizing
· Increased user count or increased transaction rate
· Proposed application/data changes
The initial sizing of a platform is potentially the most complex, because little information is available to aid the process. Frequently, such sizing is made far more difficult by the fact that the application development has not even been started at the time the systems need to be purchased, and so no information about the operation of the future system is available. Unfortunately, there is no easy solution to this problem, although some pointers on building a reasonably accurate simulation in these circumstances can be found in
Section 3.5.
If the application has already been developed, things are considerably more straightforward. If the application is a well-known package such as Oracle Financials, then it becomes easier still, because most hardware vendors have specialized expertise in these products and extensive experience regarding how they run on their particular platform. The only word of caution here is that most of these packages-particularly financial packages-have to be tailored to the particular business or accounting structure of the company, which can significantly change the operational profile of the application.
Whether the application is brand new or an off-the-shelf package, some kind of testing should be performed as a sanity check for the paper-based sizing exercise. In the case of a new application, extra caution needs to be exercised. It is not unusual for an application "proof of concept" to establish a system sizing that differs by an order of magnitude from the actual requirement.
Increased User Count or Increased Transaction Rate
A change in the size of the user community or an increase in transaction rate is relatively simple compared with the initial sizing exercise. The application profile is well known by this stage, and the footprint of each transaction can be recorded quantitatively-which is a major advantage. However, it should be clear by now that doubling the capacity of the system, for example, does not necessarily allow a doubling of user count.
Increasing the number of users and increasing the transaction rate are both common in an expanding business. Acquisitions can increase user population, and simple increases in business volume resulting from company growth can produce a steady increase in transaction volume. While the former is rarely a surprise, an increase in transaction volume is frequently overlooked and needs to be planned for in advance whenever possible.
The goal of this type of testing is to verify that, for a given release of the application code and a resized system configuration, the new user base or transaction volume will be able to operate within the contracted response times. As long as the right types of metrics are available from both current production and the simulator, this should be a relatively simple exercise. There are basically two metrics that need to be followed:
·
Throughput, a.k.a. transactions per second. Throughput will be altered in either of these situations. The throughput of the system would be expected to go up with the addition of more users. The estimated increase in transaction rate should be ascertained from business data prior to testing, in order to test the system at the correct load.
·
Response time, or user-perceived response time. The response times of the system should not exceed the contracted times.
Knowledge of how the existing system runs is essential for this type of testing. In addition, baseline testing should be run with the simulator. During these tests, the benchmark system should be loaded at the same level as the current production system. In this way, the loading imposed by the simulation software can be compared directly against the production system at an identical transaction load. This is an important test to perform, because benchmark workloads are almost always too clinical in profile when compared with the natural randomness encountered in production. Using the baseline test results, more accurate estimations can be determined for production system utilization.
Proposed Application/Data Changes
Can we run yield analysis concurrently with online users during the day? Can we add application-level auditing? If we add three times more products to the portfolio, does this impact the response time of a transaction? These are the types of questions that are frequently asked during the life cycle of an application. They are fairly significant questions with no easy theoretical answers, and there are severe penalties if they are answered incorrectly.
These questions are most accurately answered using the application simulator, particularly if remote terminal emulation is employed (see
Section 3.6). Once again, it is important to establish a set of baseline results that show how the simulation software ran prior to the change. The benchmark tool can then be executed in the new configuration, and the results of the simulation can be directly compared with those of the baseline test.
Some application changes, such as the integration of a transaction monitor, are too major for this kind of testing. Changes of such proportions should be considered as new developments, because they change the entire profile of the system.
The types of upgrades discussed here are operating system, Oracle, and hardware upgrades. While maintenance releases, such as an upgrade from Oracle 7.3.3.3 to Oracle 7.3.3.4, typically address only known problems, any release that contains feature code should be viewed suspiciously. With the Oracle RDBMS, any change in the first two fields of the version number denotes the implementation of feature code. Occasionally, "minor" feature code is implemented in maintenance releases. This happens most often shortly after a large implementation of new features, because the developers have missed the freeze date for checking in their new feature code.
With the operating system, the risk associated with each release varies from vendor to vendor. Careful attention should be paid to release notes prior to planning an upgrade. The same is true of Oracle releases, to ensure that the right steps have been taken to minimize the risk of upgrade. With hardware, certain upgrades can be performed without testing. Typically, processor and memory upgrades can be implemented with minimal risk. The things to watch are new I/O subsystems, firmware upgrades, and completely new architectures.
The bottom line of all this is that when you are building or maintaining a large-scale database system, you need to be scared of
bugs, because they will be evident in any nonmaintenance upgrade. Don't kid yourself: if you think the new release will go without problems, you are being way too optimistic.
It's a good idea to approach upgrades as follows:
· Don't upgrade until you have to, unless the new release contains fixes of critical outstanding problems that cannot be backported.
· Upgrade one thing at a time. Maximize change control, and increase the chance of keeping at least some of your upgrades in place, should problems occur.
· Bundle testing together. Minimize the time you spend testing releases, and bundle the testing of several products (such as Oracle and O/S) together. As long as you test all the combinations that you will pass through in the upgrade cycle, you should find this a low-risk optimization of your time.
The important point here is to test the new releases. Neither Oracle nor the operating system vendor will have tested the very particular set of dimensions that
your application will test. Therefore, your system is fairly likely to hold the key to exposing unforeseen race conditions, system states, and so on, and it is your responsibility to find such problems before they hurt you in production. With major Oracle upgrades, such as from Oracle7 to Oracle8, there is little opportunity to reverse the process in the event of a major problem after the upgrade, because data changes occur in the database structure that cannot be undone. This makes the testing process more important than ever.
Testing a new platform is a common use of a benchmark. This is a really great way to ascertain the
real potential of a new machine on which you intend to run your system. Although you can study the SPEC numbers for the platform, factor in the latency and bandwidth of the system interconnect, and maybe even take into account the CPI (clocks per instruction) count of the processor, you will probably still achieve, at best, only a ballpark estimate of a new system's potential. Running a quantitative benchmark of the system with the actual application is the only way to ascertain the effectiveness of a new platform. There are too many other variables, not least of which is the scalability of the O/S, that can drastically affect the real capability of the system.
Normally when testing new platforms for a system, you are primarily interested in testing the database server. This, after all, is the component of the system that has to provide the most scalability and is also the most variable in terms of pure workload. Testing of support systems, application servers, and so on is not essential in a platform change benchmark, although it could be argued that such testing is still worthwhile even if only to verify the entire system in operation on the new platform.
3.2 What Are We Simulating?
3.2.1 Defining Application Areas
It is important to have a good understanding of your application when developing a simulator for it. The first part of such an understanding is being able to identify the different types of users accessing the system, how they are using the application, and how many of them are doing so. This is important in order to be able to understand what is going on in the simulation runs and to be able to comment on why things are occurring that may not have been expected.
3.2.2 Defining the Application Split
Once you have a good understanding of the overall application areas, you need to start defining functional areas of the application. In practice, the split is never black and white-the application will, by nature, share data among application areas. That is the whole reason that it accesses a common database in the first place.
However, there will be clear boundaries between the functions defined in the applications, and probably among the day-to-day duties of the users on the system as well. For example, if a hotel had a single system to cater to all of its needs, it would include the following functional areas that can be classified as distinct areas:
· Front desk (guest check-in and check-out operations)
Although there is a strong link among all of these areas, they can be individually classified as different functional areas.
Once the split has been defined, work can begin on each area. Specifically, the process should look a little like the diagram in Figure 3.1.
3.2.3 Careful with Those Assumptions, Eugene
The "Significant Workload?" decision is a difficult one. It is very easy to make a simulated molehill out of a resource mountain, and this could compromise the entire result set of the benchmark. It's important to remember that one query can destroy a system, so although only a handful of users may be performing this type of transaction, make sure that the function they perform is definitely insignificant before discounting them from the simulation.
In addition, when an application has functional areas that overlap, it is sometimes necessary to build some types of transactions simply to feed other transactions with dependent data. In this case, either the transaction needs to be implemented or the data needs to be prebuilt into the database (see Section 3.6).
Once all the significant application components have been defined, scenarios need to be described for each transaction type. For some transactions, there will be only one way of completing the transaction, and so this will equate to a single scenario for that transaction type. More typically, there will be potentially hundreds of scenarios that could be defined.
In our hotel example, a hotel guest could have a reservation, have no reservation, have any number of frequent flyer cards, and/or select one of many different types of rooms. In such situations, it is not practical to build scenarios for all possible combinations, owing in no small part to the difficulty of providing the data required to execute them. A realistic sample of the scenarios should be adopted in this situation.
The final pieces to be created are the actual simulation and the data required to run it. These pieces will be covered in the next section.
3.3 Selecting a Development Approach
It is important to choose the most suitable development approach for your application and your environment. There are two approaches for building benchmark software:
1. Remote Terminal Emulation (RTE) software
2. Completely custom development
Each of these approaches has its merits, not only in terms of cost but also in terms of useability in different scenarios.
3.3.1 Using Remote Terminal Emulation Software
RTE software is a specialized piece of commercial software such as Performix or preVue from Rational Software. Good results can also be obtained using Expect, a public domain utility
1 that provides many of the same features but uses an interpreter instead of compiled code.
There are several different approaches that can be taken to simulate the load on the database server:
· Dumb terminal emulation
· Client/server network protocol
· HTTP (Hypertext Transfer Protocol) Web simulation
The common attribute of these approaches is that they all simulate real users accessing the system. The correct tool to use depends on your application architecture. Dumb terminal emulation works very well for simulating the use of a character-based application, regardless of how it was written or what happens at the back end of the application. The client/server emulation traps the network communication between the client and the server, and subsequently simulates the load in this way, including three-tier architectures. The HTTP emulation simulates hits on a Web server, which in turn accesses the database.
This type of software has the following advantages over writing your own simulation of the application:
· Rapid development of simulation scripts owing to supplied tools
· Ability to simulate application servers as well as database server
· Very accurate simulation of load on database server
· Less susceptibility to application code changes when compared to custom simulators
The only disadvantage of RTE software, in fact, is that it costs money.
One thing to bear in mind when looking at RTE software is that it presents the same problem of data generation that you would face in a custom simulation suite. None of the available products has the capability of knowing how your application needs to be driven. Ask your vendor about what kind of infrastructure they provide for the type of data generation you will need to be doing.
3.3.2 Custom Simulation Development
The home-grown approach to simulators involves building the code from scratch, using the Oracle Call Interface (OCI) to gain the most control available over what is executed on the database server. This approach has the following advantages over the RTE approach:
· Does not need a completed application to run
This approach also has several disadvantages:
· Much longer development time
· Needs redeveloping every time the application changes
· Does not test application servers
· Not as accurate as RTE simulations
Despite the certain disadvantages of developing the simulation from scratch, there are occasions where it still makes more sense. If the application does not yet exist, for example, it is the only choice. Also, if the simulation does not have many scenarios or much complexity, it may be more cost-effective to develop it without the aid of RTE software. For these reasons, this type of development cannot be discounted and will be covered in this chapter.
3.4 Building a Simulator Using RTE Software
An important part of the value of using RTE software is the availability of a variety of tools to assist in the process. These tools include
· Transaction capture tools
· Generalized programming interface
· Statistical analysis tools
These tools provide a fast path to a simplistic simulation of your application. In a matter of minutes, a simple simulation of your application can be running and the results analyzed.
The single most important attribute of RTE software, however, is that it simulates the real user, utilizing the same kind of input as the actual production workload. Sometimes the importance of this attribute is not apparent.
Using, for example, Pro*C, which has long been the favorite for benchmark applications, the database server is (mostly) communicated with at a SQL level. This allows SQL to be submitted and answers to be retrieved. Although this simplistic approach seems good in theory, in practice more control is required. SQL*Forms, for example, communicates with the database at the UPI layer a lower lever, Oracle-proprietor communication protocol. This allows more control over the opening and closing of cursors, the steps of execution, and subsequently the way that memory is allocated on the server.
The consequence of this is that a Pro*C emulation of the application will not produce the same work profile of the server as the real application, even if the statements are issued in the same order and with the same bind variable assignments. RTE software provides true emulation of the application's usage of the database server, complete with all its horors and triumphs.
Although RTE software provides an accurate way of simulating the application, it provides no benefit from a data perspective. It is most common for an application to be expecting certain data from the user. This may be as simple as a name, a reference number, or a date, but input is expected nonetheless. During normal use of the application this does not present a problem, because the user of the system is presented with this data from some source such as a customer in the store or paperwork on the desktop. In a simulated environment, this data is not available through these means, and so it needs to be generated in some way. This is the problem that is common to both methods of simulation.
3.5 Building a Custom Benchmark Suite
There are two common starting points in building an application simulator from scratch: situations in which the application (a) has been developed and (b) has not been developed.
3.5.1 Programming Environment
A variety of programming tools are available for use in developing a custom benchmark suite. While these tools have various merits of their own, it is recommended that the Oracle Call Interface (OCI) be used for this type of development. OCI is recommended for any programming exercise in which simple but absolute control is needed over actions requested of the RDBMS, because it is the closest thing to the explicit server instruction set in the UPI interface. OCI has a widespread reputation for being "hard," but this is really not the case. There are two things that make using OCI "hard" in comparison with standard C programming:
1. You need to know the steps involved in SQL processing from the server perspective.
2. You need massive parameter lists for each function that are mostly unused in C.
The truth is, you need to know the steps of SQL processing anyway in order to support a large system, and the parameters list problem can be easily fixed using macro wrappers for each OCI call used to eliminate redundant parameters. Once these obstacles are out of the way, OCI is in many ways more straightforward than Pro*C, because no precompilation is necessary.
3.5.2 When the Application Has Not Been Written
In order to reduce the degree of error in building initial simulations before the application has been developed, it is important to learn from some painfully gained experience. Most of this falls squarely in the "common sense" category, but it is surprising how much of it is frequently missed. When the application has not been written, the use of RTE software is clearly out of the question, and so a call-centric approach needs to be taken instead of an application-centric one.
First of all, it is important to get as close to the final transaction profile as possible, and when the application is not written this can be difficult. However, certain pieces of the application environment must be at least partially complete before it is worth conducting a preliminary benchmark. The specific things to look for are as follows:
·
Final data model. Unless the data model is virtually complete, the simulation will not be accurate. It does not need to be polished, but the basic structure of the model should be final. A classic stumbling block in this area would be where the data model becomes more normalized than the simulation, thus adding a good deal of complexity to several of the queries in the application. This could result in an undersized system.
·
Query profile. How many queries are executed within each transaction, and what do they look like (primary key single-row fetches, index-driven range scans, etc.)? Don't neglect "list-of-values" processing either, which can very quickly become a major part of the system.
·
Insert/update/delete profile. Which tables do we insert/update/delete, and how many rows are processed each time? Where do these activities occur in relation to the query workload and the commits/rollbacks?
·
Stored procedures. To what extent is application logic handled with database stored procedures? This is really hard to simulate adequately, because it is likely that you will be several months (or years) of development time away from knowing the answer to this question. It is a bad thing to have significant processing within stored procedures, in my opinion, because the primary goal of a stored procedure should be to reduce network round trips between the client and the server by bundling multiple SQL interactions into a single SQL*Net call. In fact, as your application is not written yet, you still have a chance to try to make sure that stored procedures are used for the right reasons.
3.5.3 If the Application Exists: Trap and Emulate All SQL Calls
When the application is in some kind of executable state, it is possible to capture all the calls to the database and replay them in a simulated environment during the benchmark. This is more straightforward in a two-tier environment, where there is a one-to-one relationship between the requests made through the application and the process executing the SQL against the database. In a three-tier environment, there is more complexity, but the same principles apply.
The starting point is to find a person who really knows the application and how it is used in reality. The perfect person to do this is a real-life user of the application, who can show you ways of operating the application that you, the developer, or the instructor could never imagine. This is as close to reality as you can get. If this is not an option that is available to you, then an expert developer is your only option, although the simulation will then take a more clinical, how-it-should-work type of model: try to make sure that you find some way to inject the "wild-card" factor into it.
For each application area that is defined, define the types of transactions that can be performed within it, and run through each of these screens while tracing the calls made by the session, as detailed in the next subsection. It's important to include the following actions when building the list of tasks performed by the system:
When running through each of the screens, it is very beneficial to use recognizable values for the fields that will need to be generated by the simulation, rather than using the explicit values used in the walkthrough. Values such as `
RND3HERE','
GEN2HERE' and `
12345','
99999' generally work well and can dramatically speed up later steps.
Tracing Sessions by Setting Events
Most people know of the "
alter session set sql_trace=true" technique for tracing the calls made from an application. This is a useful technique, but not as useful as it could be, because it does not contain the bind variable information for each cursor.
In addition to this well-documented approach is an undocumented method of tracing the activity of a session, a considerably more useful one for this type of activity. While useful for determining database access for use in a simulator, this method of tracing also represents one of the most powerful tools available for diagnosis of performance problems as they happen. For this reason, a full user's guide is required for this function, and so we will invest some book space accordingly.
The method for activating the improved tracing is the use of a database
event. An event is a signal to a database session that alters its behavior according to the definition of the event. The definition of the event is determined within the kernel code of the Oracle RDBMS and is not documented beyond brief descriptions in the message file. The message file is the same file that Oracle uses to look up NLS-specific error messages, such as "
max # extents (50) reached in table SCHEMA.TABLE" and, in the case of English language systems, this file is
?/rdbms/mesg/oraus.msg. Embedded in this file are the numbers and descriptions of the events that are defined within that particular Oracle release.
A word of caution, however: many (most) of these events are not for public consumption. For example, event 10066 simulates a file verification error:
10066, 00000, "simulate failure to verify file"
// *Action: level is file number to fail verification
|
These types of events are used internally to Oracle for the simulation of problems. For this reason, it is wise to stick to events that are known to be safe.
The event that we are interested in is 10046:
10046, 00000, "enable SQL statement timing"
|
This event is set with the following syntax:
10046 trace name context forever, level <x>
|
The level, or degree, of the tracing is set using numbers 1 through 15 in place of the
x. The level number corresponds to the setting of particular bits within the range 1 through 12 (
Table 3.1).
For this event, only bits 3 and 4 are used. With the event enabled for any level, the output includes the same attributes as
sql_trace=true. When bits 3 and 4 are set, additional information is returned that is extremely useful. Try using level 4 for just bind variables, level 8 for just wait states, and level 12 for all of the above.
The event can be set at either the
instance level or the
session level, and the event can be set by any one of three distinct methods:
1.
alter session set events "10046 trace name context forever, level x" (personal user session level)
2.
event="10046 trace name context forever, level x" in the
init.ora (instance level)
3. On a process-by-process basis using
svrmgrl oradebug (other user session level)
Method (
1) is the most straightforward method of setting the event on the correct session, but it is useful only if this call can be made by the application session as and when required. It involves the connected session issuing a SQL call, as detailed above, to set the event on itself.
Method (2) is useful when performing tracing en masse, because it operates at the instance level, forcing all sessions to set this event at start-up. This is the most appropriate method to use when performing application capture, because it eliminates the manual activation steps. It requires the event to be set in the
init.ora so that all sessions are affected by the change.
Method (
3) is used to set the event on any process from another session. This method is most useful for reactive problem diagnosis, because it can be set on any user session that is connected to the database and shows a consistent view of the user state, including calls and wait states. A further digression may be useful at this stage to cover the use of the "
svrmgrl oradebug" functionality that is available in releases 7.3 and above.
The
svrmgrl product that is shipped with the Oracle RDBMS contains a set of functions that were previously available only through a tool called
oradbx.
Oradbx was a binary that was shipped with prior releases of Oracle and needed to be explicitly built using the supplied makefile. This binary was not created as part of any standard installation . For this reason,
oradbx was not widely known in the Oracle community.
Now that the functionality is provided as part of
svrmgrl, it is available on every Oracle system without any manual intervention. The commands are accessed using the
oradebug prefix, as demonstrated below, to gain help on the facility:
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to debug
DUMP <dump_name> <level> Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA variable
SETVAR <p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
FLUSH Flush pending writes to tracefile
TRACEFILE_NAME Get name of tracefile
LKDEBUG Invoke lock manager debugger
CORE Dump core without crashing process
UNLIMIT Unlimit the size of the tracefile
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
|
Once again, this is a facility that should be used with extreme caution: the database can be corrupted beyond all repair if some of these commands-most notably POKE, SETVAR, and CALL-are used. In addition, requesting one of the background processes to perform an extensive dump will effectively suspend the real function of that process for that period, possibly hanging the database.
The commands in which we are most interested at this stage are
SETOSPID should be the first command issued, as it identifies the Oracle process (UNIX process) on which the tool should operate. This information is available from
V$PROCESS, joined to
V$SESSION on the
PADDR column. Next, the
UNLIMIT TRACE directive should be used to remove the limits set within the
init.ora for restricting the size of a tracefile. This makes sense, because you will manually turn off the trace when you have all the required information. Finally, the event can be set, as detailed above. From this point forward, a tracefile will be created in
user_dump_dest, of the form
ora_PID.trc. To be sure of locating the correct file, the
TRACEFILE_NAME directive can be used om
oradebug.
Whichever method is most appropriate for tracing, it can be turned off again on the session level by using the following command:
10046 trace name context off
|
Using this event provides the following advantages over the
sql_trace=true method:
· It can be set for a running session without the user being aware of it, using the
svrmgrl oradebug facility.
· It shows wait states that the execution of each statement goes through.
· It details binding information for each cursor.
The bind information can be used in generating the simulation code, and the ward states can be used in the tuning of application SQL.
Here's a sample piece of output from a 10046-generated tracefile, using level 12 to get all available information:
PARSING IN CURSOR #1 len=50 dep=0 uid=20 oct=3 lid=20 tim=3802520068 hv=650761597 ad='c12dc1f0'
select count(*) from some_table where some_col=:b1
PARSE #1:c=8,e=10,p=2,cr=29,cu=1,mis=1,r=0,dep=0,og=0,tim=3802520068
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03
bfp=400e7700 bln=22 avl=02 flg=05
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3802520068
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file scattered read' ela= 1 p1=1 p2=4752 p3=5
WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=4882 p3=2
FETCH #1:c=1,e=1,p=7,cr=7,cu=3,mis=0,r=1,dep=0,og=4,tim=3802520069
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=3802520069
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
STAT #0 id=1 cnt=0 pid=0 pos=0 obj=0 op='SORT AGGREGATE '
STAT #0 id=2 cnt=414 pid=1 pos=1 obj=0 op='FILTER '
STAT #0 id=3 cnt=414 pid=2 pos=1 obj=1876 op='TABLE ACCESS FULL SOME_TABLE'
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0
|
In this tracefile extract, there are examples of all of the attributes found in this type of trace. For this trace,
timed_statistics was set
true before the trace was initiated. Starting at the top of the trace, the "
PARSING IN CURSOR" section shows the parsing of a new cursor and the assignment of the "#1" cursor number to it. The tokens listed in Table 3.2 describe information about the cursor and its context.
This section is immediately followed by the actual statement itself, which is terminated by the "
END OF STMT" declaration. Immediately below this are the statistics for the
PARSE call itself. This is the format taken for all subsequent tokens: <ACTION> <CURSOR#>: <data>. In this case, the
PARSE directive reports back with the standard information used by
tkprof as shown in Table 3.3.
The
tkprof output for this statement would look like this:
********************************************************************************
some_table where some_col=:b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.10 2 29 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 7 7 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.09 0.11 9 36 4 1
Misses in library cache during parse: 1
|
By reading through the output in the raw tracefile, it can be seen how these standard statistics are used by
tkprof. The count column equates to a count of the number of calls made during the lifetime of this statement. In this example, two fetch calls were m
