| Previous | Table of Contents | Next |
Let’s broaden our example to say that the update of balances is a transaction executed 10 million times a day by 3,000 users for many different balances and customers in a large OLTP application. If all of these update statements were submitted with literals as illustrated in Listing 31.1, Oracle would have to parse 10 million update statements. However, if these updates were submitted using host variables as illustrated in Listing 31.2, and the same code was used in all transactions so that the statement did not differ in any way, Oracle would parse the statement once and reuse it from the shared pool from then on.
Tip:
Avoid using ODBC for client connectivity for large OLTP applications.
You may not realize it, but ODBC can be resource intensive on both the server and the client. For large OLTP applications, I am more concerned about resources consumed on the server. If you choose to ignore this tip, use either the SQLPassThru option or RDO. These options will reduce server overhead but in many cases will not allow array processing, which is highly desirable.
Tip:
If you haven’t already done so, migrate from rule-based optimization to cost-based optimization.
Unlike the other tips and recommendations in this section, I now recommend cost-based optimization for all types of applications, not just large OLTP applications. My reasons for this recommendation are the following:
As much as many database administrators still love rule-based optimization, cost-based optimization is Oracle’s stated direction. Oracle will most likely continue to provide existing rule-based optimization features for backward compatibility, but no enhancements are being made in the rule-based optimizer to include new Oracle8 features, such as partitioned tables, index-only tables, and object types.
With rule-based optimization, we explicitly control the access path all of the time. This is not necessarily a good thing. Database objects often change in ways that weren’t accurately estimated beforehand. Furthermore, the pace of most projects precludes the possibility of performing a thorough SQL review. Cost-based optimization (assuming frequent gathering of statistics) can alter the access paths of your SQL statements when the database changes, as well as compensate for poorly written SQL to some extent.
Rule-based optimization requires a detailed knowledge of the optimizer and how the syntax of the SQL statement affects Oracle’s access path. Most junior application developers do not have a detailed knowledge of the rule-based optimizer and how it works. The access path provided by the cost-based optimizer is often better than a poorly written SQL statement under the rule-based optimizer.
Please refer to Chapter 24 for more detailed information about the differences between cost- and rule-based optimization, as well as information about hint usage.
Application security is an important issue for most OLTP applications, but it is rarely addressed in books of this kind. OLTP applications are most commonly coded with one of the three security models. The first model involves coding the application to circumvent database-level security and to manage security internally within the application. The second model is where the application merely relays the user ID and password to the database, which either accepts or rejects the database connection. The third is a hybrid of the first two that relies on both application-enforced and DBMS-enforced security.
Application-Enforced
When security is enforced by the application, a generic user ID/password is used for the database. Often, this generic ID is known to all developers. Once database connectivity is established, the application then handles security (if security is provided at all) in a custom-coded way. Sometimes the application doesn’t require a user ID or password at all. I’m constantly surprised at the number of OLTP applications that use a generic user ID and password that don’t provide any means of user verification at all.
Generally, generic user IDs and passwords present a security risk because they often cannot easily be changed without adversely affecting other users and batch processes. Hence, people may have access when they should not (people who quit, transfer departments, or are terminated).
Developers who choose this method often don’t provide any sort of user authentication, and do so for programming simplicity. Unfortunately, this simplicity is provided at the expense of any real security. Very few developers take the trouble to custom-develop security code.
DBMS-Enforced
When security is enforced by the Oracle database, the application will often provide a screen on which a user enters a user ID and password. These are then used to format a connect statement to establish database connectivity. With this kind of enforcement, the connect will receive an error if the user can’t be properly authenticated.
When security is DBMS enforced, often we create public synonyms for application schema tables, views, procedures, and so on and grant the appropriate access to users via a role. Please refer to Chapters 13, “Oracle System and Object Privileges,” and 14, “Roles and Grants,” for a detailed discussion of security privileges, roles, and grants. Because of public synonyms and potential naming conflicts, a database that uses DBMS-enforced security often services one and only one schema. However, this is usually the case with large OLTP applications anyway.
DBMS-enforced security does have a price. The need to add, drop, and modify users creates a maintenance headache. However, maintenance can be made manageable. I usually write stored procedures that can add users, drop users, and change user passwords. I create these procedures under an account that has been granted the DBA role and grant execute privileges to selected end users who act as application administrators. In this way, I place the burden of security maintenance on the end users.
| Previous | Table of Contents | Next |