Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier. It’s a great script with lots of parameter and I am going to show, which are all more important. Note: It been moved to – http://whoisactive.com/ Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive] I used two table and method to load a data into a table – lightweight (minimal parameter) and heavyweight. Steps to load: Download the SP from website Create a SP in any of the DB…
-
-
How important is clustered index for a table
I am not sure, how to name this topic. May be, start by following questions. How important and effective is clustered index for a table? How to solve the heap fragmentation? Why my query is running slow most of the time for this particular table? Are the primary key and clustered index are same? There are developers still they understand that, we must need a primary key to create a clustered index. No, that’s not. Both are different used for different purpose. The primary key is to enforce the business logic. I.e. enforce the uniqueness. By default it will create a clustered index, if we are not…
-
Oracle DBA BAU Tasks daily weekly and monthly
I just copied from oracle forum to keep it here as well for reference. Here is the original https://forums.oracle.com/ords/apexds/post/oracle-dba-daily-weekly-monthly-or-quarterly-checklist-task-9948 Daily Activity Oracle Database instance is running or not Database Listener is running or not. Check any session blocking the other session Check the alert log for an error Check is there any dbms jobs running & check the status of the same Check the Top session using more Physical I/O Check the number of log switch per hour How_much_redo_generated_per_hour.sql Run the statpack report Detect lock objects Check the SQL query consuming lot of resources. Check the usage of SGA Display database sessions using rollback segments State of all the…
-
Oracle DBA day to day handy scripts MSSQL DBA usage
Oracle DBA day to day handy scripts. find tempspace. ------------------- select tot.tablespace_name,tot.mb total_mb,tot.mb - nvl(used.blocks, 0) * blk.block_size / 1024 / 1024 free_mb, round (nvl (used.blocks, 0) * blk.block_size/1024/1024/tot.mb * 100) pct_used from (select tablespace_name,block_size from dba_tablespaces where contents = 'TEMPORARY') blk, (select tablespace_name, round (sum (bytes)/1024/1024) mb from dba_temp_files group by tablespace_name) tot, (select tablespace, nvl (sum (blocks), 0) blocks from v$tempseg_usage group by tablespace) used where blk.tablespace_name = tot.tablespace_name and tot.tablespace_name = used.tablespace (+); SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P. spid, S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT (*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS,…
-
Learn oracle MS SQL server DBA in short notes
Oracle Short Notes Database – It’s a collection of files. Physical file locations: Listener.ora, TNSname.ora – $ORACLE_HOME/network/admin/ Password & Parameter file – $ORACLE_HOME/dbs/ Control file – flash_recovery_area & /opt/oracle/oradata/ Instance – It’s a collection of SGA+BG process SGA – Shared pool, Database buffer cache, redo log cache, Java pool, large pool & stream pool Shared Pool – Most recently executed statements and definitions. Data Dictionary – most recently used definitions, including table, index & privilege. Library cache – most recently used SQL & PL/SQL statements Shared SQL area – Shared PL/SQL area Database buffer pool – It stores a copy of data block. Default, keep & Recycle –…