ORACLE SQL Tuning Guide

SQL Query Performance Depend on Different Factors.

 

1) How you write the query

2) The quality of the table statistics

3) The physical data model (indexes, partitioning, etc.)

4) The logical data model (constraints, normalization, etc.)

5) The hardware of the database server

6) How the application sends SQL statements and processes their results

 

 

--------Check SQL Query Status (Row Process, CPU Time, Elapsed Time, Application Wait Time, Memory, and Other Resources For query etc.)

 

select * from  v$sqlstats

 

-------Check Session Pga Memory'

select *

from   v$mystat ms

join   v$statname sn

on     ms.statistic# = sn.statistic#

and    sn.name = 'session PGA memory';

--------Check Table Statistics

 

SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS,

       EMPTY_BLOCKS, LAST_ANALYZED

FROM   DBA_TAB_STATISTICS

WHERE  OWNER='SDBA'

AND    TABLE_NAME='CUSTOMERS';

 

----On Queries of more than 200 lines how to read or find which SQL is expensive?

Solution: Oracle SQL Developer has a SQL HotSpot button to help you find the Detail.