Oracle Performance Tuning related Queries

Oracle Performance Tuning related Queries

SQL to identify the query which takes long time

Step1 :  Run the first query ,  this will list all the programs that currently running in Application. Take the SID and use it in the second query.

SELECT
      f.user_name
      ,a.request_id "Req Id"
      ,a.concurrent_program_id "Prg Id"
      ,a.RESPONSIBILITY_ID Responsibility
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vs.serial# "Serial#"
      ,vp.spid
      ,TO_CHAR(request_date,'DD-MON-YY hh24:mi:ss') request_date
      ,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||c2.user_concurrent_program_name "Program"
FROM APPLSYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
    ,v$session vs
    ,v$process vp
WHERE
      a.controlling_manager = b.concurrent_process_id
  AND a.concurrent_program_id = c.concurrent_program_id
  AND a.program_application_id = c.application_id
  AND c2.concurrent_program_id = c.concurrent_program_id
  AND c2.application_id = c.application_id
  AND a.phase_code IN ('I','P','R','T')
  AND a.requested_by = f.user_id
  AND b.queue_application_id = q.application_id
  AND b.concurrent_queue_id = q.concurrent_queue_id
  AND c2.LANGUAGE = 'US'
  AND a.oracle_process_id = vp.spid
  AND vs.paddr = vp.addr
ORDER BY 9

Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query  ( select / insert / update ) which is actually taking time in your concurrent program.

SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE  


====================

Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

The following diagram will show a clear picture:

So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Example:
Recently I came across the following problem.

The original query was
SELECT DISTINCT so.departure_airport, so.airport_name
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170' AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
ORDER BY price ASC

Table data:
departure_airport    airport_name                       price
'MAN'                      'Manchester Airport'           344
'MAN'                      'Manchester Airport'           288
'MAN'                      'Manchester Airport'            316
'BRS'                       'Bristol'                             289
'BRS'                       'Bristol'                             345
'BRS'                       'Bristol'                             317
'BHX'                      'Birmingham Airport'             343
'BHX'                      'Birmingham Airport'             287
'BHX'                      'Birmingham Airport'             315

Here the query is executed as follows:

All the records from both the tables are taken.

Conditions mentioned in the WHERE clause is applied fetching say 9 records.
Now the distinct of departure_airport and airport_name alone is taken since price is not in SELECT clause. This gives you three rows randomly selected (mostly in the order of insertion to the table!)
And the result is sorted based on price (incorrect price column due to distinct keyword).
So the output is not ordered properly based on price!

So we need to rewrite the query as follows:

All the records from both the tables should be taken.

Conditions mentioned in the WHERE clause needs to applied on the resulting resultset.

Group the resultset based on departure_airport and airport_name to get the minimum price in each group.

Departure_airport               airport_name                                   minprice
'BHX'                                'Birmingham Airport'                       287
'BRS'                                 'Bristol'                                       289
'MAN'                                'Manchester Airport'                      288

Select the columns departure_airport, airport_name and min(so.price) from the resultset. And finally apply the ORDER By clause which orders the resultset based on the column min(so.price).

So the query should be:
SELECT so.departure_airport, so.airport_name, MIN(so.price) AS minprice
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170'
AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
GROUP BY so.departure_airport,so.airport_name
ORDER BY minprice;

Where clause and from clause for better performance: In SQL

Order of tables in the FROM clause should be:

1. Larger table

2. Smaller table

Order of conditions in WHERE clause should be:

2. Less selective

1. More selective

All Boolean conditions without built-in functions or sub queries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.