| |
 |
|
Oracle v$sqlarea script
|
Chapter 2 Inside the
Oracle Shared Pool
The sample report above was generated to force a read count
of 10. Usually, disk reads will be in the range specified by the
define statement. By tuning those statements which show large
amounts of disk reads, the overall performance of the application is
increased.
In Oracle9i the v$sqlarea DPT has been expanded
to include timing data related to total elapsed time and elapse CPU
time. These timing statistics help the DBA to monitor for SQL that
is using the majority of time for its parsing and execution. The
following select shows how to pull the top X SQL statements from the
v$sqlarea DPT.
SELECT * FROM(
SELECT
sql_text,
ceil(cpu_time/greatest(executions,1))
ave_cpu_time,
ceil(elapsed_time/greatest(executions,1))
ave_elapsed_time,
ceil(disk_reads/greatest(executions,1))
ave_disk_reads,
persistent_mem per_mem, runtime_mem run_mem,
ceil(sorts/greatest(executions,1)) ave_sorts,
ceil(parse_calls/greatest(executions,1))
ave_parse_calls,
ceil(Buffer_gets/greatest(executions,1))
ave_buffer_gets,
ceil(rows_processed/greatest(executions,1))
ave_row_proc,
ceil(Serializable_aborts/greatest(executions,1))
ave_ser_aborts
FROM
v$sqlarea
EE CODE DEPOT FOR FULL SCRIPT
OR
Elapsed_time/greatest(executions,1)>&&elapsed_time
order by elapsed_time, cpu_time, disk_reads)
where rownum<&&num_sql
/
An example result from the above SELECT would
resemble:
AVG AVG AVG AVG AVG AVG AVG
CPU Elap Disk Per. Run. AVG Parse Buff
Rows Ser. SQL Time Time Reads Mem Mem Sorts Calls Gets Proc Aborts
------------------------------------ ---- ----- ----- ----
----- ----- ----- ---- ---- ------
SELECT INET_ADDRESS, PORT, LISTENER, 0 10000 2 792
6520 0 1 31 2
0
PRESENTATION, OPTIONS FROM
AURORA$DYN$REG
select schema, classname from 0 10000 2 624
5344 0 1 19 2 0
aurora$startup$classes$
SELECT max(version) FROM 0 5000 1 568
1656 0 1 11 1 0
"SYS"."JAVA$POLICY$SHARED$TABLE"
SELECT /* DBA_UTIL. GET_DELTA */ 82 82 0 648
2880 0 1 3 0 0
:b4-a.value FROM dba_running_stats a
WHERE a.name=:b3 and a.meas_date
between :b1-35/1440 and :b1-25/1440
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
