Named PL/SQL blocks of code which can be invoked using parameters are called PL/SQL sub programs.
Advantages of PL/SQL subprograms are
- The application makes a single call to the database to run a block of statements which improves performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
- PL/SQL is secure since the code resides inside the database thus hiding internal database details from the application. The application will only make a call to the PL/SQL sub program
- PL/SQL and SQL go hand in hand so there would be no need of any translation required between PL/SQL and SQL.
- Clustering means one than one database server configured for the same user connection. When users connect, one of the server’s responds and connects based on availability. The user is completely ignorant of the fact that there are more than one database servers. It demands high cost due to infrastructure needs. Clustering is hence done on server level.
- Mirroring means, one has many configured databases on the same server. Mirrors are basically copies of the original database. Mirroring is hence done on database level.
WATER MARK is a divided segment of used and free blocks. Blocks which are below high WATER MARK i.e. used blocks, have at least once contained some data. This data might have been deleted later. Oracle knows that blocks beyond high WATER MARK don’t have data; it only reads blocks up to the high WATER MARK during a full table scan.