Home » RDBMS Server » Performance Tuning » how to troubleshoot slow running queries? (11g)
how to troubleshoot slow running queries? [message #616089] Thu, 12 June 2014 04:25 Go to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
The following SQL statement has been identified to perform poorly. It currently takes up to 16 mins to execute, but it's supposed to take a second at most.

SQL> 

SELECT Object_Class,
2 Object_Sub_Class,
3 Object_Category,
4 Object_Key,
5 Object_ID,
6 Object_Name,
7 IsHidden,
8 Sharing_Key,
9 Ownership_Key,
10 PictureSheet_Key,
11 HasPictureBlob
12 FROM Objects
13 WHERE Deletion_Flag <> 'Y' AND (Sharing_Key IN (0, 3375705, 1199264));

42345 rows selected.

Elapsed: 00:16:29.08

Execution Plan
----------------------------------------------------------
Plan hash value: 970566107

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38712 | 3629K| 634 (2)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| OBJECTS | 38712 | 3629K| 634 (2)| 00:00:08 |
-----------------------------------------------------------------------------

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

1 - filter(("SHARING_KEY"=0 OR "SHARING_KEY"=1199264 OR
"SHARING_KEY"=3375705) AND "DELETION_FLAG"<>'Y')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5140 consistent gets
0 physical reads
0 redo size
2920666 bytes sent via SQL*Net to client
20001 bytes received via SQL*Net from client
2824 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
42345 rows processed


db version 11.2

unique index on column Object_Key

I'm looking forward for suggestions how to improve the performance of this statement.

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616090 is a reply to message #616089] Thu, 12 June 2014 04:30 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Sixteen minutes for 5140 consistent gets? You have a problem that is not related to the SQL: something is blocking your session. To begin the diagnosis, while it is executing run this from a another session a few times:
select event,seconds_in_wait from v$session where wait_class<>'Idle';

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616110 is a reply to message #616090] Thu, 12 June 2014 05:58 Go to previous messageGo to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
ran the above query from different session while executing.

I got varied results.here is the sample of the results

SQL*Net more data to client    0
db file sequential read    0

db file sequential read    2

db file sequential read    1


db file scattered read
db file scattered read
db file scattered read
db file scattered read
SQL*Net message to client
db file scattered read
db file scattered read
db file scattered read
db file scattered read
db file scattered read
db file scattered read
db file scattered read
db file sequential read


log file parallel write    0
SQL*Net message to client    0
db file sequential read    0
log file sync    0

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616112 is a reply to message #616110] Thu, 12 June 2014 06:05 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
None of those wait events are to do with the session being blocked, so I have no idea why a query that reads only 5000 blocks would take so long. Sorry.
You'll have to do a proper analysis. I would start by using dbms_monitor to trace the session, with wait events. tkprof the trace file (with sys=no aggregate=no) and perhaps it will become clearer.

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616117 is a reply to message #616112] Thu, 12 June 2014 07:36 Go to previous messageGo to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
how to check why the query ran slowly

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616120 is a reply to message #616117] Thu, 12 June 2014 08:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is query launched from DB Server?
is query launched from from remote client?

>2920666 bytes sent via SQL*Net to client
>2824 SQL*Net roundtrips to/from client
it appears to me that it just took a while to push the results across the wire back to the client
about 15 records per each round trip.

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616150 is a reply to message #616120] Thu, 12 June 2014 09:37 Go to previous messageGo to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
query launched from remote client

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616153 is a reply to message #616150] Thu, 12 June 2014 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
What datatypes are the columns? Are you selecting any lobs?

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616185 is a reply to message #616153] Fri, 13 June 2014 02:18 Go to previous messageGo to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
the datatype of haspictureblob is char(1).im not selecting lob

[Updated on: Fri, 13 June 2014 02:18]

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616222 is a reply to message #616120] Fri, 13 June 2014 11:45 Go to previous messageGo to next message
kiranrathodkr916
Messages: 36
Registered: March 2014
Location: India
Member
how do you determine 15 records per roundtrip.how do you calculate that?

[Updated on: Fri, 13 June 2014 11:45]

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616224 is a reply to message #616222] Fri, 13 June 2014 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>42345 rows selected.
>2824 SQL*Net roundtrips to/from client

  1* select  42345/2824 from dual
SQL> /

42345/2824
----------
14.9946884


which is close enough to 15 for me.

Report message to a moderator

Re: how to troubleshoot slow running queries? [message #616242 is a reply to message #616120] Sat, 14 June 2014 02:04 Go to previous message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
BlackSwan wrote on Thu, 12 June 2014 14:02
is query launched from DB Server?
is query launched from from remote client?

>2920666 bytes sent via SQL*Net to client
>2824 SQL*Net roundtrips to/from client
it appears to me that it just took a while to push the results across the wire back to the client
about 15 records per each round trip.
15 is the default arraysize for SQL*Plus. To test,
SET ARRAYSIZE 5000
and run again. This will reduce the round-trips to bout 10, and is the maximum size SQL*Plus permits.

Report message to a moderator

Previous Topic: Indexing and Tuning
Next Topic: General question on dbms_stats
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 04:09:29 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.