session hang，关联子查询无法完成【CPU + Wait for CPU】
有个查询，由3各子查询关联构成，第1、2个子查询只返回一行记录，第3个子查询没有记录，这样的3个子查询关联查询超过了10分钟还未执行完成；收集hanganalyze报告，没有发现问题；
收集了ash报告发现都是CPU + Wait for CPU等待；
相关报告请查看附件。
服务器为虚拟机，配置了16g内存，给oracle12g；
配置了4个cpu；
判断性能瓶颈在CPU是否是准确的？请各位有劳帮忙看看，谢谢。
select 1
from (select SYS_INT_ID, NE_DN
from zjdxlte.tmp_cm_disc_subnet_info
where type_dn = 'SUBNETWORK'
and view_name = 'OMC'
and invoke_id = 20065) tmp,
(select SM_COLLECTOR_DEPLOY.SYS_INT_ID "ID",
SM_COLLECTOR_DEPLOY.NE_DN "KEY"
from zjdxlte.SM_COLLECTOR_DEPLOY, zjdxlte.OBJECTS_EXS
where (SM_COLLECTOR_DEPLOY.SYS_INT_ID = OBJECTS_EXS.SYS_INT_ID)
and (SM_COLLECTOR_DEPLOY.NE_DN = OBJECTS_EXS.NE_DN)
and OBJECTS_EXS.NE_CLASS = 6) "subnetwork",
(select C_LTE_ANT_INFO.NE_DN "KEY",
C_LTE_ANT_INFO.SYS_INT_ID "ID",
C_LTE_ANT_INFO.SUBNET_ID "SUBNET_ID",
C_LTE_ANT_INFO.NE_NAME "NAME"
from zjdxlte.C_LTE_ANT_INFO, zjdxlte.OBJECTS_EXS
where (C_LTE_ANT_INFO.SYS_INT_ID = OBJECTS_EXS.SYS_INT_ID)
and (C_LTE_ANT_INFO.NE_DN = OBJECTS_EXS.NE_DN)
and C_LTE_ANT_INFO.NE_CLASS = 355) "ant"
where tmp.SYS_INT_ID = "subnetwork".ID
and "subnetwork".ID = "ant".SUBNET_ID
执行 语句 给出结果
set linesize 300 pagesize 3000
set long 99999
SELECT *
FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
SQL_ID,
plan_hash_value,
TO_CHAR (FIRST_LOAD_TIME) begin_time,
'在cursor cache中' end_time,
executions "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM Gv$SQL
WHERE sql_id = '6y377cz3r7t39'
UNION ALL
SELECT '2.sqltuning set' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '6y377cz3r7t39'
UNION ALL
SELECT '3.dba_advisor_sqlstats' source,
sql_id,
plan_hash_value,
'JUST SQLSET NO DATE' begin_time,
'JUST SQLSET NO DATE' end_time,
EXECUTIONS "No. of exec",
(buffer_gets / executions) "LIO/exec",
(cpu_time / executions / 1000000) "CPUTIM/exec",
(elapsed_time / executions / 1000000) "ETIME/exec",
(disk_reads / executions) "PIO/exec",
(ROWS_PROCESSED / executions) "ROWs/exec"
FROM dba_sqlset_statements
WHERE SQL_ID = '6y377cz3r7t39'
UNION ALL
SELECT DISTINCT
'4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
source,
sql_id,
PLAN_HASH_VALUE,
TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
SQL.executions_delta,
SQL.buffer_gets_delta
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"LIO/exec",
(SQL.cpu_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"CPUTIM/exec",
(SQL.elapsed_time_delta / 1000000)
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ETIME/exec",
SQL.DISK_READS_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"PIO/exec",
SQL.ROWS_PROCESSED_DELTA
/ DECODE (NVL (SQL.executions_delta, 0),
0, 1,
SQL.executions_delta)
"ROWs/exec"
FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
WHERE SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
AND SQL.dbid = (SELECT dbid FROM v$database)
AND s.snap_id = SQL.snap_id
AND sql_id IN ('6y377cz3r7t39'))
ORDER BY source, begin_time DESC;
@?/rdbms/admin/sqltrpt
6y377cz3r7t39
谢谢刘老大，内容有点多，分开贴；
查询结果如下SOURCE SQL_ID PLAN_HASH_VALUE BEGIN_TIME END_TIME No. of exec LIO/exec CPUTIM/exec ETIME/exec PIO/exec ROWs/exec
----------------------------------------------------------------- ------------- --------------- -------------------------------------- ------------------- ----------- ---------- ----------- ---------- ---------- ----------
1.v$sql实例号:1 6y377cz3r7t39 4075299578 2016-08-10/16:08:11 在cursor cache中 2 55170505 984.968762 986.533411 0 0
1.v$sql实例号:1 6y377cz3r7t39 417863781 2016-08-10/16:08:11 在cursor cache中 1 42585 .082988 .085014 0 0
4.dba_hist_sqlstat实例号:1 6y377cz3r7t39 4075299578 2016-08-10 16:00:22 2016-08-10 17:00:24 2 55170505 984.968762 986.533411 0 0
报告结果如下SQL> @?/rdbms/admin/sqltrpt
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- -------------------------------------------------------
fnf9kt73sn6zb 2,250.20 insert into OBJECTS_EXS("ROOM","PARENT_INT_ID","OMC_ID"
9t96jhwcstp48 2,180.52 update C_LTE_ENB_INFO a set (a.la
gct9ajpcqu339 2,119.77 insert into tmp_cm_disc_subnet_info(VIEW_NAME,LAYER, TY
6y377cz3r7t39 1,973.15 select 1 from (select SYS_INT_ID, NE_DN
70mzhfyfvkw1q 1,809.89 select count(*) from ( select aa."ROOM", a
fqqyghduagzuw 1,751.34 insert into fm_alactive(application_dn,notification_i
dbd624p9f4nxk 1,577.17 delete from fm_alactive where application_dn
4vs91dcv7u1p6 1,152.10 insert into sys.aud$( sessionid,entryid,statement,ntime
b6usrg82hwsa3 1,151.66 call dbms_stats.gather_database_stats_job_proc ( )
gy3ks019phgsh 1,109.10 insert into OBJECTS_EXS("ROOM","PARENT_INT_ID","OMC_ID"
cmuczt5vmvfgw 1,103.11 BEGIN PKG_PRE_LTE_CM.CM_PRE; END;
1341cua5s6wqt 1,041.97 BEGIN pkg_fm_col.normal_alarm(:1,:2); END;
fjz8x58hjrtyj 900.37 update C_LTE_ENB_INFO a set (a.last_modified) = (sel
csjujdu0pyn8f 860.32 select SEQ_FM_AFC.nextval from dual
6px9x82t1v1j2 680.69 update OBJECTS_EXS a set (a.last_modified) = (select
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED SQL_TEXT_FRAGMENT
------------- ---------- --------------------------------------------------------------------------------------------------------------
bwpn35frx9r6x 15,753.08 insert into CM_SUBNET_INFO_BAK(VIEW_NAME,LAYER, TYPE_DN
cc3c5p9nk1qsx 7,903.32 UPDATE CM_SUBNET_INFO_BAK X SET (PARENT_DN_STRING,DN_ST
8h1dud91sbbhd 7,885.11 UPDATE CM_SUBNET_INFO_BAK T SET CHILD_NUM = (SELECT COU
cvy7zdmqj6c8z 7,875.10 update C_LTE_ANT_INFO a set(a.last_modified) = (select
8bz802bgsx785 7,875.02 update C_LTE_ANT_INFO a set(a.last_modified) = (select
cxquv2a3r4m8j 7,875.02 update C_LTE_ANT_INFO a set(a.last_modified) = (select
fudkyst20pd0p 7,875.02 update C_LTE_ANT_INFO a set(a.last_modified) = (select
1ndfj53wgmnc1 7,875.02 update C_LTE_ANT_INFO a set(a.last_modified) = (select
g0z3pkym0ty6d 2,835.72 update C_LTE_ANT_INFO a set (a.last_modified) = (se
9t96jhwcstp48 2,685.96 update C_LTE_ENB_INFO a set (a.la
7s2nby8mrf6w8 2,327.81 insert into OBJECTS_EXS("ROOM","PARENT_INT_ID","OMC_ID"
fnf9kt73sn6zb 2,250.20 insert into OBJECTS_EXS("ROOM","PARENT_INT_ID","OMC_ID"
gct9ajpcqu339 2,119.77 insert into tmp_cm_disc_subnet_info(VIEW_NAME,LAYER, TY
6y377cz3r7t39 1,973.07 select 1 from (select SYS_INT_ID, NE_DN
70mzhfyfvkw1q 1,809.89 select count(*) from ( select aa."ROOM", a
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 6y377cz3r7t39
Sql Id specified: 6y377cz3r7t39
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_2448
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 08/11/2016 16:41:40
Completed at : 08/11/2016 16:41:48
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 6y377cz3r7t39
SQL Text : select 1
from (select SYS_INT_ID, NE_DN
from zjdxlte.tmp_cm_disc_subnet_info
where type_dn = 'SUBNETWORK'
and view_name = 'OMC'
and invoke_id = 20065) tmp,
(select SM_COLLECTOR_DEPLOY.SYS_INT_ID "ID",
SM_COLLECTOR_DEPLOY.NE_DN "KEY"
from zjdxlte.SM_COLLECTOR_DEPLOY,
zjdxlte.OBJECTS_EXS
where (SM_COLLECTOR_DEPLOY.SYS_INT_ID =
OBJECTS_EXS.SYS_INT_ID)
and (SM_COLLECTOR_DEPLOY.NE_DN = OBJECTS_EXS.NE_DN)
and OBJECTS_EXS.NE_CLASS = 6) "subnetwork",
(select C_LTE_ANT_INFO.NE_DN "KEY",
C_LTE_ANT_INFO.SYS_INT_ID "ID",
C_LTE_ANT_INFO.SUBNET_ID "SUBNET_ID",
C_LTE_ANT_INFO.NE_NAME "NAME"
from zjdxlte.C_LTE_ANT_INFO, zjdxlte.OBJECTS_EXS
where (C_LTE_ANT_INFO.SYS_INT_ID =
OBJECTS_EXS.SYS_INT_ID)
and (C_LTE_ANT_INFO.NE_DN = OBJECTS_EXS.NE_DN)
and C_LTE_ANT_INFO.NE_CLASS = 355) "ant"
where tmp.SYS_INT_ID = "subnetwork".ID
and "subnetwork".ID = "ant".SUBNET_ID
-------------------------------------------------------------------------------
FINDINGS SECTION (4 findings)
------------------------------------------------------------------------------- 1- Statistics Finding
---------------------
Optimizer statistics for table "ZJDXLTE"."TMP_CM_DISC_SUBNET_INFO" are stale.
Recommendation
--------------
- Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'ZJDXLTE', tabname =>
'TMP_CM_DISC_SUBNET_INFO', estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
AUTO', cascade => TRUE);
Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 85.85%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index. If you choose to create the
recommended index, consider dropping the index
"ZJDXLTE"."IDX_TMP_SUBINFO_DNVIEW" because it is a prefix of the
recommended index.
create index ZJDXLTE.IDX$$_09900001 on
ZJDXLTE.TMP_CM_DISC_SUBNET_INFO("TYPE_DN","VIEW_NAME","INVOKE_ID","SYS_INT_
ID");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index ZJDXLTE.IDX$$_09900002 on
ZJDXLTE.OBJECTS_EXS("SYS_INT_ID","NE_DN");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index ZJDXLTE.IDX$$_09900003 on
ZJDXLTE.OBJECTS_EXS("NE_CLASS","SYS_INT_ID","NE_DN");
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index ZJDXLTE.IDX$$_09900004 on
ZJDXLTE.SM_COLLECTOR_DEPLOY("SYS_INT_ID","NE_DN");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption. 3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 4 of the
execution plan.
Recommendation
--------------
- Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
4- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 417863781 2016-08-11/09:58:56 0.085 Cursor Cache original plan
2 4075299578 2016-08-10/16:08:11 986.533 Cursor Cache
Information
-----------
- The Original Plan appears to have the best performance, based on the
elapsed time per execution. However, if you know that one alternative
plan is better than the Original Plan, you can create a SQL plan baseline
for it. This will instruct the Oracle optimizer to pick it over any other
choices in the future.
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_2448',
owner_name => 'SYS', plan_hash_value => xxxxxxxx);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
------------------------------------------------------------------------------- 1- Original
-----------
Plan hash value: 417863781
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 176 | 482 (1)| 00:00:06 |
| 1 | NESTED LOOPS | | 1 | 176 | 482 (1)| 00:00:06 |
| 2 | NESTED LOOPS | | 1 | 176 | 482 (1)| 00:00:06 |
|* 3 | HASH JOIN | | 1 | 131 | 480 (1)| 00:00:06 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 123 | 477 (1)| 00:00:06 |
| 5 | NESTED LOOPS | | 1 | 82 | 474 (1)| 00:00:06 |
| 6 | NESTED LOOPS | | 30635 | 82 | 474 (1)| 00:00:06 |
| 7 | VIEW | index$_join$_008 | 30635 | 1226K| 473 (1)| 00:00:06 |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN | PK_OBJECTS_EXS_ID | 30635 | 1226K| 121 (0)| 00:00:02 |
| 10 | INDEX FAST FULL SCAN | DN#OBJECTS_EXS | 30635 | 1226K| 469 (0)| 00:00:06 |
|* 11 | INDEX UNIQUE SCAN | PK_C_LTE_ANT_INFO_ID | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| C_LTE_ANT_INFO | 1 | 41 | 0 (0)| 00:00:01 |
| 13 | BUFFER SORT | | 1 | 41 | 477 (1)| 00:00:06 |
|* 14 | TABLE ACCESS BY INDEX ROWID| TMP_CM_DISC_SUBNET_INFO | 1 | 41 | 3 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_TMP_SUBINFO_IID | 1 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SM_COLLECTOR_DEPLOY | 1 | 8 | 3 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_OBJECTS_EXS_ID | 1 | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | OBJECTS_EXS | 1 | 45 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SYS_INT_ID"="SM_COLLECTOR_DEPLOY"."SYS_INT_ID" AND
"SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="C_LTE_ANT_INFO"."SUBNET_ID")
8 - access(ROWID=ROWID)
11 - access("C_LTE_ANT_INFO"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
12 - filter("C_LTE_ANT_INFO"."NE_CLASS"=355 AND "C_LTE_ANT_INFO"."NE_DN"="OBJECTS_EXS"."NE_DN")
14 - filter("TYPE_DN"='SUBNETWORK' AND "VIEW_NAME"='OMC')
15 - access("INVOKE_ID"=20065)
17 - access("SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
18 - filter("OBJECTS_EXS"."NE_CLASS"=6 AND "SM_COLLECTOR_DEPLOY"."NE_DN"="OBJECTS_EXS"."NE_DN")
2- Using New Indices
--------------------
Plan hash value: 939202290
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 176 | 68 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 176 | 68 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 168 | 68 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 123 | 66 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1423 | 123 | 66 (0)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 1423 | 113K| 66 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX$$_09900001 | 1 | 41 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 30635 | 1226K| 64 (0)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN | IDX$$_09900002 | 30635 | 1226K| 64 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_C_LTE_ANT_INFO_ID | 1 | | 0 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| C_LTE_ANT_INFO | 1 | 41 | 0 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 5 | 225 | 68 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX$$_09900003 | 5 | 225 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX$$_09900004 | 1 | 8 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TYPE_DN"='SUBNETWORK' AND "VIEW_NAME"='OMC' AND "INVOKE_ID"=20065)
9 - access("C_LTE_ANT_INFO"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
10 - filter("C_LTE_ANT_INFO"."NE_CLASS"=355 AND
"C_LTE_ANT_INFO"."NE_DN"="OBJECTS_EXS"."NE_DN")
12 - access("OBJECTS_EXS"."NE_CLASS"=6)
13 - access("SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID" AND
"SM_COLLECTOR_DEPLOY"."NE_DN"="OBJECTS_EXS"."NE_DN")
filter("SYS_INT_ID"="SM_COLLECTOR_DEPLOY"."SYS_INT_ID" AND
"SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="C_LTE_ANT_INFO"."SUBNET_ID")
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
------------------------------------------------------------------------------- Plan 2
------
Plan Origin :Cursor Cache
Plan Hash Value :4075299578
Executions :2
Elapsed Time :986.533 sec
CPU Time :984.969 sec
Buffer Gets :55170505
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 176 | 326K (1)| 01:05:15 |
| 1 | NESTED LOOPS | | 1 | 176 | 326K (1)| 01:05:15 |
| 2 | NESTED LOOPS | | 4935 | 176 | 326K (1)| 01:05:15 |
| 3 | NESTED LOOPS | | 4935 | 650K| 321K (1)| 01:04:16 |
|* 4 | HASH JOIN | | 106K| 9753K| 2469 (1)| 00:00:30 |
| 5 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 53 | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SM_COLLECTOR_DEPLOY | 1 | 8 | 3 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_OBJECTS_EXS_ID | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| OBJECTS_EXS | 1 | 45 | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | C_LTE_ANT_INFO | 121K| 4869K| 2464 (1)| 00:00:30 |
|* 11 | TABLE ACCESS BY INDEX ROWID | TMP_CM_DISC_SUBNET_INFO | 1 | 41 | 3 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_TMP_SUBINFO_IID | 1 | | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_OBJECTS_EXS_ID | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | OBJECTS_EXS | 1 | 41 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="C_LTE_ANT_INFO"."SUBNET_ID")
8 - access("SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
9 - filter("OBJECTS_EXS"."NE_CLASS"=6 AND "SM_COLLECTOR_DEPLOY"."NE_DN"="OBJECTS_EXS"."NE_DN")
10 - filter("C_LTE_ANT_INFO"."NE_CLASS"=355)
11 - filter("TYPE_DN"='SUBNETWORK' AND "VIEW_NAME"='OMC' AND
"SYS_INT_ID"="SM_COLLECTOR_DEPLOY"."SYS_INT_ID")
12 - access("INVOKE_ID"=20065)
13 - access("C_LTE_ANT_INFO"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
14 - filter("C_LTE_ANT_INFO"."NE_DN"="OBJECTS_EXS"."NE_DN")
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :417863781
Executions :1
Elapsed Time :0.085 sec
CPU Time :0.083 sec
Buffer Gets :42585
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan matches the original plan.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 176 | 482 (1)| 00:00:06 |
| 1 | NESTED LOOPS | | 1 | 176 | 482 (1)| 00:00:06 |
| 2 | NESTED LOOPS | | 1 | 176 | 482 (1)| 00:00:06 |
|* 3 | HASH JOIN | | 1 | 131 | 480 (1)| 00:00:06 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 123 | 477 (1)| 00:00:06 |
| 5 | NESTED LOOPS | | 1 | 82 | 474 (1)| 00:00:06 |
| 6 | NESTED LOOPS | | 30635 | 82 | 474 (1)| 00:00:06 |
| 7 | VIEW | index$_join$_008 | 30635 | 1226K| 473 (1)| 00:00:06 |
|* 8 | HASH JOIN | | | | | |
| 9 | INDEX FAST FULL SCAN | PK_OBJECTS_EXS_ID | 30635 | 1226K| 121 (0)| 00:00:02 |
| 10 | INDEX FAST FULL SCAN | DN#OBJECTS_EXS | 30635 | 1226K| 469 (0)| 00:00:06 |
|* 11 | INDEX UNIQUE SCAN | PK_C_LTE_ANT_INFO_ID | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| C_LTE_ANT_INFO | 1 | 41 | 0 (0)| 00:00:01 |
| 13 | BUFFER SORT | | 1 | 41 | 477 (1)| 00:00:06 |
|* 14 | TABLE ACCESS BY INDEX ROWID| TMP_CM_DISC_SUBNET_INFO | 1 | 41 | 3 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_TMP_SUBINFO_IID | 1 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SM_COLLECTOR_DEPLOY | 1 | 8 | 3 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_OBJECTS_EXS_ID | 1 | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID | OBJECTS_EXS | 1 | 45 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SYS_INT_ID"="SM_COLLECTOR_DEPLOY"."SYS_INT_ID" AND
"SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="C_LTE_ANT_INFO"."SUBNET_ID")
8 - access(ROWID=ROWID)
11 - access("C_LTE_ANT_INFO"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
12 - filter("C_LTE_ANT_INFO"."NE_CLASS"=355 AND "C_LTE_ANT_INFO"."NE_DN"="OBJECTS_EXS"."NE_DN")
14 - filter("TYPE_DN"='SUBNETWORK' AND "VIEW_NAME"='OMC')
15 - access("INVOKE_ID"=20065)
17 - access("SM_COLLECTOR_DEPLOY"."SYS_INT_ID"="OBJECTS_EXS"."SYS_INT_ID")
18 - filter("OBJECTS_EXS"."NE_CLASS"=6 AND "SM_COLLECTOR_DEPLOY"."NE_DN"="OBJECTS_EXS"."NE_DN")
------------------------------------------------------------------------------- 以上已经全部贴完 该SQL语句现在执行时没有返回结果的；但是如果执行成功的话应该也是没有返回结果的，因为第1、2个子查询只返回1行记录，第3个子查询返回0条记录
当执行计划 为4075299578时
只是逻辑读 较高 而已， 单次逻辑读 达到 55170505 ， 约为420 GB
判断性能瓶颈在CPU是否是准确的？请各位有劳帮忙看看，谢谢。==》 没错，逻辑读主要消耗CPU时间片
如 上述输出已经给出了 例如建索引 或采用 SPM固定执行计划的方案，均可一试 多谢！
补充提问：
分别执行子查询都是瞬间出来结果，为什么组合在一起了就出现执行计划（只当个子查询的执行计划），这背后到底是什么在搞鬼；
已经确保每次执行之前都收集了统计信息，执行计划还是经常变更；
页:
[1]