>Shared pool size, PGA USAGE TEST,STATISTICS level sql

>shared pool size boş alan
SELECT TO_NUMBER (V$PARAMETER.VALUE)VALUE, V$SGASTAT.BYTES, (V$SGASTAT.BYTES/TO_NUMBER (v$parameter.VALUE))*100
percent
FROM V$SGASTAT, v$parameter
WHERE V$SGASTAT.NAME = ‘free memory’
AND v$parameter.NAME = ‘shared_pool_size’;

select * from v$parameter

select * from V$SGASTAT where pool like ‘shared pool’
————————
set linesize 500
column display_value format a5
select * from v$sql_workarea_active;
select p.PGA_USED_MEM / 1024 / 1024 pga_used,
p.PGA_ALLOC_MEM / 1024 / 1024 pga_alloc,
p.PGA_FREEABLE_MEM / 1024 / 1024 pga_freeable,
s.MODULE,
s.ACTION
from v$process p, v$session s
where p.ADDR = s.PADDR
and s.MODULE = ‘PGA USAGE TEST’
order by p.PGA_ALLOC_MEM desc;
——————————–
select * from v$statistics_level

SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

SELECT * FROM V$FILESPACE_USAGE;

SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM V$STATISTICS_LEVEL
WHERE UPPER(STATISTICS_NAME) LIKE ‘THRESHOLD%’;

SELECT STATISTICS_NAME,
SESSION_STATUS,
SYSTEM_STATUS,
ACTIVATION_LEVEL,
SESSION_SETTABLE
FROM v$statistics_level
ORDER BY 1
/
———————————-
select u.TIMESTAMP,
d.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,’999.99′)
percent
from all_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = d.table_name
and d.owner = ‘SAPSR3’ and d.table_name like ‘Z%’
and (u.inserts > 1 or u.updates > 1 or u.deletes > 1)
order by d.last_analyzed
/

select * from dba_tables

select
statistic,
value
from
v$pq_sysstat
where
statistic = ‘Servers Busy’;

select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value

select * from v$session
———————————-

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s