Oracle Database Optimization Auxiliary SQL statement _oracle

For specific details, see Code Analysis below.

--Query executing SQL statement SELECT osuser Computer login, program to initiate the request, USERNAME login system username, SCHEMANAME, b.cpu_time spend Cpu time, STATUS, 
B.sql_text execution of SQL, B.  From V$session A left JOIN v$sql B on a.sql_address = b.address and A.sql_hash_value = b.hash_value ORDER by B.cpu_time


DESC; --Query for CPU-consuming SQL statements SELECT * FROM (select v.sql_id, V.child_number, V.sql_text, V.elapsed_time, V.cpu_time, V.disk_reads, RA

NK () over (order by v.cpu_time Desc) Elapsed_rank from V$sql v) a where Elapsed_rank <= 10; 
--Query for disk-consuming SQL statements SELECT * FROM (select v.sql_id, V.child_number, V.sql_text, V.elapsed_time, V.cpu_time, V.disk_reads,

Rank () Over (order by v.disk_reads Desc) Elapsed_rank from V$sql v) a where Elapsed_rank <= 10; --Query for slow SQL statements SELECT * FROM (select Parsing_user_id,executions,sorts command_type,disk_reads,sql_text from V$sqlarea or Der by disk_reads Desc) where rownum<10--oracle queries against uncommitted transactions select A.sid,a.blocking_session,a.last_call_et,a.event, OB Ject_name, Dbms_rowid.rowid_create (1,data_object_id,rfile#,row_wait_block#,row_wait_row#) "rowID", C.sql_text,c.sql_fulltext from V$session A,v$sqlarea C, Dba_ Objects,v$datafile where a.blocking_session is not null and A.sql_hash_value = C.hash_value and row_wait_obj#=object_id a nd file#=row_wait_file#;

ps:oracle Common SQL statement optimization

1, * number caused by the implementation of efficiency

Minimize the use of SELECT * to query, when you query using *,
The database is parsed and the * is converted to all columns.

Select COUNT (si.student_id)
From Student_info si (student_id as index)
And
Select COUNT (*) from Student_info si
Execution. The above statement is obviously faster than the following statements that do not use index statistics

2. Avoid the use of calculations on indexed columns.

In the WHERE clause, if the indexed column is part of the function. The optimizer uses a full table scan without indexing.

Example:

Low efficiency:

SELECT ... From DEPT WHERE SAL * > 25000;

Efficient:

SELECT ... From DEPT WHERE SAL > 25000/12;

3. Replace > with >=

Efficient:

SELECT * from EMP WHERE DEPTNO >=4

Low efficiency:

SELECT * from EMP WHERE DEPTNO >3

The difference is that the former DBMS will jump directly to the first DEPT equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first DEPT greater than 3 records.

4. Replace or with UNION (applicable to indexed columns)

In general, replacing OR in a WHERE clause with UNION would have a better effect. Using OR for an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, query efficiency may be reduced because you have not selected an OR. In the following example, indexes are built on both loc_id and REGION.

Efficient:

Select loc_id, Loc_desc, REGION from

LOCATION

WHERE loc_id =

UNION

SELECT loc_id, Loc_desc, region< C6/>from LOCATION

WHERE REGION = "MELBOURNE"

Low efficiency:

SELECT loc_id, Loc_desc, REGION from

LOCATION

WHERE loc_id = ten OR REGION = "MELBOURNE"

If you insist on using OR, you need to return the least recorded index column to the front.

5, in to replace or

This is a simple and easy to remember rule, but the actual execution effect also needs to examine, under Oracle8i, the two execution path seems to be the same.

Low efficiency:

SELECT .... From LOCATION WHERE loc_id = ten or loc_id = 30

Efficient

SELECT ... From LOCATION WHERE loc_in in (10,20,30);

6. Avoid using is null and is not NULL on indexed columns

7. Where execution order

Where execution is performed from top to bottom

SELECT *

From Student_info si--Student information table

Where si.school_id=10--College ID

and si.system_id=100--System ID

When placing a WHERE clause, place the conditions that can filter large amounts of data at the bottom

8, from the field of optimization:

Oracle follow loads table data from right to left, and you should put the tables that can exclude the most data behind it (the underlying table).

For example, in the associated query, put the curriculum to the back, the score table to the front, because the curriculum data is generally relatively small, associated with the time can quickly filter out some of the performance data.

9. Index invalidation

1, the operation caused by the index failure

2. Index invalidation caused by type conversion

3, the problem caused by the calculation on the index column

4, are NOT NULL caused problem (student_id is indexed)

5. The order by causes index invalidation (student_id as index)

6, automatically select the index

7.!= results in Index invalidation

8,% index failure caused by