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
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 #566427 is a reply to message #566424] Fri, 14 September 2012 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable!
I doubt you will have any look to your post as it is.

Regards
Michel
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566429 is a reply to message #566427] Fri, 14 September 2012 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566490 is a reply to message #566429] Sat, 15 September 2012 13:46 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
If the queries run fine in sqlplus, you'll need a look at hibernate fetching settings. Btw what version of Hibernate does your app uses?
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566560 is a reply to message #566490] Mon, 17 September 2012 09:27 Go to previous messageGo to next message
brown_zzz
Messages: 39
Registered: August 2012
Location: United Kingdom
Member
Any help with the hibernate fetch settings would be appreciated. The versions are as follows...

hibernate-jpa-2.0-api version 1.0.0.Final
hibernate-core version 3.3.1.GA
hibernate-annotations version 3.4.0.GA
hibernate-entitymanager version 3.4.0.GA
JDBC database driver *Oracle Driver (Thin XA) for Service connections, Versions 9.0.1 and later
Re: HIBERNATE query runs slow but ok in SQLPLUS [message #566562 is a reply to message #566560] Mon, 17 September 2012 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any help with the hibernate fetch settings would be appreciated.
problem & solution are external to Oracle

Consider posting in Hibernate forum.
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 messageGo to next message
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
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; 
Previous Topic: Remove SQL*Net waitevent from top 5 list
Next Topic: dbms_stats.gather_index_stats vs dbms_stats.gather_table_stats
Goto Forum:
  


Current Time: Thu Mar 28 16:50:28 CDT 2024