How to optimize PLSQL

xiaoxiao2021-03-05  33

(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

转载请注明原文地址:https://www.9cbs.com/read-33900.html

New Post(0)