Monday, April 19, 2010

DIFF utility: Find Differences Between Files

Working as a DBA , you may encounter situations like to compare two files to check whether the contents are same or not.
diff will surely make your life easy.Believe IT or ....

$ diff init.ora init1.ora

Check the output showing the differences in the files:
16,17c16,17
< sga_max_size=900M
< sga_target=700M
---
> sga_max_size=800M
> sga_target=600M
19a20
> # sort_area_size=0

here, < refer to file1 & > refer to file 2.
And , c stands for change
a stands for append (looks like file 2 has one more line & that is 20 which shows contents "# sort_area_size=0"

There is another tag like d (beside c & a) which stands for delete.

$ diff init.ora init1.ora > change.diff
To convert init.ora to init1.ora, use the "patch" command with the difference report output:
$ patch init.ora change.diff


We can even use "sdiff" utility too.
Ex: $ sdiff init.ora init1.ora

This will show the changes in more readable format.

Note: can also use "diff3" utility to compare differences between three files.

Find Differences Between Directories:

Possible guys!!!!

$ diff /oracle/scripts /u01/oracle/scripts


thanks... have a nice day.

Reference to vi editor

hi,
Can check a somewhat huge reference for vi editor. Very much helps to DBA guys.

$ vi filename

Some Helpful vi Command-Line Startup Options:
Option ==> Action
vi ==> Starts editing session in memory.
vi ==> Starts session and opens the specified file.
vi * ==> Opens first file that matches the wildcard pattern. Use :n to navigate to the next matched file.
view ==> Opens file in read-only mode.
vi -R ==> Opens file in read-only mode.
vi -r ==> Recovers file and recent edits after abnormal abort from editing session (like a system crash).
vi +n ==> Opens file at specified line number n.
vi + ==> Opens file at the last line.
vi +/ ==> Opens file at first occurrence of specified string pattern.


Common Techniques to Enter vi Insert Mode:
Enter Insert Command ==> Action
i ==> Insert text in front of the cursor.
a ==> Insert text after the cursor.
I ==> Insert text at the beginning of the line.
A ==> Insert text at the end of the line.
o ==> Insert text below the current line.
O ==> Insert text above the current line.

Useful vi Exit Commands
Exit Command ==> Action
:wq ==> Save and exit.
ZZ ==> Save and exit.
:x ==> Save and exit.
:w ==> Save the current edits without exiting.
:w! ==> Override file protections and save.
:q ==> Exit the file.
:q! ==> Exit without saving.
:n ==> Edit next file.
:e! ==> Return to previously saved version.

Common Navigation Commands


Command ==> Action

j (or down arrow) ==> Move down a line.
k (or up arrow) ==> Move up a line.
h (or left arrow) ==> Move one character left.
l (or right arrow) ==> Move one character right.
Ctrl+f (or Page Down) ==> Scroll down one screen.
Ctrl+b (or Page Up) ==> Scroll up one screen.
1G ==> Go to first line in file.
G ==> Go to last line in file.
nG ==> Go to n line number.
H ==> Go to top of screen.
L ==> Go to bottom of screen.
w ==> Move one word forward.
b ==> Move one word backward.
0 ==> Go to start of line.
$ ==> Go to end of line.

Common Options for Copying, Deleting, and Pasting Text

Option ==> Action
yy ==> Yank (copy) the current line.
nyy ==> Yank (copy) n number of lines.
p ==> Put yanked line(s) below the cursor.
P ==> Put yanked line(s) above the cursor.
x ==> Delete the character that the cursor is on.
X ==> Delete the character to the left of the cursor.
dw ==> Delete the word the cursor is currently on.
dd ==> Delete current line of text.
ndd ==> Delete n lines of text
D ==> Delete to the end of the current line.

Common Options for Changing Text
Option ==> Action
r ==> Replace the character that the curser is on with the next character you type.
~ ==> Change the case of a character.
cc ==> Delete the current line and insert text.
C ==> Delete to the end of the line and insert text.
c$ ==> Delete to the end of the line and insert text.
cw ==> Delete to the end of the word and insert text.
R ==> Type over the characters in the current line.
s ==> Delete the current character and insert text.
S ==> Delete the current line and insert text.

Common Options for Text Searching


Option ==> Action
/ ==> Search forward for a string.
? ==> Search backward for a string.
n ==> Repeat the search forward.
N ==> Repeat the search backward.
f ==> Search forward for a character in the current line.
F ==> Search backward for a character in the current line.

:set number ==> Displaying Line Numbers
u ==> Undoing a Command

Thanks to GUYS behind "Linux Recipes for Oracle DBAs" for this summary.

Sunday, April 18, 2010

Select the LAST N rows from a table in Oracle

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary


SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;


Select the employees getting the lowest 10 salaries




SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;


For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:



SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;


Use this workaround for older (8.0 and prior) releases:




SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;

Thanks.

Select the TOP N rows from a table in Oracle

Below is the examples to find the top 5 employees based on their salary.

Option 1: Using RANK()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employee )
WHERE salary_rank <= 5;

Option 2: Using Dense_Rank()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank
FROM employee )
WHERE salary_dense_rank <= 5;

Option 3: Using inner query
This is an example of using an inner-query with an ORDER BY clause:


SELECT *
FROM (SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Option 4: Using count distinct combination

SELECT *
FROM employee e
WHERE 5 >= (SELECT COUNT(DISTINCT salary)
FROM employee b
WHERE b.salary >= e.salary)
ORDER BY salary DESC;

Thanks.

Controlling the Autotrace Report in sql*plus

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Following is the available AUTOTRACE settings.

1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual.

2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics.

3)SET AUTOTRACE ON STATISTICS:
The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan.

4)SET AUTOTRACE ON:
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output.

5)SET AUTOTRACE TRACEONLY:
Report displays both optimizer execution path as well as execution statistics but does not display query output. If STATISTICS is enabled, query data is still fetched, but not printed.

Thanks.

All About V$ views

This can help a lot to an Oracle DBA to get info about different views:

Advisors: Information related to cache advisors

V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$MTTR_TARGET_ADVICE
V$PX_BUFFER_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAMS_POOL_ADVICE (10.2)
V$SGA_TARGET_ADVICE (10.2)
V$ADVISOR_PROGRESS (10.2)

ASM

V$ASM_ALIAS (10.1)
V$ASM_CLIENT(10.1)
V$ASM_DISK(10.1)
V$ASM_DISK_STAT(10.2)
V$ASM_DISKGROUP(10.1)
V$ASM_DISKGROUP_STAT(10.2)
V$ASM_FILE(10.1)
V$ASM_OPERATION(10.1)
V$ASM_TEMPLATE(10.1)

Backup/recovery Information related to database backups and recovery including last backup,archive logs,state of files for backup,and recovery

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVE_PROCESSES
V$BACKUP
V$BACKUP_ASYNC_IO
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$BLOCK_CHANGE_TRACKING
V$COPY_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
V$DATABASE_INCARNATION
V$DATAFILE_COPY
V$DELETED_OBJECT
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
V$INSTANCE_RECOVERY
V$MTTR_TARGET_ADVICE
V$PROXY_ARCHIVEDLOG
V$PROXY_DATAFILE
V$RMAN_CONFIGURATION
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_PROGRESS
V$RECOVERY_STATUS
V$RECOVER_FILE
V$BACKUP_ARCHIVELOG_DETAILS(10.2)
V$BACKUP_ARCHIVELOG_SUMMARY(10.2)
V$BACKUP_CONTROLFILE_DETAILS(10.2)
V$BACKUP_CONTROLFILE_SUMMARY(10.2)
V$BACKUP_COPY_DETAILS(10.2)
V$BACKUP_COPY_SUMMARY(10.2)
V$BACKUP_FILES(10.1)
V$BACKUP_PIECE_DETAILS(10.2)
V$BACKUP_SET_DETAILS(10.2)
V$BACKUP_SET_SUMMARY(10.2)
V$BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS(10.2)
V$BACKUP_SPFILE_SUMMARY(10.2)
V$DATAFILE_HEADER
V$FLASH_RECOVERY_AREA_USAGE(10.2)
V$FLASHBACK_DATABASE_LOG(10.1)
V$FLASHBACK_DATABASE_STAT(10.1)
V$OBSOLETE_BACKUP_FILES
V$OFFLINE_RANGE
V$PROXY_ARCHIVELOG_DETAILS(10.2)
V$PROXY_ARCHIVELOG_SUMMARY(10.2)
V$PROXY_COPY_DETAILS(10.2)
V$PROXY_COPY_SUMMARY(10.2)
V$RECOVERY_FILE_DEST(10.1)
V$RESTORE_POINT(10.2)
V$RMAN_BACKUP_JOB_DETAILS(10.2)
V$RMAN_BACKUP_SUBJOB_DETAILS(10.2)
V$RMAN_BACKUP_TYPE(10.2)
V$RMAN_ENCRYPTION_ALGORITHMS(10.2)
V$RMAN_OUTPUT(10.1)
V$RMAN_STATUS(10.1)
V$UNUSABLE_BACKUPFILE_DETAILS(10.2)

Caches Information related to the various caches, including objects, library, cursors, and the dictionary

V$ACCESS
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$JAVA_POOL_ADVICE
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$PGASTAT
V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$ROWCACHE
V$ROWCACHE_PARENT
V$ROWCACHE_SUBORDINATE
V$SESSION_CURSOR_CACHE
V$SGA
V$SGASTAT
V$SGA_CURRENT_RESIZE_OPS
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_RESIZE_OPS
V$SGAINFO
V$SHARED_POOL_ADVICE
V$SHARED_POOL_RESERVED
V$SYSTEM_CURSOR_CACHE
V$SUBCACHE
V$JAVA_LIBRARY_CACHE_MEMORY(10.1)
V$PROCESS_MEMORY(10.2)
V$SGA_TARGET_ADVICE(10.2)

Cache Fusion/RAC

V$ACTIVE_INSTANCES
V$BH
V$CLUSTER_INTERCONNECTS(10.2)
V$CONFIGURED_INTERCONNECTS(10.2)
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER(10.1)
V$GC_ELEMENT
VGCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GES_BLOCKING_ENQUEUE
V$GES_CONVERT_LOCAL
V$GES_CONVERT_REMOTE
V$GES_ENQUEUE
V$GES_LATCH
V$GES_RESOURCE
V$GES_STATISTICS
V$HVMASTER_INFO
V$INSTANCE_CACHE_TRANSFER
V$RESOURCE_LIMIT

Control files Information related to instance control files

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

Cursors/SQL statements Information related to cursors and SQL statements, including the open cursors, statistics, and actual SQL text

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SESSION_CURSOR_CACHE
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SYSTEM_CURSOR_CACHE V$MUTEX_SLEEP(10.2)
V$MUTEX_SLEEP_HISTORY(10.2) (for those dubious shared latches)
V$SQL_BIND_CAPTURE(10.1)
V$SQL_JOIN_FILTER(10.2)
V$SQL_AREA_PLAN_HASH(10.2)
V$SQLSTATS(10.2)
V$SYS_OPTIMIZER_ENV(10.1)
V$VPD_POLICY

Database instances Information related to the actual database instance

V$ACTIVE_INSTANCES
V$BGPROCESS
V$DATABASE
V$INSTANCE
V$PROCESS
V$SGA
V$SGASTAT
V$BLOCKING_QUIESCE(10.2)
V$CLIENT_STATS(10.1)
RAC Views: V$BH
V$ACTIVE_INSTANCES

Direct Path Operations Information related to the SQL*Loader direct load option

V$LOADISTAT
V$LOADPSTAT

Distributed/Heterogeneous Services

V$DBLINK
V$GLOBAL_TRANSACTION
V$GLOBAL_BLOCKED_LOCKS
V$HS_AGENT
V$HS_PARAMETER
V$HS_SESSION

Fixed view Information related to the v$ tables themselves

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

General General information related to various system information

V$DBPIPES
V$CONTEXT
V$GLOBALCONTEXT
V$LICENSE
V$OPTION
V$RESERVED_WORDS
V$TIMER
V$TIMEZONE_NAMES
V$TYPE_SIZE
V$_SEQUENCES
V$VERSION
V$DB_TRANSPORTABLE_PLATFORM(10.2)
V$TRANSPORTABLE_PLATFORM(10.1)
V$SCHEDULER_RUNNING_JOBS(10.2)

I/O Information related to I/O, including files and statistics

V$DBFILE
V$FILESTAT
V$WAITSTAT
V$TEMPSTAT
V$FILE_HISTOGRAM(10.1)
V$FILEMETRIC(10.1)
V$FILEMETRIC_HISTORY(10.1)
V$SYSAUX_OCCUPANTS(10.1)
V$TABLESPACE
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSEG_USAGE

Latches/locks Information related to latches and locks

V$ENQUEUE_LOCK
V$ENQUEUE_STAT
V$EVENT_NAME
V$FILE_CACHE_TRANSFER
V$GLOBAL_BLOCKED_LOCKS
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCKED_OBJECT
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1
V$ENQUEUE_STATISTICS

FOLLOWING ARE RAC VIEWS:

V$CR_BLOCK_SERVER
V$GCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GC_ELEMENT
V$GES_BLOCKING_ENQUEUE
V$GES_ENQUEUE
V$HVMASTER_INFO
V$GES_LATCH
V$GES_RESOURCES

Log Miner Information related to Log Miner

V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION

V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
V$LOGMNR_DICTIONARY_LOAD(10.2)

Metrics Information related to Metrics (ALL New in 10g!)

V$METRICNAME
V$SERVICEMETRIC
V$EVENTMETRIC
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY
V$SESSMETRIC
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$THRESHOLD_TYPES
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY

Multithreaded/shared servers Information related to multithreaded and parallel servers, including connections, queues,dispatchers, and shared servers

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$QUEUE
V$QUEUEING_MTH
V$REQDIST
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
V$DISPATCHER_CONFIG(10.1)

Object Usage Information related to object use and dependencies

V$OBJECT_DEPENDENCY
V$OBJECT_USAGE

Overall system Information related to the overall system performance

V$GLOBAL_TRANSACTION
V$SHARED_POOL_RESERVED
V$RESUMABLE
V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$STATNAME
V$SYS_OPTIMIZER_ENV
V$SYS_TIME_MODEL
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TRANSACTION
V$ALERT_TYPES(10.1)
V$EVENT_HISTOGRAM(10.1)
V$OSSTAT(10.1)
V$SYSTEM_WAIT_CLASS(10.1)
V$TEMP_HISTOGRAM(10.1)
V$XML_AUDIT_TRAIL

Parallel Query Information related to the Parallel Query option

V$EXECUTION
V$PARALLEL_DEGREE_LIMIT_MTH
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PX_SESSION
V$PX_SESSTAT

Parameters Information related to various Oracle parameters, including initialization and NLS per session

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$OBSOLETE_PARAMETER
V$PARAMETER
V$PARAMETER2
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
V$PARAMETER_VALID_VALUES(10.2)

Redo logs Information related to redo logs, including statistics and history

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY
V$THREAD (RAC related)

Replication and materialized views Information related to replication and materialized views

V$MVREFRESH
V$REPLPROP
V$REPLQUEUE

Resource Manager Information related to resource management

V$ACTIVE_SESSION_POOL_MTH
V$ACTIVE_SESSION_POOL_HISTORY
V$RSRC_CONS_GROUP_HISTORY(10.2)
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY(10.2)
V$RSRC_SESSION_INFO(10.2)

Rollback segments And Undo Information on rollback segments, including statistics and transactions

V$ROLLNAME
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT

Security/privileges Information related to security

V$ENABLEDPRIVS
V$PWFILE_USERS
V$VPD_POLICY
V$WALLET(10.2)
V$XML_AUDIT_TRAIL(10.2)

Sessions (includes some replication information and heterogeneous services) Information related to a session, including object access, cursors, processes, and statistics

V$ACTIVE_SESSION_HISTORY
V$MYSTAT
V$PROCESS
V$SESS_TIME_MODEL
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$SESSTAT
V$SESS_IO
V$SES_OPTIMIZER_ENV
V$SESSMETRIC
and V$CLIENT_STATS
and V$TSM_SESSIONS(10.2)

Services (all new for 10.1)

V$ACTIVE_SERVICES
V$SERV_MOD_ACT_STATS
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SERVICES

Sorting Information related to sorting

V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSTAT

Standby databases (Data Guard) Information related to standby databases

V$DATAGUARD_STATUS
V$LOGSTDBY
V$LOGSTDBY_STATS
V$MANAGED_STANDBY
V$STANDBY_LOG
V$DATAGUARD_CONFIG(10.1)
V$DATAGUARD_STATS(10.2)
V$LOGSTDBY_PROCESS(10.2)
V$LOGSTDBY_PROGRESS(10.2)
V$LOGSTDBY_STATE(10.2)
V$LOGSTDBY_TRANSACTION(10.2)

File mapping interface Information related to file mapping

V$MAP_COMP_LIST
V$MAP_ELEMENT
V$MAP_EXT_ELEMENT
V$MAP_FILE
V$MAP_FILE_EXTENT
V$MAP_FILE_IO_STACK
V$MAP_LIBRARY
V$MAP_SUBELEMENT

Streams Information related to streams

V$AQ
V$STREAMS_APPLY_COORDINATOR
V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$BUFFERED_PUBLISHERS(10.1)
V$BUFFERED_QUEUES(10.1)
V$BUFFERED_SUBSCRIBERS(10.1)
V$PROPAGATION_RECEIVER(10.1)
V$PROPAGATION_SENDER(10.1)
V$RULE(10.1)
V$RULE_SET(10.1)
V$RULE_SET_AGGREGATE_STATS(10.1)
V$STREAMS_TRANSACTION(10.2)

Statistics Information related to statistics in general

V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
V$STATISTICS_LEVEL
V$STATNAME
V$WAITSTAT

Transactions Information related to transactions in general

V$GLOBAL_TRANSACTION
V$LOGSTDBY_TRANSACTION
V$RESUMABLE
V$STREAMS_TRANSACTION
V$TRANSACTION
V$TRANSACTION_ENQUEUE





Note: In Oracle 10.1 and above, the V$LOCK_TYPE dynamic performance view summarizes all implemented lock types

MTV ENJOY....

Top SQL query by cpu for MSSQL Server

Check the fantastic query below to find the 25 top most cpu intensive queries for a SQL Server Instance.

select top 25 * from (SELECT pa.value AS dbid,
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))+'*') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS TSQL, total_worker_time,execution_count,
total_worker_time/execution_count avg_cpu,total_physical_reads/execution_count avg_physical_read,
total_elapsed_time/execution_count avg_elapsed_time,total_logical_reads/execution_count avg_logical_read
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid') a where a.dbid>4 order by avg_cpu desc;

Let me know any problem on execution.
Thanks.

Understanding Execution Plan Statistics

Below is the output of an execution plan statistics.
SQL> set autot trace statistics
SQL> select * from tab;

107 rows selected.


Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
3459 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

You sometimes want to know what these fields indicates. Below is the details of these fields.

1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.


2)db block gets: Number of times a CURRENT block was requested.

3)consistent gets:
Number of times a consistent read was requested for a block. This is called the logical reads indicates for processing of a query how many blocks needs to be accessed.

4)physical reads: Total number of data blocks read from disk. This number equals the value of "physical reads direct" + all reads into buffer cache.

5)redo size: For processing of a query total amount of redo generated in bytes.

6)bytes sent through SQL*Net to client: Total number of bytes sent to the client from the foreground processes.

7)bytes received through SQL*Net from client: Total number of bytes received from the client over Oracle Net.

8)SQL*Net round-trips to/from client:
Total number of Oracle Net messages sent to and received from the client.
9)sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes.

10)sorts (disk):
Number of sort operations that required at least one disk write.

11)rows processed:
Number of rows processed during the operation.

Thanks.

Type of constraints in oracle

Constraint in oracle imposes rule that restrict the values in a oracle database. There are six types of constraint in oracle database and all these constraints except not null constraint can be declared in two ways.

A brief description of these six types of constraints are listed below.

1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.

2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.

4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.

5)Check Constraint: Check constraint imposes restriction of values based on specified condition.

6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.

Constraint related queries: http://askershad.blogspot.com/2010/04/dbaconstraints-userconstraints.html

Thanks.

dba_constraints/ user_constraints/ all_constraints queries

Simply list of constranits along with the constraint_type you can achieve querying from dba_constraints/ user_constraints/ all_constraints.

SQL> select owner, constraint_name, constraint_type from dba_constraintswhere owner='&Owner_name' and table_name='&Tab_name';

Output:

In the column constraint_type there may have values C,P,U,R,V and O which means,
1) C :check constraint on a table
2) P :primary key
3) U :unique key
4) R :referential integrity
5) V :with check option, on a view
6) O :with read only, on a view

You can get constraints along with their columns and position by querying from DBA_CONS_COLUMNS/ USER_CONS_COLUMNS/ ALL_CONS_COLUMNS.

SQL> col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
SQL> select * from dba_cons_columns where table_name='&Tab_name' and owner='&Owner_name' order by constraint_name, position;

By joining both two views you can get a list of constraints , their type, column_name, column position and their reference constraint name, reference table name in the constraint by,

SQL> col r_owner for a10
SQL> Select c.constraint_name, cc.column_name, c.r_owner,c.r_constraint_name,c.constraint_type,cc.position, r.table_namefrom dba_constraints c JOIN dba_cons_columns cc ON(c.table_name=cc.table_name AND c.owner=cc.ownerAND c.constraint_name=cc.constraint_name) LEFT JOIN dba_constraints rON(c.r_constraint_name=r.constraint_name AND r.constraint_type in ('P','U') ) where c.table_name='&Tab_name' and c.owner='&Owner_name' order byconstraint_name, position;

Thanks.