The upcoming major release of Toad for Oracle 12.0 marks yet another significant milestone in Toad’s long history.

We are really excited about this release because it will bring Toad, and its popular community Toad World, together, enabling you to engage with other users and the wealth of shared knowledge they have, as well as the knowledge and expertise of our own technology and product experts.

Available soon,...

Today I want to share how to setup typing short cuts.  It's called ‘auto replace’…

There are times when working in development or test environments where you want to run something measuring the run time and IOPS (IO’s per second). For those occasions where you can run the workload in isolation, the four SQL scripts offered and described below can often be useful. Here’s the example of the output from the TIMER_RESULTS.SQL script – which offers simple run time and IOPS output data as shown here.



...

Upon occasion you may find a need to import data into a database. That database might be your own sandbox or you can use the Toad Sample database or Local Storage. I am going to show you three different Import Methods.



Scenario 1

The most common use case is taking an Excel or CSV file and import. Toad Data Point will import to the current connection. So connect to the sandbox that you want. I will use...

 

Written by Robert Pound, System Engineer, Dell

Toad® Data Point is a cross-platform query and data provisioning tool that simplifies data access, querying and analysis for data management professionals. This data analysis tool provides nearly limitless data connectivity, visual query building, data cleansing and workflow automation. For more information about Toad business intelligence products go to: http://www.quest.com/business-intelligence-suite/...

Toad for Oracle has some very useful command line processing options for those who want to automate certain database tasks. Let’s examine three incrementally complex command line option examples that should provide sufficient models for you to build upon. The third example is really the only method that you’re going to want to learn and reuse. But we’ll examine the first two nonetheless since they provide good background and...

My good friend, Marcelle Kratochvil, is the Oracle ACE Director for (as she put it to me) "all data/any data." She's been working with Oracle since V4 and specializes in Oracle Multimedia. She has been a beta tester for this product since Oracle8i and runs a SIG on multimedia and unstructured data. Check out her blog at: http://eternal-donut.blogspot.com.au

Marcelle...

Problem: Jasper queries the same, unchanging data over and over and over again.

I remember when I first started getting used to the idea of Oracle referring to functions and procedures collectively as “subprograms”. Nobody else in the industry was using the term. Newbie programmers would ask me, “What’s a subprogram? Is that like a subroutine?” Then there’s the trend of new authors inventing new terminology for old concepts, in order to help sell their programming how-to books. After excitedly purchasing...

SharePoint is becoming more and more popular for content and document management. I know we use here quite a bit for storing our project schedules, architecture docs, and so on. Starting with Toad Data Analysis 3.1 we offer exporting and importing to SharePoint lists. SharePoint lists can easily be treated as a dataset. I happen to use this every month by exporting a report that shows all of the connection types that were used by Toad Data Point. Let me show you how to do this.

...

Everyone wants their database benchmarks to score as high as possible; hence they want their database server properly configured for optimal results. But there are so many hardware and software variables that it’s hard to provide any universal best practices. However there are a few Oracle configuration parameters worth reviewing and setting rather than relying on their defaults. Don’t take any of these as “gospel”, but rather...

Hi!  Watch for my regular posts right here on Tuesdays.  I wanted to start with a bit of TOAD history.

Toad got its start back when Windows 3.1 came out ...

We get lots of questions regarding Toad for Oracle performance – with most questions falling into one of three basic categories:

I’ve installed a new version of Toad and it’s not as fast as my prior version My copy of Toad seems to run slower than other people whom I work with My current Toad install recently feels much slower or slowing over time The first scenario is quite simple – check all your...

Exporting data is a key activity for automation and the Select to File activity handles most basic exporting needs. But what if you have a little bit special situation that needs to be exported? In this case you can use all features of the Export Wizard. 



Let’s look at some scenarios of when using the Export Wizard automation activity would be appropriate.

Scenario 1 – CSV files

When you have a...

SQL join syntax can at times be both confusing and overwhelming. It’s not bad enough that one needs to think about inner vs. outer and left vs. right vs. full joins, but there’s also both the old Oracle proprietary syntax vs. the ANSI standard syntax. Whew. Now add to the equation your Oracle version, because not all database versions offered full ANSI join support – plus early on there were database optimizer issues regarding...

In this blog I’ll demonstrate via database benchmark results that properly configured virtual machines run much better than those relying on defaults. In fact just changing four basic virtual machine settings from their defaults will yield stunning improvements.

For more advanced forms of formatting use Excel macros.



Macros are VBA instructions that can be triggered by Toad. By default Excel disables macros. You will need to set up Excel to use macros. When using them the excel extension is *.xlsm. The set up may vary between versions of Excel. Starting with Excel 2007 you will need to enable macros. From the office button open the Excel Option page. Click on the Trust...

A recording of a recent Toad DB2 Tips and Tricks is now available

I get this very question at least once a month – sometimes more. The problem report is always exactly the same – “The Toad debugger toolbar icon is disabled (greyed-out) and so I cannot debug my code!” Of course since Toad won’t let them do what they want, it is therefore invariably perceived and communicated as a Toad problem and/or bug. Not so!

People have questions regarding Toad for Oracle in relation to Oracle licensing requirements. They want to know “Does Toad for Oracle require purchasing optional, additional cost Oracle price book items. In short, Toad for Oracle does not require buying anything other than the Oracle database.

However questions persist. Here are the questions asked most often with their answers.

Question: Toad for Oracle Xpert Edition, Toad Development Suite for Oracle and Toad DBA Suite for Oracle contain SQL Optimizer for tuning and optimizing SQL statements.  Does this require the Oracle Enterprise Manager (OEM) tuning pack?

...

Here is a report that uses pivot grids.

 

I do a lot of database benchmarking, and have been doing so for well over a decade. As such I’m often engaged in pre-sales, post-sales and tech support calls and/or live web sessions helping prospects and customers using Benchmark Factory to run industry standard database benchmarks (e.g. TPC-C) and/or perform workload capture/replays. What amazes me most are...

Single result sets exported to Excel may not be enough for your needs. You might have a project where the end user wants multiple results in a single page. And of course you would like to make it attractive.

In my recent blog titled “Copying Database Data to Microsoft Excel via Toad” I showed how easily Toad can export data into Excel – a favorite tool of many business analysts. But what if instead the data starts out in Excel and I want to get it loaded into Oracle? This too is a very common “use...

Many business analysts rely heavily upon Microsoft Excel as one of their primary tools for manipulating and reporting upon their business data. Common ways they often get access to the database data include:

Sending database data extraction requests to their information systems (IS) people to perform and then deliver the desired Excel Spreadsheet or “delimited” text file for them to load in Excel. However this...

Find out how to use Toad Data Modeler’s Gallery and how to add equally named columns to all tables in your entity relationship diagram.  

I visited the Czech Republic in February to do a training for Oracle and another for the IT department of a financial institution based in Brno (second largest city after Prague). I thought you might enjoy reading this....

 

You have been developing software since 1980. What were the conditions like for programming back them? Did it take longer time to write code? How have things changed over the last 30 years?

...

You may already be familiar with the Toad World Exclusive Freeware area from where you can download and use really useful utilities, including:

SQL Monitor – a utility that monitors Oracle, MySQL, and DB2 Client DLL calls, allowing you to capture all SQL calls a program sends to the database via the client DLLs. Quest Calibrate – utility to quickly find wasted space and other performance metrics!...

Here are the basics on exporting to excel. We will build on this action in the next two videos to build complex reports.

I am working on some new transformation rules for Quest SQL Optimizer to maximize SQL performance for Oracle Exadata.  It is interesting to investigate how subtle changes to SQL syntax can enable more offload operations to the storage server with corresponding improvements in execution time.

The following is one type of transformation that you might find useful.



SQL transformation to enable deeper...

Hi my name is Debbie Peabody and I'm beginning a Blog series on Automation. I have talked a lot about Automation but there are still many examples of real world scenarios that might help you out with your daily tasks.

In this Blog series I will present the data in both video and written format. In this post I will start with the basics of how to get started with Automation. (If you have been creating and running automation...

For the past several years, I have given my Coding Therapy talk to Oracle technologists, offering insights through:

Dream therapy Shock therapy Game therapy Couples therapy It is, of course, entirely tongue-in-cheek, but also (I hope) helpful to programmers, in giving them a different perspective on some of the challenges they face in their work.

These talks have usually been very well-received,...

There are two very distinct and unique types of bitmap indexes within Oracle: bitmap indexes and bitmap join indexes. Let’s examine the fundamental difference between them.

Let’s assume we’re indexing a “star schema” designed set of tables. To keep it simple let’s suppose the design has one fact table and two dimensions as show by the physical data model here in Figure #1.



...

I recorded a new video  introducing developers to one of my favorite features in Code Tester for Oracle: the rather plainly-named Run Program. I like to call it "Run-to-Test".

It has become clear in the years since we first released Code Tester that a whole...

Just arrived in Madrid before the start of Dell EMEA FRS/Tech Summit. Long flight but I am really looking forward to helping our EMEA Sales and Pre Sales teams get to know our awesome Business Intelligence and Big Data Analytics products. We start in earnest tomorrow and I have a training session for Toad Data Point and Toad BI Suite on Tuesday followed by sales enablement on Wednesday. If anyone attending has any questions...

Toad Data Modeler version 4.3 contains new verification. You can check your model objects for Max Name Length, Missing Objects and the software offers you a comfortable way how to fix problematic items one by one or all at once. The downside is that customization is no longer available, because of complexity and additional features that were added to version 4.3. What should you do in case you made customizations to the old verification?

Problem: The exception section of a block can only trap errors raised in the executable section!

Toad Extension for Eclipse version 1.6.5 contains many enhancements. One of the most useful improvement is related to management of documents stored in BLOBs. Now it is possible to view documents - open them in Eclipse or associated external application, export the content to external file, import a document to the selected BLOB field or just set the value to null.

In one important respect, writing software is no different from driving a car. You can be the best driver in the world, but if the other driver is awful, you can still have a big problem on your hands. So you drive defensively; you stay alert to the possibility that another driver might make a poor decision or be careless. You protect yourself as best you can.

It’s the same with software. You believe that you are a fine programmer (and I have no reason to doubt that!), and that your programs work well. But have you guarded your programs against people who might use them carelessly or in ignorance? Probably not.

The usage and implementation of web applications with DB2 z/OS as the backend for data is growing. New applications have been developed on distributed platforms. They use interfaces to connect to DB2, which only support dynamic SQL statements like JDBC (for Java) or CLI/ODBC (for C, C++, C# programs).

Furthermore, ERP/CRM solutions like SAP or Siebel also work with dynamic SQL statements. Those applications allow their...

Since version 8.7, our Quest SQL Optimizer has been enhanced to support Exadata. These enhancements focus on displaying smart scan and bloom filtering on both the original query plan and alternative plans.

I have been researching new rules exclusively for Exadata and have found that there are not many discussions on SQL tuning on an Exadata machine. We also have not spent a lot of effort on SQL transformation. With...

Problem: Jasper queries the same, unchanging data over and over and over again.  What's the solution?

Sure, you should certainly read my blog - and encourage everyone you know to do the same.

But there are lots of other fantastic Oracle technologists out there, and several write blogs on Toad World.



So today I would like to start the New Year by drawing your attention to the excellent writing of Dan Clamage. I've known Dan for years, since 1997 in fact (just searched my email history). He's reviewed...

A segment is any object that requires storage in a tablespace – tables, indexes and Lobs (Large-Object Binary) come to mind. You can see what segments are in your schema by querying the appropriate Data Dictionary view.

Introduction

Our latest release of Toad for IBM DB2 – version 5.2 has just been released, so here’s a quick summary of what’s included so you can be sure you need to upgrade your current version.



Continuing along our main themes for Toad for DB2 of:

Productivity – enabling users to discover more of Toad’s features and ensure that the tasks they perform offer the maximum productivity and...

I’d like to share a technique I use to help me visualize the complex queries I’m often writing. Sometimes, we miss things and write inaccurate or poorly performing queries because we lack a concise way to “grok” or see things all at once. So I came up with this tool to help me summarize the typically convoluted relationships into a more manageable form.

I’ll use a modest example just for illustrative purposes. So let’s examine the E-R diagram for a sample customer invoicing subsystem.

...

Many Toad for Oracle users are familiar with the ‘Generate Data’ option. This feature allows users to select one or more tables and populate them with data automatically. While this is certainly powerful functionality, I am often asked about more advanced cases, specifically ones in which tables are related, and the data generated in the parent/child tables must conform to referential integrity constraints defined on the tables. Understanding...

The Oracle 11g Anti-hacker's Cookbook covers an impressive breadth and depth of database security topics. No stone is left unturned. Plus quite uniquely the author fully explains every potential database security risk, demonstrates how to perform the hacks being protected against, and then of course how to mitigate or prevent them. Moreover the author covers advanced Oracle security features, including virtual private databases, label security and database vault.

...

Generating the column list for a table is something I find myself doing on a fairly regular basis, whether for writing an INSERT statement, reverse-engineering a table or some other dynamic SQL/DDL purpose. I’ve probably written this query about a million times:



SELECT DECODE(column_id, 1, ' ', ',') || LOWER(column_name) column_name

FROM user_tab_columns

WHERE table_name = 'NUMS'

ORDER...

Problem: One person's clarity is another person's bewilderment.

Somebody once asked me, in regards to a subprogram that writes back a string via the parameter list, how can the called subprogram know how big is the provided string buffer from the calling context? The parameter only defines the data type, not its size. Potentially, the unconstrained definition of VARCHAR2 can return a value up to 32,767 bytes. But if the procedure returns a string value back to a variable that’s smaller...

Bert brings a fresh view to one of Toad World's most popular blog topics.

1. A small amount of query text is sent over the network from the SQL client to the RDBMS.
2. All data scanning, filtering, sorting and grouping is done locally within the RDBMS.
3. The result set is returned over the network to the SQL client program.

There are only two real opportunities for latency in this simple ...

Last week, a coworker asked, “how can we update one table using values found in another table?” My immediate answer was to use a subquery in the SET clause, correlated to the main table. But then I remembered seeing years ago posts on the PL/SQL Pipeline (a former Quest-supported threaded discussion board) that performed the update on a query. At the time, I had never seen nor used that method. I knew you could create an...

I recently completed a project that used Data Dictionary views to generate new View DDL based on existing views, the latter used as the pattern for the new ones.

The Toad Freeware Amazon Machine Image (AMI) is a relatively simple virtual machine that’s running a minimalist Windows Server 2008 R2 operating system and which has three Quest Software (now part of Dell) Toad freeware apps preinstalled and placed on the desktop: Toad for Oracle, Toad for SQL Server and Toad for MySQL. These freeware offer a useful subset of their commercial product counterparts. In addition these freeware...

The new Quest SQL Optimizer for SQL Server 8.0 has two major enhancements: an improved SQL rewrite engine and the new Plan Control SQL tuning module. Today, I continue my discussion of these enhancements. Be sure to see Plan Guide in Quest® SQL Optimizer for SQL Server 8.0 - Part I...

原文：“PLAN GUIDE IN QUEST® SQL OPTIMIZER FOR SQL SERVER 8.0 - PART III” 作者：Richard To 　原文投稿日期：2012年11月8日

We have a table containing email address data for every process in our system – hundreds of processes, thousands of (mostly redundant) email addresses of interested parties. Due to an email server changeover, it became imperative that we be able to identify and potentially correct the email address format for all of these mostly internal email addresses. The email address domain had been standardized across the company, and many of these stored email addresses were no longer deliverable. As usual, we weren’t made aware of the full impact of the email server changeover until business users started asking us, “Hey, what happened to my ABC report? I haven’t gotten it in two days!”

...

Toad Extension for Eclipse version 1.6.3 includes new possibility how to add new tables to your Oracle Database. Instead of writing SQL code you can now use the New Table wizard that helps you to create tables comfortably and quickly.

It’s pretty common to see queries on time-dependent data that have a subquery for a partial or non-unique business key, looking for the most recent, say, entry for that partial key. For example:

select * from rssc.br_bill_calc_history bbch where bbch.bbch_create_dt = (                       SELECT MAX (bbch1.bbch_create_dt)                       FROM   rssc.br_bill_calc_history bbch1                       WHERE bbch1.bbpi_bill_prem_id...

Recently, at work, I came to the realization that it would be really helpful to be able to capture the exception stack trace whenever I hit an exception handler. The back-trace would tell me what module I was in and what line the program counter was on when it fired the exception.

 

Oracle provides two packaged procedures in Package DBMS_UTILITY -- format_error_backtrace and format_error_stack -- to unwind and...

Exciting times ahead with DELL at Hadoop World in NY

In a recent blog titled “Toad 11.6 Runs Natively on Linux” I showed how to install and run Toad 11.6 for Oracle on Linux. The steps are pretty easy – but many people simply may not have the time or patience to test drive Toad on Linux. So I’ve built two virtual machines with Toad 11.6 freeware and commercial already...

Look at the screen snapshot shown in Figure 1 below – I’m running Toad for Oracle 11.6 commercial “natively via WINE” on Linux – specifically Ubuntu Linux version 10.04 64-bit. Therefore you can now run the world’s favorite Oracle database tool on Linux. Goodbye Windows!

 

Figure 1: Toad running natively...

We are excited to announce the upcoming release of Toad for Oracle version 11.6 which will be released tomorrow, Tuesday October 2nd, 2012. This blog serves as a early look at the updates and enhancements to Toad for Oracle v11.6 including its corresponding bundled components. Toad for Oracle is the most widely used database management solution on the market today with over 2 million users. Toad brings operational efficiency...

Spotlight® on Oracle provides visual, real-time diagnostics which can help you quickly eliminate bottlenecks in your Oracle and Oracle RAC environments. Because Spotlight helps you save time finding and fixing performance issues, you can reduce downtime and IT costs.

With Spotlight, you can pinpoint and resolve bottlenecks quickly, prevent or minimize unplanned downtime, improve service levels for users and stakeholders,...

原文：“PLAN GUIDE IN QUEST® SQL OPTIMIZER FOR SQL SERVER 8.0 - PART II” 作者：Richard To 　原文投稿日期：2012年9月15日

Quest SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA) or Quest Software Foglight Performance Analysis for Oracle. Once SQL Optimizer identifies problematic SQL statements, it optimizes...

In my last post, I looked at the effect of the Exadata smart flash logging.  Overall,  there seemed to be a slight negative effect on median redo log sync times.  This chart (slightly different from the last post because of different load and configuration of the system), shows how there’s a “hump” of redo log syncs that take slightly longer when the flash logging is enabled:

...

The new Quest SQL Optimizer for SQL Server 8.0 has two major enhancements: an improved SQL rewrite engine and the new Plan Control SQL tuning module. Today, I continue my discussion of these enhancements. Be sure to see Plan Guide in Quest® SQL Optimizer for SQL Server 8.0 - Part I...

Toad Business Intelligence Suite (TBIS) is a powerful new offering from Quest Software that helps reconcile the gap that exists between:

The Analysis that is provided by traditional embedded BI Solutions and The Analysis that the Business user is actually being asked to perform. We can illustrate how the TBIS suite addresses this gap by...

Inititial thoughts from the Cassandra 2012 summit which I attended.

The inability to match the increasingly rapid speed of business change has being affecting the implementation of Business Intelligence for a number of years. Further complicating the problem is the fact that many enterprise information technology systems often inhibit business flexibility, sometimes with dire consequences.

Exadata storage software 11.2.2.4 introduced the Smart flash logging feature.  The intent of this is to reduce overall redo log sync times - especially outliers - by allowing the exadata flash storage to serve as a secondary destination for redo log writes.  During a redo log sync, Oracle will write to the disk and flash simultaneously and allow the redo log sync operation to complete when the first device completes. 

...

原文：“PLAN GUIDE IN QUEST® SQL OPTIMIZER FOR SQL SERVER 8.0 - PART I”    作者：Richard To 　原文投稿日期：2012年8月3日

The new Quest SQL Optimizer for SQL Server 8.0 has two major enhancements: an improved SQL rewrite engine and the new Plan Control SQL tuning module. Today, I want to discuss the new Plan Control module, which elevates the SQL Server 2005 Plan Guide feature to a new horizon.

 

What is plan control?

As you probably know, most vendors are willing to provide plan control features  to allow users to fix...

Toad Data Modeler supports various database platforms, from Oracle Database to PostgreSQL and others. Version 4.2 now supports SQLite database. New models can be created from scratch and existing databases can be reverse engineered into models - diagrams...

This blog discusses starting and stopping SQL Trace from within TOAD

This blog entry discusses TOAD and the TKProf Interface...

A better way to review Oracle SQL Trace Files

 

The Pacific North West BI Summit is an annual weekend retreat that assembles the top industry thought leaders in the business intelligence and data warehousing industries in an informal setting to discuss the latest trends.  I was honoured to be invited on behalf of Quest to attend this event.

原文：“NEW RULES FOR QUEST® SQL OPTIMIZER FOR SQL SERVER 8.0”  原文投稿日期：2012年5月21日
作者：杜伟业（Richard To）

 

In Toad Data Modeler there are two methods how triggers can be created. One of them allows you to use Application Variables. If you change entity name, the trigger name will be renamed automatically.

Toad Extension for Eclipse version 1.6 introduced new possibility - to run stored procedures and functions with parameters. See how easy it is to run your Oracle procedures on Mac OS and how results are displayed.

For the second time in as many Toad for Data Analysts (TDA) demos I have had a prospect express interest in the ability to perform a ‘SELECT INTO’ (you may recognize this syntax or something similar depending on what DB platforms you work with) type operation for moving schema and data from an Excel object to Oracle (or any other platform) in one fell swoop. 

 

As an example, SQL Server users are used to having...

Has Toad saved you time or helped you solve tough IT challenges?  If so, then we invite you to enter our Quest in Show video contest.

 

Here’s how it works:  Create a short video to explain what you can do with the time saved by using Toad.  Have you become a certified scuba diver?  Or a chess champion?  Or even a world-class artist? ...

Tension between IT and the Business exists - how is Quest tackling this problem

Why all the hype surrounding "Big Data"

To understand you really need to be able to define what the term "Big Data" actually means. To me the definition is clearly identified by the three V's.

Volume - Variety - Velocity

According to survey's published late in 2011 over 1.5 trillion gigabytes of data was created and replicated in that year alone (IDC 5th Annual Survey). This shows a 100% increase from two years previous and this increase in data production is not expected to slow but to rise exponentially every two years. This data is not all useful data however all of it can and is being collected. If the data can be collected then should we not be providing tools to connect, analyze and visualize the results to improve decision making.

...

Business Intelligence – Data Storage

POS Applications, HR Applications, Customer Survey results these are just some of the myriad sources of data that we are, as database administrators or developers responsible for and that a business intelligence system can consume. Within Business Intelligence systems these sources of data are usually encompassed in to two main types of storage systems to provide historical, current,...

I often get asked “Why can’t we capture the database workload for 100 concurrent users and then play it back as say 1,000 users?” For Example, Quest Software’s Benchmark Factory makes it trivially easy to capture both SQL Server and Oracle workloads for playback. But that playback to capture ratio is simply one-to-one (as it is with other similar workload capture and playback tools). But steadfast users simply want what they...

Introduction

Quest SQL Optimizer for Oracle automates the SQL optimization process and maximizes the performance of your SQL statements. SQL Optimizer analyzes, rewrites, and evaluates SQL statements located within database objects, files, or collections of SQL statements from Oracle's System Global Area (SGA) or Quest Software Foglight Performance Analysis for Oracle. Once SQL Optimizer identifies problematic SQL...

With last week's v1.6 release, Toad Extension for Eclipse adds MySQL to the list of supported platforms. So whether you're working with Oracle, PostgreSQL, or MySQL - on Windows, Linux, or Mac OS X - there's a Toad for you!

If you're currently using Toad Extension for Eclipse, here's how you enable this support.

Toad has always had the ability to take your SQL statement and convert it to another language, like Java, C++, C#, etc., as well as the ability to take existing code and strip out the non-sql characters from your Java, C++, etc., code.

The way to set this up was to go into the VIEW menu item, choose TOAD OPTIONS, then choose EDITOR, and then choose CODE ASSIST, then choose your language from MAKE CODE:



...

In my last blog, we discussed how Toad for Oracle’s Code Analysis can be used to create and clone rules for ensuring code quality through the database development lifecycle. Building on that theme, this blog will focus on how Code Analysis and Team Coding can be used conjunctively to automate code quality validation...

Imagine that your company has recently gone through a merger and your CIO calls you into a meeting to discuss the consolidation and how the migration of hundreds of users to their mission critical applications will be affected. They are concerned as to how scalable their systems are based on the proposed configuration plan in place. Being the performance engineer or DBA, your job is to provide some validation that the mission...

How to automatically map key columns in parent table with foreign key columns in child table in Toad Data Modeler.

Problem: You have lots of dynamic SQL updates, with different values each time.

Solution: Bind, don't concatenate, to optimize performance and simplify dynamic string construction.

Read what Ana Choto from American University wrote to us recently about using Toad for Oracle.

A step by step guide to building a TOAD® Reports Manager report that caters for instances where there’s “No Data Found

How to quickly search and open database objects in Toad Extension for Eclipse. Press Command+Shift+D key combination to open the Open Database Object form. Type part of database object name and use arrow key to highlight it among matching items. Then press Enter to open the object.

The new Quest SQL Optimizer for SQL Server 8.0 has two major enhancements: the improved SQL rewrite engine and the new Plan Control SQL tuning module. Today, I want to discuss some new SQL transformation rules in SQL Optimizer 8.0.

 

How to assess the SQL Server internal SQL optimizer performance?

You may be aware that every SQL statement has to be optimized by the database's internal SQL optimizer before it can be processed. Simply speaking, the database's internal SQL optimizer is designed to find the best way to process your SQL statements. There are two major factors to assess a database's internal SQL optimizer. The first is the accuracy of cost estimation and the second is the size of the plan space.

...

Or are they just sociable? Social Media has taken the world by storm, primarily through Facebook, Twitter, Yelp, Foursquare, LinkedIn, etc. And now everyone expects to have access to a network of "friends" wherever they are, through whichever device they are using. This raises an interesting question for those us in the technology world and vendors providing tools in which technologists "live": Does social media/networking...

Here we go again! After having written and blogged about this topic a fair bit in the past, having too many managers is a problem I still continue to see all too often. A good example is a site I worked with recently; it had 20+ managers just to run the workflow background processes.

Putting aside the need to address the excessive workflow background processes (which is another problem in its own right) there are major...

We software developers are a very privileged bunch. We don’t have to work in dangerous environments, and our jobs aren’t physically taxing (though carpal tunnel syndrome is always a threat). We are paid to think about things, and then to write down our thoughts in the form of code. This code is then used and maintained by others, sometimes for decades.

Given this situation, I believe we all have a responsibility to write code that can be easily understood and maintained (and, c’mon, let’s admit our secret desires, admired) by the developers who follow in our footsteps. Look at this way: if you have a child, she might grow up to be a programmer. She might even put in time at one of your previous employers. She might actually end up having to maintain code that you wrote.

The choice is yours: avoid mortifying embarrassment either by never putting your name in your code, or by writing code that you would be proud to show to, share with, and inflict on your own flesh and blood.

Everyone celebrates back to school so we are going to celebrate the end of school for a change.  It just sounds better.  With the fiscal year end upon us and the students clambering for summer the IT department is hunkering down.  End of school is when IT gets busy so let Quest Software help you prepare for the time ahead with a great program.  Toad Plus for Higher Education...

原文：“EXECUTING SQL IN TUNING LAB – PART 10 – PERFORMANCE IMPROVEMENT EXPECTATIONS”  原文投稿日期：2009年8月21日

原文：“EXECUTING SQL IN TUNING LAB – PART 9 – WHY RUN TIMES VARY”  原文投稿日期：2009年8月7日

原文：“EXECUTING SQL IN TUNING LAB – PART 8 – CRITERIA FOR BEST SQL”  原文投稿日期：2009年7月24日

原文：“EXECUTING SQL IN TUNING LAB – PART 7 – TESTING SQL WITH VARIABLES”  原文投稿日期：2009年7月10日

原文：“EXECUTING SQL IN TUNING LAB – PART 6 – TESTING IN A DEVELOPMENT ENVIRONMENT”  原文投稿日期：2009年6月26日

One of the major strengths of Benchmark Factory is the ability to Capture and Replay the activity related to a session. This is important because it doesn’t force the user rely on tracefiles or logs to obtain ‘real world’ production data for replay in a test environment. This is important for the following reasons:

Tracefiles may contain multiple user session details It is often difficult to identify which...

原文：“EXECUTING SQL IN TUNING LAB – PART 5 – SELECTING ALTERNATIVES TO TEST”   原文投稿日期：2009年6月12日

原文：“EXECUTING SQL IN TUNING LAB – PART 4 – EQUAL COMPARISON FOR RUN TIMES: ELIMINATING THE EFFECT OF NETWORK TRAFFIC”  原文投稿日期：2009年5月29日

原文：“EXECUTING SQL IN TUNING LAB – PART 3 – EQUAL COMPARISON FOR RUN TIMES: MINIMIZING THE EFFECT OF OTHER ACTIVITIES ON THE CPU”  原文设稿日期：2009年5月15日

原文：“EXECUTING SQL IN TUNING LAB – PART 2 – EQUAL COMPARISON FOR RUN TIMES: MINIMIZING THE EFFECT OF CACHING” 原文投稿日期：2009年5月1日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“EXECUTING SQL IN TUNING LAB – PART 1 – CRITERIA FOR TERMINATING SQL ”    原文投稿日期：2009年4月17日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To） 

It’s that time of year again when Toad for Oracle takes another step forward in bringing the best of class functionality for the Oracle user. Quest Software recently announced the release of Toad for Oracle v11.5 which includes many important enhancements for Developers and DBAs. One of the areas of functionality that has will be exciting for those Developers looking to improve code quality and incorporate best practices...

Many IT shops today are running a combination of Oracle and IBM DB2, which means they either have to cross-train employees to support both platforms, or hire two sets of employees, each with expertise on a particular DBMS.

The new Oracle compatibility features available with DB2 LUW 9.7 offer an alternative: porting Oracle applications to DB2 for LUW.



Although there are terminological differences between...

Yep, you read that right... I'l do my best to keep things "magical and revolutionary!" along the way.

原文：“A USEFUL NEW RULE FOR UPDATE STATEMENTS”    原文投稿日期：2012年3月23日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

I write a PL/SQL 101 column for Oracle Magazine and the March/April 2012 issue focused on Error Management. I received the following letter from a reader; we couldn't fit the whole thing in Oracle Magazine's Q&A section, so I decided to publish it here.



Hello Steven, I've very much...

I did not use to pay much attention to the update table in an UPDATE statement. I figured that there was not a lot I could do with an update table unless there were complex search conditions. I preferred to focus on the sub-queries that appear on the update table's filtering and the update SET command.  

Today, I want to introduce a new transformation rule that is not implemented in our engine yet, but it is quite...

Toad for Data Analysts Automation interface is an extremely powerful and oftentimes underutilized feature. To demonstrate the power of the Automation Interface I’ve created a simple example that you can extend for your own use. We start by using the Automation Interface Execute Script feature to run a SQL query. This sql query returns a flag value which we will assign to a variable. With the variable assigned, we can use the...

No, you’re not going crazy… it really is gone. Here’s why.

Oracle technologists, database architects, database administrators, and senior database application developers are all very curious people by nature – even more curious than most people. So naturally database oriented issues evoke debates similar to “tastes great, less filling” from the famous beer commercial. You might be surprised by the scope and passion of such database debates. One area getting lots of discussion of recent...

One of the main task as a DBA is working with objects. Since there are at least two environments (typically one or more for testing and one for production) DBA’s face the challenge of moving changes done in one environment to one or more other environment(s). Toad helps the DBA to undertake this comprehensive and time-consuming task with two very powerful features.

BTW – the features I am going to present are available...

OOW is the big (enormous, really) blast of a conference/party that Oracle hosts each year in San Francisco. I've been attending these conferences since the late 1980s, and each year it seems to get bigger. Fortunately, Oracle offers a "conference within a conference": Oracle Develop, for those of us developing applications with Oracle technology. I usually spend almost all my time at the Oracle Develop hotel, so even though...

In this part, Bert goes over some database instance checklist items to add to your virtualizatoin checklist.

You asked for it, we listened to you and made it happen. Toad Extension for Eclipse version 1.5.2 contains various Schema Browser improvements.

原文： “SQL PATCHES VS SQL PLAN BASELINES” 原文投稿日期：2012年3月12日 星期一
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

Scheduled requests are one of the mainstays of OEBS concurrent processing.

 

One of the benefits of recurring scheduled requests is they are “set and forget” they just happily run and resubmit without you having to do a thing.

 

The biggest issue I’ve found, and this applies to almost all sites, is the “forget” component. I regularly find scheduled requests that have been running for years that are no longer required, and also duplicate scheduled requests which have come about because someone doesn’t know the original request exists. Both these issues are a waste of processing resources.

...

Richard discusses why SQL tuning by SQL Plan Baselines is a safe and effective method to improve your SQL performance.

Have more questions about Cross-Connection Queries?  Here is video FAQ on the subject.

The Plan Table QBlock Name column is used in conjunction with a hint to expose exactly where the sub queries of a SQL statement appear in the Plan Table.

Quest Software is pleased to announce that Quest Backup Reporter for Oracle is now bundled as a component of the Toad DBA Suite for Oracle and Toad DBA Suite for Oracle - RAC Edition for no additional cost. As you may already know, the above-mentioned Suites offer a complete Oracle DBA solution that enables you to become more proactive by automating tasks related to administration, maintenance, performance management and change...

As many of you know, Toad Extension for Eclipse can be used for development and basic administration of Oracle databases.
The latest Community Edition - version 1.5 - introduced support for PostgreSQL 9. Supported operating systems: Windows, Linux, Mac OS.

 

At some point every DBA’s going to be asked if not forced to adopt virtualized databases. Oh yes we can argue until we’re blue in the face about performance worries this and other mostly technical concerns, but we’d simply be “fish swimming upstream”. The business has made a decision and while the business may permit appeals for concessions, those concessions will be neither automatic nor sympathetic. So unless we’re willing...

See our Advanced Analytics library in Toad for Data Analysts.

New PLAN_TABLE support starting in Oracle10g.

 

原文： “SQL OPTIMIZER FOR SQL SERVER 8.0 BETA” 原文投稿日期：2012年2月21日 星期二

作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

Consider the following block:

 

DECLARE

   l_date   DATE;

BEGIN

   l_date := '15-FEB-2011';

END;

 

I declare a date variable and then assign a string to it. Will this block raise an error? Hard to say. It depends on the NLS settings for the default date format – for the database as a whole or for my session. If this string doesn’t match the default, then Oracle will raise...

After almost a year of development, I would like to announce the beta release of SQL Optimizer for SQL Server 8.0 is now available here: http://sqloptimizersqlserver.inside.quest.com/index.jspa.

To be honest, I was primarily focused on the Oracle version of SQL Optimizer previously. Consequently, we sometimes received complaints from our SQL Server users saying that our tuning engine had not caught up with the SQL Server upgrade. I am confident that the new version of SQL Optimizer will satisfy most SQL Server users’ expectations. 

...

原文：“AN EXPERT IS ALWAYS BETTER THAN A MACHINE IN SQL TUNING - 10 COMMON MISCONCEPTIONS IN SQL TUNING #8” 原文投稿日期：2010年11月23日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“SOME COMMON SQL TUNING RULES - 10 COMMON MISCONCEPTIONS IN SQL TUNING #7” 原文投稿日期：2010年5月28日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“NO MANUAL SQL TUNING IS REQUIRED SINCE DATABASE SQL OPTIMIZER IS GETTING BETTER - 10 COMMON MISCONCEPTIONS IN SQL TUNING #6” 原文投稿日期：2010年3月26日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“SQL BEST PRACTICE IS SQL TUNING? -10 COMMON MISCONCEPTIONS IN SQL TUNING #5 ”    原文投稿日期：2006年12月7日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文： “A SQL THAT RETURNS RECORDS FASTER IS NOT NECESSARILY BETTER-10 COMMON MISCONCEPTIONS IN SQL TUNING #4” 原文投稿日期：2009年8月14日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“NO SINGLE SQL SYNTAX IS ALWAYS THE BEST - 10 COMMON MISCONCEPTIONS IN SQL TUNING#3”    原文投稿日期：2009年6月16日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“10 COMMON MISCONCEPTIONS IN SQL TUNING #2”    原文投稿日期：2009年4月28日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

If you need to bring together data from disparate databases, try Cross –Connection queries in Toad for Data Analysts!  This blog includes a video to show you how.

原文：“10 COMMON MISCONCEPTIONS IN SQL TUNING #1” 原文投稿日期：2009年3月13日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

Oracle PL/SQL provides the RAISE_APPLICATION_ERROR built-in procedure (actually defined in the DBMS_STANDARD package) so that we can communicate application-specific error messages back to our users. When you call this procedure, you provide a negative number between -20999 and -20000, as well as your error message.

Here's an example of calling RAISE_APPLICATION_ERROR from inside a trigger, ensuring that employees...

I have seen examples of sites with over 50 million rows in the fnd_concurrent_requests_u1 index; a huge waste of space! More importantly however is the problem of poor performance in such a core OEBS activity.

In my last post I outlined the method for accessing and using Quest Software’s Toad for Oracle Freeware and Toad for MySQL Freeware AMI, available at aws.amazon.com. I explained in detail how to start the instance, how to download and install an Oracle client, and how to make sure Toad Freeware recognizes the client....

Written by Bert Scalzo

As a long time student (BS, MS, PhD and MBA), educator (teaching assistant and adjunct professor) and author (9 books) I’ve spent years championing good database design, administration and development practices. Two tools that I’ve always seen as the “one two punch” to successful database work are Toad Data Modeler and Toad for Oracle. Toad Data Modeler permits one to design a database that’s effective (i.e. meets business requirements) as well as efficient (i.e. performs well). Toad for Oracle then educates and empowers one to both administer and develop successful databases. In fact the Toad family mantra is to increase database productivity with easy to use tools.

...

原文：“A REVOLUTIONARY INDEX ADVISOR IN QUEST SQL OPTIMIZER 8.5 FOR ORACLE – PART 3 ”  原文投稿日期：2012年1月6日 星期五
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

People are always asking about a 64-bit version of Toad. The short answer there is currently no 64-bit version of Toad, but the development team has heard the requests and is on top of the issue. But let’s examine some facts versus fiction regarding 64-bit to make sure you know the truth about 64-bit apps.

Creating a 64-bit application is easy – just recompile the code. FALSE. Assuming your compiler offers a 64-bit...

A common question is how to convince one’s management that Toad is to be preferred versus several other free alternatives – most notably Oracle’s SQL Developer tool. A simple answer is that Toad is quite arguably the “de facto standard” due to both tenure (over a decade) and market adoption (two million users). That translates into a mature tool offering a robust feature set and a large workforce pool. Thus a database tool...

 

Written by Russ Tuttle

Senior Solutions Architect

Quest Software

Banner and Toad should be like peanut butter and jelly.  Always together, timeless and a perfect match.



Whether Developing in Banner, navigating and managing Banner’s complex environment or querying the data and...

原文：“SQL OPTIMIZER PART 10 – HOW TO OPTIMIZE WITHOUT ORACLE HINTS”    原文投稿日期：2009年4月13日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“SQL Optimizer Part 9 – How to Shorten a Long Optimization Time”    原文投稿日期：2009年2月13日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“SQL OPTIMIZER PART 8 - DISPLAY ORDER FOR SQL ALTERNATIVES” 原文投稿日期：2009年1月30日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“OPTIMIZING SQL PART 7 – REARRANGING THE DRIVING PATH” 原文投稿日期：2009年1月16日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“OPTIMIZING SQL PART 6 – USING ORACLE OPTIMIZATION HINTS WHEN TUNING SQL” 原文投稿日期：2009年1月2日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

The most required feature - support for Bind Variables - is available in Toad Extension for Eclipse!

原文：“MY DREAM IS COMING TRUE” 原文投稿日期：2007年6月20日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

The "new" debugging architecture (Java Debug Wire Protocol-based) that was introduced in Oracle Database 9i Release 2 was a major improvement on the previous one (DBMS_DEBUG), especially because it made it possible to inspect the values of a wider range of data types. However, it's still not possible to inspect the values of all data types, as the JDWP-based debugging architecture has the following limitations on inspection...

What is a time-slot based SQL workload?

A SQL workload can come from different sources.  For example, an executing program, a database SQL repository, or a monitoring tool that captures SQL from the database, memory, network or client programs. The way a representative SQL workload is formed will ultimately affect the result of a workload based index advisor or an impact analyzer.  If you are using a monitoring tool...

If, like me, you want to play around with data in a Hadoop cluster without having to write hundreds or thousands of lines of Java MapReduce code, you most likely will use either Hive (using the  Hive Query Language HQL) or Pig.

Hive is a SQL-like language which compiles to Java map-reduce...

Tired of using Access? Toad for Data Analysts 3.0 Professional edition includes an embedded database for your local storage needs. Here is a quick video showing this new feature.

We recently released version 1.5 of the Toad Extension for Visual Studio. This release adds support for PL/SQL debugging as well as editing/executing SQL scripts - without ever leaving the Visual Studio IDE. Come take a look!

原文： “HOW TO TUNE ONE SQL FOR VARIOUS SIZE OF DATABASES ” 原文投稿日期：2010年11月5日 星期五
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“HOW TO USE QUEST SQL OPTIMIZER TO TUNE COMPLEX SQL - A REAL LIFE EXAMPLE #2”  原文投稿日期：2009年2月4日 星期三
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“HOW TO USE QUEST SQL OPTIMIZER TO TUNE DATA WAREHOUSE SQL – PART IV”    原文投稿日期：2011年5月16日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）   

Reporting with Toad for DB2 Part 2 – Graphic Reports

 

In this second part of the article we will continue with using the graphic report capabilities in Toad for DB2. For this example we have a query which gives the summarized revenue for each month of a year. We want to have a chart which visualizes the revenue over the time.

On marked rows in the data tab of a table, view, MQT, data grid of query results or directly on those objects itself you can right click, select “Send to” and “Chart Designer”:

...

Reporting with Toad for DB2 Part 1 – Data Reports

 

In order to prepare your data for presenting or reporting it might be necessary to deliver them in a more useable format than just in rows and columns. Since you are using Toad for DB2 you have useful built-in reporting capabilities like export to Microsoft Excel, exporting in different formats as well as data and graphical reports. The last two options are...

原文：“HOW TO USE QUEST SQL OPTIMIZER TO TUNE DATA WAREHOUSE SQL – PART III” 原文投稿日期：2011年8月07日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）

原文：“HOW TO USE QUEST SQL OPTIMIZER TO TUNE DATA WAREHOUSE SQL – PART II”  原文投稿日期：2011年3月3日 星期四
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）