Bert Scalzo's Blog

Toad Data Modeler Hits a Home Run!!!

Bert Scalzo — Wed, 02 Jul 2008 13:28:00 GMT

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

So if you need a data modeling tool – take Toad Data Modeler for a test drive. You won’t be sorry J

Making Toad Do What You Want

Bert Scalzo — Wed, 25 Jun 2008 16:55:00 GMT

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 help search and didn’t find anything. The good news is that both are already in there and actually handled the same way Oracle implements this feature, which Toad fully supports – but you do have to know how Oracle does it to effectively utilize Toad’s capability. Thus here’s some additional info to help solve such questions.

Look at my query below – pretty simple, but the “execute as script” (F5) output is pretty ugly. Let’s say that I want to space the columns more, set my line size, trim trailing spaces, convert tabs to spaces, and turn off the feedback (i.e. the “rows selected” message). Looking in the Toad options – one will not find these. But it is in there – so please read on.

So how would one normally fix this in SQL*Plus? The answer is by simply adding a bunch of SQL*Plus SET commands to the script – as shown here.

Now see how much easier we can read the output. Yes – we may need to scroll left and right, but we can quite easily now read the results. But I’m as lazy as they come. I don’t want to have to type those five commands every time I work on a script – but remember, there weren’t any obvious Toad options to control this. Hmmmmm – what to do, what to do …..

We do exactly as we would have in SQL*Plus, we create a LOGIN.SQL script and place the SQL*Plus SET commands in there – thus we are making use of the SQL*Plus environment “login scripting” to solve this problem. Now search again on Toad’s online help for “login” – and you’ll arrive at Toad’s login scripting options shown here.

If I simply press the edit button for login.sql script, enter those five SQL*Plus SET commands, and then check the box to execute the login scripts –re-executing the original query as a script t will now get the same output as the New & Improved version – as shown here:

As to the difference between GLOGIN.SQL and LOGIN.SQL – SQL*Plus provides two automatic script environmental setup options. LOGIN.SQL is for a particular user and is loaded from one of their local directories, whereas GLOGIN.SQL is more for departmental or enterprise wide standards and usually kept on shared file servers.

ODTUG Post Mortem

Bert Scalzo — Thu, 19 Jun 2008 13:22:00 GMT

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 database application developers. It’s not entirely a “marketing event” as Oracle Open World can sometimes feel. The technical sessions are specific and useful to the attendees – and the attending vendors generally have a very direct appeal to the developer oriented audience.

But there were some fairly notably absent Oracle tools vendors again this year – BMC, CA, IKAN, Confio, Embarcadero and Allround Automations to name a few. These vendors generally have wares of interest to the developer oriented ODTUG audience – but they’ve seemingly written off the show. ODTUG may only get about 500-600 attendees, but these are the people in the trenches who use such tools. Quest has always gone to ODTUG – even though 80+% of the attendees are already Toad customers. Attendance is not about generating new leads. We recognize the value of this conference and its attendees – so we’re there every year. If nothing else, it’s lets us rub shoulders with our key constituency. J

Finally, EMC had a booth this year – which totally surprised me. I’m glad to see them there, but it was still a surprise …

The Database “Big Easy”

Bert Scalzo — Wed, 11 Jun 2008 13:43:00 GMT

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 course Toad 9.6 is still relatively quite new, but we’ll also be pre-discussing some Toad 9.7 directions – as well as showcasing some of those features that have already been completed in the beta program (licensed Toad customers can join the Toad beta program for free by subscribing to the “Toad for Oracle Beta” Yahoo discussion group). The only requirement is that you are current on your Toad maintenance, which Toad verifies by checking for a current release on your PC. So this is not a way to get free upgrades. J

I think the most exciting thing for our booth will be to show people this year’s official new Toad bundle – “Toad DBA Suite for Oracle”. For one very reasonable price, this bundle includes all the best tools that Quest has to offer: Toad for Oracle Xpert (includes Quest SQL Optimizer for Oracle) with the DB Admin Module, Toad Data Modeler for ER diagramming, Benchmark Factory for stress testing, and Spotlight for Oracle – a client licensed version that permits users to connect to an unlimited number of databases (just one at a time though). I honestly believe the Spotlight alone in this bundle justifies the cost – with all the other great stuff essentially being free. If you’ve not seen this bundle yet – please do stop by our booth for a live demo.

Finally, I’m sure some people will be attending Sue Harper’s (Oracle product manager for SQL Developer) sessions to see what if anything their latest release has to offer. Don’t worry though, I’ll be there taking ample notes too to cover all our Toad customers’ needs. We don’t want to have them offer you anything we don’t or can’t. Myself – I’m hoping to continue to see SQL Developer discussion forum entries such as the following:

http://forums.oracle.com/forums/thread.jspa?messageID=2567106�

I’m the first to embrace competition as being good for customers – I just like to stay on the winning side of that equation J

How about a free copy of Toad DBA Suite for Oracle?

Bert Scalzo — Fri, 06 Jun 2008 13:20:00 GMT

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 and bundled offerings, entrants must email in their optimized database performance results as measured by the competition’s Benchmark Factory predefined project, and a brief write-up of the steps used to achieve those results. We’d then select the winner based upon inspection and analysis of that info. The winner would receive the following:

Recognition on Toad World and Quest newsletters as the “Toad Tuning Guru” of the year
A free copy of the Toad DBA Suite for Oracleand the first year’s maintenance
Database optimization results quoted in marketing presentations, webcasts and literature

The competition test database would be delivered as a VMware image with CentOS 4.5 Linux and Oracle 10g Xpress – both of which are free. The zipped up image file would be around a one GB download. The database and operating system would be highly sub-optimized. The idea would be to use the Toad DBA Suite for Oracle to obtain the highest transactions per second and average lowest response time using Toad. And if you’re not currently a Toad customer, you could use the free trial download to participate.

The only challenge for me will to build a Benchmark Factory project that scales the results based upon the hardware it’s utilized against. If nothing else, I could always just simply reply each entrant’s results on a standardized configuration for verification. Either way, I think the idea is technically feasible.

So – would people be interested in such a challenge and the chance to win a free copy of the Toad DBA Suite for Oracle?

Database Benchmarking Preparatory Checklist

Bert Scalzo — Wed, 28 May 2008 12:20:00 GMT

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 checklist below. While you may lack the privileges to define some of these, you better be able to answer or address all the questions/issues. Otherwise you’re simply increasing the likelihood of getting suboptimal benchmark results.

Basics

Architectural diagram of the database server, network and IO setup
Reviewed the official benchmark specification to fully understand the test – critical step!!!
Defined goals for satisfactory benchmark performance (i.e. TPS, Average Transaction Time, IO throughput, CPU utilization, memory consumption, network utilization, swapping level, etc)
Verified the assumed capacity of each and every hardware component (i.e. manually check to guarantee that each component properly configured to deliver expected performance results)
Select a database benchmarking software tool (e.g. Benchmark Factory) to automate the chosen benchmark’s base object creation, data loading and concurrent user workload execution
Select a database monitoring/diagnostic tool (e.g. Foglight, Spotlight on Oracle, SQL Server, RAC) to observe the effective database throughput and to identify potential bottlenecks or areas of concern
Select an operating system monitoring/diagnostic tool (e.g. Foglight, Spotlight on Linux, Windows) to observe the effective OS throughput and to identify potential bottlenecks or areas of concern
Select a database performance resolution/corrective tool (Foglight, Performance Analysis for Oracle, SQL Server, or SQL Optimizer) to proactively advocate potential work-around or corrective actions to improve overall performance

Storage

Number of storage arrays being used
Are the storage arrays virtualized or shared
Storage array nature (i.e. SAN, NAS, iSCSI, NFS, etc)
Storage array connective bandwidth per storage array and total
Amount of cache memory per storage array and total
How many spindles per storage array and total
Number of processors per storage array and total
Amount of memory cache per storage array and total
Sorage array caching allocation settings read vs. write
Storage array caching size/algorithm for read-ahead settings
Nature, size, speed and cache of disks per storage array and total
Number of LUN’s available for usage per storage array and total
RAID level, stripe width and stripe size/length of the LUN’s

Servers

Number of database servers being used (usually one, unless clustering or replicating)
Are the database servers virtualized or shared
Database server architecture/nature (i.e. uni-processor, SMP, DSM, NUMA, ccNUMA, etc)
Database server CPU word-size and architecture/nature (i.e. RISC vs. CISC)
Database server CPU physical count (slots) per database server and total
Database server CPU logical count (cores) per database server and total
Database server CPU speed and cache per logical unit (core)
Hyper-threading turned off if it is available – critical, otherwise will negatively skew results
Amount, type and speed of RAM per database server and total
Number and throughput of HBA’s per database server and total
HBA interconnect nature and speed (i.e. fiber, infiniband, 1GB Ethernet, 10GB Ethernet, etc)
Number and throughput of NIC’s per database server and total
Database server interconnect nature and speed (if clustering or replicating)

Operating System

Operating system word-size
Operating system basic optimization parameters set or tuned
Operating system database optimization parameters set or tuned
Disk array and inter-node Ethernet NIC’s set to utilize jumbo frames

Network

Matching cabling and switch/router throughput to fully leverage NIC’s
Disk array and inter-node Ethernet switches set to utilize jumbo frames
Disk array and inter-node paths on private networks or private VLAN’s

Database

Database word-size
Database basic optimization parameters set or tuned
Database optimization parameters set or tuned for specific benchmark

Benchmark Factory

Using most recent Benchmark Factory software version available (i.e. currently 5.5.1)
Using the best available database driver for that database platform (native vs. ODBC)
Starting a number of agents = max total concurrent users for the benchmark / 900
Place no more than four concurrent agents for the same test on a single app server
Customize the Benchmark Factory project meta-data for your specific needs:
- Partition or cluster tables
- Partition or cluster indexes
- Collect optimizer statistics
- Collect performance snapshot (e.g. Oracle Stats Pack or AWR snapshot)
- Run the workload
- Collect performance snapshot (e.g. Oracle Stats Pack or AWR snapshot)

ODTUG is just around the corner

Bert Scalzo — Wed, 21 May 2008 11:50:00 GMT

It’s that time of year once again. The Oracle Developer Tool Users Group (ODTUG) is quickly approaching (June 16^th-19^th 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. We always have the Toad product manager and me at this conference to gather customer feedback for product enhancement requests s and improvements. So please stop by our booth or presentations to engage us. Remember, we actually learn more about what we should do from you – our customers. While we have a fantastic development team and me designing new features, you guys know far better what you need and want.

Plus we also like to demonstrate some upcoming features (usually already available in the beta) and discuss some product roadmap ideas. It’s a great opportunity to become involved with where Toad might or should go. So please stop by. We want and need your input. :-)

Toad Saves Mucho Dinero

Bert Scalzo — Wed, 14 May 2008 12:15:00 GMT

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

The table below shows various resource types, the percentage of time they do database related work, the relative productivity increase by using a GUI tool, the percentage of Toad’s superiority to the other low cost competitors or freeware, and the annual net benefit of using Toad versus those lesser tools.

So with Toad starting at a mere $870 – why would anyone choose to work without it? And I’ve very purposefully chosen very, very conservative numbers. Since so many Toad users fanatically tell us they love Toad and could not do their jobs without it – the actual numbers could more realistically be as high as the following table.

So even in a small company with just five developers, one DBA, two database analysts, and just one of each manager type – the annual savings from using Toad amounts to nearly $84,000 dollars per year. And remember, that’s the additional savings of using Toad in lieu of lesser products! Therefore Toad’s meager premium over lesser products is quite easily justified.

Yet another Little Toad Gem

Bert Scalzo — Wed, 07 May 2008 13:23:00 GMT

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 can now be launched via a right hand mouse menu option. This can almost eliminate the need to launch those items from the Main Menu->Database list of menu options. But the Database Browser has been around for a while now – and it has one very cool feature that makes it indispensible!

Have you ever needed to know what resources were being consumed by all the databases on a shared server? Well, the database browser makes short work of that task, and for all of the more useful things one might need to measure. Look at the screen snapshot below. If you either select the server node or multi-select databases under that node, the Database Browser shows all the associated information in the data grid. But look at the area near the bottom circled red – it also shows the cumulative statistics for all those databases. So here I can see that my two databases use 8.6 GB of disk space, of which 7.8 GB are used. And this feature works like this for all the tabs that make sense.

How Data Modeling Can Save Money

Bert Scalzo — Wed, 30 Apr 2008 12:46:00 GMT

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.

Let’s put this into perspective. Would you purchase a brand new house where the builder simply showed you a high level “floor plan”, and then said “trust us” – we’ll just hammer together the lumber and stuff until we’re done, don’t worry. Not too sure very many of us would so nonchalantly encumber ourselves with a 30 year loan on such vague promises. We automatically expect the home builder to use that rough diagram as the precursory for an architectural blueprint that meets local codes, standards and ordinances. We expect the city zoning commission or code enforcement division to sign off on key items during the construction. We expect to spend the week before taking ownership to inspect the home and to mark things to be fixed before the closing. In short, when it’s our money and/or financial future – we expect things to simply work right.

The same is true in many other disciplines that we utilize every day. We get on elevators knowing that they were well designed and recently inspected. We readily fly on airplanes knowing that the FAA and other government agencies are making sure it’s safe and well regulated. We undergo surgery at hospitals knowing that doctors and administrators have jumped through major hoops to guarantee a reasonable level of safety. Yet we still build computerized systems with all the skill of a MASH surgeon – just get it stable enough to pass onto the next guy. No wonder then that both database administrators and application developers are quickly catching up to lawyers and politicians in terms of likeability. :-(

So let’s start this week by just looking at the two most critical database success factors: effectiveness (doing the right thing) and efficiency (doing it expediently).

How can anything be effective if it’s just cobbled together haphazardly over time? I’m not saying that everyone must do a logical data model and transform that into a physical data model. That may not be most peoples’ “cup of tea”. But slowing down a wee bit to think of the overall physical design should yield a more consistent business solution. That should translate into higher effectiveness.

The same can be said for efficiency. So by not simply and seemingly randomly adding tables and columns on an as need basis can easily result in a more fluid design. It’s much easier to code against such a design, too, which can also mean lower costs in terms of the development (i.e. less complicated to work with). Furthermore, this generally translates into lower ongoing maintenance costs as well – and often far less unplanned crisis.

Find all this “pie in the sky” thinking to be too esoteric or too academic? Well I’ve been an expert witness in several trials where companies have sued consulting firms that built substandard applications and databases. In every single case, the plaintiff won substantial judgments based solely on poor database design. So far my testimony has directly resulted in seven figure financial awards. That’s millions of dollars per case that could easily have been saved by slowing down and doing more planning or design. So as the old song goes “Look before you leap, still water runs deep” …