Most recent blog entries

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 …

Optimizing SQL Part 4 – Optimizing SQL statements with a VIEW

Richard To — Fri, 21 Nov 2008 13:54:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers optimizing SQL statements that use VIEWs.

In early versions of the Oracle database, a VIEW was handled like a temporary table that stores the temporary data from the VIEW’s SELECT statement. Then the temporary table was joined with the main portion of the SQL statement, so the tuning of a SQL statement that contained a VIEW could be separated into the VIEW’s SQL statement and main SQL statement. In more recent versions of Oracle, the SQL optimizer in Oracle can merge these two SQL statements together before it does further SQL optimization, so, it is now more complicated for you to tune a VIEW SQL statement by hand than before. Quest SQL Optimizer has a “View to Inline View transformation” technique to solve this problem.

The optimization process in Quest SQL Optimizer includes this technique of rewriting the VIEW’s SQL statement when it transforms the syntax of the original SQL statement. So it will automatically do all the work for you.

For example, with this simple SQL statement

SELECT * FROM VIEW_DEPT
WHERE DPT_AVG_SALARY > 40000

that uses the following VIEW.

CREATE OR REPLACE VIEW VIEW_DEPT
    (DPT_ID,
     DPT_NAME,
     DPT_MANAGER,
     DPT_AVG_SALARY)
AS
SELECT "DPT_ID", 
       "DPT_NAME", 
       "DPT_MANAGER", 
       "DPT_AVG_SALARY" 
  FROM DEPARTMENT 
 WHERE DPT_ID IN (SELECT EMP_DEPT 
                    FROM EMPLOYEE 
                   WHERE EMP_ID > 50)

When the VIEW’s SELECT statement is inserted into the original SQL statement, the SQL statement looks like this:

SELECT *
 FROM (SELECT "DPT_ID", 
               "DPT_NAME", 
               "DPT_MANAGER", 
               "DPT_AVG_SALARY" 
          FROM DEPARTMENT 
            WHERE DPT_ID IN (SELECT EMP_DEPT 
                               FROM EMPLOYEE 
                              WHERE EMP_ID > 50))
 WHERE DPT_AVG_SALARY > 40000

The original SQL statement and the SQL statement with the VIEW inserted will both be rewritten by the Quest SQL Optimizer to generate all the possible SQL statements which produce the same results as the original SQL statement. Some of the SQL alternatives will not include the VIEW’s SQL and others will have the VIEW’s SQL rewritten.

An example of one of the SQL alternatives is:

SELECT * 
  FROM (SELECT DPT_ID,
               DPT_NAME,
               DPT_MANAGER,
               DPT_AVG_SALARY
          FROM DEPARTMENT 
         WHERE EXISTS (SELECT 'X' 
                         FROM EMPLOYEE 
                        WHERE EMP_ID > 50 
                          AND EMP_DEPT = DEPARTMENT.DPT_ID))
 WHERE DPT_AVG_SALARY > 40000

You can control whether the VIEW’s SQL is rewritten along with the original SQL statement with the Transform view to inline view setting in the Options. Quest SQL Optimizer will also transform a VIEW that is being used by another VIEW. You control how many levels (VIEWs within VIEWs) are included when the original SQL is rewritten by specifying the Transformation levels setting.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

F-what?

Jeff Smith — Thu, 20 Nov 2008 17:10:00 GMT

After working with Toad for almost a decade (I think I first saw it as a new grad in ’99…), I tend to start taking things for granted when I do demonstrations for customers or present at conferences on the beauty of Toad. It’s very easy to get excited about CRUD Matrices and the beauty of PL/SQL debuggers, but overlook the critical core features that users often miss out on.

Well, ‘overlook’ is such a critical, judgmental word. Instead, let’s say that users often don’t realize the power that lies at their hands via the keyboard shortcuts in Toad. I want to take 20 minutes or so to write about my favorite ‘F’ key: F4 DESC.

After doing the ‘Toad-demo-thing’ for some time now, I’ve come to discover that most of our users are not aware of this feature. In fact, many people are not even aware of the SQL*Plus ‘DESC’ or ‘DESCRIBE’ command. Let’s take a quick look at it in action:

SQL*Plus shows the table structure – Column Names, Nullability, and Data type.

I know, this isn’t exactly earth-shattering. Toad has had such a feature for quite a long time. You can either type ‘DESC [object]’ in the editor or execute with F9 (using F5 will show the above information), or you can simply put your mouse-cursor on the object you want described and hit ‘F4’.

Toad’s ‘DESC’ popup window for a table object.

A few key observations:

You can ‘DESC’ ANY object or object type in the database – users, roles, tablespaces, stored procedures, directories, etc. SQL*Plus only allows you to ‘DESCRIBE’ data objects like tables and views.
The popup describe window is a miniature Schema Browser window that just contains the information for that object. So not only do you get the column information, you get everything else too!
The full power of the Schema Browser is available here. Try mouse-right-clicking in the popup window.
You can have more than one open at a time, so if you want help with a query, like looking at opposing data or even updating the data as you write the selects in the data grids, you can easily do so.
You can drag table/view column headers into the editor space (just like the Object Palette)
This is the most frequently borrowed feature in the database IDE world. Try using ‘F4’ in another database tool to see if they too have seen the wisdom of the Toad J
If you want to see something even cooler, instead of pressing ‘F4’, try ‘SHIFT’+’F4’

Enjoy!

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

Optimizing SQL Part 3– Generating more SQL alternatives for simple SQL statements

Richard To — Thu, 06 Nov 2008 16:10:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle. It covers how to generate more SQL alternatives.

When you optimize a simple SQL statement, Quest SQL Optimizer may only generate a few SQL alternatives using the default settings in the Optimizer options. It is very likely that many more alternatives can be generated, but they did not get generated because of the way the options were set.

The options settings in Quest SQL Optimizer for Oracle enable you to select which Oracle optimization hints are applied, include or exclude a few of the syntax transformation rules that are used, select how many driving paths will be tried, and increase or decrease the number of SQL alternatives generated.

We’ll take a look at how you can do this using the Optimizer options. These options apply to the optimization process in both the Tuning Lab and the Batch Optimizer.

To open the Options window, click Options .
Select Tuning Lab | Optimizer | Intelligence.
By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4. Change the intelligence level to 10.
Level 10 selects all of the Optimization options, all of the Oracle optimization hints, and sets the quotas for the number of SQL statements that will be generated very high.

Applying Level 10 to your original SQL statement maximizes the possibility of finding a better alternative for you.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

Getting to know the Query Builder in Toad for Data Analysis

Daniel Norwood — Wed, 05 Nov 2008 22:04:00 GMT

Query Builder is the heart and soul of Toad for Data Analysis. We've spent lots of time and effort working to make it very useful and intuitive. Recently, in version 2.0 of Toad for Data Analysis, we introduced a few new features including reverse engineering SQL to a Query Builder model, creation of DDL scripts and better connection awareness. The linked doc is a very nice write up that Shawn Pickett (Senior Developer) and Alan Bala (Development Team Lead) have put together.

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

Unicode Migration

Johannes Ahrends — Mon, 03 Nov 2008 17:24:00 GMT

Migrating to a database that supports Unicode is a challenge. You are safe if you are currently using US7ASCII for you Oracle database because Oracle provides a mechanism with ALTER DATABASE SET CHARACTERSET UTF8 which allows you by just bouncing the database to switch to this new character set. But what if you are currently on a different character set? In Europe we have a bunch of WE8 (Western European) or EE8 (Eastern European) character sets and as they are not a strict subset of Unicode the ALTER Database does not work. So the only chance is to export the whole database, create a new one (with UTF8 or similar) and import it again.

Is it that easy? No! The reason is that in most databases the so called length semantics hasn’t been used while creating a table. Since Oracle9i the correct syntax for creating is:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6 CHAR))

Have you used this syntax? No of course not! We all used the old version:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6))

But what is the difference? The default behavior for an Oracle database is to use “BYTE” as length semantic. And this can cause huge problems while migrating to Unicode.

Let’s assume your current database is working with WE8MSWIN1252 (common on western European Windows Databases). Entering the following name into the customer table is safe:

INSERT INTO customer (custid, lastname)
VALUES
(1,’Müller’);
COMMIT;

If you try to enter the same row in an Oracle database with AL32UTF8 you get the following error message:

ERROR at line 2:
ORA-12899: value too large for column "DEMO"."TEST1"."LASTNAME" (actual: 7, maximum: 6)

The reason is that a German umlaut takes two bytes in Unicode as well as most special characters for other languages.

When using Oracle export/import or datapump the situation doesn’t change because both utilities are using the exported format while importing into an new database.

The only chance is to first create the objects and then do the import.

Toad has a very nice switch under options -> data types which is called “Include Byte/Char spec when creating DDL scripts from 9i databases”. This behavior is not limited to 9i but all existing Oracle versions you can switch off the “BYTE or CHAR” specification for all scripts.

Now you can generate the schema script (under menu export) for all schemas you want to recreate and execute a user export or full export of the database.

On your target you first create the user (maybe with CREATE LIKE…) using Toad and load your generated schema script. Before you execute it ,please add one command to the script:

ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

This will switch the actual session into a mode where the default length semantic is now CHAR instead of BYTE. This works for a session as well as for the database but there are several known bugs if you switch it for the database so better only use it for the actual session.

The test table is now created like:

CREATE TABLE test1
(custid   NUMBER(10),
lastname VARCHAR2(6 CHAR));

which allows you to insert the lastname “Müller” now.

After all objects are successfully created you can run the import for the table data but don’t forget to switch on “IGNORE ERRORS” as the “CREATE TABLE” scripts are included in the export file.