Oracle 练习用（1）

最新推荐文章于 2024-04-26 15:10:55 发布

原创最新推荐文章于 2024-04-26 15:10:55 发布 · 108 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #Cache #SQL

Oracle 专栏收录该内容

12 篇文章

订阅专栏

本文提供了Oracle数据库管理的一系列SQL查询语句，包括查看日志文件、控制文件、数据文件等基本信息，展示如何查找大型进程及其运行状态，并通过几个关键指标评估数据库缓存效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

desc v$logfile;
select * from v$logfile;

select * from v$controlfile;

select * from v$datafile;


select * from all_tables where OWNER='ADMIN';

-- Find large process
SELECT
L.sid ssid,
substr(OPNAME,1,15) opname,
target,
trunc((sofar/totalwork)*100) pct,
to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0')
Rate,
elapsed_seconds/60 es,
time_remaining/60 tr,
PROGRAM,
MACHINE
FROM v$session_longops L,v$session S
WHERE time_remaining > 0 AND L.SID=S.SID
ORDER BY start_time;

select * from user_views;

select * from user_indexes;
select * from user_ind_columns ;

select * from MBR_SECEDE_MST;

--rename the system auto-index name
alter index SYS_C005162 rename to USER_INFO_PK;

--1 shared pool
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache; --share sql lib cache, If < 90% then must make large the shareed pool

select (sum(gets -getmisses - usage -fixED)) / sum(gets) "Row Cache" from v$rowcache; -- If < 90% then ...

--2 data cache
select name, value from v$sysstat where name in('db block gets','consistent gets','physical reads'); --If (1 - db block gets/(consistent gets + physical reads))<90% then ...

--3 log cache
select name, value from v$sysstat where name in('redo entries','redo log space requests'); --If  requests/entries is not be closed to 0 then ...