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
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
I worked with a DB once, don't remember which one (may have been Oracle) that allowed query monitoring to be turned on at the DB level and you could turn it on for a week or so and it would report to you the heaviest queries taking place during its monitoring time. I seem to remember it being a bit of a performance hit...but you can't do that type of thing without impacting performance -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Stonequist Sent: Wednesday, January 03, 2007 7: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 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
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
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
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 1
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
Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
Greetings List!! We have an issue in which some users (we know not who) are running queries which are affecting the performance of our Remedy system, causing significant delays, issues, and even complete system lock-ups for all our Users. We have three goals that we would like to accomplish: 1) Identify the user(s) and what they are doing 2) Prevent future performance degradation of this nature 3) Stop offending queries in progress should prevention be not possible to bypassed in some manner, using a method other than rebooting our database (as this brings all our users down, which is what we are trying to remedy) We are interested in any possible solutions or paths to accomplish one or more of these goals. We spoke with our Remedy Partner that provides our support, but unfortunately, their best resolution was using User/API/SQL Logging Remedy-side. Unfortunately, for this to be successful, we would need to know before the offending query is run, or have it always running (something our SA is against due to system resource concerns) Our SA provided me the following information concerning our configuration, which may be helpful (I apologize that I don't know what it all means): Distributed Server Remedy 6.3.0 patch 018 Oracle 10.0.2 Monolithic We look forward to and appreciate any assistance available. Thanks Shawn Stonequist EMNS, Inc. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:Where the Answers Are
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
You can do a couple of things: - look at the remedy logs to figure out what these people are doing (sql); then take steps to stop it. - Limit the number of records the server can return (admin tool/ar.conf) Unfortunately, from the db side, there is not much you can do to stop what has started, short of stopping your remedy server. Each queue (fast/list/custom) configured on your arserver is configured with a set min/max number of threads. At startup, remedy establishes X number of connections to your db (sum of the min threads for all queues). These are persistent sessions and should not be mucked with from the db side. Axton Grams On 1/2/07, Shawn Stonequist [EMAIL PROTECTED] wrote: Greetings List!! We have an issue in which some users (we know not who) are running queries which are affecting the performance of our Remedy system, causing significant delays, issues, and even complete system lock-ups for all our Users. We have three goals that we would like to accomplish: 1) Identify the user(s) and what they are doing 2) Prevent future performance degradation of this nature 3) Stop offending queries in progress should prevention be not possible to bypassed in some manner, using a method other than rebooting our database (as this brings all our users down, which is what we are trying to remedy) We are interested in any possible solutions or paths to accomplish one or more of these goals. We spoke with our Remedy Partner that provides our support, but unfortunately, their best resolution was using User/API/SQL Logging Remedy-side. Unfortunately, for this to be successful, we would need to know before the offending query is run, or have it always running (something our SA is against due to system resource concerns) Our SA provided me the following information concerning our configuration, which may be helpful (I apologize that I don't know what it all means): Distributed Server Remedy 6.3.0 patch 018 Oracle 10.0.2 Monolithic We look forward to and appreciate any assistance available. Thanks Shawn Stonequist EMNS, Inc. ___ 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
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
We ran into these issues awhile back given some of our tables have millions of rows, so we created a simple search capture form and search protection dialog form. These could help if uses are performing searching in forms or reports, if they are searching via ODBC then you might consider limiting use to a permission group or specific datetime range in the AR.CFG The search capture is a form with some AL's that grab fields from a form being searched or the advanced search (add the search bar field to the form), if it is a crystal report you can decode the search with Set fields ($PROCESS$ Application-Map-Ids-To-Names $ReportFormName$ $Search$) The search protection recognizes specific search fields and/or datetime field ranges. Example of one scenario: if STRSTR(SUBSTR($Search Bar$,STRSTR($Search Bar$, 'Create Date') + 13), 'Create Date') 40 then a daterange is used (although there are other permutations to check) - find the time between the range or some other action If the query doesn't meet criteria we direct the user to a separate dialog form with selectable search criteria and limited date ranges. Hope this helps Pat Dill Enterprise Tools Team Roosevelt Commons, Floor 5 206-545-3217 Office 206-931-3006 Cell -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Shawn Stonequist Sent: Tuesday, January 02, 2007 2:34 PM To: arslist@ARSLIST.ORG Subject: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2 Greetings List!! We have an issue in which some users (we know not who) are running queries which are affecting the performance of our Remedy system, causing significant delays, issues, and even complete system lock-ups for all our Users. We have three goals that we would like to accomplish: 1) Identify the user(s) and what they are doing 2) Prevent future performance degradation of this nature 3) Stop offending queries in progress should prevention be not possible to bypassed in some manner, using a method other than rebooting our database (as this brings all our users down, which is what we are trying to remedy) We are interested in any possible solutions or paths to accomplish one or more of these goals. We spoke with our Remedy Partner that provides our support, but unfortunately, their best resolution was using User/API/SQL Logging Remedy-side. Unfortunately, for this to be successful, we would need to know before the offending query is run, or have it always running (something our SA is against due to system resource concerns) Our SA provided me the following information concerning our configuration, which may be helpful (I apologize that I don't know what it all means): Distributed Server Remedy 6.3.0 patch 018 Oracle 10.0.2 Monolithic We look forward to and appreciate any assistance available. Thanks Shawn Stonequist EMNS, Inc. ___ 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
Re: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2
Shawn, why not take a look at rrrLog from misi, at www.rrr.se rrrLog: Find the performance bottle necks in your system. What form use most system resources? Find slow API/SQL-calls, and browse the filters that have been triggered. You will be able to sort by processing time and identify which sql command a used made, and who this user was. In the bottom are the logfiles, api, sql, filter, esc, but presented is a readable format. We tested the product, and found slow 'like searches', both in ars code and triggered by users. L ars -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Shawn Stonequist Sent: Tuesday, January 02, 2007 11:34 PM To: arslist@ARSLIST.ORG Subject: Performance Issues caused by User Queries - Remedy 6.3/Oracle 10.0.2 Greetings List!! We have an issue in which some users (we know not who) are running queries which are affecting the performance of our Remedy system, causing significant delays, issues, and even complete system lock-ups for all our Users. We have three goals that we would like to accomplish: 1) Identify the user(s) and what they are doing 2) Prevent future performance degradation of this nature 3) Stop offending queries in progress should prevention be not possible to bypassed in some manner, using a method other than rebooting our database (as this brings all our users down, which is what we are trying to remedy) We are interested in any possible solutions or paths to accomplish one or more of these goals. We spoke with our Remedy Partner that provides our support, but unfortunately, their best resolution was using User/API/SQL Logging Remedy-side. Unfortunately, for this to be successful, we would need to know before the offending query is run, or have it always running (something our SA is against due to system resource concerns) Our SA provided me the following information concerning our configuration, which may be helpful (I apologize that I don't know what it all means): Distributed Server Remedy 6.3.0 patch 018 Oracle 10.0.2 Monolithic We look forward to and appreciate any assistance available. Thanks Shawn Stonequist EMNS, Inc. ___ 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
