пятница, 12 января 2018 г.

Processes and Sessions Utilization

Sessions utilization

SQL> select (100*current_utilization / limit_value) as session_percent 
  from v$resource_limit
 where resource_name = 'sessions'
;

The limit_value can be checked by:
SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     772

Processes utilization

SQL> select (100*current_utilization / limit_value) as process_percent
  from v$resource_limit
 where resource_name = 'processes'
;

The limit_value can be checked by:
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                              integer     500

четверг, 11 января 2018 г.

Determine sessions count from machine, username using v$session

set linesize 256
    col machine format a50
    select machine, username, count(*) cnt
      FROM v$session
     WHERE 1 = 1
     group by machine, username
     order by username, cnt desc
    ;

Output:

MACHINE                                            USERNAME                              CNT
-------------------------------------------------- ------------------------------ ----------
...
...


How to find the SQL statements with hard parses

Find similar SQL statements

1. Find similar SQL statements using gv$open_cursor
select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor
where sid in
(select sid from v$open_cursor
group by sid having count(*) > &threshold);

Added sql_id and sort by sql_text to find similar sql's
--
-- Added sql_id and sort by sql_text to find similar sql's
--
select  sql_id, sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_id, sql_text, user_name
order by sql_text desc, count(*) desc;

Find SQL_FULL_TEXT by SQL_ID
--
-- Find SQL_FULL_TEXT by SQL_ID
--
select s.sql_id, s.sql_fulltext, s.sql_text
       /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
  from v$sql s
 where s.sql_id = '&s_sql_id'

2. Find similar SQL statements by first 80 characters in SQL_TEXT
SELECT substr(sql_text, 1, 80), COUNT(1)
FROM v$sql
GROUP BY substr(sql_text, 1, 80)
HAVING COUNT(1) > 10
ORDER BY 1

3. Find similar SQL statements by executions count
select sql_id, sql_text, count(*)
  from v$sql
 where executions=1
 group by sql_id, sql_text
 order by sql_text desc, count(*) desc;

Find the SQL statements with hard parses

Script copied from forum topic www.orafaq.com - how to find the SQL statement which have many hard parse and modified for personal needs.
If you want to find top sql's with literals, then you can do that with the following script:
-- E. Nossova, Product TuTool : www.tutool.de

set pagesize 0
set feedback off
set verify off
set linesize 180

col nline print newline
col force_match_sig format 9999999999999999999999999
col pct format 99990.99

/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate,
top_n - the number of the top sql's 
        (default: 10) */

define min_first_load_time='&min_first_load_time'
define max_first_load_time='&max_first_load_time'
define top_n='&top_n'

select 'Force Matching Signature='||t.force_match_sig||', Count='||max(t.cnt)||', PCT='||max(t.pct)||'%, Min. Username='||min(s.username)||', Max. Username='||max(s.username)||', Min. First Load Time='||max(min_first_load_time)||', Max. First Load Time='||max(max_first_load_time), max(sql_text) nline from
(select u.username, a.force_matching_signature force_match_sig, a.sql_text 
 from v$sql a, dba_users u 
 where 
 a.parsing_user_id = u.user_id and
 to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
 to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
 to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
 a.force_matching_signature != 0 and
 a.exact_matching_signature != 0 and
 a.force_matching_signature != a.exact_matching_signature ) s,
(select * from 
      (select 
           force_matching_signature force_match_sig, 
           count(*) cnt,
           min(first_load_time) min_first_load_time,
           max(first_load_time) max_first_load_time,
    round((ratio_to_report(count(*)) over ())*100, 2)  pct 
       from v$sql 
       where 
       to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
       to_date(nvl('&min_first_load_time',to_char(trunc(sysdate) /*- 1/24*/,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and 
       to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
       force_matching_signature != 0 and
       exact_matching_signature != 0 and
       force_matching_signature != exact_matching_signature 
       group by force_matching_signature
       order by 2 desc) 
where
rownum <= nvl(abs('&top_n'),10)) t
where
s.force_match_sig = t.force_match_sig
group by t.force_match_sig
order by max(t.cnt) desc
/
 



undefine min_first_load_time
undefine max_first_load_time
undefine top_n

set feedback on
set verify on
set linesize 80

Input substitution variables:
/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: trunc(sysdate) 00:00:00,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate,
top_n - the number of the top sql's 
        (default: 10) */

Enter value for min_first_load_time: 11.01.2018 00:00:00

Enter value for max_first_load_time: 11.01.2018 23:59:59

Enter value for top_n: 10

Output:
Force Matching Signature=15954354564733902021, Count=317, PCT=14.43%, Min. Username=SCOTT, Max. Username=SCOTT, Min. First Load Time=2018-01-11/01:00:04, Max. First Load Ti
me=2018-01-11/09:00:14
select count(*) from user_sequences where upper(sequence_name) = upper('SQz6zSqA41Lx0AAAE7MhJ30bj4')
...

Get all sql_id versions of matching SQLs by "Force Matching Signature":
set pagesize 0
set feedback on
set verify on

select s.sql_id, s.sql_fulltext, s.sql_text, s.sharable_mem, s.persistent_mem, s.runtime_mem
  from v$sql s
 where s.force_matching_signature = '&force_matching_signature'
 order by sql_text desc;

Input substitution variables:
Enter value for force_matching_signature: 15954354564733902021


понедельник, 31 июля 2017 г.

Determine How Long a Session has been Idle using v$session

Following SQL using information from LAST_CALL_ET:
select s.*
--select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' kill_serial_sid
  from (
        SELECT  s.sid,s.serial#,s.username
                ,s.status
                ,substr(s.machine,1,10)
                ,s.osuser,s.module
                ,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
                -- idle time
                ,to_dsinterval(
                  -- days separately
                  substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
                  -- hours
                  substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
                  -- minutes
                  substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
                  --seconds
                  substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
                ) idle_time
         FROM   v$session s, v$process p
        WHERE   s.username IS NOT NULL
          -- use outer join to show sniped sessions in
          -- v$session that don''t have an OS process
          AND p.addr(+) = s.paddr
       ) s
 where 1 = 1
   and s.status = 'INACTIVE'
   and s.idle_time >= to_dsinterval('1 00:00:00')
 ORDER BY idle_time desc;
Note: LAST_CALL_ET - If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

Search Idle Sessions using filter in Enterprise Manager

You can specify search criteria using WHERE clause

as following:
1 = 1
and username IS NOT NULL
and status = 'INACTIVE'
and to_dsinterval(
      -- days separately
      substr('0'||trunc(last_call_et/86400),-2,2) || ' ' ||
      -- hours
      substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
      -- minutes
      substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
      --seconds
      substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2)
    ) >= to_dsinterval('1 00:00:00')

среда, 7 июня 2017 г.

Search values in all columns of all tables

Улучшил процедуру whereIsValue из статьи  SQL: Поиск значения по всем колонкам всех таблиц:

  • Добавил параметр schemaName для поиска в конкретной схеме
  • Заменил user_ views на all_ чтобы была возможность поиска по любой схеме
  • Обернул в кавычки "' || columnName || '" так как столкнулся с ситуацией когда колонка называлась зарезервированным словом "DATE" 
create or replace procedure whereIsValue(p_schemaName varchar2, p_searchValue varchar2)
AS
  TYPE VALCUR IS REF CURSOR;
  cursor tabl(c_schemaName varchar2) is select table_name from all_tables where owner = c_schemaName order by table_name;
  cursor col (c_schemaName varchar2, c_tablename varchar2) is select column_name from all_tab_columns where owner = c_schemaName and table_name like c_tableName;
  valueCursor VALCUR;
  tableName varchar2(50);
  columnName varchar2(50);
  columnValue varchar2(500);
begin
  open tabl(p_schemaName);
  LOOP
    fetch tabl into tableName;
    EXIT WHEN tabl%NOTFOUND;
    --dbms_output.put_line('Search in table - ' || tableName);
    OPEN col(p_schemaName, tableName);
    LOOP     
      fetch col into columnName;
      EXIT WHEN col%NOTFOUND;
      --dbms_output.put_line('Search in column - ' || columnName);
        OPEN valueCursor for 'select "' || columnName || '" from ' || p_schemaName || '."' || tableName || '"';
        LOOP
          BEGIN
            fetch valueCursor into columnValue;        
            EXIT WHEN valueCursor%NOTFOUND;
            if (columnValue like p_searchValue) then
              dbms_output.put_line('Found in table - ' || tableName || ' and column - ' || columnName);
              exit;
            end if;
          EXCEPTION
            WHEN OTHERS then
              NULL;
          END;
        END LOOP;
        CLOSE valueCursor;
    END LOOP;
    CLOSE col;
  END LOOP;
end;
/
Примеры вызова:
sqlplus /nolog
conn system

SET SERVEROUTPUT ON

exec whereIsValue('SCOTT', '1496765408852');
exec whereIsValue('SCOTT', 'CLERK');
exec whereIsValue('SCOTT', '%JONES%');