Toad Chinese Document

Directory

I. Overview of Toad Functions
Ii. System Requirements
Iii. Installation Guide
Iv. Quick Start
1. schema browser usage
2. Introduction to SQL Editor
3. Usage of procedure Editor
4. How to debug PLSQL
5. How to Use sqlab Xpert to optimize SQL
6. How to use SQL modeler to compile queries
7. How to Use the DBA functions of Toad

I. Overview of Toad Functions

During the development of Oracle applications, it is a tedious and time-consuming task to access database objects and write SQL programs. daily management of databases also requires a lot of SQL scripts. Quest software provides an efficient Oracle Application Development Tool-toad (tools of Oracle application developers ). In the new version of Toad, the DBA module is added to help DBAs complete a lot of daily management work. It is easy to use and fast to access. With toad, we can quickly access the database through a graphical user interface to complete complex SQL and PL/SQL code editing and testing. Toad, designed by Oracle development experts for developers, is a powerful and compact professional PL/SQL development environment.
Toad has the following features:
Mode browsing:
The pattern browsing feature allows us to quickly access data dictionaries and browse tables, indexes, and stored procedures in databases. Toad provides quick access to the database, which is extremely convenient to use. The user interface is concise and the structure is reasonable. When we click a separate database object, toad immediately displays the details of this object. For example, when we click a database table, all indexes, constraints, stored procedures, SQL statements, and reference relationships with other tables related to the table are displayed on the same interface. To simplify operations, you can operate database objects in the browser window.

SQL Editor:
The main function of the SQL editor is to edit, run, and adjust SQL statements. The Toad advanced editing window includes many features to improve the productization of SQL statements written by developers. For example, you can simply generate a code template and automatically discover the package content and column name before writing SQL statements.
The SQL Editor contains an editing window and running result window, allowing developers to test the test run results during editing. The SQL editor includes not only standard editing commands, but also some enhanced functions, such as quick query of fields in the table and formatting of SQL statements. This window can process content up to 4 GB, which is very useful for large development projects. Convenient bookmarks make it easy for developers to find relevant locations. In the running result window, you can customize the configuration function and support long and long raw columns. You can detach data to a disk, print data, and edit data.

Stored Procedure Editor:
The stored procedure Editor provides the following functions: editing, compiling, testing, and debugging stored procedures and triggers. Toad provides syntax, error, and many other easy-to-use functions, such as displaying table names, column names, and Oracle functions in the pop-up window. Unlike other PL/SQL editing tools, toad allows you to operate multiple database objects in one file, you can compile an object, compile multiple objects, compile to the current cursor, and compile from the cursor. When an error occurs during running, the stored procedure stops to a problematic statement. You can use shortcuts or templates to quickly compile PL/SQL statements, or generate your own templates as needed. Toad allows you to easily edit tasks, such as Setting bookmarks, uncommenting, and formatting SQL statements.

PL/SQL debugger options:
Toad provides simple and easy-to-use PL/SQL debugging functions, saving developers valuable time for development and testing in large projects and improving the quality of application development. During the development of the stored procedure, toad can edit, debug, and run the code line by line. During running, you can enter parameters as needed and observe the changes to check whether the stored procedure is correct. During the debugging process, toad can display all breakpoints, parameters, call stacks, and output parameters in a window. With toad, it is very easy to detect errors in stored procedures. developers can run PL/SQL statements step by step to identify problems. Debugging sessions can be performed simultaneously with other program sessions.

Sqlab Xpert option:
Help developers optimize SQL, provide them with SQL Execution plans in various optimization modes, provide optimization suggestions, and compare the actual SQL running results in various modes, it helps developers develop code efficiently at high speed.

Toad can also plug-in other products, such as PL/formatter, revealnet knowledge base, and SQL impact. These products can be closely integrated with toad to provide a perfect integrated development environment. The new version also adds the DBA module to extend the applicability of toad.

Ii. system requirements:
Client:
Ü Windows 95/98/NT/2000 in both Chinese and English
Ü fully installed SQL * NET 2.0 or net8
Ü Disk: Generally, the installation requires about 20 mb of disk space. Depending on the selected module and the installation method, at least 5 MB of disk space is required (read from the network server)
Ü memory: Windows 9x/me: 16 M memory; Windows NT/2000: min 32 m]
Ü tuning module requires an additional 25 MB Space

Server:
Ü Oracle 7.3 or higher, on any hardware platform.
Ü a DBA account is required

Iii. installation:
1. Three installation methods:
A) toad and personal configuration files to PC
The default installation option is used to complete the Client installation and copy all required files to the local toad directory.
Most users should choose this installation method.
B) Personal config files to PC, read toad from network server
It is generally not recommended to read the toad program from the network server, install some custom files on the local computer, and create shortcuts to the files on the server. The network server must be configured.
C) toad to network server
Install toad on the network server so that other users can install personal config files to PC and read toad from network server.
2. Run some scripts (all under the toad/temps directory) based on the features you need)
Ü if you need toad to view the execution plan, you must run the script preptoad. SQL or notoad. SQL.
N if you want to create a toad user to store the objects required by Toad, run preptoad. SQL.
N if you want to create a toad object in the current connection user mode, run notoad. SQL.
N Note: If you select to create a toad user, you must first modify the script to specify the user's default tablespace and temporary tablespace.
Ü you need to use the profile Analyzer of Oracle8i and must run toadprofiler. SQL
Ü to enhance toad security, you must run toadsecurity. SQL

4. Getting started with Toad
1. schema Browser:
Schema browser is a distinctive module of toad. With the help of Schema browser, you can easily browse various database objects in the database, such as tables, indexes, sequences, and stored procedures, in addition, you can easily roll out DDL statements, make various modifications, and recreate the statements. To a certain type of object, the operations allowed by this column object are automatically listed.
Features:
L supports creation, viewing, and modification of all objects in the Oracle database, and integrates the functions required for managing almost all database objects.
L organize by mode-> object category-> Object operable attributes-> detailed information of the object and Object-related information, which is very organized and easy to locate the object, you can also view information about all the objects you need.
L for tables:
I. Complete table creation wizard. You do not need to enter lengthy syntax for more comprehensive consideration.
Ii. You can modify various logical and physical attributes of a table, modify column attributes, add columns, delete columns (in Oracle8i), modify the physical storage attributes of a table, and truncate and drop the table.
Iii. You can generate complete DDL statements for table creation, including storage information, permission information, constraints, indexes, and triggers, which are hard to do manually.
Iv. You can easily analyze the table and view all the information of the table. You can find the data in the table and save it as an SQL statement.
V. It is easier to reorganize the table and complete the tasks that exp/IMP cannot do. In addition, it is safer to participate less manually.

L for views, stored procedures:
I. The statements for creating views and stored procedures can be saved as text,
Ii. recompile invalid views, stored procedures, and modify view definition statements. You can easily view the Stored Procedure Code, dependencies, and permission information and save it as text.
L rollback segment,
I. simple and clear online and offline statuses for convenient online/offline operations
Ii. detailed physical information and current status of the rollback segment.
Iii. Physical storage attributes can be easily modified

L tablespace:
I. tablespace map; intuitively shows the physical distribution of data on the tablespace
Ii. intuitively view the utilization, remaining space, and fragmentation of each tablespace.
Iii. Various alter operations can be performed: online and offline, adding data files, changing the size of data files, changing physical storage properties, etc.
L complete operation support for other database objects.

2. SQL Editor:
A) There are three ways to activate SQL Editor:
I. After connecting to the database, toad automatically opens the SQL editor window.
Ii. Click the leftmost button on the toolbar.
Iii. From the menu bar: database-> SQL Editor
B) features:
L allows you to open multiple editing windows and edit multiple SQL statements at the same time.
L supports simultaneous connection to multiple databases and operations on multiple databases
L allow editing of SQL, PLSQL, Java, HTML, and text
L using the bookmarks function, you can quickly locate a statement in a large number of SQL statements
L SQL editor has powerful editing functions, making it easy to edit SQL statements.
L powerful shortcut key support and toolbar shortcut support for more efficient writing (many shortcut keys can be defined by yourself, in view-> options-> SQL editor)
L The ability to intelligently predict the code to be entered based on the preceding conditions, so that the code can be intelligently populated to some extent
L move the cursor over the table/View/stored procedure name, and press F4 to open the object description window to conveniently view the definition of the table and view, and the source code of the stored procedure,
L it is very easy to analyze the execution plan of SQL statements: click the button on the toolbar to view the explain Plan results
L some SQL * Plus commands are supported to easily execute a large number of database scripts written for SQL * Plus
L sqlab Xpert can be used to comprehensively analyze the current SQL statement and provide various reasonable optimization suggestions. You only need to click the tuning button on the toolbar:
L easy to save, open and run SQL scripts
L you can modify the query results,
L The query results can be saved in multiple formats, including HTML, plain text, and XLS.
L it is easy to get the SQL function list and usage description (show SQL Help window button)
L you can view the execution of SQL statements: the autotrace window below shows the actual execution results and execution efficiency of SQL statements.
L support for historical SQL statements (F8): You can easily query previously executed SQL statements without repeated input.
L you can execute anonymous blocks and stored procedures. The result of dbms_output is displayed in the window below, for example:

3. Stored Procedure Editor
A) several ways to enable the Stored Procedure Writer:
L click the third button on the total menu bar:
L click Database-> procedure editor,
L it can be called through schema Browser
B) some features and advantages:
1. Powerful custom settings: from the display mode of reserved words, to automatic replacement, to the definition of various shortcut keys, the more familiar you get.
2.
3. the SQL and PL/SQL keywords are automatically displayed in different colors. The syntax is clear and clear. You can customize the display methods of various keywords according to your habits. You can customize the stored procedure template. When creating a new stored procedure, the automatically generated program framework can conveniently call schema browser and place the cursor on an object. F4, schema browser opens the detailed description of this object to help you develop programs smoothly and quickly. Automatic code correction is supported. For example, if NDF is input, it is automatically replaced with no_data_found, and the input is automatically replaced with the. Similar automatic replacement can also be modified and added by yourself.
7. Easily display the left-side line number. Click the left-side line number to add or delete the debugging breakpoint.
8. Support for various shortcut keys, including not only common editing operations, but also support for custom PLSQL blocks (custom shortcuts ). For example, you can define Ctrl + Shift + D: pop up all PLSQL block types, you can choose your own.
9. In an integrated development environment, you can perform code and debugging without switching multiple nodes.
10. You can easily open multiple stored procedures at the same time and quickly switch between them through the page.
11. quickly compile the stored procedure, quickly locate the error, and select whether to compile the dependent stored procedure at the same time.
12. built-in PL/formatter. You can use PL/formatter to format the stored procedure, and provide the summary analysis and modification suggestions for the stored procedure.
PL/formatter can quickly format existing stored procedures so that all stored procedures comply with the same writing standards, which is conducive to improving the readability of the program, avoid differences in the style of modules written by different people.
PL/formatter also helps improve program modularization, enhance program structure definition, and enhance SQL sharing.
PL/formatter can also help you manage PL/SQL and Oracle versions.

13. The built-in revealnet knowledge base can be opened right-click to quickly obtain technical support and solve technical difficulties.
NPL/SQL:
N contains answers to problems that may occur throughout the development process
Ncode Library: a large number of rigorous PL/SQL code examples and ready-to-use processes, with full source code provided
N various error handling modules for reference and various character date functions
N contains more than 1400 topics
Nadmin:
N answers 80% of common questions about DBA daily work, covering over 2400 topics
N from network to performance optimization, from backup to error message
Example: What if an invalid stored procedure occurs?
4. How to debug the stored procedure?
U requires toad's independent debug option (standard version does not have this option)
To debug the stored procedure with Toad, you must install the Oracle system package dbms_debug and the Oracle probe API V2.0 or a later version.
U is integrated in stored precedure Editor, which can be compiled and debugged, greatly improving development efficiency.
U open procedure Editor, Debug menu activation, you can start debugging.
U allows row-by-row debugging and breakpoint debugging for stored procedures, and debugging for triggers.
U supports breakpoint support, watch variable support, and variable value change during runtime
U allows debug support for multi-layer calls
Examples of debugging steps:
1. Click the third button in the menu bar: open procedure Editor
2. click the button in the menu bar of procedure editor to bring up the stored procedure to be debugged.
3. click the button on the toolbar and enter the parameters to be passed in. If you do not need to pass in the parameters, click the button on the toolbar to start debugging. For example, enter the input parameters:

4. click the button in the menu bar: You can track in one step or define a breakpoint and run it directly to the breakpoint for execution in one step. During the process of running the stored procedure, the current statement is displayed with high brightness, position the mouse pointer to the variable, and toad automatically displays the current value of the variable.

5. You can stop the program execution at any time. You only need to click debug-> halt execution, or click the button on the menu bar to stop the program execution.
6. when a stored procedure calls another stored procedure, you can skip it (step over) to directly run the called stored procedure and return the call result, to continue debugging the stored procedure, you can also select trace into to open the called stored procedure and further debug the called sub-stored procedure. To maintain the debug information, select compile dependencies with debug information during compilation, which is the rightmost button in the procedure editor toolbar.
7. Real-time variable tracking: watch:
You can track a variable in real time before running: Add watch, move the mouse to the variable, and then click debug-> Add watch at cursor, this variable appears in the debug status window and returns the current value of the variable in real time. For example, the variable v_empname. Can be modified at runtime
8. breakpoint support:
Click the row number of the place where the breakpoint needs to be added to the stored procedure. The color of the row turns red. Enter the parameter and click the execute button to run it directly at the breakpoint without having to trace it line by line, more efficient debugging.
The debug option also supports conditional breakpoint. It is stopped only when the condition is met. Otherwise, it will continue to run as if the breakpoint does not exist.

? Note that the result of dbms_output.put_line appears only after the execution of the program is completed.

4. How to Use sqlab Xpert to optimize the stored procedure?
We know that the most important step of oralce database optimization is SQL optimization. Without the help of specialized tools (such as toad), SQL optimization is very difficult and boring, it also requires an in-depth understanding of Oracle and superb Oracle tuning skills. Using the sqlab Xpert tuning module of Toad enables common developers to quickly and accurately perform expert analysis on SQL.
In SQL editor and procedure Editor, select the SQL statements to be debugged (sqlab Xpert can only debug select, insert, update, and delete statements) and copy them to SQL editor, then, click the optimization button on the toolbar. Toad will automatically open the sqlab Xpert window, capture the current SQL statement, and convert it to a statement in the explain plan format.
When you open Xpert for the first time, toad will prompt you that you need to install some tables, views, packages, and other objects on the server side to store the tables in a specified tablespace (such as tools. You can simply follow the prompts to do it step by step. If the system does not allow job (job_queue_process = 0), toad prompts that some parts will not be installed now and can be ignored. After the installation is complete, you can choose to enable some non-DBA users to use the sqlab Xpert tuning function of toad.
After the installation is complete, toad will prompt you some questions about the optimization goal. You can choose to reset or set each optimization. You will always use this setting later. Of course, you can change it if needed. Reset it under the administration and preference in the sqlab option Xpert window.
Example:
The following SQL statement involves the association between multiple tables, which is complex. We can use the sqlab Xpert option of toad to help analysis:
Select a. Name. B. Name, C. emp_name
From Dep A, Manager B, EMP C
Where a. dep_no = C. dep_no
And B. manag_id = C. manag_id
And C. emp_no = 3
Copy the SQL statement to the SQL editor and click the optimization button above. Toad opens the sqlab Xpert option window.
For example:

From the figure above, we can see what the execution plan of the SQL statement is in various optimization modes. Toad provides the specific steps of each execution plan, the cost of each step, the related data involved in the table, etc. If the SQL access table has not been analyzed, toad will display in red, it also helps you analyze the table, collect statistics, and re-index the table to help the optimizer make the best choice. You can save the current analysis results to its database for future calls.
We can also seek help with the auto optimization function of sqlab: Click the auto tune button under original SQL, toad will analyze the execution results according to tkprof (of course, this requires you to go to initsid. set utl_file_dir in Ora. If the server is on a unix machine, you also need to obtain the trace file through FTP, with some small settings ).
The automatic optimization is very efficient to compare the actual running of all execution plans, and compare the actual execution time, such:

We can also ask toad to provide practical optimization suggestions: Click Original SQL and click the advice button below. Toad provides reasonable suggestions based on the SQL analysis and execution results:
For example:
1. put the table and index in two different tablespaces, because we did not specify the using index clause when creating the table and adding constraints, therefore, tables and indexes are created on the user's default tablespace.
2. Analyze the table EMP, DEP, and manager to collect the latest statistics, which helps CBO obtain the optimal execution plan.
3. Create an index on an appropriate column: manag_id and dep_no of EMP. When a table contains a large amount of data, this can greatly reduce the cost caused by full table scan.

5. SQL modeler:
SQL modeler can help developers easily write a variety of complex SQL statements. You only need to drag the mouse to write complex SQL statements.
Several ways to enable SQL modeler:
I. Choose database> SQL modeler from the menu bar and open the window.
Ii. Click the fourth button in the toolbar to open the SQL modeler window.
A window consists of three main parts:
N table selection region: the rightmost part used to select the table used in the from clause. Drag it to the left area or double-click it to select the table.
N table model area: displays the associations between selected tables and tables in a graphical manner.
N generate an SQL region: the SQL statements created in graphical mode are displayed,

In SQL modeler, you can also explain the generated SQL, debug SQL Performance, run SQL, check whether expected results are obtained, and save the generated statements, or paste the statement into SQL editor.

With the help of SQL modeler, even Junior programmers can compile complex and efficient SQL queries.
For example, the following example;

Generation steps:
1. Select the tables EMP, DEP, and manager.
2. Drag emp_name, emp_no, DEP name, and manager name of EMP with the mouse.
3. Double-click the emp_no condition column in the criteria window. In the pop-up window, enter the query conditions, such as emp_no = 3.
4. Because emp_no is not expected to appear in the result, double-click visible in the emp_no column and set it to false.
5. in this way, you can run the query to obtain the results, analyze the query SQL, open the SQL in the SQL Editor, or save it as the SQL model file, or further processing, such as adding, sorting, and summarizing.

DBA functions:
Toad is very powerful in DBA daily management and can help DBAs complete many daily tasks safely and quickly, there are some gadgets specific to Toad that can accomplish some functions that are difficult to do in common methods. The following describes the main functions of common modules:
A) database Monitor
Provides an intuitive Oracle database running status, including the following details:
We can see logical Io, physical Io, number of sessions in each State, SGA utilization, usage of the Shared Pool, waiting status of various events, and changes of various events over time.
B) instance Monitor
I. It can be used to check the running status and version of all nodes, listener, and database defined in the local tnsnames. ora file.
II. The database can be remotely started or closed (if password authentication is set)
C) UNIX monitor:
I. It can monitor the running status of Unix machines, such as detailed machine startup time, CPU utilization, main processes, resources occupied by these processes, and major disk Io.
D) trace/kill session
I. Conveniently view all information related to connected users, such as terminals, programs, and connection times.
Ii. Track a user, view the SQL statements executed by the user, and view the execution plan of the statements,
Iii. Check the Lock Object of the system, whether there is any wait for other transactions to release the lock, and easily find out what user events are waiting for and what SQL is being executed by that user.
Iv. view the status of rollback segments, users, statements, rollback segments, and read/write volumes.
E) Oracle Parameter
I. you can easily view the parameters of the Oracle System: whether the default value is used, a brief description of the role of the parameter, whether it can be dynamically changed using alter session/alter system, for those parameters that can be dynamically changed, you can double-click the mouse to modify it.

F) nls_parameter:
I. Conveniently view the NLS parameter values at the session, instance, and database levels,
II. the NLS parameter value of the session can be dynamically modified.

G) extents
I. You can view the detailed storage of tables and indexes under a user,
II. If you find tables and indexes that occupy a large amount of space, you can consider using corresponding countermeasures, such as partitioning
Iii. Find tables and indexes that are about to reach max_extents and correct them in time
IV. The indexes and tables in the same tablespace can be corrected in time.

H) tablespace
I. intuitively view the utilization, total space, used space, available space, default storage parameters, and whether the tablespace needs to be organized.
Ii. view the status of each tablespace, the data files, and the detailed information of each data file.
Iii. view the objects in each tablespace and detailed storage features of these objects
I) tablespace map:
I. graphical display of the space occupied by each table/index in the tablespace, the size of each segment, and whether the segment is continuous.
II.
J) server statistics:
I. You can intuitively see the overall operating status of the system and quickly locate the system bottleneck.
Ii. You can view the status of various wait events within the system and the status of each connection session,
K) pinned code:
I. we know that it is best to pin large stored procedures that are frequently executed to avoid swap out the memory when Oracle has insufficient memory and avoid dynamic loading, it also avoids memory cleaning when flush shared pool is used.
Ii. Determine whether to pin a stored procedure based on the size and number of executions of each module. One button is done.
L) control files:
You can view the control files in the current system and the details recorded in the control files.
(V $ controlfile, V $ controlfile_record_section)
M) redo log Manager:
I. you can easily view the log group, the current log, the name and size of the members in each group, easily add or delete the log group, switch the current log, and perform specific control on log archiving.
Ii. Almost all redo log operations can be completed on this interface.
N) repair chained rows:
I. chained rows affects the system performance. In consideration of the system performance, chained rows needs to be restored to the rows of common tables. With repaie chained rows, you can easily achieve this. Select the table to be analyzed, and analyze it to see if there is any chained rows. If so, click repair and toad will help you fix those chained rows.
O) identify space deficits
If the system encounters a 1659,1653 error, it is an unqualified DBA. However, it is difficult to predict these errors in real time. With the toad tool, you can know whether any table can be extended to the next extent if you have a tablespace. And can directly correct the problem after the problem is detected.

P) estimate table/index size
I. In the physical planning of databases, the definition of storage clauses for tables/indexes is very important and affects the system. However, it is not easy to define a reasonable storage clause for a table without any tools.
Ii. In toad, there are dedicated tools to help you properly define the storage of tables/indexes:
1. You can define the number of data to be stored in future tables.
2. Multiple estimation methods:
A) define the table size based on the analysis of existing data (most precise, but relatively slow)
B) define the table size based on the table definition data type.
C) estimate the size based on the data previously analyzed in the data dictionary (this table needs to be analyzed recently)

Q) analyze all tables
I. CBO-based SQL requires CBO to provide the optimal execution path to improve the execution efficiency of the program. However, if the statistical data of the table is not updated in time after a large number of DML operations are performed on the table, at this time, CBO cannot select the most effective execution path, resulting in low program execution efficiency.
Ii. manual analysis is troublesome. Do it one by one.
III. with the analyze alltables tool of Toad, you can analyze all the tables in the entire mode at a time. There are a wide range of control options, including whether to use estimation or completeness, and whether to analyze indexes at the same time, the percentage.
R) rebuild table
I. when the data in a table is repeatedly written into a large amount of DML, and the definition of pctfree and pctused is unreasonable, the table may become very broken, occupying a large amount of extent, and the index will also be quite broken, resulting in low efficiency.
II. The traditional method is to solve this problem through exp/IMP, but the steps are troublesome and manual operations are risky.
III. you can use the rebuild table tool to easily modify some parameters that cannot be modified through exp/IMP, such as freelists, freelists group, tablespace, and initial. You can also change the column name.
S) relocate Index
I. We know that tables and indexes generally need to be stored separately in different tablespaces, which is easier to manage and improves the performance, because they can be accessed simultaneously on tables and indexes.
II. however, in fact, for such reasons, the basic concept of separate storage of indexes is often ignored. For example, the using index clause is not added when the primary key constraint is set up, later, disable/enable will directly store the index in the same tablespace as the table because of primary key constraints and uniqueness constraints.
III. manual discovery of these indexes makes it very difficult to move one by one and is prone to errors. In toad, it is easy to find the indexes that are not properly stored based on the user/tablespace, and you can move these indexes in batches, you can also redefine the storage sub-statement of the index.
T) generate schema scripts:
I. DDL statements that can be used to generate all/Part objects of the entire user. You can save the result as an SQL statement or a custom toad format.
II. it can be used to copy the table structure. (exp/IMP has a very serious defect, that is, the table structure is dumped from a large database and cannot be imported into a small database, because it contains the storage information of the original table, even if rows = N is selected, it will occupy a considerable amount of space in the imported database)
U) Compare Schema:
I. during development, sometimes the development is performed on several development libraries at the same time, but the structure of the last two databases is not exactly the same and it is difficult to compare
II. With toad, you can compare all objects (including stored procedures) under the two database modes (users). You can select a specific comparison content.
Iii. You can directly open two connections to their respective databases, or compare them based on the files generated by toad generate schame scripts.
Iv. You can directly generate an SQL script to synchronize the two databases, and give a brief summary of the differences and detailed reports.

Reference announcement address: http://www.donews.net/lponline/services/trackbacks/199253.aspx