Application user is complaining the database is slow.How would you find the performance issue of SQL queries?

High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the session is doing join your query v$session with v$session_wait. 

SELECT NVL(s.username,'(oracle)') as username,s.sid,s.serial#,sw.event,sw.wait_time, sw.seconds_in_wait, sw.state FROM v$session_wait sw,v$session s

WHERE s.sid=sw.sid and s.username= '&username'ORDER BY sw.seconds_in_wait DESC;

1.Check the events that are waiting for something.

2.Try to find out the objects locks for that particular session.

3.Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.

4.Run SQL Tuning Advisor (@$ORACLE_HOME/rdbms/admin/sqltrpt.sql) by providing SQL_ID as the input for generating the findings and recommendations.

SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics.

SQL Tuning Advisor suggests indexes that might be very useful.

SQL Tuning Advisor suggests query rewrites.

SQL Tuning Advisor  suggests SQL profile.

More:

1.Run TOP command in Linux to check CPU usage.

2.Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.

3.Enable the trace file before running your queries,then check the trace file using tkprof create output file.

According to explain plan check the elapsed time for each query,then tune them respectively.

Cheers

Rajani