Tech Minds

SQL> show sga Total System Global Area 524288000 bytes Fixed Size                  2046008 bytes Variable Size             423626696 bytes Database Buffers           92274688 bytes Redo Buffers                6340608 bytes SQL> show parameter sga_max_size NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ sga_max_size                         big integer 500M select value/1024/1024 shared_pool_size from v$parameter where name = 'shared_pool_size'; select * From v$sgastat; select 'Memo...

db_recovery_file_dest_size usage The following scripts will check space utilization for  db_recovery_file_dest_size : col name     format a32 col size_mb  format 999,999,999 col used_mb  format 999,999,999 col pct_used format 999 select    name,    ceil( space_limit / 1024 / 1024) size_mb,    ceil( space_used / 1024 / 1024) used_mb,    decode( nvl( space_used, 0),0, 0,    ceil ( ( space_used / space_limit) * 100) ) pct_used from     v$recovery_file_dest order by    name desc; ********************************* set lines 100 col name format a60 select    name,    floor(space_limit / 1024 / 1024) "Size MB",    ceil(space_used / 1024 / 1024)   "Used MB", from    v$recovery_file_dest order by    name;

Check blocking sessions Let's create a scenario /* Create a blocking lock To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table: */ SQL> create table tstlock (foo varchar2(1), bar varchar2(1)); --Table created. SQL> insert into tstlock values (1,'a'); --1 row created. SQL> insert into tstlock values (2, 'b'); --1 row created. SQL> select * from tstlock ; FOO BAR --- --- 1   a 2   b --2 rows selected. SQL> commit ; --Commit complete. --Now grab a lock on the whole table, still in Session 1: SQL> select * from tstlock for update ; --And in Session 2, try to update a row: SQL> update tstlock set bar= 2  'a' where bar='a' ; --This statement will hang, blocked by the lock that Session 1 is holding on the entire table. /*Identify the blocking session Orac...

 Find-out hourly log switch count This will help you to figure out: – The transnational work load on your database. – The total number of archive log files being generated. – To schedule or Re-schedule your RMAN transnational log backup policy. // Script to find-out hourly log switch count: set pages 1000 select to_char(COMPLETION_TIME,'dd mon yyyy hh24') as Hour, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by to_char(COMPLETION_TIME,'dd mon yyyy hh24'),thread# order by 1; // Script to find-out daily log switch count:   SELECT to_char(first_time, 'dd-mon-yyyy') as "Date",  count(*) as "Daily log switch count"  FROM V$log_history  GROUP BY to_char(first_time, 'dd-mon-yyyy');  SELECT A.*, Round(A.Count*B.AVG/1024/1024) Daily_average_MB  FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') Day,  Count(1) Count,  Min(RECID) M...

Track the Amount of Redo Generated per Hour / Day  Amount of Redo Generated per Hour SELECT  Start_Date,   Start_Time,   Num_Logs, Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs FROM V$log_History Vlh GROUP BY To_Char(Vlh.First_Time,  'YYYY-MM-DD'), To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist, V$log Vl ,  V$database Vdb WHERE Vl.Group# = 1 ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time; Redo generated Per day: select trunc(completion_time) rundate ,count(*)  logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;

To check how many seconds have elapsed since a process started you can use this small shell script: #!/bin/bash init=`stat -t /proc/$1 | awk '{print $14}'` curr=`date +%s` seconds=`echo $curr - $init| bc` name=`cat /proc/$1/cmdline` echo $name $seconds save it on a file called sincetime and give permissions for your user to run it and put it in your $path. Than, the command: sincetime <pid> will return the name of the process with the given pid and its age in seconds. Find Out How Long A Process Has Been Running In Linux ps command has different format specifiers (keywords) that may be used to control the output format. We are going to use the following two keywords to find the uptime of an active process. etime – elapsed time since the process was started, in the form [[DD-]hh:]mm:ss. etimes – elapsed time since the process was started, in seconds. First, you need to find out the PID of a process. The following comm...