Example of data table performance optimization

No matter what storage technology the data table uses, after a certain amount of time and transaction processing, there will be some "fragments" in the storage space, resulting in performance degradation. In addition, due to the lack of prior estimates, it is likely to find that the size of the table space has been defined can not meet the needs of the situation, these problems are the administrator of the day-to-day management of the database maintenance process will encounter problems, to solve these problems an effective way is the reorganization of the table.

Reasons for data table performance degradation

1. Migrated Data rows
On data Miao mother  Mei  e--┱ terrier  to say coins Huan 鍪  Weapon mesh-impaired zinc accounted for oars 洳 Ascarina uniform eris Sunn button Support 鍪 asleep  Wu asleep  cage  Ying 跏 envy Neon  ligularia frequency street 匦 zhi lai neon  floss oars school  饩 fade sheath  the ㄒ The 9i system must keep a pointer to the ID of the new block that contains the data row in the initial block, and if there are many migrated rows in a datasheet, each operation must read two blocks of data in order to retrieve or update the migrated rows of data, so that the performance of the table is bound to degrade.
An effective way to solve the migration of data rows is to set up a valid storage parameter value "PCTFREE", which can help the administrator determine an appropriate PCTFREE value by analyzing the data of the table.
2. Linked Data rows
If the size of a data row is larger than the size of the block, such as the LOB (Large object, large objects) data type data table is most likely to produce a link to the data row. In each block, you design the pointer to the ID of the next block of data. Therefore, when the chain takes over a long time will also cause the performance decline.
The problem of solving a link to a data row can be stored in a physically contiguous block of data as much as possible by optimizing the storage of the blocks of data that make up the same data row.

The meaning of reorganization

Restructuring, as the name suggests, is the reorganization of the structure. An operation named reorg Wizard (Reorganization Wizard) is provided in Oracle 9i. Reorg Wizard helps administrators maintain the database by resolving space utilization to make it work well, and Reorg Wizard provides 3 reorganization features.
Reorganize specific scenario objects: You can reorganize specific schema objects, especially for data tables, you can move data tables between tablespaces, you can change table storage parameters, specify new idle table management parameters, and new transaction parameters. Oracle 9i's reorganization of the table is done by creating new data segments, copying data, deleting old data segments in 3 steps, can be done in the same table space, or in different table spaces, but the free space of the table space is large enough.
Reorganize the entire tablespace: primarily completes the free space fragmentation of the table space to improve performance.
To repair a migration row: Repair migrated data rows in a data table or datasheet partition.
The solution objects that can be reorganized in Oracle 9i include tables (including tables with LOB columns), table-wide partitions, indexes, index-range partitions, indexed clusters, and hash clusters. The row that fixes the transplant row feature is repaired, but does not perform a full reorganization of the entire object, and the schema object that fixes the migrated row includes tables and table partitions.

Reorganization of tables using the Reorganization Wizard

The reorganization of the tables in Oracle 9i can be done through the Graphical Reorganization Wizard operations. The reorganization operation will be performed using an Oracle Agent (proxy) by a job in Enterprise Manager, which requires Enterprise Manager console (Enterprise Manager console), Oracle Management on the server node that performs the reorganization. Server (Management Server) and agent, and the agent must be running on the same node at the database reorganization.
(1) "Log on to the Management Server" to log in to "Enterprise Manager", in the Manage Target navigator, select the network/database/myoracle.mynet/scenario/table/HR/EMPLOYEES options, and click the right mouse button, Select the "Reorganize" option in the pop-up shortcut menu, as shown in Figure 13.43.
(2) The "Welcome" interface of the Reorganization Wizard, shown in Figure 13.44, appears, click the button.

(3) The General Properties tab for objects that appear in the object properties interface of the Reorganization Wizard, as shown in Figure 13.45. In the object Navigator, the object is organized according to the User name/table/table name/subordinate object, and when you click to select an object, the General Information tab for that object appears in the object property editing area on the right.
Here, select Table/EMPLOYEES, and in the Table Space Drop-down list box on the General Information tab, you can change the table space that the datasheet belongs to.
(4) Switch to the Storage tab of objects in the object properties interface of the Reorganization Wizard as shown in Figure 13.46. You can modify the initial size argument for the data area of the table, the initial value argument for the number of transactions, the idle list argument for the free list, and so on, and then click the button after the settings have been modified.

A table reorganization can change the table space, but it cannot modify the table's structure, and if you choose to modify the table's structure, you cannot change the table space.
(5) The "Reorganization method" interface of the Reorganization Wizard, as shown in Figure 13.47, has two alternative methods: Offline reorganization and online reorganization, compared with the two methods of reorganization.
Speed (offline reorganization) radio buttons: means to use offline reorganization to focus on the speed of the reorganization, but you need to take the database offline, and all the actions that are reorganized offline are not recorded in the Redo log file and are therefore faster.
Availability (online reorganization) radio buttons: Represents a way to use online reorganization, focusing on usability. The generated script will take full advantage of the online functionality when the appropriate server can support it, and in the process of reorganization, the data table can still perform query operations on the data, but not insert, update, and delete operations, and still retain the various access rights to the table.
Here Select the availability (online reorganization) radio button and click the button.
(6) The staging table space interface of the Reorganization wizard appears as shown in Figure 13.48. The reorganization Wizard typically performs a reorganization by moving the data to a temporary object that it creates in the database. When the reorganization content is less than the entire tablespace, you can choose to create these temporary objects in their current tablespace, or you can use a different staging table space. Using the current table space is quicker because the object moves only once. However, creating and using a staging table space avoids the effect of a reorganization on the space in the current tablespace, with two options available for you to choose from.
Current Tablespace radio buttons: The table space in which the object is located if there is enough space to maintain the two copies of the current object, the data needs to be moved only once, so it is most efficient.
Staging Tablespace radio Buttons: If the object is in a tablespace that does not have enough space to maintain two copies of the current object, the data needs to be moved two times.
Select the current table Space radio button and click the button.

(7) There are two tabs in the effect report and job profiles interface of the Reorganization Wizard, as shown in Figure 13.49.
Effect Report tab: The effect report provides a list of errors, or other issues found about the reorganization job, and information about the objects being reorganized and the actions that will be performed.
Job Profiles tab: Generated job scripts, including a summary of database commands used to perform a reorganization. The actual script contains both database commands from the profile and Oracle transaction statements required to perform the job.
Clicking the button automatically generates an effect report and a job summary.
(8) The Reorganization Wizard will complete the results report and job profile generation process, the process lasted longer, after the completion of the interface as shown in Figure 13.50, click the Close button.

(9) Click the button in Figure 13.49 to appear in the "dispatch" interface of the Reorganization Wizard as shown in Figure 13.51. This can be used to specify when a reorganization job needs to be run.
Select "Execute Now" radio buttons to submit the job to the Oracle Agent job system and execute the job immediately.
Select the submit later radio button to set the date and time when the job was run, which is particularly useful if you want to run the job when fewer users are accessing the system, and it submits the reorganization job to the Oracle Agent for execution at the scheduled time. Select the "Execute Now" radio button and click the button.
(10) The "Overview" interface of the Reorganization wizard appears as shown in Figure 13.52. Summary report the setup parameters for the Reorganization Wizard, including the following 3 categories.
General information: Including reorganization options, staging table space, reorganization method parameters.
Job information: Include name, description, target, schedule parameters.
Selected objects: Includes objects that are selected for reorganization.
Click the OK button.

(11) The reorganization operation finally formed a job is submitted to the Oracle 9i operating system, the operation system is automatically scheduled to complete, the successful submission appears as shown in Figure 13.53 interface. A successful commit is not equal to a job being executed successfully, and the execution of the job will fail if the selected table space is not free or if there is a problem with the table space reading and writing.