优化大神看下 recursive calls问题吧，谢谢

我来答

2023-02-20

空表、小表查询非常慢，现2条数据查询超过10秒，且recursive calls 越执行越多

10:48:00 SQL> set autot trace
10:48:12 SQL> select * from SSJ_LHCC.F_ZJXZPPBBAK;

2 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 218475219

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 154 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| F_ZJXZPPBBAK | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
5120 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1062 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

09:42:15 SQL> select * from table(dbms_xplan.display_cursor('66pbp77fr414j',null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 66pbp77fr414j, child number 0
-------------------------------------
select * from SSJ_LHCC.F_ZJXZPPBBAK

Plan hash value: 218475219

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| F_ZJXZPPBBAK | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$1 / F_ZJXZPPBBAK@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "F_ZJXZPPBBAK"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
2 - (#keys=0) "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
3 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
4 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]

SQL_ID 66pbp77fr414j, child number 1
-------------------------------------
select * from SSJ_LHCC.F_ZJXZPPBBAK

Plan hash value: 218475219

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| F_ZJXZPPBBAK | 2 | 154 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$1 / F_ZJXZPPBBAK@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "F_ZJXZPPBBAK"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access(:Z>=:Z AND :Z<=:Z)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
2 - (#keys=0) "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
3 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]
4 - "F_ZJXZPPBBAK"."RWID"[VARCHAR2,50], "F_ZJXZPPBBAK"."NBXH"[VARCHAR2,50],
"F_ZJXZPPBBAK"."JCJG"[VARCHAR2,10], "F_ZJXZPPBBAK"."ZJXZID"[VARCHAR2,25],
"F_ZJXZPPBBAK"."OPEN_TIME"[TIMESTAMP,11], "F_ZJXZPPBBAK"."BJW"[VARCHAR2,1]

114 rows selected.

我来答

添加附件

问题补充

3条回答

默认

最新

刘贵宾

你的shared pool多大啊？当Shared Pool过小，data dictionary cache 也会相应的过小，没有足够的空间存储ORACLE的系统数据字典信息时，会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。

有用 2

打赏 0

马老师r

--表结构

11:23:56 SQL> desc SSJ_LHCC.F_ZJXZPPBBAK
Name Null? Type
-------------------- -----------------
RWID NOT NULL VARCHAR2(50)
NBXH NOT NULL VARCHAR2(50)
JCJG NOT NULL VARCHAR2(10)
ZJXZID NOT NULL VARCHAR2(25)
OPEN_TIME NOT NULL TIMESTAMP(6)
BJW NOT NULL VARCHAR2(1)

--正常执行计划

11:33:14 SQL> select * from SSJ_LHCC.F_ZJXZPPB;

2 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4198924619

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 168 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| F_ZJXZPPB | 2 | 168 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1084 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

有用 0

打赏 0

马老师r

SQL> select
2 component,
CURRENT_SIZE/1024/1024/1024 CUR_GB, 3
4 granule_size/1024/1024/1024 GRANULE_GB,min_size/1024/1024/1024 MIN_GB,max_size/1024/1024/1024 MAX_GB,
5 LAST_OPER_TIME
6 from v$sga_dynamic_components;

COMPONENT CUR_GB GRANULE_GB MIN_GB MAX_GB LAST_OPER_TIME
------------------------------ ---------- ---------- ---------- ---------- -------------------
shared pool 42 .5 37 42 2022-12-31 11:00:17
large pool 2 .5 2 46 2022-12-06 15:51:48
java pool 3.5 .5 3.5 3.5
streams pool 1 .5 1 1
DEFAULT buffer cache 269.5 .5 226 274.5 2022-12-31 11:00:17
KEEP buffer cache 0 .5 0 0
RECYCLE buffer cache 0 .5 0 0
DEFAULT 2K buffer cache 0 .5 0 0
DEFAULT 4K buffer cache 0 .5 0 0
DEFAULT 8K buffer cache 0 .5 0 0
DEFAULT 16K buffer cache 0 .5 0 0
DEFAULT 32K buffer cache 0 .5 0 0
Shared IO Pool 0 .5 0 0
ASM Buffer Cache 0 .5 0 0

14 rows selected.

SQL> !free -h
total used free shared buffers cached
Mem: 1.5T 602G 911G 1.7G 838M 17G
-/+ buffers/cache: 583G 929G
Swap: 99G 0B 99G

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 320G
sga_target big integer 320G
SQL>

这个大小内存都够用

有用 0

打赏 0

回答交流

Markdown

请输入正文

导入word

提交