01--oracle Performance Article notes

1. Connect plsql Show SQL execution time
Sql>set TIMING on
Sql>select * from T_user;
Sql>select user_name from T_user;
2, avoid using *, specific query to a column
3, query some rows of data, do not query all the data, with where romnum<=n order by a column
4, rowID efficient deletion of duplicate records:
Each record has a column named rowID, which is unique throughout the database and determines which data file, block, and line in Oracle is deleted, and the following is the most efficient:
Delete from Stu S
Where s.rowid> (select min (t.rowid)
From Stu T where T.sno=s.sno)

When there are more duplicate records in the table:
Delete from Stu S
where S.rowid not in (select min (t.rowid)
From Stu T where T.sno=s.sno)
5, high efficiency statistics record line number:
Select Table_name,num_rows
From User_tables
Where Table_name= ' Stu ';
Note: All user table basic information is saved in the User_tables view, including table space, status, cache, etc.
6, more use of commit statement, can release more resources. The resources released include:
(1) Information for recovering data on rollback segments
(2) space in REDO LOG buffer
(3) Locks obtained by program statements
7. Replace the in predicate with exists, the subquery with the EXISTS predicate does not return any actual data, it only produces the logical truth true or FALSE
Not in is the least efficient [because a table in a subquery performs a full table traversal], so avoid using the not
8. Replace the > operator with >=
9. Avoid using the DISTINCT statement in the SELECT statement [distinct uses a double-loop query to eliminate duplicate records, which requires comparison of each row of records in the table, affecting performance]
10. EXPLAIN PLAIN for + SQL to interpret the statement
SELECT * FROM table (dbms_xplan.display) View execution plan

01--oracle Performance Article notes