Oracle performance adjustment and optimization (3) (1)

BKJIA quick translation] As mentioned in sections 1st and 2nd, there are several relatively easy steps you can take to improve performance, one of these steps involves the use of automated tools to "Guide" you to write SQL statements, there are many manufacturers of Production Performance Analysis or performance adjustment tools, in the following article, we will examine one of them.

Quest Software

Many DBAs and developers use a tool named Toad, which comes from Quest Software. According to the Quest Software website, there are about 500,000 users in the Toad user community, one feature of Toad is its ability to optimize SQL queries. In other words, Oralce has not yet occupied the market for Tuning Advisor tools.

You have a variety of options for advisor tools and understand what they do. If you are not working in a production or development environment, what do you use them? You may just like it. Even if you are in a development environment, you may not use very large data. This article focuses on how to generate a large amount of data, we will introduce another product of Quest Software: DataFactory for Oracle.

DataFactory

DataFactory is used to quickly create meaningful test data for multiple database platforms, including Oracle, DB2, Sybase, and ODBC-compatible databases. Under normal circumstances, the retail price per server is USD 595, on the Quest Software website, you can download a free 30-day version.

To obtain the software version 5.5.0), you must register it with a real email address. hotmail and gmail will be rejected, you will receive an email containing the key, which is used to unlock the application and start a 30-day trial.

The installation process is quite simple. If you run Microsoft's AntiSpyware, you may receive one or more errors. Disable Real-time protection and reinstall it.

Create secondary object

A better way to understand an application is to use its auxiliary objects. The general process is as follows:

1. Create a project

2. Create a table in the solution

3. Run the script to load data.

Unfortunately, the best way to get a refresh from the constraints that disable the system name reference is to use the built-in secondary object and use a repeated processing method, you can disable all constraints until the loading script runs without errors. However, when the bug is fixed during Quest run, I can identify and disable the constraint.

After DataFactory is started, you can choose to start the self-study teaching material. Instructions on how to load the self-study teaching material objects, that is, all the help, are included in the HTML file.

The instructions in the Help System specify which tables to use, but that is not accurate. In the next step, I will mention which tables will be used for a total of 15, all starting with the DF _ prefix ).

To create a secondary object, follow these steps:

1. Select Tools) Configure to Create the secondary object Create Tutorial Objects). The Wizard for creating the secondary object is displayed.

2. Select the appropriate database type from the drop-down list of the selected connection method.

3. Click Next ).

4. Enter the connection parameters.

5. Click Next. The completion page is displayed.

6. Click Finish.) the DataFactory auxiliary table is created.

Before creating a table, you should first create a separate solution object in the database. Using Oracle 10g, I have created a quest user object/solution and granted it the connect and resource permissions ).

Click Tools Create Tutorial Objects

The help setting wizard displays its own version and the oracle logo

A list name is displayed on the completion page.

DataFactory tells you that the creation is successful.

The project folder is displayed in the frame on the left.

Click the Run button on the main menu), and an ORA-02291 error is reported immediately, which is an error that violates integrity constraints and prompts some tables more than once ), because the data loaded on a column associated with a foreign key does not insert the corresponding data to the primary table at the same time, almost all constraints are named using the SYS_Cxxxxxx naming structure, which means they are not clear names.

To solve the Integrity Constraint problem, you can disable the constraint as long as you know which TABLE you want to modify). The following query and alter table commands show the constraints that identify and disable problems:

SQL> select owner, constraint_name, table_name, column_name2 from all_cons_columns3 where constraint_name like '%9814%';OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ----- ------------------------------ -------------------- ------------QUEST SYS_C009814 DF_ORDERS CUSTID SQL> alter table df_orders2 disable constraint sys_c009814;Table altered.

Result Results) the window shows that your script has been fully successfully executed, which means you can see what you have created.

Instead of analyzing a table at a time, use the built-in DBMS_STATS which is the recommended analysis toolkit for Oracle). If you are using Oracle 10 Gb, you should add a WHERE dropped = 'no' condition to prevent the table to be deleted from being displayed when the query result is displayed.

SQL> execute dbms_stats.gather_schema_stats('QUEST');PL/SQL procedure successfully completed.SQL> select table_name, num_rows2 from user_tables3 where dropped='NO';TABLE_NAME NUM_ROWS-------------------- ----------DF_TITLES 100DF_MOVIE_CUSTOMER 1100DF_MOVIE_EMPLOYEE 900DF_DUMMY 1100DF_AUTHORS_TITLES 1100DF_MOVIE_RENTAL 700DF_PRODUCTS 100DF_MOVIE_TAPE 400DF_CUSTOMERS 1100DF_AUTHORS 1100DF_MOVIE_DISTRICT 1100DF_ORDERS 101DF_MOVIE_MOVIE 900DF_ORDERDETAILS 200DF_MOVIE_STORE 50015 rows selected.

Return to the project or list. If you select a table from the list, the Data Types of its columns and columns will be displayed. You may need to Results in the Results) and child segments) use the fixed button function.

Using DF_MOVIE_CUSTOMER as an example, how can I view its data? The random string option is exactly used here.