Data access tuning - 3. page

oracle用户权限检查
1. 系统权限记录在 dba_sys_privs 视图中：
select grantee, privilege, admin_option
from dba_sys_privs
where grantee = ‘USERNAME’ ;
2. 角色权限记录在 dba_role_privs 视图中：
select grantee, granted_role, admin_option
from dba_role_privs
where grantee = ‘USERNAME’ ;
3. 对象权限记录在 dba_tab_privs 视图中，虽然视图名看起来像是表权限，但是实际包含了包，存储过程等等对象级的权限：
select grantee, owner || ‘.’ || table_name obj_name, privilege, grantable
from dba_tab_privs
where grantee = ‘USERNAME’;
4. 其他还有部分权限，如表空间的quota权限，记录在 dba_ts_quota中：
select username,
tablespace_name,
bytes / 1024 / 1024 “used space (m)”,
decode(decode(max_bytes, -1, 0, max_bytes) / 1024 / 1024,
0,
‘unlimited’,
max_bytes / 1024 / 1024) “max space(m)”,
blocks used_blocks,
max_blocks
from dba_ts_quotas
where username = ‘USERNAME’;
select * from session_privs; 当前用户拥有的权限
SELECT * FROM DBA_SYS_PRIVS; 查询每个用户的权限
锁的检查
select b.owner,
b.object_name,
l.SESSION_ID,
DECODE(L.LOCKED_MODE,
0,
‘None’,
1,
‘Null’,
2,
‘Row-S (SS)’,
3,
‘Row-X (SX)’,
4,
‘Share’,
5,
‘S/Row-X (SSX)’,
6,
‘Exclusive’,
TO_CHAR(L.LOCKED_MODE)) MODE_HELD,
s.USERNAME
from dba_objects b, v$locked_object l, V$SESSION S
where b.object_id = l.object_id
and l.SESSION_ID = s.SID
查看一个长查询的进度
SELECT *
FROM ( select username,opname,sid,serial#,
context,b.sql_text,sofar,totalwork,
round(sofar/totalwork*100,2) “% Complete”,
elapsed_seconds
from v$session_longops , v$sql b
where sql_hash_value=b.hash_value
and sql_address = address
and totalwork <> 0
)
WHERE “% Complete” <> 100 ;
查看ACTIVE SESSION的等待事件
select a.event,
sum(decode(wait_Time, 0, 0, 1)) “Prev”,
sum(decode(wait_Time, 0, 1, 0)) “Curr”,
count(*) “Tot”
from v$session_wait a, v$session b
where a.sid = b.sid
and b.status = ‘ACTIVE’
group by a.event
— having count(*) > 10
order by 4;

在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
通过DBMS_METADATA包得到对象的DLL语句
a.获取单个的建表和建索引的语法,其他对象类似：
select dbms_metadata.get_ddl(‘TABLE’,’TABLE_NAME’,’TABLE_OWNER’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’INDEX_NAME’,’INDEX_OWNER’) from dual;

b.获取一个SCHEMA下的所有建表的语法，以scott为例，其他对象类似：
SELECT DBMS_METADATA.GET_DDL(‘TABLE’, u.table_name, u.owner) || ‘;’
FROM DBA_TABLES u
where owner = ‘SCOTT’ ;
查看回滚段名称及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

查看数据库的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = ‘Oracle’;

查看数据库的创建日期和归档方式
Select Created, Log_Mode From V$Database;

查看还没提交的事务
select * from v$locked_object;
select * from v$transaction;
查找object为哪些进程
select p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 – command),
1,
to_char(command),
‘Action Code #’ || to_char(command)) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr
and s.type = ‘USER’
and a.sid = s.sid
and a.object = ‘EMP’
order by s.username, s.osuser
怎样计算一个表占用的空间的大小？
select owner,
table_name,
NUM_ROWS,
BLOCKS * AAA / 1024 / 1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name = ‘XXX’;

注意:执行以上语句前要先对表做统计分析

select sum(a.bytes) / 1024 * 1024 “size”
from dba_extents a
where a.segment_name = ‘GOV_FDDBR’
select a.bytes / 1024 * 1024 “size”, (a.blocks * 8192) / 1024 * 1024 “da”
from dba_segments a
where a.segment_name = ‘GOV_FDDBR’
如何查看最大会话数
SELECT * FROM V$PARAMETER WHERE NAME LIKE ‘proc%’;
SQL>
SQL> show parameter processes

NAME TYPE VALUE
———————————— ——- ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 1
processes integer 200

这里为200个用户。

select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数
如何在Oracle服务器上通过SQLPLUS查看本机IP地址

select sys_context(‘userenv’,’ip_address’) from dual;

如何将表、索引移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
如何才能得知系统当前的SCN号
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
如何在字符串里加回车
select ‘Welcome to visit’||chr(10)||’www.CSDN.NET’ from dual；
如何将小表放入keep池中
alter table xxx storage(buffer_pool keep);
如何查询做比较大的排序的进程？
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
¡¡ a.username,
a.osuser,
a.status ¡¡
FROM v$session a, v$sort_usage b ¡¡
WHERE a.saddr = b.session_addr ¡¡
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

如何删除重复记录？
DELETE FROM TABLE_NAME
WHERE ROWID != (SELECT MAX(ROWID)
FROM TABLE_NAME D
WHERE TABLE_NAME.COL1 = D.COL1
AND TABLE_NAME.COL2 = D.COL2)；
如何回滚段的争用情况
select name, waits, gets, waits / gets “Ratio” 　
from v$rollstat C, v$rollname D 　
where C.usn = D.usn;
如何监控表空间的 I/O 比例
select B.tablespace_name name,
B.file_name “file”,
A.phyrds pyr,
　 A.phyblkrd pbr,
A.phywrts pyw,
A.phyblkwrt pbw 　
from v$filestat A, dba_data_files B 　
where A.file# = B.file_id 　
order by B.tablespace_name;

如何监控文件系统的 I/O 比例
Select substr(C.file#, 1, 2) “#”,
substr(C.name, 1, 30) “Name”,
C.status,
C.bytes,
D.phyrds,
D.phywrts 　
from v$datafile C, v$filestat D 　
where C.file# = D.file#;
如何在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name 　
from user_ind_columns, user_indexes 　
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name 　
order by user_indexes.table_type,
user_indexes.table_name,
　user_indexes.index_name,
column_position;
如何监控 SGA 中字典缓冲区的命中率
select parameter,
gets,
Getmisses,
getmisses / (gets + getmisses) * 100 “miss ratio”,
　(1 – (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 “Hit ratio” 　
from v$rowcache 　
where gets + getmisses <> 0 　
group by parameter, gets, getmisses;
如何监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) “Total Pins”,
sum(reloads) “Total Reloads”,
　sum(reloads) / sum(pins) * 100 libcache 　
from v$librarycache;

select sum(pinhits – reloads) / sum(pins) “hit radio”,
sum(reloads) / sum(pins) ¡¡¡¡”reload percent” ¡¡
from v$librarycache;
监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name,
gets,
misses,
immediate_gets,
immediate_misses,
Decode(gets, 0, 0, misses / gets * 100) ratio1,
Decode(immediate_gets+immediate_misses,0,0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 　
FROM v$latch
WHERE name IN (‘redo allocation’, ‘redo copy’);

监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN (‘sorts (memory)’, ‘sorts(disk)’);
如何监控当前数据库谁在运行什幺SQL语句？
SELECT osuser, username, sql_text
from v$session a, v$sqltext b 　
where a.sql_address = b.address
order by address, piece;
如何查看碎片程度高的表?
SELECT segment_name table_name, COUNT(*) extents 　
FROM dba_segments
WHERE owner NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY segment_name 　
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);
如何知道表在表空间中的存储情况?
select segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = ‘&tablespace_name’
and segment_type = ‘TABLE’
group by tablespace_name, segment_name;
如何知道索引在表空间中的存储情况?
select segment_name, count(*)
from dba_extents
where segment_type = ‘INDEX’
and owner = ‘&owner’ 　
group by segment_name;

如何知道使用CPU多的用户session?
Select a.sid,spid,status,
substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
　 from v$session a,v$process b,v$sesstat c
　 where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr
order by value desc;
物理读和内存读较高SQL
SELECT t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS / t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS / t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME / t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME / t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS / t.EXECUTIONS > 500 OR
t.BUFFER_GETS / t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;
查看低效率的SQL语句
SELECT EXECUTIONS,
DISK_READS,
BUFFER_GETS,
ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2) Hit_radio,
ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM　 V$SQLAREA 　 　
WHERE　EXECUTIONS > 0 　　
AND　　BUFFER_GETS > 0 　　
AND (BUFFER_GETS – DISK_READS) / BUFFER_GETS < 0.8 　　 ORDER BY EXECUTIONS DESC 或者 select executions exec, loads loads, parse_calls parse, disk_reads reads, buffer_gets gets, rows_processed rows_proc, sorts sorts, sql_text from v$sqlarea order by &sortkey desc; 找出oracle中从没有使用的索引 set pages 999; set heading off; spool run_monitor.sql select 'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes where owner not in ('SYS','SYSTEM','PERFSTAT') spool off; @run_monitor 等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。 Select index_name,table_name,mon,used from v$object_usage; 在V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。不幸的是，它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。 自己编写的脚本 select c.SID,c.STATUS,s.NAME,b.VALUE from v$statname s, v$sesstat b, v$session c where s.STATISTIC# = b.STATISTIC# and b.SID = c.SID and b.sid=&sid and s.NAME in ('consistent gets','physical reads','parse count (total)','parse count (hard)') V$sql:查询一条sql执行时间及消耗的cpu时间，被执行及被分析的次数 Parse_calls, Executions Cpu_time, Elapsed_time Alter system flush shared_pool 清空共享池