Control Resource Utilization in Oracle

Resource utilization can be controlled either through profiles or Oracle Database Resource Manager. Profiles are generally used to control the password protection, cpu usage assigned to users under profile. While Oracle Database Manager makes use
of total resource available in the hardware by distribution of the resources among the groups when certain resource plans are inactive. Managing resources through Database resource manager is the best solution to manage the utilization of cpu
usage, active session pool, degree of parallelism limit, automatic group switching mechanism, execution time limit, undo pool and idle time limit whereas profiles can be only temporary solution to manage resource utilization.

Here is an example to demonstrate manage cpu usage through profile.

Change the resource_limit parameter either in pfile or through the command line by executing

alter system set resource_limit=true;

CREATE PROFILE PROF_QUERY LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 1000;

CPU_PER_SESSION UNLIMITED provides cpu usage to the session for UNLIMITED TIME
CPU_PER_CALL 1000 provides cpu usage to the SQL PARSE,EXECUTE,FETCH for 10 secs for every SQL Statement.

Thus the users under the profile PROF_QUERY can use resource for unlimited time in a session and for 10 secs for each Sql Statement.

Check the default profile of the user

select * from dba_users WHERE USERNAME='TEST'

Change the profile of the user

ALTER USER TEST PROFILE PROF_QUERY;

Check the parameters of profile

select * from dba_profiles where profile='PROF_QUERY'

To implement and modify Oracle Database Manager feature for resource utilization, change compatible parameter to 8.1.0.0.0 or above, from pfile

compatible='8.1.0.0.0'

Jsp Page Example

Shows the demonstration of page directive to import java classes,get the hostname through predifined HttpServletRequest variable,access count since last reboot of machine, use of jsp:useBean Directive, class named Man and MysqlConn must exist in package pack and loads the list from the mysql database.

<%@ page import = "java.util.*,pack.MysqlConn,java.sql.*" %>
Current Date <%= new java.util.Date() %>

Remote Host <%=request.getRemoteHost() %>
<%! private int remoteAccess=0; %>
Access Count :<%= ++remoteAccess %>

angle bracket jsp:useBean id="test" class="pack.Man" scope="page"/ angle bracket
angle bracketjsp:setProperty name="test" property="empName" value="Maheswor Prajapati" /angle bracket
angle bracketjsp:setProperty name="test" property="address" value="bkt"/angle bracket
angle bracketsp:setProperty name="test" property="gender" value="male"/angle bracket
Name: angle bracket jsp:getProperty name="test" property="empName" /angle bracket
Address :angle bracketjsp:getProperty name="test" property="address" /angle bracket>
Gender: angle bracketjsp:getProperty name="test" property="gender" /angle bracket

<%! ResultSet set=null; %>
<%
MysqlConn con=new MysqlConn();
set=con.getEmp();
%>

The output is:
begin select tag
<%

while (set.next()){
%>
begin option tag value='<%=set.getString(1) %>' ><%=set.getString(2)%> end option tag
<%
}

%>

end select tag

Program to Connect Mysql Database in Java

The program connects mysql database in java. It assumes that the database named test exist in the local computer and mysql.jar is loaded in the JRE system library. It retrives and displays the name of the employee from the database.

package pack;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class MysqlConn {
static String user = "mahesh";
static String password = "prajap";
static String url = "jdbc:mysql://localhost/test";

public static Connection dbConnect() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection conn = DriverManager.getConnection(url, user,
password);
return conn;
} catch (Exception ms) {
ms.printStackTrace();
return null;
}
}

public static void main(String args[]) {
MysqlConn conn = new MysqlConn();
conn.getEmp();
}

public void getEmp() {

Connection conn = MysqlConn.dbConnect();
String queryStr = "select emp_id,emp_name from emp";

try {
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(queryStr);
while(rs.next()){
System.out.println("The employee is: "+rs.getString(2));
}

} catch (Exception ms) {
ms.printStackTrace();

}
}

}