Search This Blog

Total Pageviews

Thursday 27 May 2010

Oracle Top 20 Parallel SQL

SELECT * FROM
(SELECT substr(sql_text,1,100) sql,
ELAPSED_TIME, CPU_TIME,
disk_reads, executions,
disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE ( hash_value, address ) in (
SELECT distinct HASH_VALUE, address
FROM v$sql_plan
WHERE DISTRIBUTION is not null )
and disk_reads > 1000
ORDER BY ELAPSED_TIME DESC)
WHERE rownum <= 20;

Warning: Product user profile information not loaded!

SQL> connect scott/XXXX
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> connect system/XXXX
Connected.


SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Oracle DBA

anuj blog Archive