Oracle keeps stats on all active sessions. Since db connections from Remedy
are persistent, you can see what the heaviest sql is from the db side.
These are things your dba should know how to use/interpret. These are some
of the stats I find useful at times:
-- Disk Intensive SQL - origmod
set wrap on
set linesize 250
set pagesize 1000
column username format a20
column sql_text format a64
select a.USERNAME,
DISK_READS,
EXECUTIONS,
round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
v$sqltext.SQL_TEXT
from dba_users a,
v$session,
v$sqlarea,
v$sqltext
where PARSING_USER_ID=USER_ID and
v$sqlarea.address = v$sqltext.address and
v$sqlarea.hash_value = v$sqltext.hash_value
and v$sqlarea.ADDRESS=SQL_ADDRESS(+)
and DISK_READS > 10000
order by DISK_READS desc, EXECUTIONS desc, piece asc;
-- Buffer Scan Intensive SQL
set wrap on
set linesize 250
set pagesize 1000
column sql_text format a80
select executions,
round(disk_reads / decode(executions, 0, 1, executions) / 400,2) as
"Response",
sql_text
from v$sqlarea
where buffer_gets / decode(executions,0,1, executions) / 400 > 10
order by executions desc;
-- Shared Pool SQL With Most Loads
set wrap on
set linesize 250
set pagesize 1000
column sql_text format a80
select executions,
sql_text
from v$sqlarea
where loads > 50
order by executions desc;
-- Wait Statistics
set wrap on
set linesize 250
set pagesize 1000
select class,
count,
time
from v$waitstat;
-- Disk Sorts
set wrap on
set linesize 250
set pagesize 1000
select name,
value
from v$sysstat
where name like 'sort%';
-- Tables With Questionable Indexes
set wrap on
set linesize 250
set pagesize 1000
column table_owner format a20
column table_name format a40
column column_name format a20
select table_owner,
table_name,
column_name
from all_ind_columns
where column_position=1
group by table_owner,
table_name,
column_name
having count(*) > 1;
-- Buffer Cache hit Rate
select 100*(cur.value+con.value-phys.value)/(cur.value+con.value)
from v$sysstat cur,
v$sysstat con,
v$sysstat phys,
v$statname ncu,
v$statname nco,
v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
Axton Grams
On 1/3/07, Hall Chad - chahal <[EMAIL PROTECTED]> wrote:
We have an active link on our biggest forms that checks the Advanced
Search Criteria field (ID 1005) along with some combinations of our
other fields. Something like this:
'Search Criteria' = $NULL$
AND
(
('Description' != $NULL$ OR 'Work Log' != $NULL$ OR 'Solution' !=
$NULL$)
AND
('Assignee' = $NULL$ AND 'User ID' = $NULL$ AND 'Create Date' =
$NULL$)
)
We have about 20 fields indexed on one big form and we actually check to
make sure that one of them has a value. If all of them are null, and
they specify something in a large text field, and the advanced search is
null, then we throw an error. I realize the advanced search still gives
them free reign to do whatever they want, but most of the people who
don't know how to form a good search also don't know how to use the
advanced search bar. It's the ones who know enough to try using the
advanced search but do so very poorly that still hurt us from time to
time. But this workflow cuts out a lot of bad QBE searches.
Chad Hall
(501) 342-2650
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Shawn Stonequist
Sent: Wednesday, January 03, 2007 8:57 AM
To: arslist@ARSLIST.ORG
Subject: Re: Performance Issues caused by User Queries - Remedy
6.3/Oracle 10.0.2
Thanks you Axton, Patrick, & Lars!!!
We really appreciate the input so far and I've taken these suggestions
to
the "powers that be" for discussion!!
I look forward to any other ideas that may be out there!
Thank you all again!!
Shawn Stonequist
EMNS, Inc.
________________________________________________________________________
_______
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank you.
*************************************************************************
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where
the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers
Are"
