Monitoring free memory in Large Pool and Shared pool on Oracle
Monitoring Free Memory in Large Pool and Shared Pool (Oracle)
by Avnish Rastogi
The below process was developed to monitor free memory in shared pool and large pool proactively. Lack of memory in these two pools will cause Oracle to generate ORA-4031. Another reason for ORA-4031 is memory pool fragmentation. When we try to allocate large chunk of memory, Oracle will first flush the memory segments, which aren’t in use and merge them. If there is still not enough large piece of continuous memory, Oracle will generate ORA-4031. If your application uses Java, then Oracle may also generate ORA-4031 if Java pool is not big enough.
Oracle provides several System tables, which DBA can use to troubleshoot ORA-4031 errors.
V$SQLAREA—Oracle uses this view to store information of every sql statement and pl/sql block executed since your database is started.
X$KSMLRU—Oracle uses this view to keep tracks allocation in shared pool that causes other objects to aged out.
X$KSMSP—You can use this view to find out how free space is currently allocated.
V$SGASTAT—Oracle uses this view to displace detailed information about SGA allocation. You can use this view to see free memory in share pool and large pool. I used primary v$SGASTAT to develop following process for monitoring free memory in shared pool and large pool.
CREATE OR REPLACE MemoryMgmt
(
mem_threshold IN NUMBER /* IN MB */
) IS
large_free_mem NUMBER;
shared_free_mem NUMBER;
datetime VARCHAR2(20);
message VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE
'SELECT bytes FROM v$sgastat
WHERE pool = ''large pool'' AND name = ''free memory'''
INTO large_free_mem;
EXECUTE IMMEDIATE
'SELECT bytes FROM v$sgastat
WHERE pool = ''shared pool'' AND name = ''free memory'''
INTO shared_free_mem;
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO datetime FROM dual;
-- If Free Large Memory is less than INPUT memory then write an alert in alert log.
-- If Free Large Memory is greater than INPUT memory then write information in alert log.
IF ((large_free_mem < (mem_threshold*1024*1024)) OR (shared_free_mem < (mem_threshold*1024*1024)))
THEN
message := datetime || ' ** MM-0001 ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';
ELSE
message := datetime || ' ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';
END IF;
DBMS_SYSTEM.KSDWRT(2, message);
END MemoryMgmt;
Note that DBMS_SYSTEM package is not a documented package from Oracle and KSDWRT procedure allows to write any string into Oracle Alert Log file.
You can use two approach to resolve memory issue; one is to allocate memory using ‘alter system’ command another approach is to use any monitoring utility to monitor oracle alert log and generate email or page to OnCall DBA to allocate memory. We used second approach and used BMC Log Monitor to monitor MM-0001 string and generate trouble ticket to OnCall person. In our environment we are running thing procedure every 15 minutes using Oracle DBA jobs.
Monday, January 31, 2005
Get CD Key and MDAC Version from SQL Server 2000
Get CD Key and MDAC Version from SQL Server 2000 - ExtremeExperts: How can I find the SQL Server CD key I used to install? It would be great to get this information from my desktop.
Answer:
As said the easy way is to get to the remote machines registry key settings and search for the same. The location to look for this CD key is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\registration\CD_KEY
More often than not this is not a feasible option. But donot deter, here is a cool undocumented command that can help you get this information from the registry. It is called the xp_regread . As the name suggests its used to read the registry values.
USE master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\80\registration',
'CD_KEY'
I think the three parameters are self explanatory seeing the registry key we have to access. Since this extended stored procedure is un-documented. Use this with care. The systems adminsitrator can disable all these stored procedures at your work place ... :) ... And Microsoft can remove these stored procedures without warning.
Having seen the use of this extended procedure usage, lets get one more important registry key. I've also been asked how can we find the MDAC Version from the ISQLW window. And working on the same logic here is the solution.
EXEC master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\DataAccess',
N'Version'
I think you should be able to decifer the appropriate registry key we are trying to access.
Answer:
As said the easy way is to get to the remote machines registry key settings and search for the same. The location to look for this CD key is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\registration\CD_KEY
More often than not this is not a feasible option. But donot deter, here is a cool undocumented command that can help you get this information from the registry. It is called the xp_regread . As the name suggests its used to read the registry values.
USE master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\80\registration',
'CD_KEY'
I think the three parameters are self explanatory seeing the registry key we have to access. Since this extended stored procedure is un-documented. Use this with care. The systems adminsitrator can disable all these stored procedures at your work place ... :) ... And Microsoft can remove these stored procedures without warning.
Having seen the use of this extended procedure usage, lets get one more important registry key. I've also been asked how can we find the MDAC Version from the ISQLW window. And working on the same logic here is the solution.
EXEC master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\DataAccess',
N'Version'
I think you should be able to decifer the appropriate registry key we are trying to access.
Monday, January 03, 2005
Using Excel to Pull Data from SQL Server
Using Excel to Pull Data from SQL Server
Using Excel to Pull Data from SQL Server
Michael Schmalz
Before I start, I want to emphasize that this example uses Excel for the functionality as opposed to Enterprise Manager or any of the SQL Server User Interface functionality. In the first article that I wrote about getting data from SQL Server to Excel, you were shown how to push data into Excel using DTS. This is useful in many cases when you are dealing with large datasets. But, if you want access to specific pieces of data in real time, then DTS is not the method to use. In cases like this, two very viable options are to use Microsoft Access or Microsoft Excel. You could use Microsoft Word if you needed to mail letters to customers meeting some criteria, but similar concepts that you would use in Excel apply to Word as well. Your decision about whether to use Access or Excel will depend mostly on what you want to do with the data after you get it. For example, if you just need to page through data and don’t need calculations and don’t need to do anything else with the data, then Access is a good choice. If you need the data in a report format with or without calculations and don’t need to do anything else with the data, Access Reports or Excel could work. In that case just pick the one you are most comfortable using. But, if you want access to the data and need to do calculations and also need to be able to reference the data for other purposes, then Excel can be an excellent choice. It is also worth noting that many companies do not put Microsoft Access on everyone’s desktop. So, in those cases Excel is going to be the go to application.
Pulling Data with a Query
There is a query tool that comes with Excel that can be used to get data from a variety of sources. However, it is not easy to modify your query on the fly and you don’t have a whole lot of control over where the data goes. It simply takes a result set and places it in your worksheet where you tell it. If you want to change the number of parameters and their values on the fly and do different things with each record, then this is not the tool for you.
One of the other options you have is to use Visual Basic for Applications (VBA) and ActiveX Data Objects (ADO) to manage the data. That is the topic of this article. After you are done reading this article, you will understand how to: build a connection string to SQL Server, connect to SQL Server, execute a query, and navigate through the resulting recordset.
Step 1 – Get Connection Information on Your Server
Your first order of business is to determine how you want to connect to your server. If you are currently using your server with MS Access, then it is likely that you already have a DSN set up with your server information in it. You can easily use that DSN through VBA. If you don’t have a DSN set up, then you will need to get the connection information to your server. The information that you need is the server name or IP address, the database name, your username, and your password. I wrote this article using this information instead of a DSN. If you are using a DSN or use Windows Authentication instead of a password, then your connection string will change slightly. There are many examples about how to change your connection string for these different methods in the ADO documentation.
Step 2 – Get Excel Ready
The next thing that you must do is start-up Excel and get into a new workbook. Then, hit Atl-F11 to bring up the Visual Basic Editor. In the default view, you will see the project explorer on the left hand side of your screen with a treeview list of items. If it is not already expanded, you should click the plus sign on the listing that says “VBAProject (filename.xls)” and then right-click on the listing that says “Microsoft Excel Objects”. You will see a pop-up list with several options. You need to select Insert – Module. This will get you a blank module. As a default, ActiveX Data Objects is not available for use from VBA. In order to make ADO available for use, you need to go to the menu bar and select Tools – References. From the box that comes up, you need to scroll down the list and check the box for “Microsoft ActiveX Data Objects 2.x Library”. You should generally select the highest version number. The only caveat to this is when it will be used by multiple users. In cases like this, you should select the version that is on everyone’s computer – or better yet install the latest version on everyone’s computer. Now, you are ready to write your code.
Step 3 – Write Your Code
Your next decision is whether you want to set up a generic procedure to connect to SQL Server or if you want to simply have it as part of your module. For the purposes of this article, the connection to SQL Server will be part of the module, although the information for the connection will come from the Excel Workbook.
On the Project Explorer, you will see a treeview for Modules and a module named “Module1”. Double-click on “Module1” to bring up the blank module. Next, go to the menu bar and click on Insert – Procedure. You will want the radio button for Sub to be selected and the radio button for Public to be selected. For the name of the procedure, let’s call it GetMyData.
With the procedure ready, it is time to go back to the Excel Workbook. In Sheet1, set up your worksheet like the figure below.
You could also create a field for password, but for the purposes of this article I have the password coming from an inputbox. I would not suggest storing a password in your worksheet, but it can be done. The ADO objects that you will need are Connection, Recordset, and Field. Also, when you type in your Fields and Criteria, you must use the ' character to start your entry so that Excel does not think you are entering a formula. For the rest of the article, refer to the code below:
Public Sub GetMyData()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim adofld As ADODB.Field
Dim server As String
Dim dbname As String
Dim usernm As String
Dim tblname As String
Dim pword As String
Dim sqlstr As String
Dim whrclse As String
Dim x As Integer
Dim y As Integer
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Set xlws = Sheets("Sheet1")
pword = InputBox("Please enter your password.", "Password Prompt")
server = xlws.Range("B3").Value
dbname = xlws.Range("B4").Value
usernm = xlws.Range("B5").Value
tblname = xlws.Range("B6").Value
Set adoconn = New ADODB.Connection
Set adors = New ADODB.Recordset
adoconn.ConnectionString = "Provider=sqloledb;Datasource=" & server & _
";Database=" & dbname & ";uid=" & usernm & _
";pwd=" & pword
adoconn.Open
sqlstr = "Select " & tblname & ".* from " & tblname
x = 12
y = 0
While xlws.Cells(x, 1).Value <> ""
y = y + 1
whrclse = whrclse & " AND " & xlws.Cells(x, 1).Value & _
" " & xlws.Cells(x, 2).Value
x = x + 1
Wend
If y <> 0 Then
whrclse = " WHERE 1 = 1 " & whrclse
sqlstr = sqlstr & whrclse
End If
adors.Open sqlstr, adoconn, adOpenStatic
Debug.Print sqlstr
Set xlws = Sheets("Sheet2")
xlws.Activate
x = 1
For Each fld In adors.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld
Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset adors
xlws.Columns.AutoFit
adors.Close
adoconn.Close
Set fld = Nothing
Set adors = Nothing
Set adoconn = Nothing
Set xlrng = Nothing
Set xlws = Nothing
End Sub
In this code, there are a lot of things going on. The two concepts that I want to cover are the ones that are not necessarily covered in the documentation that comes with ADO. The first is the building of the where clause. What I am trying to accomplish with the Excel Worksheet is the ability for a user to enter as many fields and criteria as they wish. The code is going to start at the first row where fields and criteria are entered and then continue until a blank row is encountered. In order to not have to check for the first row to determine if an “AND” is needed, what I have done is created a first part of the where clause that will always be true “1 = 1”. You could use other expressions that evaluate to true or capture the first row separately and then place “AND”s in subsequent rows. But, this way is very easy. The second item that I would like to call your attention to is the line “For Each fld in adors.fields”. What I am doing there is getting the field name for each field in the recordset so that I can put column headers on.
You may also notice the Excel objects that are being used – the Worksheet Object and the Range Object. As you type this code, you will notice that there are many other properties and methods that you can access. In this case, we are using the CopyFromRecordset method of the Range Object. This method takes the entire recordset and starts the copy and paste in the range specified. The second method being used is the AutoFit method. This is setting up the Worksheet to be more readable.
Final Step
The final step is to place a command button on the Worksheet. You do this by opening up the Visual Basic Toolbar from the View > Toolbars menu. Then, you open up the Control Toolbox from the toolbar. You will see a command button on this toolbox and you simple click on it and then click and drag on your worksheet to draw the command button that you want. When you have done this, you can right-click on it to change properties (from here you can change color, caption, etc.). Then, right-click on it again and go to view code. Your one line of code for the OnClick event is:
Private Sub CommandButton1_Click()
GetMyData
End Sub
You can then fill out the sheet with your information and click the button. The code will run and place the recordset on Sheet2. You could obviously set this up with other sheet names, etc. Some suggested uses for the resulting worksheet can be a report on another sheet that refers to Sheet2, setting criteria for transactions that need reviewed, or any other time that you need to get a recordset into Excel for analysis.
Conclusion
While as an administrator it is unlikely that you will need this type of setup for your work, a method like this makes it possible for you to give recordset viewing capabilities to people on their desktop as long as Excel and MDAC are installed. My guess is that you would not use this code verbatim, but might be able to tweak the code and put it to good use. A final note that you can do is place all of the server information and user names and passwords into the Excel code and then protect the code with a password. You can set up a generic read only login that you only give access to specific views on the server. This would enable several people to use the workbook and they would never know the name of the server, the login, etc. But, how you set it up is up to you.
Using Excel to Pull Data from SQL Server
Michael Schmalz
Before I start, I want to emphasize that this example uses Excel for the functionality as opposed to Enterprise Manager or any of the SQL Server User Interface functionality. In the first article that I wrote about getting data from SQL Server to Excel, you were shown how to push data into Excel using DTS. This is useful in many cases when you are dealing with large datasets. But, if you want access to specific pieces of data in real time, then DTS is not the method to use. In cases like this, two very viable options are to use Microsoft Access or Microsoft Excel. You could use Microsoft Word if you needed to mail letters to customers meeting some criteria, but similar concepts that you would use in Excel apply to Word as well. Your decision about whether to use Access or Excel will depend mostly on what you want to do with the data after you get it. For example, if you just need to page through data and don’t need calculations and don’t need to do anything else with the data, then Access is a good choice. If you need the data in a report format with or without calculations and don’t need to do anything else with the data, Access Reports or Excel could work. In that case just pick the one you are most comfortable using. But, if you want access to the data and need to do calculations and also need to be able to reference the data for other purposes, then Excel can be an excellent choice. It is also worth noting that many companies do not put Microsoft Access on everyone’s desktop. So, in those cases Excel is going to be the go to application.
Pulling Data with a Query
There is a query tool that comes with Excel that can be used to get data from a variety of sources. However, it is not easy to modify your query on the fly and you don’t have a whole lot of control over where the data goes. It simply takes a result set and places it in your worksheet where you tell it. If you want to change the number of parameters and their values on the fly and do different things with each record, then this is not the tool for you.
One of the other options you have is to use Visual Basic for Applications (VBA) and ActiveX Data Objects (ADO) to manage the data. That is the topic of this article. After you are done reading this article, you will understand how to: build a connection string to SQL Server, connect to SQL Server, execute a query, and navigate through the resulting recordset.
Step 1 – Get Connection Information on Your Server
Your first order of business is to determine how you want to connect to your server. If you are currently using your server with MS Access, then it is likely that you already have a DSN set up with your server information in it. You can easily use that DSN through VBA. If you don’t have a DSN set up, then you will need to get the connection information to your server. The information that you need is the server name or IP address, the database name, your username, and your password. I wrote this article using this information instead of a DSN. If you are using a DSN or use Windows Authentication instead of a password, then your connection string will change slightly. There are many examples about how to change your connection string for these different methods in the ADO documentation.
Step 2 – Get Excel Ready
The next thing that you must do is start-up Excel and get into a new workbook. Then, hit Atl-F11 to bring up the Visual Basic Editor. In the default view, you will see the project explorer on the left hand side of your screen with a treeview list of items. If it is not already expanded, you should click the plus sign on the listing that says “VBAProject (filename.xls)” and then right-click on the listing that says “Microsoft Excel Objects”. You will see a pop-up list with several options. You need to select Insert – Module. This will get you a blank module. As a default, ActiveX Data Objects is not available for use from VBA. In order to make ADO available for use, you need to go to the menu bar and select Tools – References. From the box that comes up, you need to scroll down the list and check the box for “Microsoft ActiveX Data Objects 2.x Library”. You should generally select the highest version number. The only caveat to this is when it will be used by multiple users. In cases like this, you should select the version that is on everyone’s computer – or better yet install the latest version on everyone’s computer. Now, you are ready to write your code.
Step 3 – Write Your Code
Your next decision is whether you want to set up a generic procedure to connect to SQL Server or if you want to simply have it as part of your module. For the purposes of this article, the connection to SQL Server will be part of the module, although the information for the connection will come from the Excel Workbook.
On the Project Explorer, you will see a treeview for Modules and a module named “Module1”. Double-click on “Module1” to bring up the blank module. Next, go to the menu bar and click on Insert – Procedure. You will want the radio button for Sub to be selected and the radio button for Public to be selected. For the name of the procedure, let’s call it GetMyData.
With the procedure ready, it is time to go back to the Excel Workbook. In Sheet1, set up your worksheet like the figure below.
You could also create a field for password, but for the purposes of this article I have the password coming from an inputbox. I would not suggest storing a password in your worksheet, but it can be done. The ADO objects that you will need are Connection, Recordset, and Field. Also, when you type in your Fields and Criteria, you must use the ' character to start your entry so that Excel does not think you are entering a formula. For the rest of the article, refer to the code below:
Public Sub GetMyData()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim adofld As ADODB.Field
Dim server As String
Dim dbname As String
Dim usernm As String
Dim tblname As String
Dim pword As String
Dim sqlstr As String
Dim whrclse As String
Dim x As Integer
Dim y As Integer
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Set xlws = Sheets("Sheet1")
pword = InputBox("Please enter your password.", "Password Prompt")
server = xlws.Range("B3").Value
dbname = xlws.Range("B4").Value
usernm = xlws.Range("B5").Value
tblname = xlws.Range("B6").Value
Set adoconn = New ADODB.Connection
Set adors = New ADODB.Recordset
adoconn.ConnectionString = "Provider=sqloledb;Datasource=" & server & _
";Database=" & dbname & ";uid=" & usernm & _
";pwd=" & pword
adoconn.Open
sqlstr = "Select " & tblname & ".* from " & tblname
x = 12
y = 0
While xlws.Cells(x, 1).Value <> ""
y = y + 1
whrclse = whrclse & " AND " & xlws.Cells(x, 1).Value & _
" " & xlws.Cells(x, 2).Value
x = x + 1
Wend
If y <> 0 Then
whrclse = " WHERE 1 = 1 " & whrclse
sqlstr = sqlstr & whrclse
End If
adors.Open sqlstr, adoconn, adOpenStatic
Debug.Print sqlstr
Set xlws = Sheets("Sheet2")
xlws.Activate
x = 1
For Each fld In adors.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld
Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset adors
xlws.Columns.AutoFit
adors.Close
adoconn.Close
Set fld = Nothing
Set adors = Nothing
Set adoconn = Nothing
Set xlrng = Nothing
Set xlws = Nothing
End Sub
In this code, there are a lot of things going on. The two concepts that I want to cover are the ones that are not necessarily covered in the documentation that comes with ADO. The first is the building of the where clause. What I am trying to accomplish with the Excel Worksheet is the ability for a user to enter as many fields and criteria as they wish. The code is going to start at the first row where fields and criteria are entered and then continue until a blank row is encountered. In order to not have to check for the first row to determine if an “AND” is needed, what I have done is created a first part of the where clause that will always be true “1 = 1”. You could use other expressions that evaluate to true or capture the first row separately and then place “AND”s in subsequent rows. But, this way is very easy. The second item that I would like to call your attention to is the line “For Each fld in adors.fields”. What I am doing there is getting the field name for each field in the recordset so that I can put column headers on.
You may also notice the Excel objects that are being used – the Worksheet Object and the Range Object. As you type this code, you will notice that there are many other properties and methods that you can access. In this case, we are using the CopyFromRecordset method of the Range Object. This method takes the entire recordset and starts the copy and paste in the range specified. The second method being used is the AutoFit method. This is setting up the Worksheet to be more readable.
Final Step
The final step is to place a command button on the Worksheet. You do this by opening up the Visual Basic Toolbar from the View > Toolbars menu. Then, you open up the Control Toolbox from the toolbar. You will see a command button on this toolbox and you simple click on it and then click and drag on your worksheet to draw the command button that you want. When you have done this, you can right-click on it to change properties (from here you can change color, caption, etc.). Then, right-click on it again and go to view code. Your one line of code for the OnClick event is:
Private Sub CommandButton1_Click()
GetMyData
End Sub
You can then fill out the sheet with your information and click the button. The code will run and place the recordset on Sheet2. You could obviously set this up with other sheet names, etc. Some suggested uses for the resulting worksheet can be a report on another sheet that refers to Sheet2, setting criteria for transactions that need reviewed, or any other time that you need to get a recordset into Excel for analysis.
Conclusion
While as an administrator it is unlikely that you will need this type of setup for your work, a method like this makes it possible for you to give recordset viewing capabilities to people on their desktop as long as Excel and MDAC are installed. My guess is that you would not use this code verbatim, but might be able to tweak the code and put it to good use. A final note that you can do is place all of the server information and user names and passwords into the Excel code and then protect the code with a password. You can set up a generic read only login that you only give access to specific views on the server. This would enable several people to use the workbook and they would never know the name of the server, the login, etc. But, how you set it up is up to you.
Subscribe to:
Posts (Atom)