Warning: mysql_real_escape_string(): Access denied for user 'root'@'localhost' (using password: NO) in /home4/blessonj/public_html/blog/wp-content/plugins/keywords-cloud-for-wordpress/keywords-cloud.php on line 397

Warning: mysql_real_escape_string(): A link to the server could not be established in /home4/blessonj/public_html/blog/wp-content/plugins/keywords-cloud-for-wordpress/keywords-cloud.php on line 397

Posts Tagged oracle

Oracle query to get session pga & uga memory usage for inactive sessions

I’m listing below some of the queries I have created to see various oracle session related memory statistics. Replace all occurrences of XXXXXXXXXXXXXXXX with your database username. Please drop a comment below if these queries are useful for you guys.

Query for getting ‘session pga memory’ usage for inactive sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as pga_memory_usage_inactive from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name = ‘session pga memory’ group by name ;

Query for getting ‘session pga memory’ usage for all sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as pga_memory_usage_total from v$sesstat s , v$statname n where s.statistic# = n.statistic# and n.name = ‘session pga memory’ group by name ;

Query for getting ‘session uga memory’ usage for inactive sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as uga_memory_usage_inactive from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name = ‘session uga memory’ group by name ;

Query for getting ‘session uga memory’ usage for all sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as uga_memory_usage_total from v$sesstat s , v$statname n where s.statistic# = n.statistic# and n.name = ‘session uga memory’ group by name ;

You can determine how much memory each inactive session is consuming using the following query:

select TO_CHAR(ROUND(s.value/1024),99999999) || ‘ KB’ as Memory_Usage , n.name|| ‘(‘||s.statistic#||’)’ as memory_type , sid from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name like ‘%ga memory%’ order by value;

VN:F [1.9.12_1141]
Rating: 9.2/10 (5 votes cast)
VN:F [1.9.12_1141]
Rating: +2 (from 2 votes)

Tags: ,

How to get maximum limit of open_cursors, sessions and processes in Oracle

In order to display the maximum limit of open_cursors, sessions and processes in Oracle, you can run the below queries on SQL Plus:

select value from v$parameter where name = ‘open_cursors’

select value from v$parameter where name = ‘sessions’

select value from v$parameter where name = ‘processes’

VN:F [1.9.12_1141]
Rating: 8.0/10 (3 votes cast)
VN:F [1.9.12_1141]
Rating: +1 (from 1 vote)

Tags: , , ,

How to get current open_cursors utilization and maximum limit in Oracle

If you are looking for a query which will list you the current open_cursors utilization and maximum limit in Oracle database, then try the below given query:

select max(a.value) as highest_value, p.value as max_value from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = ‘opened cursors current’ and p.name= ‘open_cursors’ group by p.value;

VN:F [1.9.12_1141]
Rating: 9.0/10 (2 votes cast)
VN:F [1.9.12_1141]
Rating: 0 (from 0 votes)

Tags: ,

Viewing PGA Statistics of your Oracle Database

VIEWING PGA STATISTICS

———————————-

V$PGASTAT – This view gives instance-level statistics on the PGA memory usage and the automatic PGA memory manager. For example:

SELECT * FROM V$PGASTAT;

VN:F [1.9.12_1141]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.12_1141]
Rating: 0 (from 0 votes)

Tags: ,

Checking number of open cursors in Oracle

Run the following query to check the number of open cursors in your Oracle

select count(*) from v$open_cursor where USER_NAME=’xxxxxxxxx’

Replace xxxxxxxxx with the username your application is using.

VN:F [1.9.12_1141]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.12_1141]
Rating: 0 (from 0 votes)

Tags: ,

java.lang.Exception: SQL Error : java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)

If you get the following error in your java+jsp+oracle application, then you probably need to increase your maximum session/process limits (Default process and sessions limit in Oracle 9i are 150 and 170 respectively)

java.lang.Exception: SQL Error : java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)

Instructions for Increasing session, process and open_cursors max limits to 500 each are:

1. Login to SQL Plus as a normal user.

2. Run this query on SQL Plus (Replace xxxxxxxxxxxxx with your sys user password)

connect sys/xxxxxxxxxxxxx as sysdba

ALTER SYSTEM SET open_cursors=500 SCOPE=SPFILE;

3. Then run the below query on SQL Plus (Replace xxxxxxxxxxxxx with your sys user password)

connect sys/xxxxxxxxxxxx as sysdba

ALTER SYSTEM SET sessions=500 SCOPE=SPFILE;

4. Then run the below query on SQL Plus (Replace xxxxxxxxxxxxx with your sys user password)

connect sys/xxxxxxxxxxxxx as sysdba

ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

5. Then run the below query on SQL Plus

commit;

6. Open Control Panel > Administrative Tools >  Services

7. Restart all the oracle services that are running.

That’s it. Now the open_cursors, session and process limits will be set to 500 each.

If you want to check the current value of session and process maximum limit you can run the below query any time.

SELECT name, value FROM v$parameter WHERE name = ‘sessions’;

and

SELECT name, value FROM v$parameter WHERE name = ‘processes’;

If you want to check the process & session current utilization, maximum utilization and maximum limit you can run the below query:

select
resource_name,
current_utilization,
max_utilization,
limit_value
from
v$resource_limit
where
resource_name in ( ‘sessions’, ‘processes’);

Hope instructions are clear. Please let me know if you need any more info.

VN:F [1.9.12_1141]
Rating: 1.0/10 (1 vote cast)
VN:F [1.9.12_1141]
Rating: +1 (from 1 vote)

Tags: , ,