The joint IOUG and OAUG Collaborate 2009 conference is a just over two months away. Not only is Collaborate a premier event for Oracle database technology and applications technical sessions, it’s a good format for hobnobbing with fellow database professionals.

 

This year’s event is being held Sunday, May 3rd, through Thursday, May 7th, at the Orlando convention center. I’m taking my family along and fully intend...

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”...

The Oracle 11g database is nothing short of a modern technical marvel. From the features it offers to the many platforms it runs upon – it’s amazing how far they’ve come. But as a “true enterprise” database, Oracle 11g requires some DBA oversight to be applied when creating databases. Although Oracle supplies a very simple and yet fully capable database creation assistant (DBCA) utility, one needs to use it intelligently. AS I tried to point out in a prior blog, Oracle 11g on a notebook: Tread Lightly, creating an Oracle 11g database on a notebook is not ideally done with DBCA defaults. So, I thought I’d walk you through my basic recommendations for creating a light weight Oracle 11g database.

...

We don’t get this particular question per se as often as one would expect, but we do see enough user problems where this fundamental issue becomes the underlying question to answer for problem resolution. Below is a chart for the release dates for both Toad and Oracle. The “blue highlighted” dates are when Oracle first releases a major new version, such as Oracle 10g in January of 2004. But it’s the “red highlighted” dates...

Oracle explain plans – they are considered by many as critical when optimizing or tuning SQL statements (I however prefer to look at trace data in conjunction with explain plans). For those who prefer primarily to rely on explain plan interpretation, comprehension and improvement – Toad offers numerous capabilities to make the entire process simpler and more productive. Because let’s face it, explain plans are like much proofs...

I’ve got Toad Freeware installed and running on my Ubuntu 8.10 Linux machine – and so far, not a single problem has yet occurred (knock on wood).

 

Here are the basic steps that I had to perform in order to “Make it so” (as Star Trek Next Generation’s Captain Piccard would say):

Install Ubuntu with Wine – make sure that the Wine version is latest and greatest...

Many business analysts rely heavily upon Microsoft Excel as one of their primary tools for manipulating and reporting upon their business data. Sometimes they will send data extraction requests to their information systems (IS) people to perform and then deliver, sometimes they’ll instead access it themselves in place via the external data interface within Excel, and sometimes they’ll extract the data themselves from the Oracle...

Lately I’ve been seeing what I think might be an interesting yet disturbing new trend: whenever there’s a problem that initially seems unusual and/or unexplainable, it’s not uncommon to quickly question virtualization as a potential source of the issue. I fully understand that virtualization adds a new variable to the basic equation – and represents greater complexity due to more moving parts. But it seems to be somewhat rushed...

A few weeks ago I wrote Part III of this series – and fortunately (or unfortunately for my poor ego) an astute reader wrote to ask for some clarification on my Excel chart. Ends up my Excel formula had a mistake – so many thanks to Johannes V. Djernæs from Miracle A/S in Denmark. So I’ll be...

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....

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...

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...

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...

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...

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...

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...

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...

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...

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...

Last week’s blog was the third in a series about how to make Toad run scripts and/or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I will highlight the fourth and final (???) method of running scripts against multiple databases using the new Toad...

Last week’s blog was the second in a series about how to make Toad run scripts or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I’ll highlight the third method of running scripts against multiple databases using the “Toad Editor” and Dynamic SQL...

Last week’s blog was the first in a series about how to make Toad run scripts or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I’ll highlight the second method of running scripts against databases using the “Toad Project Manager”.   ...

One of the more frequent questions people ask me is why can’t Toad run scripts or commands against multiple databases at once? Of course as usual the answer is Toad already can – so I’m going to dedicate the next couple weeks’ blogs to this topic.    This week I’ll highlight the first method of running scripts for multiple databases using the “Toad Script Manager”. It has been in Toad for a long time now – and it’s available...

When you go through the entire exhaustive process of doing an effective (i.e. accurate) logical model so that you have properly captured all the true business rules and requirements, you sure don’t want your data modeling tool to compromise those findings as you move into the physical model – which is database platform dependent. Yet most data modeling tools do a very poor job of translating certain data modeling constructs...

I have been doing Oracle work for just over two decades – and I’ve probably written thousands of SQL scripts. Many of those were DBA oriented in their task nature, but even application development presents times where SQL scripts are used. For example, the need may arise for the operating systems’ or third party schedulers to invoke a shell script to execute a SQL script to execute some PL/SQL code.

 

Historically...

Sometimes, even in today’s world of super GUI’s, a SQL script is still the best way to do something. Plus there are always those people who steadfastly prefer command line to GUI’s no matter what. Can Toad accommodate and even facilitate these needs? Of course you know that the answer is a resounding yes.

 

Let’s say that we want to turn on and off the referential integrity within an entire schema – whose list...

I have always believed that Toad Data Modeler offers the best, easy to use data modeling tool on the market for the money. Now my faith has been confirmed – others are starting to see just what a gem Toad Data Modeler. Take a look at this review. I agree 100% of course – but everything there is true.

 

http://www.databasejournal.com/features/oracle/article.php/3754671

...

A lot of times people will ask Quest tech support or via our Yahoo discussion groups how to make the editor run SQL commands and do stuff more like SQL*Plus or how to better control the output look and feel (i.e. behavior). Along those lines and another very popular question is where one can set an option in Toad for the editor to do something specific (e.g. turn off feedback, etc) – because they have already done an online...

There are quite a few blogs out there about what happened or what’s new from this week’s ODTUG conference in New Orleans. So rather than restate what many others are reporting, I thought I’d try a slightly different approach – to instead report more on what was noticeably absent or different this year. J  

The Oracle Developers’ Tools User Conference (i.e. ODTUG) is, in my opinion, the premier annual event for Oracle...

Next week the annual ODTUG conference will be held in New Orleans. I’ll be attending sessions and visiting vendor booths in order to stay current with the database DBA and development markets (more developer oriented at this particular show). I’ll report my findings and things of interest next week. I especially hope to corner some Oracle folks on the 11g R2 beta – which I’ve already heard rumblings for.

 

Of...

I’m working with our product team to develop a novel idea – and need feedback from you, our customer base. So here comes what may seem like a silly question: How would you like to win a free copy of the Toad DBA Suite for Oracle?  

Let me explain. I’m proposing that we sponsor an Oracle database tuning challenge. The rules would be rather simple: Using the Toad DBA Suite for Oracle’s  many screens, wizards, utilities...

There have been several papers and blogs from Quest on the issues of benchmarking successfully, however the condition persists where people experience trying issues with Benchmark Factory and obtaining satisfactory benchmark results. I still maintain that it’s primarily attributable to a lack of proper and complete precursory work. But people generally seem to need more than a notice to prepare more, so I’ve constructed the...

It’s that time of year once again. The Oracle Developer Tool Users Group (ODTUG) is quickly approaching (June 16th-19th in New Orleans – see below). For Quest, this is a really great show. It’s very well attended by and representative of our Toad customer base. It’s not uncommon for a vast majority of this show’s attendees to be Toad users.

 

This presents a great opportunity for both our customers and Quest....

Sometimes people will compare Toad against low cost competitors or freeware (remember, Toad offers a freeware version as well). The idea being that one can save money by going with a lesser product. That seems fiscally responsible – until you do the math that is. So, let’s work a simple example to demonstrate just how valuable Toad can be to...

There are so many useful little make or break features inside Toad, it’s very often difficult to pick one to highlight. A few weeks ago I started to divulge some things coming in Toad 9.7 (and already in the beta). One item I mentioned was the enhancements to the Database Browser, where most DBA screens and/or utilities...

It’s not uncommon to discover databases that have simply evolved over time rather than having been built according to some overall design or “master plan”. And there seem to be no shortage of answers as to why this occurs. Sometimes DBA’s are just far too busy with a myriad of other tasks. Sometimes developers are under the gun for deliverables so quickly that there’s just not time. Or my favorite, this project started as just a couple new tables and then ballooned into a monster. Whatever the reason, the situation exists far too often in the real world of information systems.

...

When using either Toad’s Stats Pack Browser or AWR Browser screens (both part of the optional DBA module and/or bundle), the plethora of graphs can sometimes be a wee bit overwhelming – as shown in the screen snapshot below. So how is any DBA in an urgent situation or hurry to both successfully and expediently interpret such database activity?

 

That’s where Toad’s basic mantra once again comes into play – making...

I’ve seen some people on competing product discussion boards say things like Toad’s schema browser Is far too bloated – with too many tabs and images sucking up all the precious real-estate. But like anything else in Toad, if you don’t experiment with and utilize all the cool options it offers – you could very easily arrive at this quite mistaken conclusion. So now let’s examine just how one configures the schema browser for...

I know – Toad 9.6 was just released this week – on Aprils Fool’s day no less. But we’ve already been hard at work on the next release (and the beta should start in a few weeks).

 

In last week’s blog, I asked you guys - our customers - to submit ideas for what should be added in Toad 9.7, because as we’ve always said, “Toad is your product, thus you should drive it.” So far though (and to my great disappointment), no one has commented. So I’m going to “prime the pump” by leaking some things prior to the first 9.7 beta, to hopefully stimulate you guys to chime in – and help us to drive Toad to where you want it to go.

...

Recently, my Toad World blog asking whether we needed to update the Toad Handbook generated as many or more replies than all my other past blogs combined. So it obviously seems best to ask you guys – our customers and users – for more opinions and ideas.

 

I’m hoping this week’s post will open up quite a lively exchange. Every time we ask people what they’d like to see in the next version of Toad – we get the stock answer of just fix bugs. And we’ve heard you loud and clear – that remains a key focus for each and every release. But if you had to pick your one new, must have feature – what would it be?

...

I often get asked by both new and exiting customers to aid them in the cost justification process for purchasing Toad or upgrading their licenses. I thought since I get asked this so often, maybe a short blurb on the topic would be generally beneficial.

 

To write this blog, I’m using Microsoft Word. When I’m done, I’ll be emailing it to our Toad World site manager using Microsoft Outlook. Very few people would...

An increasingly common question these days is does Toad support Oracle RAC (i.e. Real Application Clusters)? And if so – how, when and where?

 

HOW: There is nothing special required to support a RAC connection, other than a properly constructed tnsnames.ora file. Probably the best source for a correct RAC aware tnsnames.ora file should be your DBA. But I've included an example below. Some people ask why three...

The Toad Handbook is over four years old now. Even the more recent second edition of the Toad Pocket Reference is over two years old. And there’ve...

One key task for many DBAs is maintaining a database version control or change revision history. Database version control affords the DBA the ability to restore the database to any prior state, or to detect unplanned database revisions that cause the database to be out of compliance with its specification. One could argue that the latter scenario would unlikely happen in a well controlled production environment – however sometimes...

Did you know that Toad’s Database Probe screen is customizable? That’s why it’s vastly superior to the simple Database Monitoring screen. Here’s a basic screen snapshot:       Maybe I don’t think it’s a big deal that the SGA’s REDO LOG buffer can be pretty big these days – especially since it kind of sets itself...

Ever wonder which indexes are being used in your database? Well Oracle 9i introduced the capability to track index usage. But you had to learn new ALTER INDEX command syntax to support this and then how to query V$OBJECT_USAGE view to see the index usage information. Well I’m too darn lazy, and why not be – as with most things in Oracle, Toad cleanly exposes this feature and increases its usability.

 

Let’s say...

Sometimes people will request that Toad have a certain special new feature, often special to the task at hand which they’re trying to accomplish. Keeping in mind my recent Toad blog about SQL*Plus compatibility, there are very often easy ways to use existing Toad features to do what they want.  In fact, many times it’s simply...

Sometimes people will ask why can’t Toad work more like SQL*Plus – and the funny thing is the answer is quite often it does. You really just need to know how SQL*Plus works – and then look for the same feature within Toad. Let’s take a very, very common scenario that gets asked almost every quarter. Look at my SQL*Plus SELECT command in the screen snapshot below. Suppose that I always like to define the six SET commands for...

Last month, Jeff Smith wrote an excellent blog about Toad’s new Stats Pack capabilities.  And as Jeff pointed out, Stats Pack is an older – but free technology. While Oracle’s AWR and ADDM reports are clearly the next generation of performance diagnostics tools – and arguably preferable, you must purchase the OEM Tuning and Diagnostics packs to utilize these facilities. So even though DBA’s can access AWR and ADDM reports either through PL/SQL calls or the Toad DBA GUI – which simplifies access further, many people who use AWR and ADDM may not be properly licensed. Thus Stats Pack may well remain relevant for some time into the future due to its cost.

...

There are many different types of Toad® users, and since job titles and descriptions vary so widely, it’s tough to say a typical Toad user looks like this and thus needs this. Thus Toad has evolved over time to meet the ever growing needs of our customers. Quest has two key Toad Yahoo discussion groups to facilitate the evolution and revolution of Toad The first group, groups.yahoo.com/group/toad, provides a useful and...

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...

When we create a super-and-sub type (a.k.a. Inheritance) entity relationship in a logical data model, it’s very clear what the business requirements being communicated are – that different subsets of attributes comprise the “cumulative entity” being modeled depending upon the business context.

So the Toad Data Modeler 3.0 logical diagram shown below relates that Entity1 always has the parent attributes of E1_Attribute1-4,...

This blog posting is not meant to demean or slander the Oracle database in anyway. I’ve staked almost my entire career on Oracle technologies – and I intend to finish these last 7-10 years doing more Oracle stuff. To quote a funny Saturday Night Live skit: “Oracle has been very, very good to me.” So my comments are more about how “uninformed” installation of the latest Oracle release on a notebook could lead to negative surprises. 

...

Probably one of the most useful and frequented tasks performed with Toad® for Oracle is saving data to a file, such as comma delimited text (or any of the other numerous formats supported). It’s a relatively easy feature to find – you simply depress the “right hand mouse” (RHM) while anywhere within the displayed data grid and choose the “save as” option as shown below, but what if you could make that save operation run 10X...

Oracle’s Parallel Query Option (PQO) is a fantastic tool, but much like any good tool, it can very easily be used in the wrong situation or simply abused. In those cases, PQO can actually make database performance much worse. Let’s examine some common misuses or misperceptions regarding optimal and efficient PQO usage.

1.      PQO make sense universally on any SMP or multi-processer database server 

Not really....

OK – now that I grabbed your full attention with that snappy title, let’s see just how to run the Toad® on your Linux desktop or notebook. Let’s say that you’re running Redhat, CentOS, SuSE or Ubuntu Linux on your PC, let’s further assume that you would like to run Toad either commercial or freeware on that PC against your local or remote Oracle database. But shoot, Toad is a Windows only program – so what’s a body to do?

...

1. I’m using a tool like Quest’s Benchmark Factory®, so that’s all I need.



Wrong. I highly recommend that anyone doing benchmarking read the specs for whatever industry standard tests they are going to perform. Because software to automate these tests will ask questions or present options that you cannot really define unless you understand their context – which is defined in the spec.



For example,...

Often people will write or call to relate that Toad® seems sluggish when working against Oracle 10g databases – and that creating new connections in particular seems to take an unreasonably long time. They are often especially frustrated since this problem did not occur with Oracle versions prior to 10g. So what is Toad doing that’s wrong (i.e. a bug)?

The short answer is that this is an Oracle 10g DBA best practices...

A common task for DBAs is to create a test or development environment that has a subset of production data for testing and development purposes. As usual, Toad® offers a feature for doing just that – the Data Subset Wizard, found under main menu Database-> Export->Data Subset Wizard.



There are two common scenarios where...

Last week, Jeff Smith’s blog on the FTP utility in Toad® was very good. He even says at the end that “If you like Toad’s FTP, then I recommend you check out the REXEC and TELNET features as well.” That made me think – it probably would be nice just to see a list of all the UNIX and Linux capabilities within Toad in one article....

Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:

Easier to segregate and read (without mixing up join versus restriction code) Easier to construct join code correctly (especially in the case of “outer” joins) Portable syntax will work on all other ANSI compliant...

As I wrote last week, Oracle 11g has a plethora of really great new features. One that should really hit a home run with data warehousing DBAs is “Invisible Indexes.” The name is not a joke – they are exactly what they say. An invisible index is ignored by the query optimizer when forming an explain plan for a SELECT statement, but they are fully maintained during DML such as INSERT, UPDATE, DELETE and MERGE.

Let’s...

Oracle 11g has a plethora of great new features. One area that has improved with each of the last four releases has been partitioning, i.e. the method to break larger tables into smaller, more manageable and efficient sub-tables. While it was originally touted as a boon for just the data warehousing world, partitioning nonetheless works well in many situations. With Oracle continuing to improve upon them – partitioning is now a powerful asset in any DBA’s tool belt. So let’s examine some of the new table partitioning techniques introduced with Oracle 11g....

Oracle 11g is out for Linux – and like every new release, there’s tons of cool new stuff. So over the next few weeks, I will write about some of Oracle 11g’s new features. This week, I’ll look at Virtual Columns and Virtual Indexes. 

When designing a relational database, analysts often look at prior systems’ screens and reports for insight. So the database analyst designing the business entity of a “product” might see...

We all know that “a picture is worth a thousand words,” so I provided a few pictures (shown below) to try and briefly yet succinctly explain what ASM is and how it’s different than the previous ways of managing disk space within Oracle.

Traditional Methods To begin, let’s review how DBAs have historically allocated disk space to Oracle – so that we have a baseline to compare against, and thus see how much simpler ASM makes the overall picture, which generally translates to easier setup and management. Figure 1 shows the traditional options.

...

Ever hear people say they miss the good ole days – when times and things were much simpler than now? Well the same is true for Oracle DBAs, the early 90’s (circa 1993) were much simpler in terms of hardware selection for an Oracle server as summarized below.

CPU

Architecture

CPU

Family

...

I use Toad® for Oracle almost everyday to tune and optimize problematic databases for customers, partners and friends. And I noticed that I followed a pattern – which seemed consistent across all such attempts. And although I wrote the whitepaper “Maximize Database Performance via Toad”, in hindsight it...

One of the most widely recognized and often quoted database benchmarks is the TPC-C. For over 14 years, the TPC-C has been the industry standard OLTP test; however, it’s very clearly showing its advanced age. The TPC-C does not adequately mimic today’s real-world database workloads, nor does it properly stress the capabilities of today’s hardware and database engines. As such, the TPC-C is quickly loosing favor!

...

Probably the single most painful task in data modeling (or any modeling for that matter) is switching tools. Forget the high direct costs like purchasing licenses and the obvious indirect costs of retraining your staff, because it’s the migration of all your meta-data from one tool to the other that looms large on the horizon of pain. I’ve even witnessed people who will remain on an unsupported tool by a defunct vendor rather...

You’ll have to forgive me for spoofing the famous movie line “We don’t need no stinking badges” (Treasure of the Sierra Madre, 1948 and Blazing Saddles, 1974), it just seemed quite apropos J



I was discussing data modeling needs recently with a potential customer – and like many shops I visit, they saw no need for data modeling. They believed that their DBAs, data administrators and application architects knew...

In this installment I thought I'd write about the exciting, and soon to debut, Toad Data Modeler 3.0 release rather than yet another boring academic or technical topic on data modeling. TDM 3.0 is quite exciting. While it has not yet been made available for public beta – it will be very soon (so please email the product manager John.Pocknell@Quest.com to get signed up). And once...

Over the decades I’ve worked with many data modelers – and I’ve learned something key to building a great model that is not always intuitively obvious: spend at least 50% of your data modeling time on the relationships. Often modelers are so overly concerned with the entities, attributes and unique identifiers or keys they don’t allocate enough time for the proper questioning, construction and review of relationships. Therefore,...

Probably one of the most often discussed and hotly debated topics in both data modeling and database design is that of normalization. Many database designers, architects, DBAs and senior developers have differing positions and/or beliefs on the topic. However quite unfortunately, they often are not communicating with optimal effectiveness due to some fundamental differences in terminology and understanding. The simple solution...

Last month I wrote about “Why Reverse Engineering is Always Worthwhile.” So the logical next issue to examine is forward engineering – its different approaches and its many comparative benefits. The best way to do this is via an analogical example J

I live in Dallas-Fort Worth. Let’s assume that I drove down to Austin at the start of the this year’s college football season to watch my #1 Ohio State Buckeyes play against...

In many shops that I visit, neither the DBA’s nor the database developers place much value on performing data modeling. Often there are seemingly sound reasons for this exclusion: the historically high price of data modeling tools, data models are not required as a project milestone or deliverable, and quite often the database was simply either inherited or supports a third party application – and thus is not open for modification....