The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or
DETERMINISTIC hints are used. The same rules that apply to the function itself also apply to any functions or procedures that it calls.If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is
parsed).To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.
Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.WNDS,RNDS,WNPS,RNPS.
WNDS - Write No Database state
RNDS - Read No Database state
WNPS - Write No Package state
RNPS - Read No Package state
TRUST - the function body is not checked for violations of the constraints listed in the pragma
You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.You can specify up to four
constraints (RNDS, RNPS, WNDS, WNPS,TRUST) in any order. To call a function from parallel queries, you must specify all four
constraints. No constraint implies another.
When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function
is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object
type spec (including the system-defined constructor for object types). You can still declare the pragma for individual
subprograms, overriding the default pragma.A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded
subprograms always applies to the most recent subprogram declaration.
Typically, you only specify this pragma for functions. If a function calls procedures, then you need to specify the pragma for
those procedures as well.
Examples
This example asserts that the function BALANCE writes no database state (WNDS) and reads no package state (RNPS). That is, it does not issue any DDL or DML statements, and does not refer to any package variables, and neither do any procedures or functions that it calls. It might issue queries or assign values to package variables.
CREATE PACKAGE loans AS
FUNCTION balance(account NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (balance, WNDS, RNPS);
END loans;
Jun 14, 2010
Jun 9, 2010
ORACLE NetworkTopology
The Oracle network architecture encompasses many components - all of which neatly corresponds to the OSI networking model (see below Figure oraclenw1.jpg). This architecture enables Oracle client and server applications to transparently communicate over protocols such as TCP/IP. The session protocol that interfaces between the applications (Oracle Call Interface, or OCI, on the client and Oracle Program Interface, or OPI, on the server) and the network layer is known as Net8 (Net9), and before that SQL*Net. Between the OCI/OPI and Net8 layer is a presentation protocol called Two-Task Common (TTC) that is responsible for character set and data type conversion differences between the client and the server. The Net8 session protocol has three components - the Net Foundation and Routing/Naming/Auth and TNS - the last two making up Protocol Support. Supported transport protocols include TCP/IP, with or without TCP, Named Pipes and Sockets Direct Protocol (SDP), which enables communication over Infiband high-speed networks. Underpinning all of this is the Transparent Network Substrate protocol, also known as TNS. The task of TNS is to select the Oracle Protocol Adapter, wrapping the communication in one of the supported transport protocols.
Main data dictionary view of network model is USER_SDO_NETWORK_METADATA which is in the user MDSYS", where "MD" stands for "Multi Dimensional" .
There are two types of Network
1) LOGICAL
2) SPATIAL
Among Spatial Netwrok there are 3 sub classification
a) SDO Geometry
b) LRS Geometry
c) Topology Geometry
LOGICAL
--------
A network data model for representing capabilities of objects (modeled as nodes and links) in a network.
The network data model provides PL/SQL procedures (package SDO_NET) to simplify network creation and management.
The default values for network table names, column names, and metadata are as follows
Node table name:
Link table name:
Path table name:
Path Link table name:
each link in each path in the network)
Step 1:
EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST'-- network name
, 1 -- no of hierarchy level
, FALSE -- directed link?
, FALSE -- no with cost?
);
Step 2:
Populate the node and link tables.
Step 3:
Validate the network. select SDO_NET.VALIDATE_NETWORK('NET_TEST') from dual ;
Then in _NODE$ table COST named field will be created , and if directlink parameter is set as TRUE then in _LINK$ table Biridected named field will be created .
Apr 23, 2010
Unindexed Foreign Keys
Less Overhead on Unindexed Foreign Keys
A table level share lock is placed on unindexed foreign keys in a child table when doing an update or delete on primary key column in the referenced parent table. the lock is realesed immediate after obtaining it. If multiple pk key are updated or deleted , the lock is obtained and released once per row .The obtaining and realesing of the shared lock are as follow as
1) Get a save point .
2) Obtain a share lock .
3) Roll back to save point .
The share lock has only one purpose , to check whether you have pending transactions against any rows . If that case , the share lock request would fail becuse of exclusive row locks.
Prior to Oracle 9i
A table level share lock is placed on unindexed foreign keys in a child table when doing an update or delete on primary key column in the referenced parent table. the lock is realesed immediate after obtaining it. If multiple pk key are updated or deleted , the lock is obtained and released once per row .The obtaining and realesing of the shared lock are as follow as
1) Get a save point .
2) Obtain a share lock .
3) Roll back to save point .
The share lock has only one purpose , to check whether you have pending transactions against any rows . If that case , the share lock request would fail becuse of exclusive row locks.
Prior to Oracle 9i
Apr 5, 2010
RAID 2,RAID 3,RAID 4
RAID 2
Data is distributed in extremely small increments across all disks and adds one or more disks that contain a Hamming code for redundancy.This is not considered commercially viable due to the added disk requirements (10 to 20 percent must be added to allow for the Hamming disk.
The use of the Hamming(7,4) code (four data bits plus three parity bits) permits using 7 disks in RAID 2, with 4 being used for data storage and 3 being used for error correction.
RAID 2 is the only standard RAID level, other than some implementations of RAID 6, which can automatically recover accurate data from single-bit corruption in data. Other RAID levels can detect single-bit corruption in data, or can sometimes reconstruct missing data, but cannot reliably resolve contradictions between parity bits and data bits without human intervention.
--------------------------------
Characteristics & Advantages
"On the fly" data error correction
Extremely high data transfer rates possible
The higher the data transfer rate required, the better the ratio of data disks to ECC disks
-------------------------
Disadvantages
Very high ratio of ECC disks to data disks with smaller word sizes - inefficient
Entry level cost very high - requires very high transfer rate requirement to justify.
Transaction rate is equal to that of a single disk at best .
No commercial implementations exist / not commercially viable.
-------------------------------------
RAID 3
-------------
RAID 3 - Is rarely used . So here it is not discusiing
One of the side effects of RAID 3 is that it generally cannot service multiple requests simultaneously.
In our example, a request for block "A" consisting of bytes A1-A6 would require all three data disks to seek to the beginning (A1) and reply with their contents.A simultaneous request for block B would have to wait.
RAID 3. also distributes data in small increments but adds only one parity disk. This results in good performance for large transfers, but small transfers show poor performance.
RAID Level 3 requires a minimum of 3 drives to implement
Characteristics & Advantages
-----------------------------
Very high Read,write data transfer rate
Disadvantages
------------------
Transaction rate equal to that of a single disk drive at best (if spindles are synchronized)
Controller design is fairly complex
Very difficult and resource intensive to do as a "software" RAID
RAID 4
Data is distributed in extremely small increments across all disks and adds one or more disks that contain a Hamming code for redundancy.This is not considered commercially viable due to the added disk requirements (10 to 20 percent must be added to allow for the Hamming disk.
The use of the Hamming(7,4) code (four data bits plus three parity bits) permits using 7 disks in RAID 2, with 4 being used for data storage and 3 being used for error correction.
RAID 2 is the only standard RAID level, other than some implementations of RAID 6, which can automatically recover accurate data from single-bit corruption in data. Other RAID levels can detect single-bit corruption in data, or can sometimes reconstruct missing data, but cannot reliably resolve contradictions between parity bits and data bits without human intervention.
--------------------------------
Characteristics & Advantages
"On the fly" data error correction
Extremely high data transfer rates possible
The higher the data transfer rate required, the better the ratio of data disks to ECC disks
-------------------------
Disadvantages
Very high ratio of ECC disks to data disks with smaller word sizes - inefficient
Entry level cost very high - requires very high transfer rate requirement to justify.
Transaction rate is equal to that of a single disk at best .
No commercial implementations exist / not commercially viable.
-------------------------------------
RAID 3
-------------
RAID 3 - Is rarely used . So here it is not discusiing
One of the side effects of RAID 3 is that it generally cannot service multiple requests simultaneously.
In our example, a request for block "A" consisting of bytes A1-A6 would require all three data disks to seek to the beginning (A1) and reply with their contents.A simultaneous request for block B would have to wait.
RAID 3. also distributes data in small increments but adds only one parity disk. This results in good performance for large transfers, but small transfers show poor performance.
RAID Level 3 requires a minimum of 3 drives to implement
Characteristics & Advantages
-----------------------------
Very high Read,write data transfer rate
Disadvantages
------------------
Transaction rate equal to that of a single disk drive at best (if spindles are synchronized)
Controller design is fairly complex
Very difficult and resource intensive to do as a "software" RAID
RAID 4
Apr 3, 2010
RAID
RAID an acronym for Redundant Array of Inexpensive Disks or Redundant Array of Independent Disks, is a technology that allows high levels of storage reliability from low-cost and less reliable PC-class disk-drive components, via the technique of arranging the devices into arrays for redundancy.
Consider Personal computer they come with more than enough memory, have more than enough processing power, and have lots of disk space. The one thing they don’t come with is a backup (or external) drive. if User need to protect their important files, such as e-mails, pictures , and all .Many homeowners don’t think about backups. They go on merrily for years, adding their financial data, their photographs, and their collections of e-mail to their hard drives. Then one day they realize about the hard drive failure. Their hard drive fails, their computer won’t boot . What about servers do they come with extra disks? Usually, yes, servers are ordered with extra disks. Depending on the model of the server, you will have a different number of options and disks available. The primary reason for extra disks, besides the need for disk capacity, is to protect yourself through the utilization of RAID in the event of a hard disk failure.
For example, Figure below shows one possible RAID configuration. In this Figure , three hard drives are tied together to appear as one. The same data gets written to all three drives. Because the three drives are mirror images of each other, we can trash one of the drives and still have access to our data on the other two. We can even trash two drives, leaving just one good one.
Figure 1
RAID for Performance
----------------------
RAID can also be utilized to improve performance. Knowing how to do that can be
helpful when trying to performance tuning queries. The faster you can read and write data to and from your disks, the faster your response times will be. You can use RAID to increase your I/O throughput by taking advantage of
striping your data across several disks at once.
For example, a traditional hard disk has a little arm inside of it that moves back and forth every time you want to read and write data. IF you had to do 50 read and 50 write operations, and each one took 1/50th of a second. Then it will take Two seconds on one disk . Now, configure RAID as shown in Figure below
Now you have two little arms moving back and forth across two disks at the same time. You would still have your two hundred operations to perform, but they would be done on two drives simultaneously. Each individual drive would get half the work. Your overall time would be reduced from two seconds to one second.
Another method for improving performance would be caching. Most RAID controllers
allow for some amount of data to be stored in cache.
---------------------------------------------------------
RAID for Fault Tolerance
RAID uses two methods to provide for fault tolerance: mirroring and parity. Mirroring is exactly what it sounds like: you have two (or more) copies of your data on two (or more) different disks. As in Figure 1
Parity occurs when you add extra data called parity data to one of your disks, which can then be used to reconstruct your real data when one disk fail
Raid Level 0 (RAID 0) - Striped Disk Array without Fault Tolerance: Provides data striping (spreading out blocks of each file across multiple disk drives) but no redundancy. This improves performance but does not deliver fault tolerance. If one drive fails then all data in the array is lost.
RAID 0 is also used in some gaming systems where performance is desired and data integrity is not very important.#and in Video Production and Editing,
,Image Editing, Pre-Press Applications,any application requiring high bandwidth
RAID 1 - Mirroring and Duplexing: Provides disk mirroring.
A RAID 1 creates an exact copy (or mirror) of a set of data on two or more disks. This is useful when read performance or reliability are more important than data storage capacity. A classic RAID 1 mirrored pair contains two disks
Characteristics & Advantages
One Write or 2 Reads possible per mirrored pair.Twice the Read transaction rate of single disks, same Write transaction rate as single disks.100% redundancy of data means no rebuild is necessary in case of a disk failure, just a copy to the replacement disk.Simplest RAID storage subsystem design
Recommended Applications
Accounting,Payroll,Financial,Any application requiring very high availability
RAID 5
-------
This level is also known as striping with parity. It is very similar to RAID 0, with one additional disk needed to hold the parity bit that gets striped across all disks. . Without a doubt, RAID 5 is a favorite RAID level,
and I have seen it used by many administrators.The reason for RAID 5 being so well liked is that if one disk fails, you simply have to add in a new disk (before a second disk fails; otherwise, you lose everything) and the array will rebuild itself. While RAID 5 can offer better performance than RAID 1, the overhead for that parity bit means it will not perform as well as RAID 0. But you do get fault tolerance with RAID 5, and that is why so many administrators love using RAID 5 over anything else.
Consider Personal computer they come with more than enough memory, have more than enough processing power, and have lots of disk space. The one thing they don’t come with is a backup (or external) drive. if User need to protect their important files, such as e-mails, pictures , and all .Many homeowners don’t think about backups. They go on merrily for years, adding their financial data, their photographs, and their collections of e-mail to their hard drives. Then one day they realize about the hard drive failure. Their hard drive fails, their computer won’t boot . What about servers do they come with extra disks? Usually, yes, servers are ordered with extra disks. Depending on the model of the server, you will have a different number of options and disks available. The primary reason for extra disks, besides the need for disk capacity, is to protect yourself through the utilization of RAID in the event of a hard disk failure.
For example, Figure below shows one possible RAID configuration. In this Figure , three hard drives are tied together to appear as one. The same data gets written to all three drives. Because the three drives are mirror images of each other, we can trash one of the drives and still have access to our data on the other two. We can even trash two drives, leaving just one good one.
Figure 1
RAID for Performance
----------------------
RAID can also be utilized to improve performance. Knowing how to do that can be
helpful when trying to performance tuning queries. The faster you can read and write data to and from your disks, the faster your response times will be. You can use RAID to increase your I/O throughput by taking advantage of
striping your data across several disks at once.
For example, a traditional hard disk has a little arm inside of it that moves back and forth every time you want to read and write data. IF you had to do 50 read and 50 write operations, and each one took 1/50th of a second. Then it will take Two seconds on one disk . Now, configure RAID as shown in Figure below
Now you have two little arms moving back and forth across two disks at the same time. You would still have your two hundred operations to perform, but they would be done on two drives simultaneously. Each individual drive would get half the work. Your overall time would be reduced from two seconds to one second.
Another method for improving performance would be caching. Most RAID controllers
allow for some amount of data to be stored in cache.
---------------------------------------------------------
RAID for Fault Tolerance
RAID uses two methods to provide for fault tolerance: mirroring and parity. Mirroring is exactly what it sounds like: you have two (or more) copies of your data on two (or more) different disks. As in Figure 1
Parity occurs when you add extra data called parity data to one of your disks, which can then be used to reconstruct your real data when one disk fail
Raid Level 0 (RAID 0) - Striped Disk Array without Fault Tolerance: Provides data striping (spreading out blocks of each file across multiple disk drives) but no redundancy. This improves performance but does not deliver fault tolerance. If one drive fails then all data in the array is lost.
RAID 0 is also used in some gaming systems where performance is desired and data integrity is not very important.#and in Video Production and Editing,
,Image Editing, Pre-Press Applications,any application requiring high bandwidth
RAID 1 - Mirroring and Duplexing: Provides disk mirroring.
A RAID 1 creates an exact copy (or mirror) of a set of data on two or more disks. This is useful when read performance or reliability are more important than data storage capacity. A classic RAID 1 mirrored pair contains two disks
Characteristics & Advantages
One Write or 2 Reads possible per mirrored pair.Twice the Read transaction rate of single disks, same Write transaction rate as single disks.100% redundancy of data means no rebuild is necessary in case of a disk failure, just a copy to the replacement disk.Simplest RAID storage subsystem design
Recommended Applications
Accounting,Payroll,Financial,Any application requiring very high availability
RAID 5
-------
This level is also known as striping with parity. It is very similar to RAID 0, with one additional disk needed to hold the parity bit that gets striped across all disks. . Without a doubt, RAID 5 is a favorite RAID level,
and I have seen it used by many administrators.The reason for RAID 5 being so well liked is that if one disk fails, you simply have to add in a new disk (before a second disk fails; otherwise, you lose everything) and the array will rebuild itself. While RAID 5 can offer better performance than RAID 1, the overhead for that parity bit means it will not perform as well as RAID 0. But you do get fault tolerance with RAID 5, and that is why so many administrators love using RAID 5 over anything else.
Subscribe to:
Posts (Atom)