(Walter Fan Fetched from Oracle 9i performance Tuning Tips & Techniques Richard J.Niemiec) 1 DBMS_APPLICATION_INFO use real-time monitoring DECLARE lv_count_num PLS_INTEGER:. = 0; lv_start_time_num PLS_INTEGER; BEGIN
2. Record the timing information in the database table
3. Reduce the unit iteration of the PL / SQL program and iteration time
4. Use the RowID to process iteration
5. Standardize the data type, the arrangement of the IF statement, and the PLS_INTEGER standardization - ensure that the data type in the comparison is the same - sort the IF condition based on the frequency of the condition - use the PLS_INTEGER PL / SQL data type to operate integer operations
6. Reduce call to sysdate
7. Reduce call to mod functions
8. Preload objects in shared pool
9. Identifies the PL / SQL object that needs to be loaded.
10. Use and modify dbms_shared_pool.sizes
11. Get detailed information from DBA_OBJECT_SIZE
12. Discover invalid object Col "Owner" Format A12col "Object" format A20col "OTYPE" Format A12col "Change Date" format A20Select Substr (Owner, 1, 12) "Owner", Substr (Object_name, 1, 20) "Object" ", Object_type" otype ", to_char (last_ddl_time, 'DD-MON-YYYY HH24: MI: SS')" Change Date "from dba_objects where status <> 'valid' Order By 1, 2
Re-build all stored procedures, functions, and package begin dbms_utility.compile_schema ('gw');
13. Discover disabled triggers
14. Use the PL / SQL as a quick reference table
15. Find and adjust the SQL of the object used
16. Use time information when processing the Date data type
17. Adjust and test PL / SQL
18. Learn about the meaning of PL / SQL object positioning
19. Open a large cursor using the rollback segment
20. Use temporary table to improve performance
21. Integrated user tracking mechanisms to position execution location
22. Limit dynamic SQL use
23. Simple example
Query SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, Column_PositionFrom DBA_IND_COLUMN_POSITIONFROM DBA_IND_COLUMNSWHERE TABLE_NAME = 'xxx'and table_owner =' xxx'order by index_name, column_position
In v $ SQLAREA to find the most resource query Select B.Username Username, A.Disk_reads Reads, A.Execurn_Ratio, A. A.EXECUTIONS, 0, 1, A.EXECUTIONS, 0, 1, A.EXECUTIONS, 0, 1, A.EXECUTIONS, A. SQL_Text StatementFrom V $ SQLAREA A, DBA_USERS BWHERE A.PARSING_USER_ID = B.user_idand a.disk_reads> 100000ORDER BY A.Disk_reads DESC