CH09.html

This chapter demonstrates techniques for gaining maximum scalability from a single system image. This should be considered an essential prerequisite before considering Parallel Server. If the system does not perform and scale adequately as a single entity, attempting to accept Parallel Server will provide even worse scalability.

The application design is the single most efficient area to optimize. Unfortunately, database engineers are often left to tune the database into efficient operation without input into the application. This is like trying to tune a lawn mower into a racing car. If the fundamental design of the application is flawed, little can be done on the server side to make the application scale and perform adequately. This is why the first portion of this chapter is dedicated to things that the database server engineer can do to point the application developers in the right direction.

When an application is being designed for a large user population, it is important that the system be built to provide maximum concurrency. In order to satisfy this requirement, the application developer needs to develop the application using many of the same rules employed by the developer of an operating system. One of the most vital things that the application developer needs to do is to eliminate data-based contention in the application.

Data-based contention occurs when many users of the application are competing to write to the same physical records. Oracle must maintain the data integrity of the database, and so locking is utilized to prevent concurrent writes to the same piece of data.

Oracle employs row-level locking as the default locking mechanism for maintaining data integrity. This means essentially that when a write is to occur to a particular row in the database, the database automatically locks only that row. The rest of the rows in the table, and indeed in the data block, will be accessible to other sessions for read and write. This is a major plus for the database engineer, because it means that false lock conflicts do not occur in Oracle. However, the system is still not immune to problems if locks are allowed to persist, and this should be carefully explained to the application developer. In an order-processing system, for example, the application might be designed to work as follows.

To ensure that an order is taken in its entirety, without encountering out-of-stock problems at commit time, the designer of the application decides that the inventory required for the order should be decremented as the order is taken. The entire order can then be committed as a unit at the end of the order, in the safe knowledge that all the inventory is available.

This design would work fine as a single-user system, but this really defeats the object. The problem with this design is that the first user to take an order for a particular item will hold a lock on the inventory for that item, and until the order is complete, this lock will be held and no other users will be able to use this item in their orders.

There are at least two more scalable ways to implement this system. The first way involves not doing anything until the very end of the transaction, leaving all locking operations until right before the commit. This way, locks are held only for short periods, and so lock collisions are prevented. Unfortunately, this design is also defective, because this design can easily result in deadlocks (more on this later in this section).

The second option is to update and commit each line item as it is entered. This also holds locks for very short periods but is not subject to deadlock because only one row is locked at any given time. However, if the session is terminated halfway through the order entry process, the inventory is corrupted because the stock is decremented without the order being completed. With a small change, this design can be made to work.

If the application creates a new order record at the start of the order, immediately followed by a commit, then there is a master record for all work that follows. When the line item is updated to reflect the decrement, a record can be inserted into an ORDER_HISTORY table at the same time, referencing the order number. As an INSERT cannot be blocked by other locks, this cannot result in a deadlock, and the order is committed as it goes along. If the session is terminated, the entry process can be continued at a later stage by querying the order record on screen and continuing the order.

With this design, all locks are held very briefly (before being committed) and yet the atomicity of the transaction is maintained. In addition, the transaction is completely restartable.

Restartability is very important in building any large system, for both online processing and batch processing. In the case of online processing, things must be restartable, or at least recoverable from a business transaction perspective, in order to lessen the impact of the inevitable system or network failures. In the case of the order entry application, it would not be acceptable for the stock to be unaccounted for in the event of a failure, nor is it practical to have developers manually patch the data after every such incident.

In the case of batch processing, all batch jobs need to be written under the assumption that they will be killed off part way through. This sounds severe, but batch processing has a nasty habit of interfering with the response time of online sessions, and it is likely that there will be occasions of batch overrun that necessitate the killing of such jobs. If the job is not restartable, it will need to be started from the beginning the next evening, and will be even less likely to clear the backlog plus the new transactions.

In the first design presented, the design was deemed to be prone to deadlocks. Deadlocks present a real problem in database systems, and thus systems should be designed around them as much as possible. In the example, we could have two order-processing users entering the following order:


User 1 User 2 1 x Foamy Soap 6 x Green Sponges 3 x Green Sponges 3 x Foamy Soap

If both of these sessions were to attempt their commits simultaneously, each would manage to update and lock the first line item in its order. When each session got to its next line item, the other user would already have this record locked, and a deadlock would be evident. There would be no way out of this situation, other than for one of the sessions to rollback and reattempt the operation after a backoff period. Oracle would intervene at this point and carry this out against one of the users, at which point an error would be reported to the session: ORA-00060: deadlock detected while waiting for resource.

The creation of a nonblocking application is the responsibility of the application designer, not the database engineer, and especially not the user. After all, if a user is able to provoke deadlocks and general lock contention, then the user will do so all day long.

Many texts refer to efficient SQL, but the real goal in building very large systems is to produce scalable SQL. The term "scalable" can be interpreted in many different ways when referring to SQL statements, and we will cover each in turn.

Inefficient SQL is bad for everyone. It not only causes poor response times for the user that submits the call, but also affects all other users as a result of one or more of the following:

SQL statements can be classified as inefficient for a variety of different reasons, the impact of which is dependent on the particular blend of inefficiency that the statements provoke. For example, a full table scan requires a great deal of CPU in addition to the disk bandwidth it consumes; a long index range scan pollutes the buffer cache and causes a great deal of scattered I/O.

Most new applications should now be using the cost-based optimizer (CBO), which is required in order to use the new execution plans and new features such as partitioned tables effectively. All discussions in this book assume the use of the CBO, not the rule-based optimizer.

The ultimate goal for data retrieval in a TP system is to go straight to the location of the single required row and read it in; for now, we are not concerned about whether the record is cached or not. In most cases, this kind of lookup is not possible, because some kind of overhead is required to locate the row initially. If we assumed that B-tree indexes were this lookup mechanism, the number of Oracle blocks we would expect to need in order to read the data would be from two to five, depending on the depth of the B-tree.

If all queries in the system could be written this way, life would be good. Unfortunately, it is a rare day that queries can be written this efficiently. In practice, the act of normalizing the data model implies that several fetches could be necessary in order to retrieve the single item of data required, probably in the form of a multitable join. In addition, user-friendly applications require that the user be able to search using wildcards to some degree and that certain data applies only to specific ranges of dates. All of these requirements push up the cost of data retrieval significantly.

Oracle 8.1 makes a significant improvement in the usability of the CBO in a development environment by providing static plans and user-definable plans for the CBO to use. This allows a small development database to be set up to provide the same execution plans from the CBO as from the production database. Before this innovation, developing SQL for a CBO environment was difficult to do in a reliable way.

One thing that should be considered essential when writing SQL for the CBO, whether tested against a stable plan environment or not, is the use of optimizer hints. It is almost always true that the developer knows a great deal more about the data than the CBO is able to, and the use of hints takes some of the gamble out of the CBO. The specific gamble in question is that of the CBO electing to use a different execution plan than expected, owing to a change in one or more of the statistics. The CBO is more than capable of sabotaging a query that was previously not a problem, particularly in very dynamic environments, such as a system that has recently gone into production. New systems are especially prone to erratically changing plans as a result of the rapid proportional growth of some of the tables in the system, compared with the tables it joins against.

Using a simple query against V$SQLAREA, offending SQL can easily be identified:


col gets_per_exe form 9999999 SELECT hash_value,buffer_gets/executions gets_per_exe FROM V$SQLAREA WHERE executions>0 AND buffer_gets/executions>10 ORDER BY 2 desc / HASH_VALUE GETS_PER_EXE ---------- ------------ 3359911567 2865 4279157786 1188 607327990 121 32929334 102 3013728279 56 2303392424 51 3565234031 37 1425443843 34 3382451116 34 731738013 32 2407357363 26 1592329314 24 3441224864 20 1714733582 19 1428100621 15 2918884618 14 4261939565 14 3127019320 13 1737259834 12 365454555 11 20 rows selected. SQL>

Included in the V$SQLAREA view is the MODULE column. This column should be populated using the DBMS_UTILITY.SET_APPLICATION_INFO procedure by every module in the application. This is often the only way to determine the source of an SQL statement. Once this column is populated, the query can be confined to online modules, because batch jobs will always dominate the list if this is not the case.

When the priority for fixing these statements is being determined, consideration should be given to the execution rate. If the query that uses 102 buffer gets per call is executed 10,000 times more than the query that uses 2,865 gets, it is clearly a higher priority.

One of the best ways to tune a statement is to put a trace on a session that calls it, using the method detailed under "Tracing Sessions by Setting Events" in Section 3.5.3. Once a tracefile that contains the query has been obtained, a simple test case can be put together:


PARSING IN CURSOR #1 len=38 dep=0 uid=22 oct=3 lid=22 tim=0 hv=3024638592 ad='83842e20' select * from sys.dual where 1234=:b0 END OF STMT PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=0 BINDS #1: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=24 offset=0 bfp=4014b328 bln=22 avl=03 flg=05 value=1234 EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=1,cu=4,mis=0,r=1,dep=0,og=4,tim=0

The query can be pulled out of the tracefile and put into a script, using bind variables as in the original query.¹ The tracefile can also be checked for the number of buffer gets it is expected to need for those particular bind combinations. This is determined by checking the cr= token in the FETCH line. A reproducible test case can now be constructed for this query:


variable b0 number begin :b0:=1234; end; / alter session set timed_statistics=true; alter session set events '10046 trace name context forever, level 12'; select * from sys.dual where 1234=:b0; exit

The cost of the join can make or break a query, and it is occasionally worthwhile to denormalize the model a little for specific performance cases. For example, if an intermediate table is used, would the addition of another column make driving off this table more efficient?

One common misconception among SQL programmers is that it is good to try to do everything in one statement. There are some cases in which this is correct, but more often it is a very bad thing for performance. If the data is known, it is often better to break complex correlated subquery statements into two cursors and glue them together with procedural logic. Unfortunately for the programmer, this often means more typing in order to set up the variables required for coding.

Another thing to look out for is the number of records being returned by a query. For most kinds of online usage, it is not useful to return a large number of rows for the user to look through. If anything, this normally points to poor user-friendliness on the part of the application. Lists of values are best kept to a small number of rows, where "small" means a maximum of about 30 records. Obviously, there are cases in which this is inappropriate or impossible, but these situations should be exceptions to the general rule.

Large data sets can be spotted by once again using the V$SQLAREA view. With the same type of query that was used to obtain the number of buffer gets, we can obtain the average number of rows returned by a query:


col rows_per_exe form 9999999 SELECT hash_value,rows_processed/executions rows_per_exe FROM V$SQLAREA WHERE executions>0 AND rows_processed/executions>1500 ORDER BY 2 desc / HASH_VALUE ROWS_PER_EXE ---------- ------------ 603042213 30651 3490405195 12721 3189300438 9600 647382473 6840 3399070700 4962 893287616 3807 327344171 3529 3137157321 2668 2437259511 2666 2056336900 2513 1767743086 2415 1623323819 2317 3691742970 1853 3575556459 1749 88760771 1692 1074455672 1520 980033159 1520 17 rows selected. SQL>

In this example, the average number of rows per execution was restricted to 1,500 in order to reduce the list returned. It can be seen that some of these queries are returning well over a reasonable number of rows. Once the MODULE filter has been applied, this list can be worked on in order to ensure that reasonable requests are being made to the database.

In addition to making SQL "reasonable" and efficient, other factors need to be considered in order to ensure that the SQL does not create excessive contention in the database. These factors fall into two main areas:

When a heavily read table is updated, the blocks are subject to cleanout at the next read, as discussed in Section 5.5.3. This can cause big slowdowns for the next reader of the block. The following tkprof output shows the cost of a given query for which no block cleanout is required:


select sum(MONEY) from MY_CASH where CASH_ID between 10000200 and 90004611 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.78 1.17 417 420 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.79 1.18 417 420 0 1

The important fields to look at are the cpu and elapsed on Fetch. Compare them with the same query that needs to perform block cleanout on each block as it passes through:


select sum(MONEY) from MY_CASH where CASH_ID between 10000200 and 90004611 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 2 1.18 3.02 417 838 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.18 3.03 417 838 0 1

The block cleanout is clearly expensive, even in this small example. The implication of this is that a relatively fast update performed during online operation of the database can affect the response times of queries for a long time afterwards. In addition, if many other sessions are also trying to read the data, they will queue up waiting on the event "buffer busy waits"-that is, waiting for the cleanout to be performed by the first session that accesses the block.

In addition to contention between writers and readers, contention between two readers can also occur. Although this occurs only at significantly higher read rates than write-read contention, it can still present response time problems if not managed adequately. Consider the following query:


SELECT /+ INDEX (test_tab,ti1) / b from test_tab where a in ('AA','AB' ,'AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR' ,'AS','AT','AU','AV','AW','AX','AY','AZ','A[','A\','A]','A^','A_','A`','Aa','Ab' ,'Ac') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.02 0.01 0 71 0 35 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.04 0.04 0 71 0 35

In this query, the B-tree is traversed for each of the values specified in the IN clause. For this particular example, the BLEVEL² of the index is 0, meaning that it is so small that there are no root or branch blocks-only a single leaf block. Therefore, there is one consistent read for each of the rows, and one for the table reference (plus an extra get as a result of a second fetch attempt by SQL*Plus). The conclusion, therefore, is that the B-tree must be traversed from root to leaf for every fetch from it.

While this does not present a large problem for this particular (small) query, it can present a problem in larger tables/indexes. For example, if an index has a BLEVEL of 2, this means that there is a root and a branch block above every leaf. Therefore, three blocks must be traversed to find any given leaf. The key problem is that every access has to go through the root block-a classic bottleneck.

Although we are reading only the root block, we need to take out a latch on the hash chain that the dba belongs to in order to pin the buffer. If several queries with long IN lists all hit this index at the same time, latch contention will result on the hash chain. This shows up as an increased number of misses and sleeps in v$latch and results in response time problems as Oracle serializes on one latch:


select hladdr "LATCH ADDRESS", dbarfil "FILE#", dbablk "BLOCK#", gets, lc.sleeps from x$bh bh, v$latch_children lc where lc.addr=bh.hladdr and state!=0 /* ie not FREE */ and sleeps>10000 order by sleeps desc / LATCH_AD RFILE# BLOCK# STAT GETS SLEEPS -------- ---------- ---------- ---- ---------- ---------- A2F07930 311 26123 XCUR 90757897 103036 9C0A1080 157 7683 XCUR 373660716 95946 9C18A048 305 133251 XCUR 97899571 62771 9C18A048 304 476892 XCUR 97899571 62771 9C18A048 322 206671 XCUR 97899571 62771

This script pulls out all hash chain latches that have been slept on more than 10,000 times since start-up, identifying "hot" chains. Depending on the number of hash chains configured for the instance, one or more blocks will be reported in the output. Although it is likely that only one buffer is being contended for, all other dbas that hash to that chain will also be affected by the slow down. It can be seen from the output that the hash chain latch at address A2F07930 has been slept on about twice as many times as the latch at address 9C18A048, for the same number of gets. This indicates some kind of burst access for this buffer, such as large IN lists in a query.

If it is a root block that is contended for, the block number will correspond to the first block of the index segment after the segment header. Once a problem of this type has been identified, action can be taken to improve it. Such problems are discussed in Section 9.2.

One of the major changes in Oracle for release 7 was the addition of the shared pool and shared SQL areas. Prior to this, all SQL was parsed privately for each user who executed it. Shared SQL allows far greater throughput to be achieved on the database server, as a result of the reduction in work required to interpret the request. Full implementation of shared SQL should be considered essential for a large-scale application, because the cost of not doing so is very high-higher than it was prior to the introduction of the shared SQL facility.

Contrary to this need is the misuse of the dynamic SQL feature of Oracle. This feature is now present in all of the Oracle tools, including PL/SQL and Pro*C, and allows dynamic construction of SQL statements based on the flow of the code and the variables passed to it. Unfortunately, this very powerful facility is mostly abused as an easy way of constructing queries, resulting in unique pieces of SQL for every request. The proper use of dynamic SQL is to allow more structured program flow and to implement complex requests, not to embed literals in statements and to break the SQL sharing.

Dynamic SQL can still be employed in large-scale systems as long as its use is carefully managed. The important thing to remember when using dynamic SQL in a piece of code is to keep the number of potential permutations as low as possible. For example, consider a module to which are passed a variable number of parameters that are eventually needed as predicates in the where clause. Anywhere from one to 400 parameters could be passed to this module, all of which must be supplied to the query in the module. The temptation is to construct the where clause of the statement dynamically for every parameter supplied:


inputs : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ... 399, 400 sprintf(preds,"WHERE xyz in ("); for (i=0;i<nparams;i++) { sprintf( tmps, "%d,",inp[i]); strcat(preds,tmps); } sprintf(preds+strlen(preds)-1,")");

Although easy to do, this is about the worst thing that can be done here, because it creates a totally unique statement for every execution. There are basically two levels of optimization that can be performed on this statement. The first simply uses the same code base as the original, but constructs the IN clause out of bind variables and assigns variables to those variables at the same time. While not ideal, this cuts down on the number of possibilities enormously, because any call with the same number of parameters will reuse the same cached version of the SQL statement.

A better way to do this is to define a finite number of cursors. The first cursor may allow for as many as ten parameters, the second up to 20, and so on. If a standard increment of ten parameters were maintained, this would result in a maximum of 40 cursors in this example, which would be very much more manageable.

All the detail of setting up for dynamic SQL method 4 has been omitted, concentrating instead on the actual construction and binding of the statement. The trick in this particular example is to bind the unused placeholders as NULL. No value can equal NULL, even NULL (IS NULL is the only test that will identify a NULL value). Therefore, binding the unused variables to be NULL ensures that no data is returned for them.

WHERE EMPNO IN (:b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9)

Limiting the number of permutations of a given statement is the only way that dynamic SQL can be adopted. In the very dynamic case of any number of inputs discussed above, this could also have been achieved by creating a single cursor with hundreds of bind variables. The unused variables could then be bound as NULL as in the bucket example above. However, this brings about another problem and should also be avoided.

There is a measurable cost associated with each bind operation. Each one results in work being performed on both the client side and the server side, and if the number of redundant (NULL bound) variables is high, an enormous amount of CPU can be wasted doing redundant work. To demonstrate this, I wrote a Pro*C program that executes a simple SELECT statement 500 times. The query looks like this:


SELECT one_column FROM my_table WHERE one_column IN ( :b0, :b1 );

Both of the bind variables were set to be NULL, and so no data was returned by this query. I also wrote another program that behaved exactly the same way, but bound 400 NULL variables instead of two. Table 9.1 shows the results when this program was run against a PA-8200-based database server.

The 400-bind version of the code took 333 times as much CPU to perform the same net work (returning no data). Some of this CPU will be used parsing the much larger SQL statement, and some will be spent performing the actual binding operations. At the end of the day, it doesn't matter where it is being spent-excessive binding should be avoided wherever possible.

When writing SQL for an application, it is easy to fall into the trap of poorly bound SQL. This is not another reference to the use of bind variables, but to the careful definition of data sets within an SQL statement. A single query may have a minor footprint one day and a huge footprint the next day, all based on the data supplied to the statement. This falls into two main categories:

Most transactional systems have an abundance of date-related rules. The dates affected by these rules can range from dates on which transactions are made to dates that determine the eligibility of certain records in the database. The implication of this is that there are also an abundance of queries in the system that are constructed as follows:


SELECT xyz FROM abc WHERE SYSDATE between START_DATE and END_DATE;

As soon as such a query is written, it is guaranteed to perform a range scan of some description on the index in the date columns. If the date ranges are understood by the developer, and the data is protected from skew through the application, this still does not present a huge problem. However, it is more common for date ranges to grow quickly out of control, particularly on a new, growing system. This results in increased I/O on the system, a polluted buffer cache, and wasted CPU.

In one such scenario, certain information is considered active only if the supplied date lies between the start and end dates for that information, such as seasonal types of products in a supermarket. A certain type of chocolate Easter egg, for example, is available for order by the store manager only between January and April of the current year. The query that retrieves available products excludes items that do not have a start_date in the past and an end_date in the future.

The problem with this type of data is that it rapidly becomes very nonselective because of the way in which it is administrated by the users of the system. In this example, it is likely that 99 percent of all the available products would be made active forever, making any index that leads on the start and end dates completely useless. The server has no choice but to range scan most of the index, going back to the table for more information when necessary.

It is important to prevent this kind of data skew if the application is to scale well: The more data that goes into a table, the more work it takes to retrieve data from that table. If there is another attribute of the query that is more selective, that attribute should be made the leading edge of the index. Otherwise, it is likely that a full table scan is a better alternative than the huge index range scan. However, this is typically not a good solution either, and a design change needs to be investigated.

In the case of this example, it may be more practical to have a B-tree index on an "inactive flag," which is updated periodically by a batch routine. The flag would have a value only if the product was inactive for the current period, therefore keeping the number of keys in the index low. More typically, however, it is not just SYSDATE that is compared against the start and end dates, but rather some arbitrary supplied date. In this case, the adoption of an inactivity flag would not provide the required result.

The most common problem in many large systems is the lack of enforcement of controls on user input. If user input is not carefully controlled, the users have the power to wreak havoc on the system with very large, unbounded queries.

The classic unbounded query is a name search. Names are typically indexed columns and are normally quite selective apart from well-known exceptions. In a search for a customer profile, for example, the provision of a few last name characters and the first initial dramatically reduces the number of keys that need to be scanned in the index. If users are allowed to be unreasonable here, they will be unreasonable here-they simply don't know any better. Good practice in the name example is to require three characters of the last name before a wildcard can be added. Names of fewer than three characters don't really need the wildcard functionality to speed the user along.

I encountered an interesting case study in a furniture store one day. A price ticket was not visible for a certain table, and so I asked an employee to look it up on the computer. Unfortunately, the name of the table began with an accented character, and the employee was supplied with a U.S. keyboard that had no accented characters. I noticed that the application was Oracle, with the familiar face of SQL*Forms showing itself on the terminal.

The employee proceeded to input the only string he could in order to find the item: "%bo". Of course, while the server was grinding its way through the entire B-tree index on the product name, the employee was whining and complaining to me that the computer system was broken and that he wished they would get a new one. This is a classic problem-in this instance complicated by the need for a special European keyboard on the terminal, combined with some application field enforcement.

Traditional client/server applications are known as two-tier applications. Of the two tiers, the lower tier is the database, and the top tier is the application program (see Figure 9.1).

In this example, Oracle8i and SQL*Forms 4.5 have been used, in a classic two-tier pairing. In the upper tier, SQL*Forms has all of the logic to display the forms to the users, to implement the business logic, and to access the database. In the lower tier, Oracle8i consists of the database itself and a transaction manager.

The term "transaction manager" is defined by The Open Group³ as a piece of software that coordinates a transaction and enforces its atomicity. In formal terms, a transaction manager coordinates the transaction across all the involved resource managers, which is The Open Group's name for any shared, consistent resource. The resource manager in this case is the actual Oracle database.

In a single-database environment, the transaction manager used is the Oracle Server transaction manager, and the resource manager is the physical database. All the atomicity required for a two-tier transaction is managed within the Oracle Server, with the application logic issuing commit calls directly to the database.

The upper tier in this example contains the Forms application, which in itself consists of two logically different components: business logic and presentation logic. The same physical executable is responsible for displaying the forms to the user, following the business logic, and performing data access to the database.

These disadvantages offset the benefits of this solution: rapid development times, relatively uncoordinated development, and use of 4GL development tools. These problems are also difficult to avoid within a two-tier architecture. The way to solve them is to adopt a