分类 ORACLE 下的文章

V$SESSION_LONGOPS

作者: wukaiqiang
时间: 2022-02-16
分类: ORACLE
评论

V$SESSION_LONGOPS视图记录了执行时间长于6秒的某个操作(这些操作可能是备份，恢复，Hash Join,Sort ,Nested loop，Table Scan, Index Scan 等等)。

- 阅读剩余部分 -

解锁

作者: wukaiqiang
时间: 2022-02-11
分类: ORACLE
评论

数据库锁表分析

1、会话1 update Scott.emp set deptno=10;
2、会话2 update scott.emp set deptno=30;
查询是否存在锁定

select s1.INST_ID,

   s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
   ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
   s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where l1.INST_ID = s1.INST_ID
and s1.INST_ID = s2.INST_ID
and s2.INST_ID = l2.INST_ID
and s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;

19:22:53 SQL> select s1.INST_ID,
19:24:50 2 s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
19:24:50 3 ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
19:24:50 4 s2.sid || ' ) ' AS blocking_status
19:24:50 5 from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
19:24:50 6 where l1.INST_ID = s1.INST_ID
19:24:50 7 and s1.INST_ID = s2.INST_ID
19:24:50 8 and s2.INST_ID = l2.INST_ID
19:24:50 9 and s1.sid = l1.sid
19:24:50 10 and s2.sid = l2.sid
19:24:50 11 and l1.BLOCK = 1
19:24:50 12 and l2.request > 0
19:24:50 13 and l1.id1 = l2.id1
19:24:50 14 and l2.id2 = l2.id2;

INST_ID

BLOCKING_STATUS

SYS@localhost.localdomain (SID=16 ) is blocking SYS@WORKGROUP\LAPTOP-5RF2B7J0 ( SID=15 )
查询update后未提交的会话

SELECT

A.SID,
A.SERIAL#,
A.USERNAME,
A.EVENT,
A.WAIT_CLASS,
A.SECONDS_IN_WAIT,
A.PREV_EXEC_START,
B.LOCKED_MODE,
C.OWNER,
C.OBJECT_NAME,
C.OBJECT_TYPE

FROM

V$SESSION A
INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID

WHERE

A.WAIT_CLASS = 'Idle'
AND A.SECONDS_IN_WAIT >10

/SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒/

19:27:05 SQL> SELECT
19:27:07 2 A.SID,
19:27:07 3 A.SERIAL#,
19:27:07 4 A.USERNAME,
19:27:07 5 A.EVENT,
19:27:07 6 A.WAIT_CLASS,
19:27:07 7 A.SECONDS_IN_WAIT,
19:27:07 8 A.PREV_EXEC_START,
19:27:07 9 B.LOCKED_MODE,
19:27:07 10 C.OWNER,
19:27:07 11 C.OBJECT_NAME,
19:27:07 12 C.OBJECT_TYPE
19:27:07 13 FROM
19:27:07 14 V$SESSION A
19:27:07 15 INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
19:27:08 16 INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID
19:27:08 17 WHERE
19:27:08 18 A.WAIT_CLASS = 'Idle'
19:27:08 19 AND A.SECONDS_IN_WAIT >10;

SID SERIAL# USERNAME EVENT WAIT_CLASS SECONDS_IN_WAIT PREV_EXEC_START LOCKED_MODE OWNER OBJECT_NAME OBJECT_TYPE

16         61 SYS                  SQL*Net message from client                   Idle                                                                          38 2021-12-15 19:26:31           3 SCOTT     DEPT                           TABLE
16         61 SYS                  SQL*Net message from client                   Idle                                                                          38 2021-12-15 19:26:31           3 SCOTT     EMP                            TABLE

Elapsed: 00:00:00.06

----------查看被锁对象
set lin 400 pages 49999
col USERNAME for a15
col owner for a15
col OBJECT_NAME for a35
col PROGRAM for a35
col PROCESS for a15
select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
from gv$locked_object a, dba_objects b,gv$session c
where a.object_id=b.object_id and c.sid=a.session_id;

19:32:51 SQL> set lin 400 pages 49999
19:33:07 SQL> col USERNAME for a15
19:33:07 SQL> col owner for a15
19:33:07 SQL> col OBJECT_NAME for a35
19:33:07 SQL> col PROGRAM for a35
19:33:07 SQL> col PROCESS for a15
19:33:07 SQL> select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
19:33:07 2 from gv$locked_object a, dba_objects b,gv$session c
19:33:07 3 where a.object_id=b.object_id and c.sid=a.session_id;

LOCKED_MODE INST_ID OWNER OBJECT_NAME OBJECT_ID SESSION_ID SERIAL# USERNAME SQL_ID PROCESS PROGRAM

      3          1 SCOTT           DEPT                                     87106         15          9 SYS             022scd5v03tnp      23708:31696     navicat.exe
      3          1 SCOTT           DEPT                                     87106         16         61 SYS                                19093           sqlplus@localhost.localdomain (TNS
                                                                                                                                                           V1-V3)

alter system kill session 'sid,serial#'

---------------------创建触发器（若审计没开，可创建触发器追踪用户被锁原因）
create table logon_error(AUTHENTICATED_IDENTITY varchar2(200),HOST varchar2(200),ip_address varchar2(200),"sysdate" date);

CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN

INSERT INTO logon_error VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'),sys_context('userenv', 'ip_address'),SYSDATE);
COMMIT;

END IF;
END;
/
查询触发器监控结果：
set pages 130;
set lines 1300;
col AUTHENTICATED_IDENTITY for a20;
col HOST for a20;
col IP_ADDRESS for a20;
select * from logon_error;

转载行迁移和行连接

作者: wukaiqiang
时间: 2022-02-10
分类: ORACLE
评论

行迁移和行链接都会导致Oracle性能下降，这篇文章将介绍什么是行迁移和行链接，它们带来的问题，如何来判断它们，并提供了解决它们的办法。

什么是行迁移和行链接
行迁移
Oracle的数据块会保留部分空间供以后更新使用，通常的数据块结构如下：

PCTFREE定义一个块保留的空间百分比，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被update（具体看下面的PCTFREE介绍）。
当一条记录被更新时，数据库引擎首先会尝试在它保存的数据块中寻找足够的空闲空间，如果没有足够的空闲空间可用，这条记录将被拆分为两个部分，第一个部分进包括指向第二个部分的rowid，该部分任然保留在原来的数据块中，第二个部分包含所有的具体数据，将保存到另外一个新的数据块中，这个就成为行迁移。

为什么不将整行都放到新的数据块中？
原因是这样会导致该行数据rowid发生变化，而rowid被存储在索引中，也有可能被客户端临时保存在内存中，rowid的变化可能导致查询错误。

PCTFREE介绍

PCTFREE可以在建表的时候指定：

create table test1 pctfree 20 as select rownum as id from all_objects where rownum <= 1000;
这里指定表test1的PCTFREE为20，通过下面的方式可以查看该值：

select table_name, pct_free from user_tables where table_name = 'TEST1';

TABLE_NAME PCT_FREE

TEST1 20
在创建了表格后你可以通过alter table来调整PCTFREE值：

alter table test1 pctfree 15;
行链接
行链接和行迁移不同，行链接是当一条记录太大，在一个数据块中无法存入，这时会被拆分为2个或以上的部分，存储在多个块中，这多个块之间会构造一个链，如下：

行迁移是由于更新导致的，而行链接的原因则可能为：
1）直接插入大的记录；
2）更新记录导致记录大于一个数据块，在这时，这样记录可能会同时变为行迁移和行链接。

行迁移和行链接带来的问题
行迁移不会影响全扫描（全扫描更多介绍请看“Oracle性能分析4：数据访问方法之全扫描”），因为第一个部分不包含数据，会被直接跳过；但对于通过rowid进行访问（索引扫描或者直接使用rowid查询），则开销会翻倍，主要由于一次读取需要访问两个块。
行链接则和数据访问方式无关，每次访问到第一个记录片段之后，都需要通过rowid去访问其他的记录片段。
行迁移和行链接也会影响行级锁，因为每个记录片段都需要持有锁，锁的开销和记录片段的个数的增长成正比。

确定行迁移和行链接
确定行迁移和行链接可以使用下面几种方法。

查看v$sysstat和v$sesstat视图
该视图中统计项table fetch continued row可以确认是否出现了行迁移和行链接。

select name,value from v$sysstat where name = 'table fetch continued row';

NAME VALUE

table fetch continued row 27455
但这个值只能提示你数据库的某个地方存在行迁移或者行链接，如果要评估导致的影响，你需要和table scan rows gotten和table fetch by rowid的统计信息对比。

查看具体表的行迁移和行链接信息
对指定表执行：

analyze table <table_name> list chained rows
如果发现了行链接或者行迁移的记录，它们的rowid就会被记录到CHAINED_ROWS这张表中，该表可以使用$ORACLE_HOME/rdbms/admin目录下的UTLCHAIN.SQL或UTLCHN1.SQL脚本创建，建表语句如下：

create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid urowid,
analyze_timestamp date
);
下面的存储过程将可以用来分析符合条件的所有表：

begin
for obj in (select owner, object_name

            from dba_objects
           where object_type = 'TABLE'
             and <other conditions>) loop
dbms_output.put_line(obj.owner || '.' || obj.object_name);
execute immediate 'analyze table ' || obj.owner || '.' ||
                  obj.object_name || ' list chained rows';

end loop;
end;
然后通过查看CHAINED_ROWS表中的数据发现哪些表中存在行迁移和行链接：

select table_name,head_rowid from CHAINED_ROWS

TABLE_NAME HEAD_ROWID

IND$ AAAAACAABAAAAAdAAA
IND$ AAAAACAABAAAAAdAAG
IND$ AAAAACAABAAAAAmAAI
IND$ AAAAACAABAAAGpRAAH
IND$ AAAAACAABAAAN0lAAD
IND$ AAAAACAABAAAN0oAAE
......
在这里我们可以看到那些表的那些行存在行迁移和行链接，但并不知道具体是行迁移和行链接，我们可以通过计算记录的长度，再将该长度和块大小进行比较，从而识别出他们具体是行迁移还是行链接。
计算一行的长度使用下面的语句：

select vsize() + vsize() + ... + vsize() from

where rowid = '';
数据库的块大小在参数db_block_size中保存，参看参数的方法详见“Oracle参数查看方法小结”。

表统计信息中查看行迁移和行链接
表DBA_tables的chain_cnt字段表示行迁移和行链接的数量信息，但dbms_stats包不会收集这个统计信息，该值始终为0。

select table_name,chain_cnt from dba_tables where table_name = 'IND$';

TABLE_NAME CHAIN_CNT

IND$ 0
通过下面的SQL语句可以收集该信息：

analyze table ind$ compute statistics;
然后再次查看：

select table_name,chain_cnt from dba_tables where table_name = 'IND$';

TABLE_NAME CHAIN_CNT

IND$ 13
但该方法会导致被分析表的所有对象的统计信息都被覆盖，因此，在实践中不推荐使用。

解决办法
行迁移和行链接的解决办法不同，因此在处理前一定要区分清楚是行迁移还是行链接。

行迁移
首先我们应该避免行迁移，方法是在原块中保留足够的空闲空间，即调整PCTFREE参数值，值的大小需要评估记录扩展的平均大小。
当出现了行迁移后，则只能通过移动数据来解决，具体的方式有：
1）通过导出、导入或者ALTER TABLE MOVE对表进行重整；
2）将迁移的数据复制到临时表中，在原表上删除再重新插入这些数据。

行链接
处理行链接只能增加数据块的大小，但在一些情况下，可以通过将常用字段放在表的前面，不常访问的字段放在表的末尾来提高某些查询的效率（由于Oracle查询时只会取查询相关的字段）。
————————————————
版权声明：本文为CSDN博主「tomato__」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/tomato__/article/details/40146573

创建物化视图提示：ora 12018

作者: wukaiqiang
时间: 2022-01-16
分类: ORACLE
评论

创建物化视图，提示ora 12018 ora 表或视图不存在

查看物化视图的log表
select * from dba_view_logs
授予远程用户访问物化视图log的权限

grant seelct on mlog$表名

关于oracle的值得推荐几本书籍

作者: wukaiqiang
时间: 2022-01-16
分类: ORACLE
评论

1、《基于Oracle的SQL优化》
链接：https://pan.baidu.com/s/1l-7MjKef4vKbBrs5gATdWg
提取码：wkqq
2、《收获不止oracle》
链接：https://pan.baidu.com/s/1h_CWm0gQim9IvbKWJevULQ
提取码：wkqq
3、《rac集群管理》
链接：https://pan.baidu.com/s/153973bd1MnzMlkcqBop2Zw
提取码：wkqq
4、《oracle管理》
链接：https://pan.baidu.com/s/1JMxJnhPHt-vcruferwzZpg
提取码：wkqq
5、《收获不止sql优化》
链接：https://pan.baidu.com/s/1Gwjtvwe7rZZBfhC1Qum_Ig
提取码：wkqq

V$SESSION_LONGOPS

解锁

INST_ID

BLOCKING_STATUS

转载行迁移和行连接

TABLE_NAME PCT_FREE

NAME VALUE

TABLE_NAME HEAD_ROWID

TABLE_NAME CHAIN_CNT

TABLE_NAME CHAIN_CNT

创建物化视图提示：ora 12018

关于oracle的值得推荐几本书籍

最新文章

分类

归档

其它