Home » RDBMS Server » Performance Tuning » Sentence tuning (Oracle9i)
icon5.gif  Sentence tuning [message #559573] Wed, 04 July 2012 07:03 Go to next message
estonolose
Messages: 4
Registered: October 2011
Junior Member
Hi,

I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's
causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.

SELECT DISTINCT hash_value,
                sql_text
FROM   gv$sql sq
WHERE  hash_value IN (SELECT DISTINCT prev_hash_value
                      FROM   gv$session se
                      WHERE  sid IN (SELECT sid
                                     FROM   gv$lock l
                                     WHERE  type = 'TX'
                                            AND ctime >= 2000
                                            AND l.inst_id = se.inst_id
                                            AND l.sid = se.sid)
                             AND sq.inst_id = se.inst_id);


This is the execution plan.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=1 Bytes=609)
   1    0   SORT (UNIQUE) (Cost=93 Card=1 Bytes=609)
   2    1     HASH JOIN (Cost=91 Card=1 Bytes=609)
   3    2       MERGE JOIN (CARTESIAN) (Cost=79 Card=5 Bytes=2850)
   4    3         VIEW* OF 'GV$LOCK'                                   :Q404510
                                                                       00

   5    4           HASH JOIN* (Cost=68 Card=1 Bytes=79)               :Q404510
                                                                       00

   6    5             HASH JOIN* (Cost=56 Card=4 Bytes=280)            :Q404510
                                                                       00

   7    6               VIEW OF 'GV$_LOCK' (Cost=44 Card=4 Bytes=152)
   8    7                 UNION-ALL
   9    8                   VIEW OF 'GV$_LOCK1' (Cost=22 Card=2 Bytes=
          178)

  10    9                     UNION-ALL
  11   10                       FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost
          =11 Card=1 Bytes=102)

  12   10                       FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=
          11 Card=1 Bytes=102)

  13    8                   FIXED TABLE (FULL) OF 'X$KTADM' (Cost=11 C
          ard=1 Bytes=102)

  14    8                   FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=11 C
          ard=1 Bytes=102)

  15    6               FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=11 Card=
          100 Bytes=3200)

  16    5             FIXED TABLE* (FULL) OF 'X$KSQRS' (Cost=11 Card=1 :Q404510
           Bytes=9)                                                    00

  17    3         BUFFER (SORT) (Cost=79 Card=5 Bytes=2640)
  18   17           VIEW* OF 'GV$SQL'                                  :Q404520
                                                                       00

  19   18             FIXED TABLE* (FULL) OF 'X$KGLOB' (Cost=11 Card=5 :Q404520
           Bytes=2765)                                                 00

  20    2       VIEW* OF 'GV$SESSION'                                  :Q404530
                                                                       00

  21   20         FIXED TABLE* (FULL) OF 'X$KSUSE' (Cost=11 Card=1 Byt :Q404530
          es=65)                                                       00



   4 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SID",A1."TYPE",A1."C
                                   TIME" FROM "GV$LOCK" A1 WHERE A1."CT

   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_FROM_SERIAL
  16 PARALLEL_FROM_SERIAL
  18 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SQL_TEXT",A1."HASH_V
                                   ALUE" FROM "GV$SQL" A1

  19 PARALLEL_COMBINED_WITH_PARENT
  20 PARALLEL_TO_SERIAL            SELECT A1."INST_ID",A1."SID",A1."PREV_HASH_V
                                   ALUE" FROM "GV$SESSION" A1

  21 PARALLEL_COMBINED_WITH_PARENT


Thanks
Re: Sentence tuning [message #559577 is a reply to message #559573] Wed, 04 July 2012 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Use v$ instead og gv$ unless you are in RAC
2/ Remove DISTINCT in IN subquery, it is useless
3/ The correct join condition is on HASH_VALUE and SQL_ADDRESS

Regards
Michel
Re: Sentence tuning [message #559582 is a reply to message #559577] Wed, 04 July 2012 07:31 Go to previous messageGo to next message
estonolose
Messages: 4
Registered: October 2011
Junior Member
I have changed the query with your recommendations but the cost is equal. I'm in RAC database.

SELECT DISTINCT hash_value,
                sql_text
FROM   gv$sql sq
WHERE  ( hash_value, address ) IN (SELECT prev_hash_value,
                                          sql_address
                                   FROM   gv$session se
                                   WHERE  sid IN (SELECT sid
                                                  FROM   gv$lock l
                                                  WHERE  type = 'TX'
                                                         AND ctime >= 2000
                                                         AND
                                                 l.inst_id = se.inst_id
                                                         AND l.sid = se.sid)
                                          AND sq.inst_id = se.inst_id);  

Re: Sentence tuning [message #560530 is a reply to message #559582] Fri, 13 July 2012 21:24 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
What I do to see what was locking earlier is I look at the average seconds
of execution time which can be calculated with the following script. I am
usually interested in the query with the longest average execution time
but in this case I might be interested in the query with 13083 executions
because its total seconds of elapsed time is over 16 thousand.

This query is very good to run before and after a new code release test
because queries with long execution times will run slow on our web site.
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 >50 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;
Re: Sentence tuning [message #560755 is a reply to message #559573] Mon, 16 July 2012 13:25 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Your statement is meaningless. You cannot find sql's causing TX enqueues. So you don't need to tune this sql.
Re: Sentence tuning [message #560757 is a reply to message #560755] Mon, 16 July 2012 13:45 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I have found long running queries to have been locking. I have taken long running queries and then run them manually to find that they take less time. The difference in time tells me that they query was locking and the locking was running up the average execution time. I then create indexes or cache tables or limit updates to one node and the problem goes away. One query with a long average run time was fixed by increasing the size of pga_aggregate_target that caused a 63 second sort to run in 1/2 second.
Re: Sentence tuning [message #561088 is a reply to message #560757] Wed, 18 July 2012 14:28 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Quote:
The difference in time tells me that they query was locking and the locking was running up the average execution time.


Why? Perhaps they were simply slow without being locked. You could have checked application wait time of your sql's (column APPLICATION_WAIT_TIME) in AWR or in v$sql to verify enqueue waits.

In each case it has nothing to do with your query, that is definitely wrong.
Re: Sentence tuning [message #561092 is a reply to message #561088] Wed, 18 July 2012 15:05 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I agree, the queries could have been slow without locking.
I am just showing a technique that has worked for me to
identify the longest running queries. At the NFL, locking
inserts and locking updates were identified by this method.
We moved the updates to one node and the execution time dropped.
Previous Topic: DB FILE SEQUENTIAL READ IN Full table scan
Next Topic: SQL optimisation
Goto Forum:
  


Current Time: Mon Oct 20 06:00:22 CDT 2014

Total time taken to generate the page: 0.07692 seconds