With a demanding database, you may want to know how to find out the most expensive sqls. Running on Oracle, we have Cost-Based Optimizer, the optimizer will choose the lowest cost of sql plan to run. So, if we could list the highest optimizer cost, we can map them to the most consuming sql statements.
Oracle offers a lot of SQL related dynamic views for us to check. In this case, we can check V$SQL, which has a column called OPTIMIZER_COST is calculated by optimizer.
Since 9i does not record "this sql execution start time", so we use the "last load time" instead of "execution start time", which is the time that SQL child cursor last loading into library cache and it's a VARCHAR2 type, so we need to convert it to date in the statement. Now, let's see an example:
SELECT *
FROM
(SELECT a.inst_id,
substr(a.sql_text,1,50) SQL_TEXT,
a.optimizer_cost,
b.username
FROM gv$sql a,
dba_users b
WHERE a.PARSING_USER_ID =b.user_id
AND sysdate - to_date(a.last_load_time,'yyyy-mm-dd/hh24:mi:ss') <=7
ORDER BY a.optimizer_cost DESC nulls last
)
WHERE rownum<=10;
Then, you will see the top 10 consuming statements.
Note that, the above statement is for RAC environment, if you have a standalone db, you will need to replace the GV$SQL to V$SQL.
But, after a period of watching, you may discover that some statements on the list are usually keep unchanged next week and next month ... , so you may want to focus on specific users, hence, try to add a predicate in the first where clause, like this:
...
WHERE a.PARSING_USER_ID =b.user_id
AND b.username IN ('USER1','USER2')
...
There are useful columns in V$SQL that can be an indicator also, like CPU_TIME, ELAPSE_TIME. But ELAPSE_TIME represents a clocking behavior including many waits, it implies the resource consuming with less accurate. If your concern is IO performance, IO_COST in V$SQL_PLAN can be your indicator.
Since top consuming statements are usually good candidates to be tuned, now you have a good standing to ask developers to focus them.