Home » RDBMS Server » Performance Tuning » HIBERNATE query runs slow but ok in SQLPLUS (Oracle 11g)
HIBERNATE query runs slow but ok in SQLPLUS [message #566424] |
Fri, 14 September 2012 07:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6245e07fb26043a159d370ab0821704a?s=64&d=mm&r=g) |
brown_zzz
Messages: 39 Registered: August 2012 Location: United Kingdom
|
Member |
|
|
I have a query which takes 5 minutes when run through the java app which uses hibernate. I've cut and pasted the SQL directly from hiberate trace file and run it in sqlplus/sqldeveloper and it runs instantly (0.01 seconds)(uses the index all ok and explain plan looks good - see below.) I don't know how to get the explain plan when it's running through the app or why it should be any different anyway as the query is identical.
Please can anyone help?
My query is as follows:
SELECT /*+ INDEX (SPD SPD_SEQ_CODE) */ SPD.*
FROM SEQ_ADDR_DATA SPD, SEQ_ADDR_LEVELS SPL
WHERE SPD.SPVR_ID = '10'
AND SPL.SPLE_ID = SPD.SPLE_ID
AND SPL.SPLE_LEVEL <= '2' AND SPDA_ID NOT IN
(SELECT TVBL2.SPDA_ID
FROM TEMP_VERSION_BOXS TVB2,
TEMP_VERSION_BOX_SECTIONS TVBL2
WHERE TVB2.TBOX_ID = TVBL2.TBOX_ID
AND TVB2.TMPV_ID = '100'
UNION
SELECT SPD2.SPDA_ID
FROM TEMP_VERSION_BOX_SECTIONS TVBL2,
TEMP_VERSION_BOXS TVB2,
SEQ_ADDR_DATA SPD2,
SEQ_ADDR_LEVELS SPL2,
TEMP_VERSIONS TV2
WHERE TVB2.TMPV_ID = '100'
AND TVB2.TBOX_ID = TVBL2.TBOX_ID
AND TV2.TMPV_ID = TVB2.TMPV_ID
AND SPD2.SPVR_ID = TV2.SPVR_ID
AND SPL2.SPLE_LEVEL <= '2'
AND SPL2.SPLE_ID = SPD2.SPLE_ID
AND SPD2.PARENT_SPDA_ID = TVBL2.SPDA_ID) ORDER BY SPDA_ID;
The sub select returns 1 row
The whole select returns 537 rows
SEQ_ADDR_DATA has 16,000,000 rows
SEQ_ADDR_LEVELS has 13 rows
TEMP_VERSION_BOXS has 6 rows
TEMP_VERSION_BOX_SECTIONS has 2 rows
TEMP_VERSIONS TV2 has 2 rows
The create tables, primary keys and indexes are as follows:
CREATE TABLE SEQ_ADDR_DATA
(SPDA_ID NUMBER(38) NOT NULL
,SEQ_CODE VARCHAR2(30) NOT NULL
,SEQ_SEQUENCE NUMBER(38) NOT NULL
,DISPLAY_INFO VARCHAR2(255)
,LABEL_INFO VARCHAR2(255)
,PARENT_JOIN_VALUE VARCHAR2(255)
,SPLE_ID NUMBER(38) NOT NULL
,SPVR_ID NUMBER(38) NOT NULL
,SEQ_DEPTH_SPLE_LEVEL NUMBER(38)
,PARENT_SPDA_ID NUMBER(38) NULL
,CLASS_TYPE VARCHAR2(1) DEFAULT 'C'
,VERSION NUMBER(38) NOT NULL
)
/
CREATE TABLE SEQ_ADDR_LEVELS
(SPLE_ID NUMBER(38) NOT NULL
,SPLE_LEVEL NUMBER(38) NOT NULL
,NAME VARCHAR2(30) NOT NULL
,DESCRIPTION VARCHAR2(500)
,DISPLAY_TITLE VARCHAR2(30)
,SPRO_ID NUMBER(38) NOT NULL
,PARENT_SPLE_ID NUMBER(38)
,CODE VARCHAR2(5) NOT NULL
,CLASS_TYPE VARCHAR2(1) DEFAULT 'C'
,VERSION NUMBER(38) NOT NULL
,AUDIT_USERNAME VARCHAR2(255) NOT NULL
,AUDIT_TIMESTAMP DATE NOT NULL
)
/
CREATE TABLE TEMP_VERSION_BOXS
(TBOX_ID NUMBER(38) NOT NULL
,TMPV_ID NUMBER NOT NULL
,CPING_TBOX_ID NUMBER(38)
,BOX_REFERENCE VARCHAR2(10) NOT NULL
,BOX_TYPE VARCHAR2(1) NOT NULL
,NUMBER_OF_CPED_BOXS NUMBER(38)
,LABEL_TEXT VARCHAR2(1000)
,DISP_DEF VARCHAR2(1000)
,PARENT_CPED_TBOX_ID NUMBER(38)
,CLASS_TYPE VARCHAR2(1) DEFAULT 'C'
,VERSION NUMBER(38) NOT NULL
,AUDIT_USERNAME VARCHAR2(255) NOT NULL
,AUDIT_TIMESTAMP DATE NOT NULL
)
/
CREATE TABLE TEMP_VERSION_BOX_SECTIONS
(TBLI_ID NUMBER(38) NOT NULL
,TBOX_ID NUMBER(38) NOT NULL
,SEQ_CRITERIA_FROM VARCHAR2(100)
,SEQ_CRITERIA_TO VARCHAR2(100)
,SPFC_ID NUMBER(38)
,MFCO_ID NUMBER(38)
,SPDA_ID NUMBER(38)
,SITE_ID NUMBER
,CLASS_TYPE VARCHAR2(1) DEFAULT 'C'
,VERSION NUMBER(38) NOT NULL
,AUDIT_USERNAME VARCHAR2(255) NOT NULL
,AUDIT_TIMESTAMP DATE NOT NULL
)
/
CREATE TABLE TEMP_VERSIONS
(TMPV_ID NUMBER(38) NOT NULL
,TEMP_ID NUMBER(38) NOT NULL
,MTYP_ID NUMBER(38)
,SPVR_ID NUMBER(38)
,VERSION_NUMBER NUMBER(5) NOT NULL
,TEMP_STATUS VARCHAR2(10) NOT NULL
,STATUS_TIMESTAMP DATE NOT NULL
,CLASS_TYPE VARCHAR2(1) DEFAULT 'C'
,VERSION NUMBER(38) NOT NULL
,AUDIT_USERNAME VARCHAR2(255) NOT NULL
,AUDIT_TIMESTAMP DATE NOT NULL
)
/
ALTER TABLE SEQ_ADDR_DATA
ADD (CONSTRAINT SPDA_PK PRIMARY KEY
(SPDA_ID))
/
ALTER TABLE SEQ_ADDR_LEVELS
ADD (CONSTRAINT SPLE_PK PRIMARY KEY
(SPLE_ID))
/
ALTER TABLE TEMP_VERSION_BOXS
ADD (CONSTRAINT TBOX_PK PRIMARY KEY
(TBOX_ID))
/
ALTER TABLE TEMP_VERSION_BOX_SECTIONS
ADD (CONSTRAINT TBLI_PK PRIMARY KEY
(TBLI_ID))
/
ALTER TABLE TEMP_VERSIONS
ADD (CONSTRAINT TMPV_PK PRIMARY KEY
(TMPV_ID))
/
CREATE INDEX SPD_SEQ_CODE ON SEQ_ADDR_DATA (SPVR_ID, SPLE_ID, SEQ_CODE)
/
CREATE INDEX SPD_PARENT_SPDA ON SEQ_ADDR_DATA (SPVR_ID, SPLE_ID, PARENT_SPDA_ID)
/
CREATE INDEX SPD_SEQ_SEQUENCE ON SEQ_ADDR_DATA (SPVR_ID, SPLE_ID, SEQ_SEQUENCE)
/
CREATE INDEX SPLE_SPLE_FK_I ON SEQ_ADDR_LEVELS (PARENT_SPLE_ID)
/
CREATE INDEX SPLE_SPRO_FK_I ON SEQ_ADDR_LEVELS (SPRO_ID)
/
EXPLAIN PLAN
============
SELECT STATEMENT
67187742
SEQ
ORDER BY 67187742
FILTER
Filter Predicates
NOT EXISTS ( (SELECT TVBL2.SPDA_ID FROM TEMP_VERSION_BOX_SECTIONS TVBL2,TEMP_VERSION_BOXS TVB2 WHERE TVB2.TBOX_ID=TVBL2.TBOX_ID AND TVB2.TMPV_ID=100 AND LNNVL(TVBL2.SPDA_ID<>:B1))UNION (SELECT SPD2.SPDA_ID FROM TEMP_VERSIONS TV2,SEQ_ADDR_LEVELS SPL2,SEQ_ADDR_DATA SPD2,TEMP_VERSION_BOXS TVB2,TEMP_VERSION_BOX_SECTIONS TVBL2 WHERE SPD2.PARENT_SPDA_ID=TVBL2.SPDA_ID AND TVB2.TBOX_ID=TVBL2.TBOX_ID AND TVB2.TMPV_ID=100 AND SPD2.SPDA_ID=:B2 AND SPD2.SPVR_ID=TV2.SPVR_ID AND SPL2.SPLE_ID=SPD2.SPLE_ID AND SPL2.SPLE_LEVEL<=2 AND TV2.TMPV_ID=100 AND TV2.SPVR_ID IS NOT NULL))
NESTED LOOPS
NESTED LOOPS
6575571
TABLE ACCESS
SEQ_ADDR_LEVELS FULL 3
Filter Predicates
SPL.SPLE_LEVEL<=2
INDEX
SPD_SEQ_CODE RANGE SCAN 4808
Access Predicates
AND
SPD.SPVR_ID=10
SPL.SPLE_ID=SPD.SPLE_ID
TABLE ACCESS
SEQ_ADDR_DATA BY INDEX ROWID 1315114
SEQ
UNIQUE 10
UNION-ALL
NESTED LOOPS
NESTED LOOPS
2
TABLE ACCESS
TEMP_VERSION_BOX_SECTIONS FULL 2
Filter Predicates
LNNVL(TVBL2.SPDA_ID<>:B1)
INDEX
TBOX_PK UNIQUE SCAN 0
Access Predicates
TVB2.TBOX_ID=TVBL2.TBOX_ID
TABLE ACCESS
TEMP_VERSION_BOXS BY INDEX ROWID 0
Filter Predicates
TVB2.TMPV_ID=100
NESTED LOOPS
NESTED LOOPS
6
NESTED LOOPS
6
NESTED LOOPS
4
NESTED LOOPS
3
TABLE ACCESS
TEMP_VERSIONS BY INDEX ROWID 1
Filter Predicates
TV2.SPVR_ID IS NOT NULL
INDEX
TMPV_PK UNIQUE SCAN 0
Access Predicates
TV2.TMPV_ID=100
TABLE ACCESS
SEQ_ADDR_DATA BY INDEX ROWID 2
Filter Predicates
SPD2.SPVR_ID=TV2.SPVR_ID
INDEX
SPDA_PK UNIQUE SCAN 1
Access Predicates
SPD2.SPDA_ID=:B1
TABLE ACCESS
SEQ_ADDR_LEVELS BY INDEX ROWID 1
Filter Predicates
SPL2.SPLE_LEVEL<=2
INDEX
SPLE_PK UNIQUE SCAN 0
Access Predicates
SPL2.SPLE_ID=SPD2.SPLE_ID
TABLE ACCESS
TEMP_VERSION_BOX_SECTIONS FULL 2
Filter Predicates
SPD2.PARENT_SPDA_ID=TVBL2.SPDA_ID
INDEX
TBOX_PK UNIQUE SCAN 0
Access Predicates
TVB2.TBOX_ID=TVBL2.TBOX_ID
TABLE ACCESS
TEMP_VERSION_BOXS BY INDEX ROWID 0
Filter Predicates
TVB2.TMPV_ID=100
|
|
|
|
|
|
|
|
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566710 is a reply to message #566562] |
Tue, 18 September 2012 14:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
I would run the following two sqls that take snapshots of what is being read logically and physically during
the execution of your sql. Then without any doubt or complicated analysis you have the table(s) that are being read in a loop that will benefit from better indexing. Then you can create indexes to reduce the physical reads and you can create indexes to reduce the logical reads.
ENDOCP1P > @io605
INSTANCE IO_PER_MINUTE STATISTIC_NAME OBJECT_NAME
--------- ------------- ----------------------- ----------------------------------------
NDOCP2 1776 physical reads NFLPROD.D_1F000D5D8000010A.
NDOCP2 1776 physical read requests NFLPROD.D_1F000D5D8000010A.
NDOCP2 5040 physical read requests NFLPROD.DM_RELATION_S.
NDOCP2 117900 physical read requests NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 119148 physical reads NFLPROD.DM_SYSOBJECT_R_COMP2.
NDOCP2 639924 physical reads direct NFLPROD.DM_RELATION_S.
NDOCP2 639936 physical reads NFLPROD.DM_RELATION_S.
-------------
sum 1525500
ENDOCP1P > @logical605
GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
.001 NDOCP1 logical reads TABLE SYS.SEG$
.001 NDOCP1 logical reads INDEX SYS.I_OBJ4
.001 NDOCP1 logical reads INDEX SYS.I_COL3
.001 NDOCP1 logical reads TABLE SYS.JOB$
.001 NDOCP1 logical reads INDEX SYS.I_SYN2
.001 NDOCP1 logical reads INDEX SYS.I_TS#
.001 NDOCP1 logical reads INDEX SYS.I_OBJ5
.003 NDOCP1 logical reads INDEX SYS.I_COL1
.003 NDOCP1 logical reads INDEX SYS.I_COL2
.003 NDOCP1 logical reads TABLE SYS.FILE$
.004 NDOCP1 logical reads INDEX SYS.I_OBJ#
.025 NDOCP2 logical reads TABLE SYS.IND$
.034 NDOCP4 logical reads TABLE SYS.IND$
.042 NDOCP4 logical reads INDEX SYS.I_IND1
.045 NDOCP3 logical reads TABLE SYS.IND$
.048 NDOCP3 logical reads INDEX SYS.I_IND1
.048 NDOCP1 logical reads INDEX SYS.I_IND1
.053 NDOCP2 logical reads INDEX SYS.I_IND1
.104 NDOCP3 logical reads TABLE SYS.OBJ$
.104 NDOCP4 logical reads TABLE SYS.OBJ$
.107 NDOCP2 logical reads TABLE SYS.OBJ$
.116 NDOCP3 logical reads TABLE SYS.USER$
.123 NDOCP4 logical reads TABLE SYS.USER$
.135 NDOCP2 logical reads TABLE SYS.USER$
.170 NDOCP1 logical reads TABLE SYS.OBJ$
.217 NDOCP1 logical reads TABLE SYS.USER$
.258 NDOCP1 logical reads TABLE SYS.TS$
.267 NDOCP1 logical reads INDEX SYS.I_OBJ1
.422 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_R
.595 NDOCP2 logical reads TABLE SYS.TS$
.602 NDOCP2 logical reads INDEX SYS.I_OBJ1
.608 NDOCP3 logical reads TABLE SYS.TS$
.624 NDOCP4 logical reads TABLE SYS.TS$
.640 NDOCP3 logical reads INDEX SYS.I_OBJ1
.642 NDOCP4 logical reads INDEX SYS.I_OBJ1
.776 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_S
.844 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D8000010A
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000109
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000146
2.451 NDOCP2 logical reads INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
2.451
----------
sum 13.421
IO605.sql looks like the following:
set termout off
set lines 160
break on report
col instance for a9
compute sum of io_per_minute break on report
drop table gv$segment_statistics1;
drop table gv$segment_statistics2;
column statistic_name format a23
create table gv$segment_statistics1 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
execute dbms_lock.sleep(5);
create table gv$segment_statistics2 as select * from gv$segment_statistics
where upper(statistic_name) like '%PHYSICAL%' and owner not like 'E%';
set wrap off
set termout on
select i.instance_name instance,(b.value-a.value)*12 IO_PER_MINUTE,
a.statistic_name,
a.owner||'.'||a.object_name||'.'||a.subobject_name object_name
from gv$segment_statistics2 b,gv$segment_statistics1 a,gv$instance i
where a.statistic_name=b.statistic_name
and a.inst_id=i.inst_id and b.inst_id=i.inst_id
and a.inst_id=b.inst_id and a.object_type=b.object_type
and a.owner||'.'||a.object_name||'.'||a.subobject_name=
b.owner||'.'||b.object_name||'.'||b.subobject_name
and upper(a.statistic_name) like '%PHYSICAL%'
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistics1 purge;
drop table gv$segment_statistics2 purge;
set termout on
logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.inst_id
and a.object_type=b.object_type
and a.owner||'.'||a.object_name||' '||a.subobject_name
=b.owner||'.'||b.object_name||' '||b.subobject_name
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
|
|
|
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566712 is a reply to message #566710] |
Tue, 18 September 2012 14:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
The other thing that I would do would be to check the seconds of elapsed time in gv$sqlarea to be sure that you have the correct sql that is taking the long time.
ECSCDAP4P > @v$sqlarea_elapsed.sql
TOT_SEC_ELAPSED HASH_VALUE EXECUTIONS INSTANC AVG_SEC_PER_EXEC SQL_TEXT
--------------- ---------- ---------- ------- ---------------- ---------
57.039289 130260116 56 CSCDAP4 1.01855873 select *
57.642821 535777823 56 CSCDAP4 1.02933609 select *
16332.6634 304521275 13083 CSCDAP4 1.24838825 select *
104.498913 3115159362 83 CSCDAP2 1.25902305 select *
93.248114 1077395695 66 CSCDAP4 1.41285021 select *
129.68471 188283226 83 CSCDAP2 1.56246639 select *
910.364119 2431632681 56 CSCDAP1 16.2565021 select *
----------
sum 13483
ECSCDAP4P > select sql_text
2 from gv$sqltext b
3 where b.hash_value=2431632681
4 and inst_id=1
5* order by inst_id,hash_value,b.piece;
SQL_TEXT
----------------------------------------------------------------
select * from ( select audiovideo0_.id as id0_, audiovideo0_1_.E
XTERNALID as EXTERNALID0_, audiovideo0_1_.author as author0_, au
diovideo0_1_.body as body0_, audiovideo0_1_.byline as byline0_,
audiovideo0_1_.club_id as club18_0_, audiovideo0_1_.comments as
comments0_, audiovideo0_1_.copyright as copyright0_, audiovideo0
_1_.CONTENT_DATE as CONTENT8_0_, audiovideo0_1_.imageURL as imag
eURL0_, audiovideo0_1_.shortName as shortName0_, audiovideo0_1_.
SHOW_IN_DYNA_LISTS as SHOW11_0_, audiovideo0_1_.source as source
0_, audiovideo0_1_.status as status0_, audiovideo0_1_.summary as
summary0_, audiovideo0_1_.thumbnailImagePath as thumbna15_0_, a
udiovideo0_1_.title as title0_, audiovideo0_1_.url as url0_, aud
iovideo0_.largeImagePath as largeIma2_79_, audiovideo0_.mediumIm
agePath as mediumIm3_79_, audiovideo0_.smallImagePath as smallIm
a4_79_, audiovideo0_.encodings as encodings79_, audiovideo0_.hig
hResolutionStream as highReso6_79_, audiovideo0_.length as lengt
h79_, audiovideo0_.lowResolutionStream as lowResol8_79_, audiovi
deo0_.mediumResolutionStream as mediumRe9_79_, audiovideo0_.MOBI
LE_STREAM_URL_1 as MOBILE10_79_, audiovideo0_.MOBILE_STREAM_URL_
2 as MOBILE11_79_, audiovideo0_.MOBILE_STREAM_URL_3 as MOBILE12_
79_, audiovideo0_.playType as playType79_, audiovideo0_.primaryC
hannel as primary14_79_, audiovideo0_.SINGLE_BITRATE_PATH as SIN
GLE15_79_, audiovideo0_.streamingServer as streami16_79_, audiov
ideo0_.type as type79_ from AUDIO_VIDEO audiovideo0_ inner join
CONTENT audiovideo0_1_ on audiovideo0_.id=audiovideo0_1_.id wher
e audiovideo0_1_.status = 'ACTIVE' and audiovideo0_1_.CONTENT_DA
TE <= CURRENT_TIMESTAMP and audiovideo0_.type='VIDEO' and audiov
ideo0_1_.SHOW_IN_DYNA_LISTS='Y' and audiovideo0_1_.club_id=:1 an
d (audiovideo0_.id in (select content1_.id from CONTENT content1
_ inner join CONTENT_PERSONNEL personnel2_ on content1_.id=perso
nnel2_.CONTENT_FK inner join PERSONNEL personnel3_ on personnel2
_.PERSONNEL_FK=personnel3_.id where personnel3_.id in (select ch
eerleade4_.id from CHEERLEADER cheerleade4_ inner join PERSONNEL
cheerleade4_1_ on cheerleade4_.id=cheerleade4_1_.id))) order by
audiovideo0_1_.CONTENT_DATE desc ) where rownum <= :2
The v$sqlarea_elapsed.sql looks like the following:
set pages 50
set lines 120
set wrap off
break on report
compute sum of executions break on report
column instance_name format a7
select elapsed_time/1000000 tot_sec_elapsed,hash_value,executions,i.instance_name,
elapsed_time/executions/1000000 Avg_sec_per_exec,
sql_text
from gv$sqlarea s,gv$instance i
where executions >0 and elapsed_time/executions/1000000>1
and upper(sql_text) not like '%DBMS_STATS%'
and upper(sql_text) not like '%WRH$%'
and upper(sql_text) not like '%WRI$%'
and upper(sql_text) not like '%OEM%'
and upper(sql_text) not like '%DR$%'
and upper(sql_text) not like '%DBMS%'
and upper(sql_text) not like '%DBID%'
and upper(sql_text) not like '%OWNER%'
and upper(sql_text) not like '%JOB$%'
and upper(sql_text) not like '%V$%'
and upper(sql_text) not like '%SEQUENCE#%'
and upper(sql_text) not like '%DBSNMP%'
and upper(sql_text) not like '%CTXSYS%'
and upper(sql_text) not like '%BACKUP_TYPE%'
and upper(sql_text) not like '%MGMT%'
and upper(sql_text) not like '%OBJ#%'
and elapsed_time/1000000>1 and s.inst_id=i.inst_id
order by elapsed_time/executions/1000000;
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:42:53 CDT 2024
|