开发人员反映查询v$lock特别慢;
手工执行报错
[oracle@testdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 7 16:53:19 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> select count(*) from v$lock ;
select count(*) from v$lock
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
查询temp使用情况发现
TABLESPACE_NAME FREE(G) TOTAL(G) USED(%) FREE(%)
------------------------------ ---------- ---------- ---------- ----------
TEMP 0 1 100 0
SQL> SQL>
TOTAL(GB) FREE(GB) TEMP_FILE USED(GB) TOTAL(GB) FREE(GB) USED(GB)
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
1 1 /mnt/oradata/xnjc/temp01.dbf 0 1 1 0
temp全部被使用,temp表空间大小只要1G
SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SID USERNAME TABLESPACE HASH_VALUE SEGTYPE CONTENTS BLOCKS
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
8 SEC TEMP 0/2866845384 LOB_DATA TEMPORARY 128
sec用户正在使用temp
temp表空间分配太小,先解决这个分配问题
SQL> alter tablespace temp add tempfile '/mnt/oradata/xnjc/temp02.dbf' size 128m autoextend on next 100m
/
Tablespace altered.
下面验证v$lock访问慢问题
SQL> select count(*) from v$lock ;
COUNT(*)
----------
111
访问要30多秒时间
查看下执行计划
SQL> SELECT * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 1 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
|* 2 | HASH JOIN | | 1 | 50 | 1 (100)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 100 | 3800 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 100 | 1900 | 0 (0)| 00:00:01 |
| 6 | FIXED TABLE FULL | X$KSQRS | 100 | 1900 | 0 (0)| 00:00:01 |
| 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | FILTER | | | | | |
| 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 |
| 11 | UNION-ALL | | | | | |
|* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 ||* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 ||* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------2 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("R
ADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
9 - filter(USERENV('INSTANCE') IS NOT NULL)PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
13 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
14 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
15 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
16 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
17 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) ANDPLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
18 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
20 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
21 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
"INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)56 rows selected.
id=3走了笛卡尔执行计划 估算出问题,X$KSUSE 不会只有一条数据,实际查询
SQL> select count(*) from X$KSUSE ;
COUNT(*)
----------
2272
SQL> exec dbms_stats.gather_fixed_objects_stats ; 收集统计信息
重新执行
select count(*) from v$lock; 秒出问题解决
查看调整后的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2965011029
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 36 | 9 (100)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
| 2 | HASH JOIN | | 13905 | 488K| 9 (100)| 00:00:01 |
| 3 | HASH JOIN | | 136 | 3264 | 7 (100)| 00:00:01 |
| 4 | VIEW | GV$_LOCK | 136 | 1632 | 6 (100)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | FILTER | | | | | |
| 7 | VIEW | GV$_LOCK1 | 128 | 1536 | 4 (100)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 16 | 0 (0)| 00:00:01 |
| 10 | FIXED TABLE FULL| X$KSQEQ | 127 | 2159 | 4 (100)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KTADM | 1 | 16 | 1 (100)| 00:00:01 |
| 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 14 | 0 (0)| 00:00:01 |
| 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 14 | 0 (0)| 00:00:01 |
| 14 | FIXED TABLE FULL | X$KTATL | 1 | 26 | 0 (0)| 00:00:01 |
| 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 14 | 0 (0)| 00:00:01 |
| 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 16 | 0 (0)| 00:00:01 |
| 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 14 | 0 (0)| 00:00:01 |
| 18 | FIXED TABLE FULL | X$KTCXB | 1 | 16 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSUSE | 2272 | 27264 | 0 (0)| 00:00:01 |
| 20 | FIXED TABLE FULL | X$KSQRS | 10224 | 119K| 1 (100)| 00:00:01 |
--------------------------------------------------------------------------------笛卡尔执行计划消除------
总结:
GATHER_FIXED_OBJECTS_STATS此过程收集统计所有的动态性能表
必须有SYSDBA或具有ANALYZE
ANY
DICTIONARY
权限才能执行此过程