Like most things in life, there are prerequisites and preparations for Oracle databases.  Regardless of version, this is especially true for application access and usage. Oracle is a highly scalable and configurable database, therefore DBAs must configure their database for how it will be used to guarantee meeting their users’ expectations – including database developers. Failure to do so can lead to perceptions of “slow performance” and sometimes even seemingly odd Oracle error messages that may unfortunately be misconstrued as application bugs. Toad is no exception to this.

 

Now it would be nice if I could identify the 20 or so Oracle spfile or init.ora parameters that would yield ideal Oracle database behavior for the optimal Toad experience.  But, as Toad provides so many capabilities and people use it so differently, it’s hard to identify the ideal settings that universally apply. But I’ll try to identify a couple – and hope that these examples lead readers to see a pattern and address all those that make sense within their environment.

 

Let’s assume that you’re going to use Toad for just doing schema browsing, data browsing (whether in the schema browser and/or editor data grid), data filtering, data manipulation, data exports (i.e. data grid “Save As”) and basic SQL execution to obtain such data.  Then the database simply needs to be setup to best handle typical OLTP type database access. Thus the DBA might set the following parameters:

Again, I’m not trying to identify every possible database configuration setting that you might need to consider – but rather to establish a pattern. Look at how your users will use Toad and then configure your database accordingly.

 

Now what if you’re going to be doing lots of PL/SQL compiles, executions, debugging and profiling? Not properly configuring your database for these special needs can result in many different and seemingly odd Oracle messages – including ORA-4031: unable to allocate %s bytes of shared memory.  What does this mean and why did Toad seemingly cause Oracle to return this error? That’s easy – you’re doing extensive PL/SQL work that requires space within the shared pool according to the Oracle architecture and how things internally work. If you just use the default settings when creating your database, then you will generally be at risk when doing lots of PL/SQL work – regardless of the tool being used. So here are some example parameters that I’d set for this use case:

Now some people might say “We’re using Oracle’s 10g’s automatic SGA_TARGET or Oracle 11g’s automatic MEMORY_TARGET allocation, so we don’t need to set these!”  But if you know what your needs will be – then set these as floors that the automatic memory management cannot go below. In other words add your wisdom and insight to Oracle's automatic attempts at a generic solution.

 

Here are a few other PL/SQL specific items to review:

There are far more setting that we could discuss, but hopefully you can see the pattern. When setting up your Oracle database for whatever application and usage it will have to support, then properly configuring your Oracle database will help you to avoid painful and unnecessary headaches.

 

Happy Toading ….