Bert Scalzo's Blog

Speeding-Up Oracle on Your Notebook PC – Part 2

Bert Scalzo — Wed, 26 Nov 2008 14:50:00 GMT

Just a very brief blog this Thanksgiving week (my favorite holiday of the year) – part 2 of last week’s relatively easy suggestions on how to make Oracle run as fast as possible on minimal computer setup. As before, my goal is simply to squeeze as much performance blood from the Oracle turnip as possible when deployed on limited capacity equipment. The one additional suggestion below only makes sense for environments where instance recovery is not a key issue – e.g. a demo notebook PC for traveling consultant types. So make sure not to use this unless you understand what the drawbacks are (discussed below) and can live with them.

Place the Oracle REDO LOG files on a RAM Disk. For extremely transaction heavy environments this can make a huge difference. But for general purpose usage, it may provide just a small improvement. Thus apply and use the technique with forethought.

While Oracle is running it is writing transactional history to the REDO LOG files such that if the instance is brought down unexpectedly, then the outstanding transactions can be played forward to restore to the state of the instance failure, and then rolled back for the un-committed transactions. If you’re running in NOARCHIVELOG mode, then the contents of the REDO LOG files get overwritten once the database transactions’ round-robin consume the allocated space – so database transactions can get lost (i.e. aged out) anyhow in this setup. So placing the REDO LOG files on the RAM Disk just adds one more scenario where data is aged out (and rather abruptly) – at computer shutdown.

So for purely demonstration or development environments, where raw speed is the most critical factor – this technique has merit. If it makes sense for your database computing needs, then give it a try …

Run Toad off a USB Flash Drive

Bert Scalzo — Wed, 19 Nov 2008 21:32:00 GMT

Lot’s of people have asked “How can I setup Toad to run off a flash drive?” The reason for this question varies in nature and intent by requestor – but the general concept is that some users need the ability to carry Toad with them. Some are contractors who must use whatever computer and/or desk is open that day. Some are database developers who must travel around their company to perform or assist with various tasks. And finally, some are DBA’s who need to travel around while diagnosing and troubleshooting problems. In all cases, the request makes total sense (i.e. making portable a legal license). Now we have a way to do it!

Note – Toad is normally licensed per seat, therefore this is not a mechanism or technique to circumvent or violate the spirit of that license agreement. The basic idea is that per seat means for a single USB Flash Drive being used by a single user on a single computer at a time – and thus no concurrency.

Here are the steps:

Format the USB Flash Drive as an NTFS file system.
1. File explorer, right hand mouse, properties, hardware tab
2. Select the USB Flash Drive and press the properties button
3. Policies tab, choose Optimize for Performance and press OK
4. Format the USB Flash Drive – NTFS should now be available
Create TOAD_FLASH directory on your USB Flash Disk (at topmost level)
Download and then unzip the TOAD_FLASH.zip file onto your USB Flash Disk
Unzip the contained Oracle Instant Client installation file onto your USB Flash Disk
Install Toad for Oracle and copy the Toad home (install directory) to your flash drive

Example: xcopy /s /q "C:\Program Files\Quest Software\Toad 9.7" "X:\Toad 9.7\"
Run TOAD_FLASH_SETUP.bat file to install (and save pre-existing Toad setups)
Run TOAD_FLASH_RUN.bat file to launch Toad (must enter license first time)
Run TOAD_FLASH_RESTORE.bat file to restore PC back to prior Toad state

You can download the file at http://www.bertscalzo.com/Files/TOAD_FLASH.zip

Speeding-Up Oracle on Your Notebook PC

Bert Scalzo — Wed, 19 Nov 2008 15:29:00 GMT

Just a quick blog this week – some relatively easy suggestions on how to make Oracle run as fast as possible on minimal computer setups such as a notebook PC. My goal is simply to squeeze as much performance blood from the Oracle turnip as possible when deployed on limited capacity equipment. The suggestions below should work for most operating systems, including Windows, Mac-OS and Linux – simply interpret and apply the suggestions in a contextually appropriate manner.

Exclude Oracle data files directory(s) from all anti-virus and anti-spyware automatic and manual scans. Myself, I exclude the entire “C:\Oracle” directory – where I place all my Oracle files.
Eliminate all unnecessary background processes (demons) or services. Here are some basic examples (note that your computing environment needs and thus extraneous list will surely vary – so check with your local administrators to be sure and thus safe):
1. Windows XP
  1. ATI Hot Key Poller
  2. Computer Browser
  3. Distributed Link Tracking Client
  4. Error Reporting Service
  5. Indexing Service
  6. Upload Manager
2. Redhat Linux(s)
  1. anacron / cron
  2. httpd
  3. ISDN
  4. Net FS
  5. NFS Lock
  6. Send Mail
Clean up (i.e. purge) the Oracle logging directory tree. For 9i and 10g, this was subdirectories such as BDUMP and UDUMP under your “admin/SID” directory. Starting with 11g, you need to look at the entire “DIAG” directory. Myself, I use a freeware called CCCleaner to purge all these files such that they don’t accumulate.
Place the Oracle “DIAG” directory on a RAM Disk. This also has the side effect of automatically purging the files (step #3 above) when the laptop is shutdown.
Exclude Oracle data files directory(s) from excessive or unnecessary last update timestamp maintenance.
1. Windows
  1. ```
  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem\NtfsDisableLastAccessUpdate = 1
```
2. Redhat Linux(s)
  1. chattr +A file_name per Oracle control, data and log file
  2. chattr –R +A directory_name per Oracle SID data directory
  3. Edit /etc/fstab for the Oracle Home and/or data file systems
    Example: /dev/sda6 / home ext3 defaults,noatime 1 1

Just Make Oracle Go Faster

Bert Scalzo — Wed, 12 Nov 2008 16:25:00 GMT

Catchy title – huh? But don’t laugh, in today’s hectic world many database professionals (whether DBA’s or developers – and sometimes even end users) just wish that the Oracle database would go faster. That may seem self evident – but let’s just accept that premise for now. So what does this mean for Toad users?

Toad offers numerous database monitoring, diagnostic and tuning screens or utilities. But not all of us have sufficient spare time to dig through the database haystack to find such issues. Moreover we all don’t know the database internals anymore – because Oracle 10g and 11g have become such technical behemoths. For example, just how many of us now know all the various wait events and what they really, really mean anymore? There are just too many to stay an expert on every little internal detail.

So what’s an Oracle professional to do? How should we attack a performance problem where we know that the SLA is being missed – but there are a lot of things going on that we may or may not know all the details about. And like the database, applications have become very complex too – often with numerous technology layers beyond the database. So how can we quickly and easily locate the needle in the haystack? Because we all want to concentrate on fixing items that quickly yield measurable results.

For those who have licensed Oracle’s diagnostic and tuning packs – Toad offers screens to support more productive work with those facilities. Because not everyone wants or is permitted to run OEM – as some shops limit OEM access to production DBA’s. That’s OK as Toad with the DB Administrators module (or bundle) can help you out here.

My experience has been that the fastest way to locate and concentrate on the most likely database performance issues is via Oracle’s AWR and ADDM reports. For those who do not have those Oracle options (they are optional OEM add-on licenses) – you can very often use the Stats Pack report in a similar fashion. Simply take a manual AWR and/or Stats Pack snapshot before and after some time period where database performance is generally not acceptable. Then run the reports using Toad’s screens (rather than OEM).

Here are examples of the AWR and ADDM screen’s that I’m talking about – and the tabs (i.e. reports) of key interest. Often just these two reports alone can point me into the right direction for where to spend my tuning time and effort.

Figure 1: Toad Screen for AWR Report

Figure 2: Toad Screen for ADDM Report

By starting with an analysis of just these two reports, I can often locate the most critical areas of concern and thus promptly address the most major performance issues – and all within just a few minutes time. What I’m saying is that for 20% of my diagnostic time and effort, I can locate and often correct 80% of my biggest issues.

Then from here, I can now dive into the other more complex or detailed Toad screens or utilities for performance optimization – such as the Database Health Check (part of Toad Database Admin Module) and/or the SQL Optimizer (part of Toad Xpert Edition).

Thus I can use Toad to quickly solve some major issues (so as to quiet things down a bit so I can look further). Then I can use the many other Toad features to find the remaining and more complex performance issues that need attention. So Toad permits me to do the heavy lifting very fast, and then to follow-up with the more intricate work to its logical conclusion. In other words, Toad can do for database performance what it has always done for SQL and PL/SQL development – improve your productivity by letting you concentrate on the task at hand rather than the Oracle mechanisms to do it.

You gotta love Toad J

Toad Makes DB Waits Easy

Bert Scalzo — Wed, 05 Nov 2008 15:19:00 GMT

I’ve always been a huge fan of Cary Millsap’s “Method-R” technique for Optimizing Oracle Performance via detailed analysis of all of the business process wait states. It is fundamentally sound since it’s based on genuine business concerns and detailed analysis of a mission critical business processes’ overall execution time (and not simply just the database waits or ratios). But while many people like this approach, few have found the tools to support this technique. But Toad offers many screens for DB Wait analysis. Did you really expect me to say otherwise?

Let’s examine a few (most assume that you own the DB Admin module or bundle).

If we want to begin an end trace collection for a particular database session in order to create database trace files, then we simply use Toad’s Session Browser as shown below. Remember, all of the trace files will be on the database server – and we can retrieve them using Toad’s FTP Screen.

Figure 1: Toad's Session Browser

Once you have the trace file, you can then explore it using Toad’s Trace File Browser as shown below. Note that this screen offers a link to the FTP screen for convenience.

Figure 2: Toad's Trace File Browser

I could go on as there are numerous other screens to support this database wait centric optimization technique. But then the real question arises – “What does this all mean?” We’re all not wait event experts like Cary Millsap, Tom Kyte, Mike Ault and many other Oracle experts out there. That’s where Toad makes life easy once again – as all the most common wait events have pop-up windows to explain why they generally occur and how to possibly fix them. These pop-up help screens are very easy to locate as they always shows up as a blue underlined hyperlink on wait event screens. So when you double-click on them, you get help such as this.

Figure 3: Wait Event Explanation Screen

Below are screen snapshots of just some of the many other places where you’ll find this wait event explanation feature in Toad. Remember to simply look for database wait event information (i.e. tabs) on a particular screen, and then double click on those marked with a blue underlined hyperlink. Now we all can look like wait event know it alls J

Figure 4: Wait's on Trace File Browser

Figure 5: Wait's on the Session Browser, Sessions

Figure 6: Wait's on the Session Browser, Waits #1

Figure 7: Wait's on the Session Browser, Waits #2

Figure 8: Wait's on the Session Browser, Waits #3

Oracle + VMware = Success Part III

Bert Scalzo — Thu, 30 Oct 2008 14:25:00 GMT

A few weeks ago I wrote about how simply adjusting various virtualized infrastructure default settings that a database could perform up to 440% better. Of course “your mileage” will vary, but the key point is that taking the time to properly layer your virtual infrastructure settings can have substantial impacts – which are only magnified by the net or cumulative effect of a virtual server hosting multiple database servers. And while my example was specific to Oracle, the same principles generally apply for any database being deployed on a virtual server.

I presented this information since there are still many who question deploying database servers on virtualized platforms. My key contention is that as long as there are sufficient resources and/or bandwidth to handle the net requests, that requiring database servers to be islands onto themselves is now passé’. Even if we say that there is a 10-20% overhead (which I’m inclined to say is closer to ten than twenty), with cheap hardware these days the benefits just far outweigh the negatives – just spend 10% more and get a bigger server to handle multiple databases. But it may be a few years before that opinion is generally or more widely accepted.

This week I’m going to elaborate on just one of the techniques I used to get such stellar results. If you refer back to the first blog, I’m going to explain choice #10. As a reminder:

#10 = Adjust VM client file system block size to more closely match Oracle block size

The default block size for both the host and client OS file systems’ is generally not the same as your Oracle block size (although hopefully the Oracle block size is a multiple of the OS file block size). Since the host may be servicing multiple Oracle databases with different block sizes, different database platforms (e.g. MySQL), or being used as to host other applications (e.g. web server) – we cannot always make this adjustment at the host level. But generally we can make it for each of the Oracle database clients.

Let’s assume we have a Linux host running a Linux based Oracle database client. Let’s assume that we create the host file system using the default block size: 2K. Let’s further assume that we do the same thing on the client – but that we size the database blocks at 4K. Thus each Oracle physical IO requests asks the client OS for two IO’s, and the client asks the host for two IO’s. That’s a total of four IO requests (although only two physical IO’s in reality – but there is overhead for each request, so larger numbers are worse). Now if the client had instead created its file system at 4K, then the IO numbers would instead be client = 1 and host = 2 for a total of 3 (which is obviously less than 4). Here’s a chart to show some common combinations and their sometimes surprising overheads:

Host OS block size	2	2	2	2	2	2	2	2	4	4	4	4	4	4	4	4
Client OS block size	2	2	2	2	4	4	4	4	2	2	2	2	4	4	4	4
DB block size	2	4	8	16	2	4	8	16	2	4	8	16	2	4	8	16
Total IO requests	4	7	13	25	5	7	12	22	4	7	13	25	4	6	11	21

Obviously there are some very bad choices in the chart above. So a seemingly good 16K database block size might actually result in total 25 IO requests across the virtualization infrastructure. While it’s still effectively just 16K being read, the extra overheads added by this mismatch only serve to multiply the bad effects. So choose wisely.

Oracle + VMware = Success Part II

Bert Scalzo — Wed, 22 Oct 2008 10:40:00 GMT

Last week I wrote about how simply adjusting various virtualized infrastructure default settings that a database could perform up to 440% better. Of course “your mileage” will vary, but the key point is that taking the time to properly layer your virtual infrastructure settings can have substantial impacts – which are only magnified by the net or cumulative effect of a virtual server hosting multiple database servers. And while my example was specific to Oracle, the same principles generally apply for any database being deployed on a virtual server.

This week I’m going to elaborate on just one of the techniques I used to get such stellar results. If you refer back to the prior blog, I’m going to explain what choices #5 and #9 are. As a reminder:

#5 = Change VM host registry settings to improve file system IO for databases
#9 = Change VM client file system settings to improve IO performance for databases

In reality, this is one and the same improvement simply being applied to two different virtualization levels: the host and each client. Thus I’m simply going to present how to accomplish this technique for both Windows and Linux, and then leave it to the reader to make sure to apply it properly across all their various virtualization layers.

Both the Windows NTFS and Linux ext2/3 file systems maintain multiple “meta-data” information related to file access – such as date created, last time updated, etc. So an IO request might actually generate multiple physical IO’s – one for the data file and one or more for updating the related meta-data. In the case of the VMware server, we really don’t care to keep OS detailed file system information about the hosted clients’ data file access – it’s simply neither useful nor critical (unless doing snapshots). And at the Oracle database level, we know Oracle accesses its files every so many seconds – so why pay to update the meta-data with that information. Oracle keeps its own timestamps (i.e. SCN) in the file headers.

For Windows, we simply adjust the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\
Control\FileSystem\NtfsDisableLastAccessUpdate = 1

By the way, Toad offers a screen to do this for Windows:

For Linux, there are several ways to accomplish the same result. We can set the attribute for the individual Oracle files as follows:

chattr +A file_name

Or we can do it for an entire directory:

chattr –R +A directory_name

However the best method (because it automatically handles any file additions) is to edit the /etc/fstab and add the NOATIME attribute:

/dev/sda6    /              ext3    defaults,noatime    1 1
/dev/sda1    /boot          ext2    defaults,noatime    1 2
/dev/cdrom   /mnt/cdrom     iso9660 noauto,owner,ro     0 0
/dev/sda5    swap           swap    defaults            0 0

Many people ask if I know similar settings for other operating systems such as AIX, HPUX and Solaris – I don’t. But please reply and share that information.

Oracle + VMware = Success

Bert Scalzo — Tue, 14 Oct 2008 22:59:00 GMT

Many people swore that database on a virtual machine would not fly – or at best, would be accepted very, very slowly. I was not one of those people – so I wrote a new book on just that subject: Oracle on VMware: Expert tips for Database Virtualization. However I don’t expect people in these tough economic times to just go out and buy every new book, so I thought I’d share some other papers I’ve seen worth reading on the subject – plus a brief synopsis on the Oracle + VMware presentations I did this year at Oracle Open World.

One of the first and most robust papers I’ve seen on the subject is from VMware itself: Deployment of Oracle Databases on VMware Infrastructure. This fine paper is 119 pages of highly useful DBA information for both effectively and efficiently deploying Oracle databases on a VMware infrastructure. For those who don’t have the funds for my book, this is a darn good choice. And even if you get my book, the paper makes an excellent companion.

Now at first Oracle was a little slow to fully embrace databases on VM’s. But then they débuted their own virtual machine solution – and things changed. There are now papers that show that benchmarks of VM’s versus bare metal are reaching acceptable results. There are even papers now for successfully deploying RAC on VM’s. So the trend winds have changed. Virtualization is no longer verboten for databases – and I for one think the world is better for it.

So at Oracle Open World, I presented some materials to show that deploying Oracle on VMware with “defaults across the board” could cost you as much as 440% in terms of achievable performance. However, we must approach configuration, optimization and tuning in a slightly different manner – because we now must fully consider the four core shared resources shown here:

So by using Quest tools

Benchmark Factory to generate a database load
Toad for Oracle with DB Admin, Spotlight for Oracle and Performance Analysis for Oracle to monitor and diagnose the database performance
Foglight for VMware to monitor and diagnose virtual machine performance

I was able to easily modify all the default settings across the board to obtain that 440% improvement for the TPC-C OLTP benchmark (your mileage will of course vary). See below how the response time for 300 concurrent users decreased from 1080 ms to 200. And while 1080 ms (i.e. 1 second) may have been acceptable, what would happen if the user load increased to 1000 or more users. Obviously the tuned scenario would scale much better to handle a much higher concurrent user load.

So what black magic was applied to get these results? The ten simple steps listed below (note that the VM server was running Windows 2003 Server, but similar issues would also apply had it been Linux or some other OS):

Obtain a baseline test for relative comparisons
On VM host, exclude VM clients from active, online virus scans
Remove Windows Indexing Service – because don’t need fast file searches
Remove other extraneous Windows services
Change VM host registry settings to improve file system IO for databases
Optimize the VM host configuration and options
Optimize the VM client OS configuration and options for Oracle database
Remove other extraneous VM client OS services or daemons
Change VM client file system settings to improve IO performance for databases
Adjust VM client file system block size to more closely match Oracle block size

If you’re curious about #’s 5, 9 and 10 – I’ll write about those in an upcoming blog.

RAC the Casba

Bert Scalzo — Wed, 17 Sep 2008 13:44:00 GMT

On many occasions, people will email or call me to get help with perceived RAC connectivity issues for various client applications – Toad and other Quest products included. Now some times they may have setups that we’ve not pre-tested, so they have a potential bug – or at least another regression testing scenario for us. But many times, it’s simply a SQL*Net configuration issue. While it’s easy to draw a simple diagram of what RAC is, it’s almost an art form to write RAC “tnsnames.ora” files that work as intended. So I figured a quick refresher on RAC and SQL*Net configuration files might be useful.

Let’s start by drawing a picture of the basic concept of an Oracle Real Application Cluster (a.k.a. RAC). RAC is essentially a group of database servers and Oracle instances running as a collective group so as to appear as a single server and database to the outside world. Thus to all the end users, web servers and application servers out there – there is really just the RAC server and database. The fact that it’s a group performing as a cluster is really transparent and unimportant – except the correct tnsnsmes.ora files J

So what should the tnsnames.ora file look like? Let’s show an example where the RAC database has just two servers and instances: RAC1 and RAC2.

RAC =
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1-vip)(PORT=1521))
    (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2-vip)(PORT=1521))
    (LOAD_BALANCE = YES)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
 )

RAC1 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac1)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = RAC)
            (INSTANCE_NAME = RAC1)
        )
    )

RAC2 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL=TCP)(HOST=linux_rac2)(PORT=1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = RAC)
            (INSTANCE_NAME = RAC2)
        )
    )

So let’s use Toad as an example – how should we connect to the database? Generally speaking, probably 98% of the time we should simply specify the RAC instance/service name and let Oracle load balance us to whichever node has capacity. Only when we need to debug PL/SQL should Toad users (because of an Oracle limitation/requirement) specify connecting to a specific node – such as RAC1. That’s because the Oracle debugger API needs to create two sessions on the same node so one can watch the other. If we tried to debug using RAC, it might load balance the two sessions onto different nodes – and then we’d get a debugger scoping error. But for all other times we should simply use RAC to connect – period.

Finally, look at the RAC entries for the TCP host addresses. Why do they say node-vip instead of just node as with the RAC1 and RAC2 corresponding entries? What is the VIP portion? Let’s look at my PC’s Windows hosts file:

192.168.100.104   linux_rac1
192.168.100.105   linux_rac1-vip
192.168.200.104   linux_rac1-priv
192.168.100.106   linux_rac2
192.168.100.107   linux_rac2-vip
192.168.200.106   linux_rac2-priv

Why are there three IP addresses per database server? Look back to our RAC architecture diagram above. The RAC instances require two distinct TCP/IP addresses, one for the public network (e.g. linux_rac1) and one for the private network (linux-rac1-priv). So what exactly are the seemingly extra entries for linux_rac1-vip and linux_rac2-vip, and why do we use them as our addresses for the RAC entry in our tnsnames.ora file?

When the DBA sets up the cluster nodes, one of the steps involved creating virtual IP addresses. That’s what these entries point to. A virtual IP address is the path by which users should access the node, and whenever a node crashes – Oracle can dynamically reassign the virtual IP address to point to one of the other nodes in the cluster. That way we have transparent application failover (TAF) – because all the network access is virtualized and dynamic. Hence nodes can come and go, or be physically relocated – and to the database nothing has changed. Only the Oracle listeners on the database servers need be aware of this dynamic addressing scheme.

Speed Up Toad - Updated

Bert Scalzo — Wed, 10 Sep 2008 11:38:00 GMT

It’s not uncommon for software to increase in install size and memory footprint over the years – and Toad is no exception. But, that does not mean that these newer versions have to run more slowly. We take great pride in keeping Toad expedient as it matures. Thus do not simply assume that if your Toad is running slower these days – that it’s just the “cost of progress”. Most often there are simple steps to keeping Toad humming along.

Below are ten easy ways to make sure you’re getting the best productivity from Toad:

Run the Toad Advisor – and see what it turns up. Often the “low hanging fruits” it points out can make a significant performance improvement for the few seconds it takes to run and examine.
Make sure your Oracle data dictionary (i.e. catalog) statistics are appropriate for your database version. So if your Oracle database version is 9i or less, there should be NO statistics collected for SYS or SYSTEM. And if your Oracle database version is 10g or greater, then there must be statistics gathered for SYS and SYSTEM. And note that Oracle 10g does NOT do this automatically for you. Unfortunately, the automatic 10g “stats job” does everything except these schemas. See the following for more info:

http://asktoad.com/DWiki/doku.php/faq/answers/database_versions#why_can_toad_sometimes_seem_to_run_slower_on_10g

http://www.toadworld.com/Community/ExpertsBlog/tabid/67/EntryID/135/Default.aspx
Make sure the option for “Highlight Table Names” is off – as this setting requires Toad to load all database table names into memory. This could lead to excessive memory consumption and slow connection times. This is probably the single most often reason people seem to experience slowness – especially on larger databases.
Verify that your option for “SQL Recall: Statements to Save” is set to a reasonable size. Toad has to read and parse the XML file /user files/SAVEDSQL.XML every time it starts up. XML by nature is not too brief, so having a large saved statement size can easily result in an overly big XML file. You may even need to truncate or replace that XML file if your initial setting is too high and results in too big a file. NOTE – Toad has reworked the way is does saved SQL, so this basically only applies to Toad 9.5 and before.
Watch out for “network drives”!!! If you have network drives in your Windows path, SQL path, or any of the Toad options that write to files – and especially the ones that do so automatically at timed intervals (e.g. save settings every, backup editor every, etc) – then you’re just asking for things to run slowly.
Review the editor option for auto-save timing (i.e. backup every N minutes) and think about how many editor windows and/or tabs that you routinely work with. I’ve seen where power users (those who have dozens of editor windows and/or tabs open) will see a noticeable hiccup every so many minutes – and wonder why.
Review your general options setting for OCI Array Buffer Size. It defaults to 500. If you’ve set it lower or higher, you be sending excessive network packets (where the # of network packets = amount data returned from database server to Toad / OCI Array Buffer Size). If you’ve set it higher, you may run into PC limitations depending upon your machine’s memory – and the number of applications you run concurrently.
Review your compile option for Compile Body and Spec as a Pair. Remember that for many versions of Oracle, when you compile the Spec you also cause an Oracle internal dependency recompilation flag to be set. Thus in Toad if you’re compiling the dependencies, you may be creating more work for yourself. Plus, you might be affecting others’ work. Be a good compilation citizen.
Review your Schema Browser Data & Grid option setting for Enable FK Lookup. If you have numerous foreign keys, if the foreign keys involve many columns, or if the lookup tables are really big – these can slow down the performance of the data grid.
Review your Schema Browser Data & Grid option setting for Use No Parallel Hint. When tables and/or databases have a default parallel degree setting, then the Schema Browser Data Grid browsing will use parallel queries. That means you’ll spawn lots of server processes to do a simple task – plus Oracle persists those processes so as to save on the creation/destruction costs. Checking this box can often reduce database server process resource consumption – and like chicken soup, it won’t hurt J