| Unexplained SQL Slowness (AWR Supplied) [message #644114] |
Wed, 28 October 2015 05:31  |
 |
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 #644151 is a reply to message #644142] |
Wed, 28 October 2015 10:19  |
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
