1. Toad Sql Tuning Module Is Not Installed
2. Toad Sql Tuning Module Is Not Installed On This Machine

• Carmel Howard
• 2 years ago
• Views:

Transcription

SQL Tuning via Toad. Tips for Optimizing SQL Performance. © 2008 Quest Software, Inc. ALL RIGHTS RESERVED. Module Reports SQL*Loader Doc Generator, Manager, Adv data types Brower. On launching SQL Optimizer from within Toad, the message, 'The SQL Tuning module is not installed on th 33166, WORKAROUND:In Toad version 9.0 please.

1 Quick User Guide for Toad for Oracle 8.6 SQL Tuning SQL Tuning Version SQL Tuning definitively solves SQL bottlenecks through a unique methodology that scans code, without executing programs, to isolate poor queries that are candidates for optimization. Those queries are transformed in every way possible using automated rewrite technology, guaranteeing the same results as the original query. Once every alternative has been generated the most efficient statement for your environment can be identified. Similarly, SQL Tuning can generate virtual index scenarios to influence SQL performance. SQL Tuning also offers Best Practices that provide tuning recommendations for other factors that affect SQL performance such as statistics. SQL Tuning provides the following functionality: Module Best Practices Function Provides database tuning guidelines for improving SQL performance. SQL Optimizer Creates SQL statements that are alternatives to your source SQL statement. Validates the performance of the source and alternative statements on your system, using your indexes, and your memory configuration to find the best performing statement. Index Expert SQL Inspector SQL Scanner Rewrites SQL statements in a recursive manner by applying SQL transformations so that every SQL statement is rewritten until all possible semantically equivalent SQL alternatives and alternative execution plans have been generated. The source and alternate SQL are then benchmarked to find the highest performing SQL statement for a database environment. Generates Index sets alternatives for a specific SQL statement. The Virtual Index Simulator functionality provided in Index Expert displays the execution plan for every index set combination that Oracle would generate if the indexes were physically created. These Index set alternatives can be benchmarked to identify the most efficient index set for a SQL statement. Collects SQL performance data from the Oracle SQL Area. Monitors currently running SQL, or takes snapshots to identify SQL statements that consume the largest amount of resources or that are most frequently executed. Extracts SQL statements embedded within the database objects, text/binary files, SQL Inspector files, and application source codes. It analyzes which of these SQL statements has performance bottlenecks. You can input problematic SQL Revised: October 2005 Page 1

2 Page 2 found here into the SQL Optimizer. The SQL Inspector and the SQL Scanner are in an out-of-process module which runs in a separate window. 1.0 Best Practices Best Practices does an overall analysis of a SQL statement and your database and then proposes common ways to improve performance. However, these recommendations may not be the correct solution for your application, so you need to understand and thoroughly test the recommendations before you apply them to your production system. A recommendation may help to improve a specific SQL performance, but it may affect other SQL statements as well. When evaluating the recommendations, you need to take into account that database performance is a result of the complex mix of the following: System resources (CPU, I/O, memory, database architecture, and more) Data distribution System architecture SQL execution plans User's usage behavior 1. Enter a SQL statement into the SQL Text window. 2. Click the Get Best Practices button. 3. Review the recommendations in the Best Practices layout. 2.0 SQL Optimizer In the SQL Optimizer, the SQL optimization is a two-step process. In the first step, the SQL Optimizer automatically transforms the original SQL statement and generates semantically equivalent alternative SQL statements with unique execution plans. For every alternative execution plan, SQL Tuning displays the Oracle Cost estimation. Once the SQL alternatives have been generated, the second step consists of locating the most-efficient SQL alternatives for your database environment by benchmark testing the SQL alternatives against your database. The results obtained from the second step indicate the time required to execute each SQL statement, as well as, runtime statistics. Tip: The Oracle Cost is only an estimate of the resources it takes to execute a SQL statement. It is essential to execute the alternative SQL statements in order to determine which statement performs the best in your environment. 1. In the SQL Text window of the SQL Details Layout, enter a SQL statement. 2. In the upper right corner of the SQL Optimization Console, click the Parsing User option and select the user

3 Page 3 which matches your SQL statement. 3. Click the Optimize button. This step launches the SQL Optimizer that automatically transforms the SQL statement. The use of hints and other optimization options such as transforming a view to an inline view and the ANSI JOIN syntax are optional. In the Preferences, the intensity of the SQL transformation process is controlled by the Intelligence Level. The Intelligence Levels control how many Oracle hints are applied to transform SQL and how many SQL alternatives are created. 4. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress optimization process. Compare Scenarios When the optimization is finished, the Compare Scenarios Layout is displayed. At this point since you have not yet run the SQL statements, the top window in the layout displays only the Oracle Cost values. These are only estimations of how each statement will perform. You need to test each statement to obtain its actual runtime statistics. 5. To compare the original SQL statement and the execution plan to the alternative SQL statements, click the drop-down list at the top of the SQL Text windows to select the SQL statements you want to compare. Test the Alternative SQL statements The Execute function provides an efficient way of test running SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. For SQL statements such as INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data. 6. To prepare to execute the source and the alternative SQL statements, click the Preferences button. Click the Execution button. 7. In the Scenario Execution section, select the following option that best suits your SQL statement. All SQL Twice Running all SQL twice enables you to eliminate some factor that can affect the accuracy of the results. If some SQL statements have been recently executed, then the SQL information is likely to be resident in the cache and it may execute faster because of that. Also, if the SQL statements use different indexes, one index may be resident in the cache and the other not. This option eliminates these factors since it runs all SQL statements twice, throws out the first execution time and uses the second one when all SQL statements and indexes should have the necessary items in the cache. Original SQL Twice The first time you access data from table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory so the following SQL statements run faster. So to have a comparable test, the first SQL is run twice and the time from the second run is compared to the time from the other statements. All SQL Once For long running SQL there is no need to run any statement twice since the effect from caching is diminished over time.

4 Page 4 8. In the Row Retrieval section, select Run on server. With this option, the Execute function retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic. 9. In the Execute Complete section, select Cancel scenarios that exceed 120% of the Original execution time. 10. If your SQL statement runs in milliseconds, set the Execute each scenario n time to 10 in the Multi-Execute option. Click OK. 11. Click the down arrow on the right of the Execute Scenario button and select Execute All. 12. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process. When the batch run is finished, the Execution Statistics layout is displayed. 13. To test SQL alternatives under different user loads to simulate production activity, click on the Test SQL for Scalability button. This feature requires Benchmark Factory for Databases version Once you have identified the alternative SQL statement you want to deploy, you can copy and paste it back in your application. If the SQL was submitted from Toad, you can click on the Return SQL button. 3.0 Index Expert Index Expert analyzes the syntax of a SQL statement, the relation between tables, and selectivity of the data to identify columns as index candidates. Index candidates are combined into multiple index sets and it gives you all the alternative index sets that generate a unique execution plan for the SQL statement. It does this without physically creating the indexes in the database. Index Expert provides performance estimations for every index set to assist you in selecting which index set alternatives to test, evaluate, or implement. Index alternatives can be benchmarked to identify which set will yield the greatest performance gain for the SQL statement. 1. Enter the SQL statement in the SQL Text window. 2. Click the Generate Virtual Indexes button. 3. For each index scenario the virtual plan is displayed which is the execution plan under simulated indexes. Use the Comparer layout to compare the execution plan of the original SQL and the virtual plan of the index scenario. This will allow you to determine if the index set would offer performance improvement. Test the Index Sets with the Original SQL statement The Execute function provides an efficient way of testing indexes. It physically creates the indexes on the database, runs the SQL statement, and then drops the indexes. Note: This process may impact the performance of other SQL statements. 4. Select the index sets that you would like to test. 5. Click the down arrow on the right of the Execute Scenario button and select Execute Selected.

5 Page 5 6. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process. When the batch run is finished, the Execution Statistics layout is displayed. 4.0 SQL Inspector You can retrieve executed SQL statements from the Oracle SQL Area or currently running SQL statements from Oracle s open cursor. After you have captured the SQL statements and statistics according to your retrieval criteria, you may view all information graphically to help you identify the resource intensive SQL statements. After you have identified these SQL statements, you can then move a specific SQL statement to the SQL Editor so you can tune it. Or, you can add the Inspector to the SQL Scanner enabling you to identify potentially problematic SQL statements. Note: To use the SQL Inspector, you must have the privileges view SYS.V_$SQLAREA, SYS.V_$SESSION, SYS.V_$OPEN_CURSOR, and either SYS.V_$SQLTEXT_WITH_NEWLINES or SYS.V_$SQLTEXT. 4.1 Retrieve previously executed SQL statements from Oracle SQL Area 1. Click the SQL Inspector button or select Tools SQL Inspector. 2. If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the Inspector Manager window appears. Click the Add Inspector button or select Inspector Add Inspector to bring up the Add Inspector wizard. 3. Under the General Information page, select Snapshot or the Inspector type. 4. Enter an Inspector name. 5. Under the Order By page, the first column in the Order By box is the column that is used to extract the SQL statements when you are not displaying all records. So if you are displaying only 100 records, it will extract the top 100 records based on the first column you select in the Order by box. Note: If you have a larger SGA, it will take some time to sort the SQL statements before the extraction is done. 6. You can narrow the selection criteria for the SQL statements that the Inspector will extract. Under the Collecting Criteria page, select Top n records and set the Number of records to be displayed to your desired number. 7. Under the Snapshot Setting page, adjust the date and time for the SQL statements you want to retrieve from the Oracle SQL Area. 8. Adjust any other settings in the Add Inspector wizard to satisfy your requirements. Click Finish. 9. Click the Inspect button or select Schedule Inspect to start retrieving the SQL statements and statistics from the Oracle SQL Area. Once the SQL statements have been retrieved, a chart preview will also be displayed. 10. Click the Inspected SQL Viewer button or select Inspector Inspected SQL Viewer to view all retrieved SQL statements and statistics in more detail, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics. 11. Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL

6 Page 6 Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section. Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console. 4.2 Retrieve currently running SQL statements 12. Click the SQL Inspector button or select Tools SQL Inspector. 13. If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the Inspector Manager window appears. Click the Add Inspector button or select Inspector Add Inspector to bring up the Add Inspector wizard. 14. Under the General Information page, select Monitor for the Inspector type. 15. Enter an Inspector name. 16. Under the Monitor Settings page, set the amount of time you want to monitor the incoming SQL statements. 17. Adjust the other settings under the tabs across the top of the Add Inspector window to satisfy your requirements. Click Finish. 18. Click the Inspect button or select Schedule Inspect to start the monitoring process. 19. The monitoring process will stop according to the duration or end time defined on the Add Inspector wizard. If you want to stop the monitor before the set time, click the Abort Inspect button. 20. After the monitoring function is complete, a chart preview will also be displayed. 21. Click the Inspected SQL Viewer button or select Inspector Inspected SQL Viewer to view all retrieved SQL statements and statistics in more details, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics. 22. Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section. Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console. 23. Another way of identifying a problematic SQL statement is to scan the Inspector file using the SQL Scanner section. 5.0 SQL Scanner Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, SQL Inspector files, and application source codes. The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective execution plans from Oracle, and then performs an analysis that determines which of these SQL statements are likely to contain performance bottlenecks. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer and/or examine the extracted SQL statements with their execution plans.

7 Page 7 Tip: An effective use of the SQL Scanner is to review existing code to proactively identify the SQL statements that can potentially cause performance problems without the need of executing the applications. In this way you can prevent performance degradation. Another effective use of the SQL Scanner is to locate the SQL that is causing performance problems in your applications. For example, if you know that you have a slow running report, you can scan the program text or binary file to extract the SQL statements that it contains without having to execute it. The SQL Scanner identifies SQL statements that are likely to create performance problems. You can then use the SQL Optimizer to provide alternative SQL statements that may improve the performance. 1. Click the SQL Scanner button or select Tools SQL Scanner. The SQL Scanner is a separate module. It logs into Oracle using the same user logon that was used to connect to SQL Tuning. 2. When you scan the database objects or the application files, you first create a Group to store the items you want to scan. If this is the first time you have used the SQL Scanner, the Create Group window appears. Otherwise, click the Create button in the Group Manager window. 3. Enter a new Group name, e.g. Test. Click OK to close the Create Group window. 4. Check that your new Group name is highlighted in the list box. Click the Open button. 5. The selected group is opened in the Job Manager window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files, database objects, or Inspector files you want to scan. Note: If you are using an existing Group, click the Add Jobs button or select Group Add Jobs. 6. In the Add Jobs wizard, click Next until you are at the page for the item that you want to scan. Database Objects page a. Expand the database user branch on the left side of the window. b. Highlight the schema, a database object type, or an individual database object, and click the Add Database Objects button to move the item to the right pane. (Whether or not you can scan all of the selected database objects depends on your database privileges.) SQL Inspector page a. From the left pane, select the Inspector. b. Click the Add SQL Inspector button to move the Inspector to the right pane. c. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning. Source Code page a. Click the Text or binary files, Oracle SQL*Plus scripts, or COBOL programming source code option. b. Click the Add button and select the files you want to scan. c. Click the Open button to insert the files in the Add Jobs wizard. d. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning. Summary page

8 Page 8 a. If you want to scan for SCLD SQL statements, select the Scan as SCLD checkbox to use the scanning algorithm to find single command line dynamic (SCLD) SQL statements. An SCLD SQL statement is a statement that is all on one command line, is within a quoted string, and may have a parameter that is inserted by the application. This is a common format for programming languages like Java and Visual Basic. 7. Click the Scan button or select Job Scan to start the scanning process. 8. Details are filled in the Job Manager grid as the scanning process completes each job. It shows you how many SQL statements were found in the Job and how each SQL statement is classified. 9. To view the scanned SQL statements, highlight the item by clicking the row and click the Scanned SQL Viewer button or select Job Scanned SQL Viewer. 10. The name of the file or database object appears at the top of the window in the drop down list box. 11. The first SQL statement found is shown in the top pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the window to view the other SQL statements. 12. Notice the execution plan for each statement in the upper right pane. The lower right pane tells why the SQL statement is classified as simple, complex, problematic, or invalid. 13. You can narrow the number of source SQL statements to view only the problematic and/or complex statements with View Problematic SQL and/or View Complex SQL. 14. Select one SQL statement you would like to tune. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3 Note: SQL statements need to be manually copied from the SQL Scanner and pasted in the SQL Optimization console. 15. A new copy of SQL Tuning is opened and the SQL statement is copied to the SQL Text window of the SQL Optimization Console using the same user logon as the scanned SQL statement.

Quest SQL Optimizer. for Oracle 8.0. User Guide

Quest SQL Optimizer for Oracle 8.0 User Guide 2010 Quest Software, Inc. ALL RIGHTS RESERVED. This guide contains proprietary information protected by copyright. The software described in this guide is

More information

for Oracle 7.5.3 User Guide

Quest SQL Optimizer for Oracle 7.5.3 User Guide 2010 Quest Software, Inc. ALL RIGHTS RESERVED. This guide contains proprietary information protected by copyright. The software described in this guide is

More information

MyOra 3.0. User Guide. SQL Tool for Oracle. Jayam Systems, LLC

MyOra 3.0 SQL Tool for Oracle User Guide Jayam Systems, LLC Contents Features.. 4 Connecting to the Database.. 5 Login.. 5 Login History.. 6 Connection Indicator.. 6 Closing the Connection.. 7 SQL

More information

Creating a Patch Management Dashboard with IT Analytics Hands-On Lab

Creating a Patch Management Dashboard with IT Analytics Hands-On Lab Description This lab provides a hands-on overview of the IT Analytics Solution. Students will learn how to browse cubes and configure

More information

MyOra 3.5. User Guide. SQL Tool for Oracle. Kris Murthy

MyOra 3.5 SQL Tool for Oracle User Guide Kris Murthy Contents Features.. 4 Connecting to the Database.. 5 Login.. 5 Login History.. 6 Connection Indicator.. 6 Closing the Connection.. 7 SQL Editor..

More information

Working with SQL Server Integration Services

SQL Server Integration Services (SSIS) is a set of tools that let you transfer data to and from SQL Server 2005. In this lab, you ll work with the SQL Server Business Intelligence Development Studio to

More information

SQL Server 2005: SQL Query Tuning

SQL Server 2005: SQL Query Tuning Table of Contents SQL Server 2005: SQL Query Tuning..3 Lab Setup..4 Exercise 1 Troubleshooting Deadlocks with SQL Profiler..5 Exercise 2 Isolating a Slow Running Query

More information

Introduction to Microsoft Access 2010

Introduction to Microsoft Access 2010 A database is a collection of information that is related. Access allows you to manage your information in one database file. Within Access there are four major objects:

More information

ORACLE BUSINESS INTELLIGENCE WORKSHOP

ORACLE BUSINESS INTELLIGENCE WORKSHOP Creating Interactive Dashboards and Using Oracle Business Intelligence Answers Purpose This tutorial shows you how to build, format, and customize Oracle Business

More information

SQL Server An Overview

SQL Server An Overview SQL Server Microsoft SQL Server is designed to work effectively in a number of environments: As a two-tier or multi-tier client/server database system As a desktop database system

More information

Introduction to Microsoft Access 2013

Introduction to Microsoft Access 2013 A database is a collection of information that is related. Access allows you to manage your information in one database file. Within Access there are four major objects:

More information

MyOra 4.5. User Guide. SQL Tool for Oracle. Kris Murthy

MyOra 4.5 SQL Tool for Oracle User Guide Kris Murthy Contents Features.. 4 Connecting to the Database.. 5 Login.. 5 Login History.. 6 Connection Indicator.. 6 Closing the Connection.. 7 SQL Editor..

More information

Customer evaluation guide Toad for Oracle v12 Database administration

Thank you for choosing to download a Toad for Oracle trial. This guide will enable you to evaluate Toad s key technical features and business value. It can be used to evaluate the database administration

More information

Identifying Problematic SQL in Sybase ASE. Abstract. Introduction

Identifying Problematic SQL in Sybase ASE Written by Darren Mallette, Senior Technical Consultant, Dell Software Abstract Database administrators (DBAs), developers, quality assurance (QA) analysts and

More information

MS SQL Performance (Tuning) Best Practices:

MS SQL Performance (Tuning) Best Practices: 1. Don t share the SQL server hardware with other services If other workloads are running on the same server where SQL Server is running, memory and other hardware

More information

Module 15: Monitoring

Module 15: Monitoring Overview Formulate requirements and identify resources to monitor in a database environment Types of monitoring that can be carried out to ensure: Maximum availability Optimal performance

More information

Performance data collection and analysis process

Microsoft Dynamics AX 2012 Performance data collection and analysis process White Paper This document outlines the core processes, techniques, and procedures that the Microsoft Dynamics AX product team

More information

SQL Server Performance Tuning and Optimization

3 Riverchase Office Plaza Hoover, Alabama 35244 Phone: 205.989.4944 Fax: 855.317.2187 E-Mail: rwhitney@discoveritt.com Web: www.discoveritt.com SQL Server Performance Tuning and Optimization Course: MS10980A

More information

Advanced BIAR Participant Guide

State & Local Government Solutions Medicaid Information Technology System (MITS) Advanced BIAR Participant Guide October 28, 2010 HP Enterprise Services Suite 100 50 West Town Street Columbus, OH 43215

More information

Implementing and Maintaining Microsoft SQL Server 2008 Integration Services

Course 6234A: Implementing and Maintaining Microsoft SQL Server 2008 Integration Services Length: 3 Days Language(s): English Audience(s): IT Professionals Level: 200 Technology: Microsoft SQL Server 2008

More information

vtcommander Installing and Starting vtcommander

vtcommander vtcommander provides a local graphical user interface (GUI) to manage Hyper-V R2 server. It supports Hyper-V technology on full and core installations of Windows Server 2008 R2 as well as on

More information

Data Integrator Performance Optimization Guide

Data Integrator Performance Optimization Guide Data Integrator 11.7.2 for Windows and UNIX Patents Trademarks Copyright Third-party contributors Business Objects owns the following

More information

Pivot Table Reporting

QUICK DOC: [Pivot Table Reporting} Pivot Table Reporting For the purposes of this document we are using the Utility Consumption Tracking Module to create our report and charts, but almost any data exported

More information

Microsoft SQL Server: MS-10980 Performance Tuning and Optimization Digital

coursemonster.com/us Microsoft SQL Server: MS-10980 Performance Tuning and Optimization Digital View training dates» Overview This course is designed to give the right amount of Internals knowledge and

More information

DB Audit Expert 3.1. Performance Auditing Add-on Version 1.1 for Microsoft SQL Server 2000 & 2005

DB Audit Expert 3.1 Performance Auditing Add-on Version 1.1 for Microsoft SQL Server 2000 & 2005 Supported database systems: Microsoft SQL Server 2000 Microsoft SQL Server 2005 Copyright SoftTree Technologies,

More information

URL:

Prism Suite Asset Management Guide published June, 2012 This publication could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes

More information

Microsoft SQL Server Performance Tuning and Optimization

1800 ULEARN (853 276) www.ddls.com.au Microsoft 10980 - SQL Server Performance Tuning and Optimization Length 5 days Price $4070.00 (inc GST) Version A Overview This course is designed to give the right

More information

BID2WIN Workshop. Advanced Report Writing

BID2WIN Workshop Advanced Report Writing Please Note: Please feel free to take this workbook home with you! Electronic copies of all lab documentation are available for download at http://www.bid2win.com/userconf/2011/labs/

More information

Setting Up ALERE with Client/Server Data

Setting Up ALERE with Client/Server Data TIW Technology, Inc. November 2014 ALERE is a registered trademark of TIW Technology, Inc. The following are registered trademarks or trademarks: FoxPro, SQL Server,

More information

Developing SQL and PL/SQL with JDeveloper

Seite 1 von 23 Developing SQL and PL/SQL with JDeveloper Oracle JDeveloper 10g Preview Technologies used: SQL, PL/SQL An Oracle JDeveloper Tutorial September 2003 Content This tutorial walks through the

More information

SAS Task Manager 2.2. User s Guide. SAS Documentation

SAS Task Manager 2.2 User s Guide SAS Documentation The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2015. SAS Task Manager 2.2: User's Guide. Cary, NC: SAS Institute

One usually finds here artificial things, created by people. Test de wartegg interpretacion pdf.

More information

Improving the Performance of the Model Repository

Improving the Performance of the Model Repository 1993-2016 Informatica LLC. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or

More information

HDAccess Administrators User Manual. Help Desk Authority 9.0

HDAccess Administrators User Manual Help Desk Authority 9.0 2011ScriptLogic Corporation ALL RIGHTS RESERVED. ScriptLogic, the ScriptLogic logo and Point,Click,Done! are trademarks and registered trademarks

More information

!'#'$%&'(()!!!'#$%&'())*'&+%

!'#'$%&'(()!!!'#$%&'())*'&+% May 2015 BI Publisher (Contract Management /Primavera P6 EPPM) Using List of Values to Query When you need to bring additional fields into an existing report or form created

More information

5nine Hyper-V Commander

5nine Hyper-V Commander 5nine Hyper-V Commander provides a local graphical user interface (GUI), and a Framework to manage Hyper-V R2 server and various functions such as Backup/DR, HA and P2V/V2V. It

More information

SAP Business Objects Business Intelligence platform Document Version: 4.1 Support Package 7 2015-11-24. Data Federation Administration Tool Guide

SAP Business Objects Business Intelligence platform Document Version: 4.1 Support Package 7 2015-11-24 Data Federation Administration Tool Guide Content 1 What's new in the.. 5 2 Introduction to administration

More information

SAP BusinessObjects Business Intelligence platform Document Version: 4.0 Support Package Live Office User Guide

SAP BusinessObjects Business Intelligence platform Document Version: 4.0 Support Package 8-2013-10-31 Table of Contents 1 About this document..5 1.1 Who should read this document..5 1.2 Document history..5

More information

Analyzing Excel Data Using Pivot Tables

NDUS Training and Documentation Analyzing Excel Data Using Pivot Tables Pivot Tables are interactive worksheet tables you can use to quickly and easily summarize, organize, analyze, and compare large amounts

Delphi and C++Builder Decompiler is an easy-to use application which can help you to restore your lost source code. Delphi exe decompiler. If you lose your Delphi or C++Builder project sources, but have an executable file, then this tool can rescue part of lost sources.

More information

Merging Labels, Letters, and Envelopes Word 2013

Merging Labels, Letters, and Envelopes Word 2013 Merging.. 1 Types of Merges.. 1 The Merging Process.. 2 Labels - A Page of the Same.. 2 Labels - A Blank Page.. 3 Creating Custom Labels.. 3 Merged

More information

Quick Start Guide. Ignite for SQL Server. www.confio.com. Confio Software 4772 Walnut Street, Suite 100 Boulder, CO 80301 866.CONFIO.

Toad Sql Tuning Module Is Not Installed

Quick Start Guide Ignite for SQL Server 4772 Walnut Street, Suite 100 Boulder, CO 80301 866.CONFIO.1 www.confio.com Introduction Confio Ignite gives DBAs the ability to quickly answer critical performance

More information

DataPA OpenAnalytics End User Training

DataPA OpenAnalytics End User Training DataPA End User Training Lesson 1 Course Overview DataPA Chapter 1 Course Overview Introduction This course covers the skills required to use DataPA OpenAnalytics

More information

Toad for Data Analysts, Tips n Tricks

Toad for Data Analysts, Tips n Tricks or Things Everyone Should Know about TDA Just what is Toad for Data Analysts? Toad is a brand at Quest. We have several tools that have been built explicitly for developers

More information

The software shall provide the necessary tools to allow a user to create a Dashboard based on the queries created.

IWS BI Dashboard Template User Guide Introduction This document describes the features of the Dashboard Template application, and contains a manual the user can follow to use the application, connecting

More information

IBM Operational Decision Manager Version 8 Release 5. Getting Started with Business Rules

IBM Operational Decision Manager Version 8 Release 5 Getting Started with Business Rules Note Before using this information and the product it supports, read the information in Notices on page 43. This

More information

QW SQL Wizard (July 13, 2010)

QW SQL Wizard (July 13, 2010) QWSQLWIZ allows you to create a connection to an external database, supply an SQL query, and link the resulting data into a Quality Window application for analysis. Once the

More information

Introduction to Microsoft Access 2003

Introduction to Microsoft Access 2003 Zhi Liu School of Information Fall/2006 Introduction and Objectives Microsoft Access 2003 is a powerful, yet easy to learn, relational database application for Microsoft

More information

Also on the Performance tab, you will find a button labeled Resource Monitor. You can invoke Resource Monitor for additional analysis of the system.

Blaupunkt 530 software update instalation method. 1348 CHAPTER 33 Logging and Debugging Monitoring Performance The Performance tab enables you to view the CPU and physical memory usage in graphical form. This information is especially useful when you

More information

Deploying the BIG-IP System v10 with Oracle Application Server 10g R2

DEPLOYMENT GUIDE Deploying the BIG-IP System v10 with Oracle Application Server 10g R2 Version 1.1 Table of Contents Table of Contents Deploying the BIG-IP system v10 with Oracle s Application Server 10g

More information

SonicWALL GMS Custom Reports

SonicWALL GMS Custom Reports Document Scope This document describes how to configure and use the SonicWALL GMS 6.0 Custom Reports feature. This document contains the following sections: Feature Overview

More information

Effector Integrated Development Environment

Effector Integrated Development Environment User manual for the development tool of Oriana Effector. JOIN OUR UNIQUE WORLD Table of content Introduction.. 4 Simple view.. 4 Advanced view.. 4 Workflows..

More information

Microsoft Access 2010 handout

Microsoft Access 2010 handout Access 2010 is a relational database program you can use to create and manage large quantities of data. You can use Access to manage anything from a home inventory to a giant

More information

Multi-host Internet Access Portal (MIAP) Enhancement Guide version 11.0

Multi-host Internet Access Portal (MIAP) Enhancement Guide version 11.0 April 2013 Prepared By Enterprise Services Directorate DISA DECC Mechanicsburg Mechanicsburg, PA 17050 MIAP RWeb 11: New Features

More information

Introduction to Microsoft Access 2007

Introduction to Microsoft Access 2007 Introduction A database is a collection of information that's related. Access allows you to manage your information in one database file. Within Access there are four

More information

Appendix A How to create as Guide. SAS Documentation

SAS BI Dashboard 4.3 User's Guide SAS Documentation The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2010. SAS BI Dashboard 4.3: User s Guide. Cary, NC: SAS Institute

More information

1 Changes in this release

Oracle SQL Developer Oracle TimesTen In-Memory Database Support Release Notes Release 4.0 E39883-01 June 2013 This document provides late-breaking information as well as information that is not yet part

More information

How do I rearrange the format of the detail report so that subtests show as column headings instead of appearing in the body of the report?

CREATING PIVOT TABLE REPORTS How do I rearrange the format of the detail report so that subtests show as column headings instead of appearing in the body of the report? Show subtests as column headings

More information

NetIQ and LECCOTECH: Integrated Solutions for Optimal SQL Server Performance October 2003

NetIQ and LECCOTECH: Integrated Solutions for Optimal SQL Server Performance October 2003 Contents Introduction.. 1 Traditional Methods of Monitoring and Tuning.. 1 The NetIQ and LECCOTECH Solution..

More information

2 SQL in iseries Navigator

2 SQL in iseries Navigator In V4R4, IBM added an SQL scripting tool to the standard features included within iseries Navigator and has continued enhancing it in subsequent releases. Because standard features

More information

Novell ZENworks Asset Management 7.5

Novell ZENworks Asset Management 7.5 w w w. n o v e l l. c o m October 2006 USING THE WEB CONSOLE Table Of Contents Getting Started with ZENworks Asset Management Web Console.. 1 How to Get Started..

More information

Quick Guide for ImageNow (IN) Client for Human Resources

Quick Guide for ImageNow (IN) Client for Human Resources Welcome to UVa s HR Online Document Management Service known as ImageNow (IN-HR). ImageNow procedures include capturing and digitally linking documents

More information

System Administration and Log Management

CHAPTER 6 System Overview System Administration and Log Management Users must have sufficient access rights, or permission levels, to perform any operations on network elements (the devices, such as routers,

More information

Logi Ad Hoc Management Console Overview

Logi Ad Hoc Management Console Overview Version 11 Last Updated: March 2014 Page 2 Table of Contents INTRODUCTION.. 3 System Requirements.. 4 Management Console Overview.. 6 Configuration Wizard Overview..

More information

SQL Server 2008 R2 Replication High Availability Solution

SQL Server 2008 R2 Replication High Availability Solution Replication is one of the High Availability features available in SQL Server. Replication is the process of moving data and database schema changes

More information

Developing Physical Solutions for InfoSphere Master Data Management Server Advanced Edition v11. MDM Workbench Development Tutorial

Developing Physical Solutions for InfoSphere Master Data Management Server Advanced Edition v11 MDM Workbench Development Tutorial John Beaven/UK/IBM 2013 Page 1 Contents Overview Machine Requirements

More information

Asset Track Getting Started Guide. An Introduction to Asset Track

Asset Track Getting Started Guide An Introduction to Asset Track Contents Introducing Asset Track.. 3 Overview.. 3 A Quick Start.. 6 Quick Start Option 1.. 6 Getting to Configuration.. 7 Changing

More information

Managing User Accounts

Managing User Accounts This chapter includes the following sections: Configuring Local Users, page 1 Active Directory, page 2 Viewing User Sessions, page 6 Configuring Local Users Before You Begin You

More information

Spectrum Technology Platform. Version 9.0. Administration Guide

Spectrum Technology Platform Version 9.0 Administration Guide Contents Chapter 1: Getting Started..7 Starting and Stopping the Server..8 Installing the Client Tools..8 Starting the Client Tools..9

More information

for Sage 100 ERP Business Insights Overview Document

for Sage 100 ERP Business Insights Document 2012 Sage Software, Inc. All rights reserved. Sage Software, Sage Software logos, and the Sage Software product and service names mentioned herein are registered

More information

Reports in Unified Intelligence Center

Types of Reports, page 1 Stock Reports, page 3 Create Reports, page 3 Import Reports, page 5 Types of Reports There are three types of reports based on the query types: Historical report Retrieves data

More information

Managing User Accounts

Managing User Accounts This chapter includes the following sections: Active Directory, page 1 Configuring Local Users, page 3 Viewing User Sessions, page 5 Active Directory Active Directory is a technology

More information

This module covers Recording an HTTP Test for IBM Rational Performance Tester versions 8.0 and higher.

This module covers Recording an HTTP Test for IBM Rational Performance Tester versions 8.0 and higher. Page 1 of 36 This presentation covers seven topics. It gives an overview of Rational Performance Tester

More information

Outlook Profile Setup Guide Exchange 2010 Quick Start and Detailed Instructions

HOSTING Administrator Control Panel / Quick Reference Guide Page 1 of 9 Outlook Profile Setup Guide Exchange 2010 Quick Start and Detailed Instructions Exchange 2010 Outlook Profile Setup Page 2 of 9 Exchange

More information

Oracle Business Intelligence Publisher: Create Reports and Data Models. Part 1 - Layout Editor

Oracle Business Intelligence Publisher: Create Reports and Data Models Part 1 - Layout Editor Pradeep Kumar Sharma Senior Principal Product Manager, Oracle Business Intelligence Kasturi Shekhar Director,

More information

Contents. 2. cttctx Performance Test Utility.. 8. 3. Server Side Plug-In.. 9. 4. Index.. 11. www.faircom.com All Rights Reserved.

c-treeace Load Test c-treeace Load Test Contents 1. Performance Test Description.. 1 1.1 Login Info.. 2 1.2 Create Tables.. 3 1.3 Run Test.. 4 1.4 Last Run Threads.. 5 1.5 Total Results History..

More information

Reducing or increasing the number of people assigned to an activity. A sudden increase of the number of instances flowing through the process.

2 Process Simulation 2 Process Simulation.. 1 2.1 Process Simulation Overview.. 1 2.2 Process Simulation Lab.. 1 2.2.1 Creating Process Simulation Models.. 2 2.2.2 Creating the Project Simulation Definition..

More information

Oracle Database 11g: SQL Tuning Workshop

Oracle University Contact Us: + 38516306373 Oracle Database 11g: SQL Tuning Workshop Duration: 3 Days What you will learn This Oracle Database 11g: SQL Tuning Workshop Release 2 training assists database

More information

Epicor ERP Performance Diagnostic and Troubleshooting Guide 9.05.701

Epicor ERP Performance Diagnostic and Troubleshooting Guide 9.05.701 Disclaimer This document is for informational purposes only and is subject to change without notice. This document and its contents,

More information

1 Quick User Guide for Toad for Oracle 8.6 SQL Tuning SQL Tuning Version SQL Tuning definitively solves SQL bottlenecks through a unique methodology that scans code, without executing programs, to isolate poor queries that are candidates for optimization. Those queries are transformed in every way possible using automated rewrite technology, guaranteeing the same results as the original query. Once every alternative has been generated the most efficient statement for your environment can be identified. Similarly, SQL Tuning can generate virtual index scenarios to influence SQL performance.

SQL Tuning also offers Best Practices that provide tuning recommendations for other factors that affect SQL performance such as statistics. SQL Tuning provides the following functionality: Module Best Practices Function Provides database tuning guidelines for improving SQL performance. SQL Optimizer Creates SQL statements that are alternatives to your source SQL statement. Validates the performance of the source and alternative statements on your system, using your indexes, and your memory configuration to find the best performing statement. Index Expert SQL Inspector SQL Scanner Rewrites SQL statements in a recursive manner by applying SQL transformations so that every SQL statement is rewritten until all possible semantically equivalent SQL alternatives and alternative execution plans have been generated. The source and alternate SQL are then benchmarked to find the highest performing SQL statement for a database environment. Generates Index sets alternatives for a specific SQL statement.

The Virtual Index Simulator functionality provided in Index Expert displays the execution plan for every index set combination that Oracle would generate if the indexes were physically created. These Index set alternatives can be benchmarked to identify the most efficient index set for a SQL statement. Collects SQL performance data from the Oracle SQL Area. Monitors currently running SQL, or takes snapshots to identify SQL statements that consume the largest amount of resources or that are most frequently executed.

Extracts SQL statements embedded within the database objects, text/binary files, SQL Inspector files, and application source codes. It analyzes which of these SQL statements has performance bottlenecks. You can input problematic SQL Revised: October 2005 Page 1 2 Page 2 found here into the SQL Optimizer. The SQL Inspector and the SQL Scanner are in an out-of-process module which runs in a separate window.

1.0 Best Practices Best Practices does an overall analysis of a SQL statement and your database and then proposes common ways to improve performance. However, these recommendations may not be the correct solution for your application, so you need to understand and thoroughly test the recommendations before you apply them to your production system. A recommendation may help to improve a specific SQL performance, but it may affect other SQL statements as well. When evaluating the recommendations, you need to take into account that database performance is a result of the complex mix of the following: System resources (CPU, I/O, memory, database architecture, and more) Data distribution System architecture SQL execution plans User's usage behavior 1.

Enter a SQL statement into the SQL Text window. Click the Get Best Practices button. Review the recommendations in the Best Practices layout. 2.0 SQL Optimizer In the SQL Optimizer, the SQL optimization is a two-step process. In the first step, the SQL Optimizer automatically transforms the original SQL statement and generates semantically equivalent alternative SQL statements with unique execution plans. For every alternative execution plan, SQL Tuning displays the Oracle Cost estimation. Once the SQL alternatives have been generated, the second step consists of locating the most-efficient SQL alternatives for your database environment by benchmark testing the SQL alternatives against your database.

The results obtained from the second step indicate the time required to execute each SQL statement, as well as, runtime statistics. Tip: The Oracle Cost is only an estimate of the resources it takes to execute a SQL statement. It is essential to execute the alternative SQL statements in order to determine which statement performs the best in your environment.

In the SQL Text window of the SQL Details Layout, enter a SQL statement. In the upper right corner of the SQL Optimization Console, click the Parsing User option and select the user 3 Page 3 which matches your SQL statement.

Click the Optimize button. This step launches the SQL Optimizer that automatically transforms the SQL statement. The use of hints and other optimization options such as transforming a view to an inline view and the ANSI JOIN syntax are optional. In the Preferences, the intensity of the SQL transformation process is controlled by the Intelligence Level.

The Intelligence Levels control how many Oracle hints are applied to transform SQL and how many SQL alternatives are created. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress optimization process. Compare Scenarios When the optimization is finished, the Compare Scenarios Layout is displayed. At this point since you have not yet run the SQL statements, the top window in the layout displays only the Oracle Cost values. These are only estimations of how each statement will perform. You need to test each statement to obtain its actual runtime statistics.

To compare the original SQL statement and the execution plan to the alternative SQL statements, click the drop-down list at the top of the SQL Text windows to select the SQL statements you want to compare. Test the Alternative SQL statements The Execute function provides an efficient way of test running SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. For SQL statements such as INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data. To prepare to execute the source and the alternative SQL statements, click the Preferences button.

Click the Execution button. In the Scenario Execution section, select the following option that best suits your SQL statement.

All SQL Twice Running all SQL twice enables you to eliminate some factor that can affect the accuracy of the results. If some SQL statements have been recently executed, then the SQL information is likely to be resident in the cache and it may execute faster because of that. Also, if the SQL statements use different indexes, one index may be resident in the cache and the other not.

This option eliminates these factors since it runs all SQL statements twice, throws out the first execution time and uses the second one when all SQL statements and indexes should have the necessary items in the cache. Original SQL Twice The first time you access data from table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory so the following SQL statements run faster. So to have a comparable test, the first SQL is run twice and the time from the second run is compared to the time from the other statements. All SQL Once For long running SQL there is no need to run any statement twice since the effect from caching is diminished over time.

In the Row Retrieval section, select Run on server. With this option, the Execute function retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic. In the Execute Complete section, select Cancel scenarios that exceed 120% of the Original execution time.

Toad Sql Tuning Module Is Not Installed On This Machine

If your SQL statement runs in milliseconds, set the Execute each scenario n time to 10 in the Multi-Execute option. Click the down arrow on the right of the Execute Scenario button and select Execute All. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process.

When the batch run is finished, the Execution Statistics layout is displayed. To test SQL alternatives under different user loads to simulate production activity, click on the Test SQL for Scalability button. This feature requires Benchmark Factory for Databases version Once you have identified the alternative SQL statement you want to deploy, you can copy and paste it back in your application. If the SQL was submitted from Toad, you can click on the Return SQL button.

3.0 Index Expert Index Expert analyzes the syntax of a SQL statement, the relation between tables, and selectivity of the data to identify columns as index candidates. Index candidates are combined into multiple index sets and it gives you all the alternative index sets that generate a unique execution plan for the SQL statement. It does this without physically creating the indexes in the database. Index Expert provides performance estimations for every index set to assist you in selecting which index set alternatives to test, evaluate, or implement. Index alternatives can be benchmarked to identify which set will yield the greatest performance gain for the SQL statement. Enter the SQL statement in the SQL Text window. Click the Generate Virtual Indexes button.

For each index scenario the virtual plan is displayed which is the execution plan under simulated indexes. Use the Comparer layout to compare the execution plan of the original SQL and the virtual plan of the index scenario. This will allow you to determine if the index set would offer performance improvement.

Test the Index Sets with the Original SQL statement The Execute function provides an efficient way of testing indexes. It physically creates the indexes on the database, runs the SQL statement, and then drops the indexes. Note: This process may impact the performance of other SQL statements. Select the index sets that you would like to test. Click the down arrow on the right of the Execute Scenario button and select Execute Selected. Look at the progress bar in the bottom right corner of the SQL Optimization Console to see the progress of the batch run process.

When the batch run is finished, the Execution Statistics layout is displayed. 4.0 SQL Inspector You can retrieve executed SQL statements from the Oracle SQL Area or currently running SQL statements from Oracle s open cursor. After you have captured the SQL statements and statistics according to your retrieval criteria, you may view all information graphically to help you identify the resource intensive SQL statements. After you have identified these SQL statements, you can then move a specific SQL statement to the SQL Editor so you can tune it. Or, you can add the Inspector to the SQL Scanner enabling you to identify potentially problematic SQL statements.

Note: To use the SQL Inspector, you must have the privileges view SYS.V$SQLAREA, SYS.V$SESSION, SYS.V$OPENCURSOR, and either SYS.V$SQLTEXTWITHNEWLINES or SYS.V$SQLTEXT. 4.1 Retrieve previously executed SQL statements from Oracle SQL Area 1. Click the SQL Inspector button or select Tools SQL Inspector. If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the Inspector Manager window appears. Click the Add Inspector button or select Inspector Add Inspector to bring up the Add Inspector wizard. Under the General Information page, select Snapshot or the Inspector type.

Enter an Inspector name. Under the Order By page, the first column in the Order By box is the column that is used to extract the SQL statements when you are not displaying all records. So if you are displaying only 100 records, it will extract the top 100 records based on the first column you select in the Order by box. Note: If you have a larger SGA, it will take some time to sort the SQL statements before the extraction is done. You can narrow the selection criteria for the SQL statements that the Inspector will extract. Under the Collecting Criteria page, select Top n records and set the Number of records to be displayed to your desired number. Under the Snapshot Setting page, adjust the date and time for the SQL statements you want to retrieve from the Oracle SQL Area.

Adjust any other settings in the Add Inspector wizard to satisfy your requirements. Click Finish. Click the Inspect button or select Schedule Inspect to start retrieving the SQL statements and statistics from the Oracle SQL Area. Once the SQL statements have been retrieved, a chart preview will also be displayed. Click the Inspected SQL Viewer button or select Inspector Inspected SQL Viewer to view all retrieved SQL statements and statistics in more detail, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics. Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy.

Then go the SQL Optimization console, paste the SQL and then continue from the SQL 6 Page 6 Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section. Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console. 4.2 Retrieve currently running SQL statements 12. Click the SQL Inspector button or select Tools SQL Inspector.

If this is the first time you have used the SQL Inspector, the Add Inspector wizard appears. Otherwise, the Inspector Manager window appears. Click the Add Inspector button or select Inspector Add Inspector to bring up the Add Inspector wizard.

Under the General Information page, select Monitor for the Inspector type. Enter an Inspector name. Under the Monitor Settings page, set the amount of time you want to monitor the incoming SQL statements.

Adjust the other settings under the tabs across the top of the Add Inspector window to satisfy your requirements. Click Finish. Click the Inspect button or select Schedule Inspect to start the monitoring process. The monitoring process will stop according to the duration or end time defined on the Add Inspector wizard. If you want to stop the monitor before the set time, click the Abort Inspect button. After the monitoring function is complete, a chart preview will also be displayed. Click the Inspected SQL Viewer button or select Inspector Inspected SQL Viewer to view all retrieved SQL statements and statistics in more details, or click the Show SQL button from the SQL Inspector window to view the selected range of SQL statements and statistics.

Select one SQL statement that you feel is problematic and should be tuned. Select the SQL statement, right click and select copy. Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3. Another way of identifying problematic SQL statements is to scan the Inspector file using the SQL Scanner section. Note: SQL statements need to be manually copied from the SQL Inspector and pasted in the SQL Optimization console. Another way of identifying a problematic SQL statement is to scan the Inspector file using the SQL Scanner section. 5.0 SQL Scanner Use the SQL Scanner to analyze SQL statements embedded within database objects, text/binary files, SQL Inspector files, and application source codes.

The SQL Scanner extracts each SQL statement embedded within the scanned database objects and files, retrieves their respective execution plans from Oracle, and then performs an analysis that determines which of these SQL statements are likely to contain performance bottlenecks. You can copy the SQL statements analyzed as problematic (top priority) or complex (second priority) into the SQL Optimizer and/or examine the extracted SQL statements with their execution plans. 7 Page 7 Tip: An effective use of the SQL Scanner is to review existing code to proactively identify the SQL statements that can potentially cause performance problems without the need of executing the applications. In this way you can prevent performance degradation. Another effective use of the SQL Scanner is to locate the SQL that is causing performance problems in your applications.

For example, if you know that you have a slow running report, you can scan the program text or binary file to extract the SQL statements that it contains without having to execute it. The SQL Scanner identifies SQL statements that are likely to create performance problems. You can then use the SQL Optimizer to provide alternative SQL statements that may improve the performance. Click the SQL Scanner button or select Tools SQL Scanner. The SQL Scanner is a separate module. It logs into Oracle using the same user logon that was used to connect to SQL Tuning. When you scan the database objects or the application files, you first create a Group to store the items you want to scan.

If this is the first time you have used the SQL Scanner, the Create Group window appears. Otherwise, click the Create button in the Group Manager window. Enter a new Group name, e.g. Click OK to close the Create Group window. Check that your new Group name is highlighted in the list box.

Click the Open button. The selected group is opened in the Job Manager window. For a new Group, the Add Jobs wizard is automatically opened so you can select what files, database objects, or Inspector files you want to scan. Note: If you are using an existing Group, click the Add Jobs button or select Group Add Jobs. In the Add Jobs wizard, click Next until you are at the page for the item that you want to scan. Database Objects page a. Expand the database user branch on the left side of the window.

Highlight the schema, a database object type, or an individual database object, and click the Add Database Objects button to move the item to the right pane. (Whether or not you can scan all of the selected database objects depends on your database privileges.) SQL Inspector page a. From the left pane, select the Inspector. Click the Add SQL Inspector button to move the Inspector to the right pane. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning. Source Code page a.

Click the Text or binary files, Oracle SQL.Plus scripts, or COBOL programming source code option. Click the Add button and select the files you want to scan. Click the Open button to insert the files in the Add Jobs wizard. Set the Scan using Parsing User in the drop-down boxes to correspond with the SQL that you are scanning. Summary page 8 Page 8 a.

If you want to scan for SCLD SQL statements, select the Scan as SCLD checkbox to use the scanning algorithm to find single command line dynamic (SCLD) SQL statements. An SCLD SQL statement is a statement that is all on one command line, is within a quoted string, and may have a parameter that is inserted by the application. This is a common format for programming languages like Java and Visual Basic.

Click the Scan button or select Job Scan to start the scanning process. Details are filled in the Job Manager grid as the scanning process completes each job.

It shows you how many SQL statements were found in the Job and how each SQL statement is classified. To view the scanned SQL statements, highlight the item by clicking the row and click the Scanned SQL Viewer button or select Job Scanned SQL Viewer. The name of the file or database object appears at the top of the window in the drop down list box. The first SQL statement found is shown in the top pane. Click the tabs, e.g.

SQL1, SQL2, SQL3, etc., at the bottom left of the window to view the other SQL statements. Notice the execution plan for each statement in the upper right pane. The lower right pane tells why the SQL statement is classified as simple, complex, problematic, or invalid. You can narrow the number of source SQL statements to view only the problematic and/or complex statements with View Problematic SQL and/or View Complex SQL. Select one SQL statement you would like to tune. Select the SQL statement, right click and select copy.

Then go the SQL Optimization console, paste the SQL and then continue from the SQL Optimizer Section, Step 3 Note: SQL statements need to be manually copied from the SQL Scanner and pasted in the SQL Optimization console. A new copy of SQL Tuning is opened and the SQL statement is copied to the SQL Text window of the SQL Optimization Console using the same user logon as the scanned SQL statement.