Oracle-动态性能视图解读开发者社区

Oracle-动态性能视图解读

SGA----V$PARAMETER     V$SGA   V$OPTION V$PROCESS
       V$SESSION        V$VERSION V$INSTANCE

控制文件-------V$THREAD V$CONTROLFILE V$DATABASE
            V$DATAFILE   V$DATAFILE_HEADER   V$LOGFILE

where inst_id = USERENV('Instance')

select view_definition from v_$fixed_view_definition where
view_name='V$FIXED_TABLE';

SQL> select count(*) from v$fixed_table where name like 'V$%';
  COUNT(*)
----------
       552

SQL>SELECT * FROM GV$LOCK WHERE INST_ID = 1;

SQL>  select count(*) from v$fixed_table where name like 'GV$%';
  COUNT(*)
----------
SQL> select count(*) from v$fixed_table where name like 'V$%';
  COUNT(*)
----------
       552

SQL> select  a.VIEW_DEFINITION from v$fixed_view_definition a  where a.VIEW_NAME = 'V$FIXED_TABLE';
VIEW_DEFINITION
-------------------------------------------------------------
select  NAME , OBJECT_ID , TYPE , TABLE_NUM from GV$FIXED_TABLE where inst_id = USERENV('Instance')

SQL> select view_definition from v$fixed_view_definition where view_name='GV$FIXED_TABLE';
VIEW_DEFINITION
------------------------------------------------------------
select inst_id,kqftanam, kqftaobj, 'TABLE', indx from x$kqfta union all select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537 from x$kqfvi union all select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537 from x$kqfdt

create or replace view v_$fixed_table as select * from v$fixed_table;
create or replace public synonym v$fixed_table for v_$fixed_table;
create or replace view gv_$fixed_table as select * from gv$fixed_table;
create or replace public synonym gv$fixed_table for gv_$fixed_table;

SQL> select object_type from all_objects where object_name=upper('v$datafile');
OBJECT_TYPE
-------------------
SYNONYM

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as cc@cc
SQL> select * from v$fixed_table ;
NAME                     OBJECT_ID TYPE  TABLE_NUM
------------------------------ ---------- ----- ----------
X$KQFTA              4294950912 TABLE          0
X$KQFVI               4294950913 TABLE          1
X$KQFVT              4294951149 TABLE          2
.......

SQL> col host_name format a20
SQL> select instance_name,host_name,status from   V$instance;
INSTANCE_NAME HOST_NAME STATUS
---------------- -------------------- ------------
cc             entel2          OPEN
SQL>

SQL> col name  format a25;
SQL> col value format a25;
SQL> select  * from V$sga ;
NAME                                   VALUE
------------------------- -------------------------
Fixed Size                          2265384
Variable Size                  1258295000
Database Buffers                5251268608
Redo Buffers                    18993152

select * from v$sgainfo ;

select a.NAME ,a. VALUE ,a.DESCRIPTION  from v$parameter  a where a.name='db_name';

SQL> select * from v$version ;
BANNER
------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$option;
PARAMETER                            VALUE
-------------------- ------------------------------
Partitioning                         TRUE
Objects                              TRUE
Real Application Clusters            FALSE
Advanced replication                 TRUE
Bit-mapped indexes                   TRUE
Connection multiplexing              TRUE
.........

SQL> select sid,serial#,username from V$session where username is not null;
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
         1       3293 DRMR80
         5      10127 DRMR80
         7      44691 CC
         8       8603 CC
................

select a.terminal,a.spid,a.pga_alloc_mem
  from V$process a ,V$session b      where a.addr=b.paddr and b.username='CC';

select name,description from V$bgprocess
where paddr<>'00';

SQL> select name, log_mode,created from V$database;
NAME LOG_MODE CREATED
--------- ------------ -----------
CC     NOARCHIVELOG 2016-06-27

SQL> select * from v$controlfile;

select file#,name,bytes from v$datafile ;

select * from v$dbfile ;

SQL> col member format a50
SQL>  select group#,member from V$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         1 /oradata/cc/redo01.log
         2 /oradata/cc/redo02.log
         3 /oradata/cc/redo03.log

select group#,thread#,sequence#,bytes,members,status   from V$log;

select thread#,status,groups,instance,sequence# from v$thread ;

 select a.username,a.machine,b.lmode,b.request
      from V$session a,V$lock b
     where a.sid=b.sid and a.type='USER';

select a.oracle_username,b.owner||'.'||b.object_name object
from V$locked_object a,dba_objects b
where a.object_id=b.object_id;

select a.name,b.xacts from V$rollname a,V$rollstat b
      where a.usn=b.usn;

 select * from v$tablespace a ;

v$session + v$session_wait (在10g里功能被整合,凑合算1个吧.)
v$process  --显示与ORACLE相关的所有进程信息（包括后台进程和服务器进程）
v$sql
v$sqltext
v$bh (更宁愿是x$bh)
v$lock
v$latch_children
v$sysstat
v$system_event

v$sysstat ,--整个系统的统计信息
v$system_event , --This view contains information on total waits for an event.每个事件的等待情况
v$parameter

v$process , v$session , 
v$session_wait ,--This view lists the resources or events for which active sessions are waiting. 当前活动的会话正在等待的事件情况
v$session_event , --This view lists information on waits for an event by a session. 每个会话对每个事件的等待情况
v$sesstat –-每个会话的统计信息

v$sql , v$sqlarea , v$SQL_PLAN , V$SQL_PLAN_STATISTICS, v$sqltext_with_newlines

v$latch , v$latch_children , v$latch_holder , v$lock ,V$ENQUEUE_STAT ,V$ENQUEUE_LOCK

v$segstat , v$filestat , v$tempstat ,v$datafile , v$tempfile

v$Librarycache , v$rowcache , x$ksmsp

v$db_cache_advice , v$PGA_TARGET_ADVICE, v$SHARED_POOL_ADVICE

v$system_event, v$session_wait ,v$session_event

v$waitstat-- This view lists block contention statistics.块竞争引起的等待统计

v$bh

SQL>select * from v$session;

SELECT a.sql_text,
b.status,
b.last_call_et,
b.event
FROM v$sql a, v$session b
WHERE a.sql_id = b.sql_id AND b.sid = 771;

SELECT sql_text
  FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
ORDER BY piece ASC

select * from v$sql ;
SELECT sql_text,
cpu_time / (1000 * 1000) t_cpu,
TRUNC (elapsed_time / (1000 * 1000)) t_elap,
(cpu_time / elapsed_time / (1000 * 1000)) * 100 pct
FROM v$sql
WHERE sql_text LIKE 'SELECT OFFER_GROUP_ID_SEQ.NEXTVAL FROM DUA%';

SQL>alter system flush shared_pool;

SQL> show parameter cursor_sharing
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
cursor_sharing                       string      EXACT
SQL>

select parsing_user_id   puid,
       parsing_schema_id psid,
       sql_text,
       sql_id,
       child_address
  from v$sql
 where sql_text like 'SELECT REF_VALUE_ID,REF_VALUE_TYPE%';

select * from v$sql_shared_cursor where sql_id='fwfm66c9cs1q8'

SELECT a.sql_text,
b.sid,
b.status,
b.last_call_et,
b.event
FROM v$sql a, v$session b
WHERE a.sql_id = b.sql_id AND b.STATUS='ACTIVE';

SELECT a.sql_text,
b.sid,
b.status,
b.last_call_et,
b.event
FROM v$sql a, v$session b
WHERE a.sql_id = b.sql_id AND b.STATUS='INACTIVE';

SELECT a.sid, b.name, a.VALUE
FROM v$sesstat a, v$statname b
WHERE a.sid = 837 AND a.statistic# = b.statistic#
AND b.name IN
('consistent gets',
'physical reads',
'parse count (total)',
'parse count (hard)');

SELECT event,
p1text,
p2text,
p3text,
wait_time,
seconds_in_wait,
state
FROM v$session_wait
WHERE sid = 837;

SQL> alter session set cursor_sharing=similar;
会话已更改。
SQL> select * from all_objects set_similar where object_id=10;
SQL> select * from all_objects set_similar where object_id=20;
SQL> select sql_text from v$sql where sql_text like '%set_similar%';
SQL_TEXT
------------------------------------------------------------------------------
select * from all_objects set_similar where object_id=:"SYS_B_0"
select * from all_objects set_similar where object_id=:"SYS_B_0"

SQL> alter system flush shared_pool;

SQL> alter session set cursor_sharing=force;
SQL> select * from all_objects set_similar where object_id =2;
SQL> select * from all_objects set_similar where object_id =1;
SQL> select sql_text from v$sql where sql_text like '%set_similar%';
SQL_TEXT
-------------------------------------------------------------
select * from all_objects set_similar where object_id =:"SYS_B_0"

SQL> alter system flush shared_pool;

Oracle-动态性能视图解读

Oracle-动态性能视图解读

系列相关

动态性能视图概述

数据库不同的状态下的动态性能视图

NOMOUNT

MOUNT

OPEN

V$, V_$, GV$, X$

X$ 表

GV$ 和 V$ 同义词

V$ 视图

GV$ 视图

V$FIXED_VIEW_DEFINITION 视图

GV_$, V_$ 视图

常用动态性能视图

1、 V$FIXED_TABLE

2、 V$INSTANCE

3、V$SGA

4、 V$SGAINFO

5、 V$PARAMETER

6． V$VERSION

7． V$OPTION

8． V$SESSION

9． V$PROCESS

10． V$BGPROCESS

11． V$DATABASE

V$CONTROLFILE

13． V$DATAFILE

14． V$DBFILE

V$LOGFILE

16． V$LOG

17． V$THREAD

18． V$LOCK

19． V$LOCKED_OBJECT

20． V$ROLLNAME V$ROLLSTAT

21． V$TABLESPACE

oracle最重要的9个动态性能视图

按组分的几组重要的性能视图

1.System 的 over view

2.某个session 的当前情况

3.SQL 的情况

4. Latch / lock /ENQUEUE(闩,锁相关)

5. IO 方面的

6.shared pool / Library cache

7.几个advice也不错

8.等待事件:

9.块竞争:

10.热块:

Oracle 性能相关的几个视图和参数

V$SQL

用 V$SQL 查看 SQL 内容:

用 V$SQL 查看 SQL 执行和等待时间

共享池中的 SQL

V$SQL_SHARED_CURSOR

V$SESSION

V$SESSTAT

V$SESSION_WAIT

性能参数

CURSOR_SHARING

cursor_sharing=exact（默认值）

cursor_sharing=similar

cursor_sharing=force