Maybe this is right tool for you-SQLA!
How it starts?
Somewhere in the same time (precisely 21-NOV-2003 as written is in the file header of most source code) as AWR was coming to our lives, in the same time when STATSPACK ruled in most Oracle instances, group of Oracle core programmers introduced new way of analyzing and presenting top SQLs-SQLA. SQLA stands for SQL Area, Plan and Statistics for Top DML!! However such a reports were not ideal because beside straight result, creating any additional information (sql plan for an example) was not so easy to implement. The worst comes in presentation level where plain text files are really something not too easy to maintain as html for an example. Remember that STATSPACK was in that time best in plain output-half formatting "report like" text file. This is where SQLA takes place...
We all know many ways to find Top SQLs in our database. here is one example which shows Top 10 SQLs order by buffer_gets:
set linesize 170;
set pagesize 80;
col sql_text for a80;
SELECT * FROM (
select round(buffer_gets/(executions+1)) as buffer_gets_per_exec,
executions,
buffer_gets,
elapsed_time,
elapsed_time / executions time_per_exec,
sql_text
from v$sqlarea
where buffer_gets/(executions+1) >= 20
and executions >= 1
order by buffer_gets desc
)
WHERE ROWNUM<=10
;
and the output is like (shorten output version): set pagesize 80;
col sql_text for a80;
SELECT * FROM (
select round(buffer_gets/(executions+1)) as buffer_gets_per_exec,
executions,
buffer_gets,
elapsed_time,
elapsed_time / executions time_per_exec,
sql_text
from v$sqlarea
where buffer_gets/(executions+1) >= 20
and executions >= 1
order by buffer_gets desc
)
WHERE ROWNUM<=10
;
| BUF_PER_EXEC | EXEC | BUF_GETS | SQL_TEXT |
| ------------ | ----- | -------- | -------------------------------------------------- |
| 233337 | 1 | 466674 | delete from sys.wri$_optstat_histgrm_history where nvl(:1, savtime) <= savtime and savtime < nvl(:2, savtime+1) and obj# = nvl(:3, obj#) and intcol# = nvl(:4, intcol#) |
| 113171 | 1 | 226341 | delete from WRH$_SYSMETRIC_HISTORY tab where ( :beg_snap <= tab.snap_id and tab.snap_id <= :end_snap and dbid = :dbid) and not exists ( select 1 from WRM$_BASELINE b where (tab.dbid = b.dbid) and (tab.snap_id >=b.start_snap_id) and (tab.snap_id <= b.end_snap_id) ) |
| … | |||
| 10788 | 2 | 32364 | BEGIN prvt_advisor.delete_expired_tasks; END; |
| SQL> |
How it works?
Basically SQLA runs query against V$SQLAREA, V$SQL_PLAN, V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA views for retrieving "Top x" DML commands according to BUFFER_GETS and DISK_READS (logical and physical reads).Once it identifies top SQL in terms of logical or physical reads (usually top 10 or something like that), it reports the actual SQL statements (as full text), as well as their explain plan. If "execution plan" if used (in 9i and certainly not in 8i this is not mandatory way of executing sql!) SQLA can run on Oracle 9i but Oracle 10g is also supported (especially Oracle XE-free version). Oracle 11g (which I haven’t tested) should also be in the list-I do not see any problems but for real results need additional testing. It also reports CBO stats for accessed objects and indexed columns.
When used on an Oracle Apps database, it may report the actual application user who issued the expensive SQL through a FORM or Concurrent Program.
How to install?
- Unzip file SQLA.rar
into dedicated directory on db server preserving case on all scripts names in UPPER CASE (i.e. SQLAREAT.SQL).
- Run sqlplus from that directory all the time.
- If you are using script SQLAREAT.SQL for the first time, connect as main application user (APPS if using Oracle EBS or any other privileged user in all other cases whith access to most schema objects
including DBA and V$ views) and execute:
sqlplus apps/apps_passwd (priv_user/priv_passwd);Use that kind of starting point for all other scripts running SQLA.
SQL> START SQLACREA.SQL;This step creates a staging repository that is used by SQLAREAT.SQL.
If not sure if the staging repository has been created or not, simply execute SQLACREA.SQL and it will re-create it. - If you get PLS-00201 errors, execute SQLAGRNT.SQL as SYSTEM, SYS or INTERNAL
How to use?
- Once the staging repository is created, execute SQL> START SQLAREAT.SQL;
- SQLAREAT.SQL creates an HTML spool file with most expensive SQL. Execute this script manually or within a cron job.
Suggested frequency is every 15 minutes during peak time (high system load window). Executing this script in cron job is very similar idea like one expressed in my previous blog topicwhich deals with pure AWR reports Automated AWR reports in Oracle 10g/11g - If SQLAREAT.SQL is used over a period of time (i.e. peak hours), use included SQLAREAR.SQL scipt to extract most expensive SQL observed during a range of snapshots captured previously by previously mentioned SQLAREAT.SQL SQL> START SQLAREAR.SQLi.e.SQL> START SQLAREAR.SQL LR 1 4;Where p_process_type is LR for logical reads or PR for physical reads.
- Regardless it is not a real parameter, p_top is hard coded in SQLAREAT.SQL script itself to default value "10". This means that only top 10 SQLs will be extracted from range of snaps. Changing this parameter allows you to wide/narrow behavior of SQLA reports.
- In addition to SQLAREAT.SQL and SQLAREAR.SQL, use the SQLAREAS.SQL if you want to report additional statistics SQL> START SQLAREAS.SQL;
How to uninstall?
- If you need to uninstall this tool, execute commands below and remove scripts SQLA* from dedicated directory SQL> START SQLADROP.SQL
- If you has previously executed SQLAGRNT.SQL, then in uninstall process use SQLAREVK.SQL for proper when uninstalling
For the end...
In the time I was writing this post I realize that Note:238684.1 from Oracle support, which was the basic link where you can see all original information about SQLA (as well as how to download them) are no longer available. Instead of this I saw short notice:Deprecated. Use AWR instead.
So this post is even more interesting because there is no official download now. For many of us...old fashioned DBA or "poor" Oracle user...not to mentioned again Oracle XE users...
P.S.
In the time of writing this post I was not aware of any Oracle copyright or licensing on this product. However use it on your own responsibility or in agreement with official Oracle dealer.
Cheers!
