Home » RDBMS Server » Performance Tuning » Unexplained SQL Slowness (AWR Supplied) (Oracle 11.2.4.0, Linux)
Unexplained SQL Slowness (AWR Supplied) [message #644114] Wed, 28 October 2015 05:31 Go to next message
ganna76
Messages: 4
Registered: October 2015
Location: Scotland
Junior Member
Hi All,

I am a developer who has to live without DBA rights, struggling to diagnose the reasons behind some very slow performance on our Oracle 11.2.0.4 database.

Background: We are in the middle of an application upgrade.

* Old environment runs v6 of a vendor product on Oracle 10.2.0.4.
* New environment runs v8 of a vendor product on Oracle 11.2.0.4.

All sorts of queries seem to be slower in the new environment. But as a simple example, we have a table TASSET which has about 120,000 rows in each environment (slightly more in Old). Sample query:

select count(*) from TASSET where DESCRIPTION like 'Swiss%';

In the Old environment it takes about 3 seconds. In the New environment it takes anywhere between 25 seconds and 210 seconds. They return a similar number of rows (111 v 121).

I have gathered stats in new UAT... I gathered them against a 100% sample size out of desperation but no difference.

Neither table has an index on the DESCRIPTION column so I'm aware this isn't efficient, however I cannot work out why it should be relatively so much slower in New. Note users also intermittently report slowness with all sorts of other tables, so (to me) it points to something more fundamental being wrong. The project has to wait weeks to get time from a DBA so I have my hands tied with much of the investigation, as I cannot perform many of the checks you would no doubt ask me to check before coming here for help.

I have attached an AWR from the DBA from 2 weeks back, which the vendor claims shows there is a high % of time being spent on IO Wait. Can you kind people give me your opinion? Note I am not able to produce an AWR at will myself because of access rights, so the one I have is 2 weeks old, but it still happened during a time of extreme slowness. Also note the SQL itself may be ugly, but (1) it's the same as the old vendor application which is fast and (2) as it's a vendor product, I can't tweak the SQL myself. Nevertheless I don't think the SQL is the problem here as even a simple SELECT/WHERE statement is taking minutes to complete.

Note the AWR in PDF format was over the allowed attachment size, so I have saved it as *txt and I'm suggesting you Save As into HTML to view the report.

Lastly in case it's relevant, here is the explain plan for my sample SQL in both Old and New environments. I am a bit troubled as to why the new explain plan expects to only check 2 rows and what SORT AGGREGATE means, but maybe that's nothing to do with it.

EXPLAIN PLAN FOR select count(*) from tasset where description like 'Swiss%';
SELECT * FROM table(dbms_xplan.display);


=================
Old Environment Explain Plan (fast environment where it takes 3 seconds and returns a count of 111):


plan FOR succeeded.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
Plan hash value: 2086953440

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 9920 | 6428 (1)| 00:01:18 |
|* 1 | TABLE ACCESS FULL| TASSET | 20 | 9920 | 6428 (1)| 00:01:18 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("DESCRIPTION" LIKE 'Swiss%')

13 rows selected

================
New Environment Explain Plan (slow environment where it takes between 25 and 210 seconds to run and returns a count of 121):


plan FOR succeeded.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
Plan hash value: 2535472451

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2145 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | 35 | | |
|* 2 | TABLE ACCESS FULL| TASSET | 2 | 70 | 2145 (1)| 00:00:26 |
-----------------------------------------------------------------------------

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

2 - filter("DESCRIPTION" LIKE 'Swiss%')

14 rows selected

Re: Unexplained SQL Slowness (AWR Supplied) [message #644115 is a reply to message #644114] Wed, 28 October 2015 05:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Your scans of a table that size may well be direct rather than indirect following your DB upgrade. Try this,
alter session set "_serial_direct_read"=never;
and then run your query a couple of times.

[Updated on: Wed, 28 October 2015 05:37]

Report message to a moderator

Re: Unexplained SQL Slowness (AWR Supplied) [message #644121 is a reply to message #644115] Wed, 28 October 2015 06:55 Go to previous messageGo to next message
ganna76
Messages: 4
Registered: October 2015
Location: Scotland
Junior Member
John Watson wrote on Wed, 28 October 2015 10:36
Your scans of a table that size may well be direct rather than indirect following your DB upgrade. Try this,
alter session set "_serial_direct_read"=never;
and then run your query a couple of times.


Magic. Now takes 1 second.

Can you explain to me what that parameter is doing? Although it helps the SQL I am running, it doesn't speed up the vendor application. Is there a way to give all sessions this by default? I'd rather not need to ask the vendor to change their code.

Also, are there any drawbacks to setting this to 'never'?
Re: Unexplained SQL Slowness (AWR Supplied) [message #644122 is a reply to message #644121] Wed, 28 October 2015 07:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I recorded a tutorial on this topic here,
Direct/indirect scans
you can of course set that parameter for the instance rather than just a session, but it is very much a hacker's quick fix. Not a real solution. You have to discuss it with your DBA. If you don't have one, my boss would be happy to quote Smile
--
John Watson
Oracle Certified Master DBA.
Re: Unexplained SQL Slowness (AWR Supplied) [message #644142 is a reply to message #644122] Wed, 28 October 2015 08:56 Go to previous messageGo to next message
ganna76
Messages: 4
Registered: October 2015
Location: Scotland
Junior Member
I am laughing because after my last post I went away and googled it and found that tutorial myself and watched it, didn't realise that was you. Very informative, thanks. We do have a DBA but he's in a huge demand and we all have to wait our turn.

I will take this info to the vendor though as it sounds like some of their SQL isn't working efficiently since the behaviour of this parameter has changed from when they coded the app originally against Oracle 10.

Thanks very much for your assistance.
Re: Unexplained SQL Slowness (AWR Supplied) [message #644151 is a reply to message #644142] Wed, 28 October 2015 10:19 Go to previous message
ganna76
Messages: 4
Registered: October 2015
Location: Scotland
Junior Member
Leaving aside this hidden parameter discussed above.... The other thing I should mention is that this (slow) table has 649 chained rows -- it has 229 columns, and 112,000 entries. So 649 is not a lot relatively speaking. Could under 1% of the population being chained have such a dramatic affect on performance?

I have asked the DBA about this before and he said that it was such a low volume of chained rows that it wouldn't be worth fixing them.
Previous Topic: A SQL query with self join and analytical function is running slow
Next Topic: Please help for tuning .sql query
Goto Forum:
  


Current Time: Thu Mar 28 17:59:45 CDT 2024