Find Number Of Sessions On Each DatabaseFind Number Of Sessions On Each Database select COUNT(1)as Number_Of_Connections, DB_NAME(dbid) as Database_Name from sysprocesses group by dbid order by 1 desc Thank you for giving your valuable time to read the above information. Please click here to subscribe for… Read More
Find MAX DOP Value of SQL Instance select case when cpu_count / hyperthread_ratio > 8 then 8 else cpu_count / hyperthread_ratio end as optimal_maxdop_setting from sys.dm_os_sys_info; Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates… Read More
CPU_Usage_History of Past 4 hours DECLARE @ts_now bigint SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info SELECT top 240 record_id, EventTime, CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END… Read More
SELECT physical_device_name, backup_start_date, backup_finish_date, backup_size/1024.0 AS BackupSizeKBFROM msdb.dbo.backupset bJOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_idWHERE database_name = ‘Database_Name’ORDER BY backup_finish_date DESC
Check List before DB is ready for Performance Test 1) Init Parameters properly configured or not.2) sessions Monitoring script.3) OS Watchers running or not.4) AWR Snapshots configured or not.5) CPU’s Count.6) SGA Size7) OEM (Grid) 8) Keep handy Monitoring Scripts (Check Load Test Monitoring in… Read More
If the Oracle Home is filled and the DB gets hanged, then the first thing we need to check is— Audit File / Network Logs / dump locations — Check the file system using the below commanddf -h | grep oracle (IF THE OUTPUT HAS 100% USAGE)… Read More
1. EXPLAIN PLAN FOR SELECT <STATEMENT> set linesize 300SELECT * FROM TABLE(dbms_xplan.display); 2. FROM MEMORY – Get the execution plan which is generated by CBO Use DBMS_XPLAN package:select * from table (dbms_xplan.display_cursor (‘&sqlid’) ) ; For the above, you need to have SQL ID. To… Read More
cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More
set linesize 200 set pagesize 500 spool /tmp/Oct29.html set markup html on set numwidth 40 select * from emp; set long 100000 select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off
Ask DB details and query details before start working on it. When did this query last took 2 hours?What is the change in volume of data?How many times this query runs each day?Start and end time of this query execution for past 1 month.Any DDL changes… Read More
