| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Full table scans
Kalle <kminerva_at_jippii.fi> wrote in message news:<3EB79950.8CA5BA2F_at_jippii.fi>...
> Hi all, > > how I am able to locate the worst possible sql-statements from sga.. > > Merci Beaucoup... > > Kalle
Hi Kalle, here is an SQL, it looks worse but it detects the worse
SQL's
Orca
/** [ SQL $RCSfile: TOP_SQL.sql,v $ $Revision: 1.6 $ ]
AUTHOR [ $Author: orca $@Karl Reitschuster/PLZ/CSC ] DATE_CRE [ 02.04.2003 ] DATE_MOD [ $Date: 2003/04/10 10:04:56 $ ] RUN AS [ USER_WITH_DICT_ACCESS ] DB [ Oracle 8.1.7 ] CMNT [ TOP consuming SQL : Disk Reads/Buffer Gets/Sorts ]*/
SELECT *
AS disk_exec,
to_char(greatest(s.disk_reads , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS disk_reads,
to_char(greatest(s.buffer_gets , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS buffer_gets,
to_char(greatest(s.rows_processed , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS rows_processed,
to_char((1 - (greatest(s.disk_reads, 0) /
greatest(buffer_gets, 1))) * 100, '999G990D90',
AS "Disk/Buffer",
to_char(greatest(greatest(buffer_gets, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Buffer/Exec",
to_char(greatest(greatest(s.buffer_gets, 0) /
decode(greatest(s.rows_processed, 0), 0, -1, s.rows_processed), 0),
AS "Buffer/Row",
to_char(greatest(greatest(s.parse_calls, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Parse/Exec",
greatest(s.parsing_user_id , 0)
AS parsing_user_id,
greatest(s.sorts , 0)
AS sorts,
greatest(s.loaded_versions , 0)
AS loaded_versions,
greatest(s.open_versions , 0)
AS open_versions,
greatest(s.users_opening , 0)
AS users_opening,
greatest(s.users_executing , 0)
AS users_executing,
greatest(s.loads , 0)
AS loads,
greatest(s.invalidations , 0)
AS invalidations,
greatest(s.parse_calls , 0)
AS parse_calls,
greatest(parsing_schema_id , 0)
AS parsing_schema_id,
greatest(s.kept_versions , 0)
AS kept_versions,
to_char(greatest(s.hash_value , 0))
AS hash_value,
to_char(greatest(s.module_hash , 0))
AS module_hash,
s.action
AS action,
to_char(greatest(s.action_hash, 0))
AS action_hash,
greatest(s.serializable_aborts, 0)
AS serializable_aborts,
to_char(greatest(s.sharable_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Sharable K",
to_char(greatest(s.persistent_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ')
AS "Persistent K",
to_char(greatest(s.runtime_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Runtime K",
greatest(s.child_number, 0)
AS child_number,
s.outline_category
AS outline_category
FROM v$sql s
WHERE s.parsing_user_id <> 0
ORDER
BY s.disk_reads DESC
)
AS disk_exec,
to_char(greatest(s.disk_reads , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS disk_reads,
to_char(greatest(s.buffer_gets , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS buffer_gets,
to_char(greatest(s.rows_processed , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS rows_processed,
to_char((1 - (greatest(s.disk_reads, 0) /
greatest(buffer_gets, 1))) * 100, '999G990D90',
AS "Disk/Buffer",
to_char(greatest(greatest(buffer_gets, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Buffer/Exec",
to_char(greatest(greatest(s.buffer_gets, 0) /
decode(greatest(s.rows_processed, 0), 0, -1, s.rows_processed), 0),
AS "Buffer/Row",
to_char(greatest(greatest(s.parse_calls, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Parse/Exec",
greatest(s.parsing_user_id , 0)
AS parsing_user_id,
greatest(s.sorts , 0)
AS sorts,
greatest(s.loaded_versions , 0)
AS loaded_versions,
greatest(s.open_versions , 0)
AS open_versions,
greatest(s.users_opening , 0)
AS users_opening,
greatest(s.users_executing , 0)
AS users_executing,
greatest(s.loads , 0)
AS loads,
greatest(s.invalidations , 0)
AS invalidations,
greatest(s.parse_calls , 0)
AS parse_calls,
greatest(parsing_schema_id , 0)
AS parsing_schema_id,
greatest(s.kept_versions , 0)
AS kept_versions,
to_char(greatest(s.hash_value , 0))
AS hash_value,
to_char(greatest(s.module_hash , 0))
AS module_hash,
s.action
AS action,
to_char(greatest(s.action_hash, 0))
AS action_hash,
greatest(s.serializable_aborts, 0)
AS serializable_aborts,
to_char(greatest(s.sharable_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Sharable K",
to_char(greatest(s.persistent_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ')
AS "Persistent K",
to_char(greatest(s.runtime_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Runtime K",
greatest(s.child_number, 0)
AS child_number,
s.outline_category
AS outline_category
FROM v$sql s
WHERE parsing_user_id <> 0
ORDER
BY s.buffer_gets DESC
)
AS disk_exec,
to_char(greatest(s.disk_reads , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS disk_reads,
to_char(greatest(s.buffer_gets , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS buffer_gets,
to_char(greatest(s.rows_processed , 0), '999G999G990',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS rows_processed,
to_char((1 - (greatest(s.disk_reads, 0) /
greatest(buffer_gets, 1))) * 100, '999G990D90',
AS "Disk/Buffer",
to_char(greatest(greatest(buffer_gets, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Buffer/Exec",
to_char(greatest(greatest(s.buffer_gets, 0) /
decode(greatest(s.rows_processed, 0), 0, -1, s.rows_processed), 0),
AS "Buffer/Row",
to_char(greatest(greatest(s.parse_calls, 0) /
decode(greatest(s.executions, 0), 0, -1, s.executions), 0),
AS "Parse/Exec",
greatest(s.parsing_user_id , 0)
AS parsing_user_id,
greatest(s.sorts , 0)
AS sorts,
greatest(s.loaded_versions , 0)
AS loaded_versions,
greatest(s.open_versions , 0)
AS open_versions,
greatest(s.users_opening , 0)
AS users_opening,
greatest(s.users_executing , 0)
AS users_executing,
greatest(s.loads , 0)
AS loads,
greatest(s.invalidations , 0)
AS invalidations,
greatest(s.parse_calls , 0)
AS parse_calls,
greatest(parsing_schema_id , 0)
AS parsing_schema_id,
greatest(s.kept_versions , 0)
AS kept_versions,
to_char(greatest(s.hash_value , 0))
AS hash_value,
to_char(greatest(s.module_hash , 0))
AS module_hash,
s.action
AS action,
to_char(greatest(s.action_hash, 0))
AS action_hash,
greatest(s.serializable_aborts, 0)
AS serializable_aborts,
to_char(greatest(s.sharable_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Sharable K",
to_char(greatest(s.persistent_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ')
AS "Persistent K",
to_char(greatest(s.runtime_mem, 0) / 1024, '999G990D90',
'NLS_NUMERIC_CHARACTERS = '',.'' ') AS "Runtime K",
greatest(s.child_number, 0)
AS child_number,
s.outline_category
AS outline_category
FROM v$sql s
WHERE parsing_user_id <> 0
ORDER
BY s.sorts DESC
)
/** $Header: /opt/cvs/Oracle.lib/DBA/Common/Sql/TOP_SQL.sql,v 1.6
2003/04/10 10:04:56 orca Exp $
$Log: TOP_SQL.sql,v $
Revision 1.6 2003/04/10 10:04:56 orca
changed RUN AS to [ USER_WITH_DICT_ACCESS ]
Revision 1.5 2003/04/10 10:02:17 orca
Totally reformated the Script
additional columns TOP_ITEM. So it's easier to
see which TOP-Criteria meets the SQL
Revision 1.4 2003/04/02 15:44:10 orca initial revision
Revision 1.3 2003/04/02 15:17:11 orca cvs-var Name extrhaiert keinen Content
Revision 1.2 2003/04/02 15:15:40 orca cvs-var Name extrhaiert keinen Content
Revision 1.1 2003/04/02 15:11:15 orca initital revision
*/ Received on Thu May 08 2003 - 05:40:41 CDT
 |
 |