what_hurts.sql


Print this article  Send the URL to a friend

— ==========================================================================

— 
— Copyright (C) RoughSea Ltd, 2004
— http://www.roughsea.com
— 
— This program is free software; you can redistribute it and/or modify
— it under the terms of the GNU General Public License as published by
— the Free Software Foundation; either version 2 of the License, or
— any later version.
— 
— This program is distributed in the hope that it will be useful,
— but WITHOUT ANY WARRANTY; without even the implied warranty of
— MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
— GNU General Public License for more details.
— 
— You should have received a copy of the GNU General Public License
— along with this program; if not, write to the Free Software
— Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
— 
— ==========================================================================
— 
— The following query is especially useful for identifying where Oracle spends
— most of the time when bind variables are not used.
— Properly identifying queries which are identical but for constants isn’t very
— easy. The problem is that since Oracle sees hard-coded queries (assuming of course
— no CURSOR_SHARING=FORCE setting) as individual queries, each one may seem relatively
— innocuous and we may miss on the significance, in terms of load on the system, of
— all those little queries put together ... We can get some clues from a high parsing
— rate, but pinning down the querie is often difficult.
— This is especially worrying when trying to have a look at what is happening by
— querying the SGA. This (rather simple) query gets queries in the SGA but only takes
— what is likely to be similar between hard-coded queries before summing up.
— A number in OCCURRENCES close to EXECUTIONS points to hard-coded queries.
— 
— This query requires Oracle 9.x or above. It can be modified to work on earlier versions
— by removing references to the ELAPSED_TIME column.
—  set linesize 132 set trimspool on set pagesize 999 col SQL_TEXT format A80 word_wrapped set recsep off spool sga select sum(elapsed_time) elapsed_time, sum(buffer_gets) buffer_gets, count(*) occurrences, sum(executions) executions, substr(sql_text, 1, decode(command_type, 3, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — SELECT 7, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — DELETE 6, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — UPDATE 2, decode(instr(upper(sql_text), ’WHERE’), 0, instr(upper(sql_text), ’VALUES’)+6, instr(upper(sql_text), ’WHERE’)+5), — INSERT length(sql_text))) || ’...’ SQL_TEXT from v$sql where command_type != 0 group by substr(sql_text, 1, decode(command_type, 3, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — SELECT 7, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — DELETE 6, decode(instr(upper(sql_text), ’WHERE’), 0, length(sql_text), instr(upper(sql_text), ’WHERE’)+5), — UPDATE 2, decode(instr(upper(sql_text), ’WHERE’), 0, instr(upper(sql_text), ’VALUES’)+6, instr(upper(sql_text), ’WHERE’)+5), — INSERT length(sql_text))) order by 1 desc, 2 desc / spool off