Keyword ORACEL SQL Performance TUNING Source Http://www.dbasupport.com 1. Use the appropriate Oracle Optimizer Oracle's optimizer with 3: a. Rule (based on rules) b. COST (Based on cost) c. Choose Selective) Set the default optimizer, you can pass the various declarations of the Optimizer_Mode parameters in the init.ora file, such as Rule, Cost, Choose, All_Rows, First_Rows. You are of course in the SQL sentence class or a session (session) Covert it. In order to use cost-based optimizer (CBO, COST-based Optimizer), you must regularly run the Analyze command to increase the accuracy of object statistics in the database. If the database is optimizer mode Set to selectability, then the actual optimizer mode is related to whether it is running an Analyze command. If Table has been over, the optimizer mode will automatically become CBO, in turn, the database will use an optimizer in Rule. By default, Oracle uses the Choose optimizer, in order to avoid unnecessary full-table scans, you must try to avoid using the Choose optimizer, and directly use rules or cost-based optimizers. 2. Access TABLE method Oracle uses two ways to record in two access tables: a. Full table scan full table scan is to sequentially access each record in the table. Oracle uses a multi-data block (Database Block) to optimize the full surface Scan. B. Access table through the ROWID, you can use ROWID-based access methods, improve access table efficiency, RowID contains physical location information recorded in the table .. Oracle implements data and storage data with indexing (index) The connection between the physical location (RowID) is usually indexed to quickly access the RowID method, so those index-based queries can be improved. 3. Share SQL statement In order not to repeat the same SQL statement, After the first resolution, Oracle stores the SQL statement in memory. This block is shared by all database users in the shared buffer pool of the SGA (System Global Area). So, when you When executing a SQL statement (sometimes called a cursor), if It is exactly the same as the previously performed statement, and Oracle can quickly obtain the statements that have been parsed and the best execution path. Oracle's functionality greatly increases SQL execution performance and saves memory usage. Unfortunately It is Oracle to provide a cache buffering for a simple table. This feature does not apply to multi-table connection queries. The database administrator must set the appropriate parameters for this area in Init.ora, the larger the memory area, the greater the memory area. You can retain more statements, of course, the possibility of sharing is also bigger. When you submit an SQL statement to Oracle, Oracle will first find the same statement in this memory. You need to indicate, Oracle Take a strict match for both, to reach a sharing, the SQL statement must be exactly the same (including space, wrap, etc.). Shared statements must meet three conditions: A. Character level comparison: The currently executed statement and The statement in the shared pool must be exactly the same. For example: select * from EMP; and each of the following select * from Emp; select * from Emp; select * from Emp; B. The object referred to in the two statements must be identical: For example: how do users access the object name Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym Work_city public synonym Plant_detail table owner consider the following SQL statements can be shared between two users can share the cause SQL select max. (SAL_CAP) from Sal_Limit;
