среда, 17 января 2024 г.

How to convert seconds to HH24:MI:SS

 First example is convert seconds to HH24:MI with using to_char:

  1. with t as (select 800000000 sec from dual)
  2. select nvl(trim(to_char(trunc(round(sec/3600, 2)), 'FM9999999900')), '00')
  3. || ':'
  4. || nvl(to_char(mod(trunc(round(sec/60, 2)), 60), 'FM00'), '00') in_hh_mi
  5. from t
  6. ;
IN_HH_MI       
---------------
222222:13      
1 row selected.

 Next example is convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:

  1. with t as(select NUMTODSINTERVAL(nvl(800000000, 0) , 'second') ds_int from dual)
  2. select ds_int
  3. ,to_char(extract(day from ds_int) * 24
  4. + extract(hour from ds_int), 'FM9999999900')
  5. || ':' || to_char(extract(minute from ds_int), 'FM00')
  6. || ':' || to_char(extract(second from ds_int), 'FM00') in_hh_mi_ss
  7. from t;
DS_INT                                             IN_HH_MI_SS        
-------------------------------------------------- -------------------
+9259 06:13:20.000000                              222222:13:20       
1 row selected.
Thanks to forum.oracle.com topic convert from numberic seconds to HH:MM:SS answer of michaelrozar17

 Last example is pl/sql function seconds_to_hh24_mi_ss to convert seconds to HH24:MI:SS with using NUMTODSINTERVAL:

  1. declare
  2. l_var varchar2(50);
  3.  
  4. function seconds_to_hh24_mi_ss(p_seconds number) return varchar2
  5. is
  6. l_ds_int INTERVAL DAY(9) TO SECOND;
  7. begin
  8. l_ds_int := NUMTODSINTERVAL(nvl(p_seconds, 0), 'second');
  9. return to_char(extract(day from l_ds_int) * 24
  10. + extract(hour from l_ds_int), 'FM9999999900') -- hh
  11. || ':' || to_char(extract(minute from l_ds_int), 'FM00') -- mi
  12. || ':' || to_char(extract(second from l_ds_int), 'FM00') -- ss
  13. ;
  14. end;
  15. begin
  16. l_var := seconds_to_hh24_mi_ss(800000000);
  17. dbms_output.put_line(l_var);
  18. end;
PL/SQL procedure successfully completed.

222222:13:20

четверг, 3 мая 2018 г.

Getting information about table size, lob segment and lob index segment size which belongs to table

  1. set linesize 256
  2. col owner for a30
  3. col table_name for a30
  4. col segment_name for a90
  5. col LOB_SECURED for a11
  6. col LOB_COMPRESSION for a15
  7. with t_tab as (
  8. select -- Information about table size
  9. t.OWNER,
  10. t.TABLE_NAME,
  11. t.COMPRESSION as TABLE_COMPRESSION,
  12. t.COMPRESS_FOR as TABLE_COMPRESS_FOR
  13. from DBA_TABLES t
  14. where 1 = 1
  15. and t.TABLE_NAME = NVL(UPPER('&s_table_name'), t.TABLE_NAME)
  16. and t.OWNER = NVL(UPPER('&s_owner'), t.OWNER)
  17. and t.OWNER not in ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'XDB')
  18. )
  19. select -- Information about table size
  20. t.OWNER,
  21. t.TABLE_NAME,
  22. t.TABLE_COMPRESSION,
  23. t.TABLE_COMPRESS_FOR,
  24. ts.SEGMENT_NAME as SEGMENT_NAME,
  25. cast(null as varchar2(30)) as LOB_SECURED,
  26. cast(null as varchar2(30)) as LOB_COMPRESSION,
  27. ROUND(ts.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  28. from t_tab t
  29. join DBA_SEGMENTS ts
  30. on ts.SEGMENT_NAME = t.TABLE_NAME
  31. where 1 = 1
  32. union all
  33. select -- Information about lob columns size for table
  34. t.OWNER,
  35. t.TABLE_NAME,
  36. t.TABLE_COMPRESSION,
  37. t.TABLE_COMPRESS_FOR,
  38. ls.segment_type || ' "' || dl.segment_name || '" for table "' || dl.table_name || '"' as SEGMENT_NAME,
  39. dl.securefile as LOB_SECURED,
  40. dl.compression as LOB_COMPRESSION,
  41. ROUND(ls.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  42. from t_tab t
  43. join DBA_LOBS dl
  44. on dl.table_name = t.TABLE_NAME
  45. join DBA_SEGMENTS ls
  46. on ls.SEGMENT_NAME = dl.segment_name
  47. where 1 = 1
  48. union all
  49. select -- Information about lob index for lob column
  50. t.OWNER,
  51. t.TABLE_NAME,
  52. t.TABLE_COMPRESSION,
  53. t.TABLE_COMPRESS_FOR,
  54. lis.segment_type || ' "' || dl.index_name || '" for LOBSEGMENT "' || dl.segment_name || '"' as SEGMENT_NAME,
  55. dl.securefile as LOB_SECURED,
  56. dl.compression as LOB_COMPRESSION,
  57. ROUND(lis.bytes/(1024*1024),2) SPACE_ALOCATED_MB
  58. from t_tab t
  59. join DBA_LOBS dl
  60. on dl.table_name = t.TABLE_NAME
  61. join DBA_SEGMENTS lis
  62. on lis.SEGMENT_NAME = dl.index_name
  63. where 1 = 1
  64. ORDER BY SPACE_ALOCATED_MB desc, OWNER, TABLE_NAME, SEGMENT_NAME
  65. ;

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

Processes and Sessions Utilization

Sessions utilization

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

The limit_value can be checked by:
  1. SQL> show parameter sessions
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. sessions integer 772

Processes utilization

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

The limit_value can be checked by:
  1. SQL> show parameter processes
  2.  
  3. NAME TYPE VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. processes integer 500

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

Determine sessions count from machine, username using v$session

  1. set linesize 256
  2. col machine format a50
  3. select machine, username, count(*) cnt
  4. FROM v$session
  5. WHERE 1 = 1
  6. group by machine, username
  7. order by username, cnt desc
  8. ;

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
  1. select saddr, sid, user_name, address,hash_value,sql_id, sql_text
  2. from gv$open_cursor
  3. where sid in
  4. (select sid from v$open_cursor
  5. group by sid having count(*) > &threshold);

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

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

2. Find similar SQL statements by first 80 characters in SQL_TEXT
  1. SELECT substr(sql_text, 1, 80), COUNT(1)
  2. , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
  3. FROM v$sql
  4. WHERE 1 = 1
  5. --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
  6. --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
  7. GROUP BY substr(sql_text, 1, 80)
  8. HAVING COUNT(1) > 10
  9. ORDER BY 2 desc

2.1. Find similar SQL statements by first 80 characters in SQL_TEXT which included specified tables:
  1. SELECT substr(s.sql_text, 1, 80), COUNT(1)
  2. , round(SUM(elapsed_time) / power(10, 6), 3) as total_elapsed_time
  3. FROM v$sql s
  4. join (
  5. select owner, object_name from dba_objects
  6. where owner = 'SCOTT'
  7. and object_type = 'TABLE'
  8. and (
  9. object_name like upper('emp%')
  10. or
  11. object_name like upper('sal%')
  12. or
  13. object_name in ('TAB1', 'TAB2', 'TAB3')
  14. )
  15. ) o
  16. on o.owner = s.parsing_schema_name
  17. WHERE 1 = 1
  18. and upper(sql_fulltext) like '%' || upper(o.object_name) || '%'
  19. and parsing_schema_name = 'SCOTT'
  20. --and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
  21. --and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
  22. GROUP BY substr(s.sql_text, 1, 80)
  23. HAVING COUNT(1) > 10
  24. ORDER BY 2 desc

2.3. Find similar SQL statements that have only one execution to see whether they are similar using V$SQLSTATS:
  1. SELECT SUBSTR(SQL_TEXT, 1, 80), COUNT(*)
  2. FROM V$SQLSTATS
  3. WHERE EXECUTIONS < 4
  4. GROUP BY SUBSTR(SQL_TEXT, 1, 80)
  5. HAVING COUNT(*) > 1
  6. ORDER BY 2 DESC;

3. Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
  1. --
  2. -- Find SQL_FULL_TEXT by first 80 characters of SQL_TEXT
  3. --
  4. select s.sql_id,
  5. round(s.sharable_mem / 1024 / 1024, 2) as sharable_mem,
  6. round(s.persistent_mem / 1024 / 1024, 2) as persistent_mem,
  7. round(s.runtime_mem / 1024 / 1024, 2) as runtime_mem,
  8. substr(sql_text, 1, 80), s.sql_fulltext
  9. /*,to_char(substr(s.sql_fulltext, 1, 32767)) as sql_fulltext*/
  10. from v$sql s
  11. where s.sql_text like '%' || '&s_sql_text' || '%'
  12. order by dbms_lob.getlength(s.sql_fulltext) 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:
  1. -- E. Nossova, Product TuTool : www.tutool.de
  2.  
  3. set pagesize 0
  4. set feedback off
  5. set verify off
  6. set linesize 180
  7.  
  8. col nline print newline
  9. col force_match_sig format 9999999999999999999999999
  10. col pct format 99990.99
  11.  
  12. /* reports top SQL's with literals from the sqlarea,
  13. input parameters:
  14. min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
  15. format,
  16. default: trunc(sysdate) 00:00:00,
  17. max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
  18. format,
  19. default: sysdate,
  20. top_n - the number of the top sql's
  21. (default: 10) */
  22.  
  23. define min_first_load_time='&min_first_load_time'
  24. define max_first_load_time='&max_first_load_time'
  25. define top_n='&top_n'
  26.  
  27. 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
  28. (select u.username, a.force_matching_signature force_match_sig, a.sql_text
  29. from v$sql a, dba_users u
  30. where
  31. a.parsing_user_id = u.user_id and
  32. to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
  33. 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
  34. to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
  35. a.force_matching_signature != 0 and
  36. a.exact_matching_signature != 0 and
  37. a.force_matching_signature != a.exact_matching_signature ) s,
  38. (select * from
  39. (select
  40. force_matching_signature force_match_sig,
  41. count(*) cnt,
  42. min(first_load_time) min_first_load_time,
  43. max(first_load_time) max_first_load_time,
  44. round((ratio_to_report(count(*)) over ())*100, 2) pct
  45. from v$sql
  46. where
  47. to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between
  48. 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
  49. to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
  50. force_matching_signature != 0 and
  51. exact_matching_signature != 0 and
  52. force_matching_signature != exact_matching_signature
  53. -- Excluded from report SQL Tuning statements
  54. and upper(sql_fulltext) not like '%' || 'OPT_DYN_SAMP' || '%'
  55. and upper(sql_fulltext) not like '%' || '/* DS_SVC */' || '%'
  56. group by force_matching_signature
  57. order by 2 desc
  58. )
  59. where
  60. rownum <= nvl(abs('&top_n'),10)) t
  61. where
  62. s.force_match_sig = t.force_match_sig
  63. group by t.force_match_sig
  64. order by max(t.cnt) desc
  65. /
  66.  
  67.  
  68.  
  69. undefine min_first_load_time
  70. undefine max_first_load_time
  71. undefine top_n
  72.  
  73. set feedback on
  74. set verify on
  75. set linesize 80

Input substitution variables:
  1. /* reports top SQL's with literals from the sqlarea,
  2. input parameters:
  3. min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
  4. format,
  5. default: trunc(sysdate) 00:00:00,
  6. max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
  7. format,
  8. default: sysdate,
  9. top_n - the number of the top sql's
  10. (default: 10) */
  11.  
  12. Enter value for min_first_load_time: 11.01.2018 00:00:00
  13.  
  14. Enter value for max_first_load_time: 11.01.2018 23:59:59
  15.  
  16. Enter value for top_n: 10

Output:
  1. 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
  2. me=2018-01-11/09:00:14
  3. select count(*) from user_sequences where upper(sequence_name) = upper('SQz6zSqA41Lx0AAAE7MhJ30bj4')
  4. ...

Get all sql_id versions of matching SQLs by "Force Matching Signature":
  1. set pagesize 0
  2. set feedback on
  3. set verify on
  4.  
  5. select s.sql_id, s.sql_fulltext, s.sql_text, s.sharable_mem, s.persistent_mem, s.runtime_mem
  6. from v$sql s
  7. where s.force_matching_signature = '&force_matching_signature'
  8. order by dbms_lob.getlength(s.sql_fulltext) desc;

Input substitution variables:
  1. Enter value for force_matching_signature: 15954354564733902021