December 2004

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

December 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists – Shared Server Issues in Oracle 9i

You’re invited to submit your Oracle-related questions to us at askdbspecialists12@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes to us from Mike in Redwood City, CA: We are thinking of configuring our Oracle 9i database to use the shared server architecture instead of dedicated server. In practice, are there any issues or common problems with the shared server architecture that we should be aware of?

Brian Keating of the Database Specialists team responds: The Oracle shared server architecture provides a mechanism for a relatively large number of user connections to be established to a database with a relatively small amount of physical memory required. However, there are a number of potential issues with the shared server architecture that DBAs should be aware of before even considering implementing this feature. I will mention three of them here.

First of all, the shared server architecture is particularly sensitive to inefficient SQL. Poorly tuned SQL statements can easily take many minutes or even hours to complete. This is a problem even with dedicated sessions, of course, but the issue is much more severe in a shared server environment. This is because by definition there are a finite number of shared servers available—as specified by the parameter max_shared_servers. Therefore, if frequently-executed SQL statements take a very long time to complete, then it is very easy to get into a situation where all of the shared servers are busy running those statements—and PMON is not able to spawn any more servers. This situation essentially results in a system-wide “hang” in which many user sessions are not able to do anything.

Another type of issue that can occur with shared servers is that in some cases a shared server process can completely abort. When this happens, an ORA-00600 [16365] error will be listed in the alert log and a trace file will be generated. PMON will then restart the aborted server process automatically. There is very limited information available on this issue; but my understanding is that this issue can occur when a shared server completes executing a SQL statement and tries to write the results of that statement to a virtual circuit—but that circuit has been updated (or “dirtied”) by a dispatcher. In other words the sequence of events is this: A shared server picks up a SQL statement from a virtual circuit and begins executing it. Then a dispatcher places a new message onto that same virtual circuit. When the shared server completes its SQL statement and tries to update the virtual circuit with the results of that statement, the problem occurs. More detailed information on this issue can be found in Metalink Doc ID 106607.1.

The final type of issue that I will mention here is an issue in which all of the service handlers in an environment get into a “blocked” state. In a shared server environment, dispatchers “register” with a listener by establishing service handlers to that listener. Evidently, when the service handlers are under a relatively heavy load, those service handlers can sometimes get into a “blocked” state—such that those service handlers will not accept any new connections to the database. When that happens, users receive errors (usually ORA-12537) when they try to establish new connections to the database. Existing connections are apparently not affected, however. The issue generally seems to last for a few minutes at a time, before appearing to resolve itself. This certainly appears to be a type of “threshold” issue; i.e. the service handlers stop accepting requests when the load on them reaches a certain level. As such, it may be possible to reconfigure the listener (or instance) to allow the service handlers to operate under a heavier load in order to increase this threshold. I have not had a chance to test this, however.

The amount of physical memory required to handle large numbers of dedicated server connections is usually much higher than the memory required for shared server connections. Therefore, if memory is severely limited in an environment—and if the issues above can be avoided—then the shared server architecture may still be a viable choice in that environment. For a more in-depth discussion of the issues raised here, please read my white paper on the subject at http://dbspecialists.wpengine.com/presentations.html#shared_serv9i.

Speeding Up Queries

Database Specialists founder Roger Schrag has been invited to share his expertise on performance issues and speeding up queries at the Rocky Mountain Oracle Users Group Training Days 2005. The conference will take place in Denver, Colorado February 9-10, 2005. NoCOUG’s Great Resources

Roger returns this year for the RMOUG conference, joining “internationally recognized speakers and local favorites” including Craig Shallahamer, Jonathan Lewis, and Cary Millsap. Roger will give a presentation entitled Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN.

Over 1,000 Oracle professionals attended last year’s RMOUG Training Days, which includes educational tracks such as database administration, data warehousing, network administration, and application development. RMOUG packs a lot of technical sessions and tremendous value for Oracle database administrators and developers into the two-day event.

Advance registration discounts apply until January 10, 2005.

 

Yet again, you’ll find some great resources on the website of the Northern California Oracle Users Group (NoCOUG). The group held a full-day conference last month with over 200 attendees, and many of the speakers have made their presentations available for download on the NoCOUG website. Here is a sampling of topics: A Year-end Thank You

  • Conquering Oracle Latch Contention by Craig Shallahamer, President, OraPub
  • Optimizing Oracle9i Instance Memory by Lenka Vanek, Sr. Product Manager, Quest Software
  • Use EXPLAIN PLAN and TKPROF to Tune Your Applications by Roger Schrag, President, Database Specialists, Inc.
  • Indexing Strategies in Oracle—an Overview by Scott Martin, President, Terlingua Software
  • I Love the Java Jive: J2EE Overview for Oracle Technologists by Peter Koletzke, Technical Director and Principal Instructor, Quovera

To view these presentations and more, browse the NoCOUG website.

 

We’d like to extend warm holiday greetings and a big “thank you” to our clients and colleagues in the Oracle arena. We thank our clients for entrusting us with your mission-critical systems, whether you look to us for for all of your database administration and Oracle support needs, or just for advice on a specific performance or other Oracle-related issue. We are thrilled to contribute to your success in positive ways, and we are proud to be an active part of the Oracle user group community. A Resource Recap

To our colleagues, thank you for your questions, participation, and feedback on our various Oracle-related resources. We’ve always believed in sharing knowledge, and we appreciate the opportunity to do so. Let’s keep the dialogue going in 2005!

 

As 2004 draws to a close, we’d like to take a moment to thank all of you for the continued success of The Specialist. We hope you have enjoyed the news and resources we have shared pertaining to Oracle technology. Our own experienced team has contributed resources as well. Highlights include white papers on:

  • Recovering databases with missing archived logs
  • Installing Oracle on Linux
  • Using bulk binds in PL/SQL to boost performance
  • Native PL/SQL compilation

This year we continued our Ask DB Specialists column, and our team answered questions on topics such as password management, bulk binding, shared memory segments, and cursor sharing. (You’ll find back issues on our website.) Keep those questions coming! We encourage your input and suggestions. And we look forward to sharing more with you next year!

Leave a Reply

Your email address will not be published. Required fields are marked *