Help - Search - Members - Calendar
Full Version: SQL Performance 11g vs 10g
Oracle DBA Forums > Oracle > Oracle Forum
Simon Greener
Folks,

I am stumped and need some help.

One of my customers has Oracle 10gR2 Enterprise Edition. They have asked me to develop a small PL/SQL application of which there is a critical, single SQL statement that generates data from existing tables. This SQL statement cannot be posted to this forum due to sensitivity of what it does. But my problem is not that the query doesn't work, it does, and it works very, very fast...... but only on the 10gR2 database!

If I run a select count(*) from (.... complicated query ... ) on 10gRg in runs in a matter of seconds. If I insert /*+append*/ into <table> select ... complicated query... it completes in 10minutes. All of which is brilliantly acceptable.

However, I decided to install 11g on my personal computer in order to learn some of the new aspects of 11g. I loaded up my customer's data when I started developing the PL/SQL application for them. Initially the performance I was getting was similar to the 10gR2 customer database. Then, for some reason, it all went pear-shaped. My fast queries now run impossibly slow. For example, none of the queries terminate after 2 days of processing never mind 10minutes! I tried looking at v$session_longops view but my long running query never appeared. (My laptop is a Core2Duo machine with 4G Ram and 200G 7200RPM drive - I have never had any problems with Oracle running fast on my laptop - I am convinced it is something with the query/statistics.)

While the original query was hinted (USE_NLs, ALL_ROWS, INDEX) I still played around with the hints and explain plan to check execution. I also created up to date stats on not just the objects being queried but also all objects in the schemas involved.

Here is the latest explain plan for the same query on the 10gR2 database and the 11g database. I would appreciate any suggestions particularly how to identify what the problem is with a query when it is running.

11g
====

CODE
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3154020718
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |  7113K|   529M| 23951   (1)| 00:04:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID         | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX                       | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|   3 |  TABLE ACCESS BY INDEX ROWID         | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|*  4 |   DOMAIN INDEX                       | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|   5 |  TABLE ACCESS BY INDEX ROWID         | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN                  | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|   7 |  SEQUENCE                            | ZOI_GID_SEQ               |       |       |            |          |
|   8 |   VIEW                               |                           |  7113K|   529M| 23951   (1)| 00:04:48 |
|   9 |    NESTED LOOPS                      |                           |  7113K|   122M| 23951   (1)| 00:04:48 |
|* 10 |     TABLE ACCESS FULL                | WW_SECTION                |   871 | 13936 |   277   (2)| 00:00:04 |
|  11 |     COLLECTION ITERATOR PICKLER FETCH| GETPIPEDVECTOR            |       |       |            |          |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MDSYS"."SDO_NN"("S"."GEOMETRY","MDSYS"."SDO_GEOMETRY"(2001,82469,"SDO_POINT_TYPE"(TO_NUMBER
              (:B1),TO_NUMBER(:B2),NULL),NULL,NULL),'sdo_num_res=1')='TRUE')
   4 - access("MDSYS"."SDO_NN"("S"."GEOMETRY","MDSYS"."SDO_GEOMETRY"(2001,82469,"SDO_POINT_TYPE"(TO_NUMBER
              (:B1),TO_NUMBER(:B2),NULL),NULL,NULL),'sdo_num_res=1')='TRUE')
   6 - access("M"."FID"=:B1)
  10 - filter("P"."INPUT_DEPTH" IS NOT NULL AND "P"."OUTPUT_DEPTH" IS NOT NULL AND
              ("P"."ID_FUNCTION"=10004 OR "P"."ID_FUNCTION"=10007 OR "P"."ID_FUNCTION"=10008))

29 rows selected

10gR2
=====
CODE
select * from table(dbms_xplan.display);

explain plan succeeded.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 534504094

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |  7074K|   121M| 19491   (2)| 00:03:54 |
|   1 |  TABLE ACCESS BY INDEX ROWID        | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX                      | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|   3 |  TABLE ACCESS BY INDEX ROWID        | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|*  4 |   DOMAIN INDEX                      | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|   5 |  TABLE ACCESS BY INDEX ROWID        | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|*  6 |   INDEX UNIQUE SCAN                 | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID        | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|*  8 |   INDEX UNIQUE SCAN                 | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID       | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|* 10 |    DOMAIN INDEX                     | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|  11 |  TABLE ACCESS BY INDEX ROWID        | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|* 12 |   INDEX UNIQUE SCAN                 | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|  13 |   TABLE ACCESS BY INDEX ROWID       | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|* 14 |    DOMAIN INDEX                     | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|  15 |  TABLE ACCESS BY INDEX ROWID        | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|* 16 |   INDEX UNIQUE SCAN                 | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|  17 |   TABLE ACCESS BY INDEX ROWID       | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|* 18 |    DOMAIN INDEX                     | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|  19 |    TABLE ACCESS BY INDEX ROWID      | WW_SECTION_MODEL          |     1 |     9 |     1   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN               | WW_SECTION_MODEL_P        |     1 |       |     0   (0)| 00:00:01 |
|  21 |     TABLE ACCESS BY INDEX ROWID     | SP_SOIL_COHESIVENESS      |     1 |    97 |     3   (0)| 00:00:01 |
|* 22 |      DOMAIN INDEX                   | SP_SOIL_COHESIVENESS_GEOM |       |       |            |          |
|  23 |  SEQUENCE                           | ZOI_GID_SEQ               |       |       |            |          |
|  24 |   NESTED LOOPS                      |                           |  7074K|   121M| 19491   (2)| 00:03:54 |
|* 25 |    TABLE ACCESS FULL                | WW_SECTION                |   866 | 13856 |   233   (5)| 00:00:03 |
|  26 |    COLLECTION ITERATOR PICKLER FETCH| GETPIPEDVECTOR            |       |       |            |          |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
   4 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
   6 - access("M"."FID"=:B1)
   8 - access("M"."FID"=:B1)
  10 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
  12 - access("M"."FID"=:B1)
  14 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
  16 - access("M"."FID"=:B1)
  18 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
  20 - access("M"."FID"=:B1)
  22 - access("MDSYS"."SDO_NN"("S"."GEOMETRY",,'sdo_num_res=1')='TRUE')
  25 - filter("P"."INPUT_DEPTH" IS NOT NULL AND "P"."OUTPUT_DEPTH" IS NOT NULL AND
              ("P"."ID_FUNCTION"=10004 OR "P"."ID_FUNCTION"=10007 OR "P"."ID_FUNCTION"=10008))

50 rows selected


Thanks in advance
Simon
dave
are the stats different - have you tried copying the stats from 10g to 11g to see if it maintains thee prformance?
Simon Greener
QUOTE (dave @ Jul 1 2008, 05:51 PM) *
are the stats different - have you tried copying the stats from 10g to 11g to see if it maintains thee prformance?


No Dave I haven't tried that. How would I do it?

And, even if I do this, why the radical performance difference? I mean I think I could calculate the result by hand in less than the 2 days I let it run on 11g!

regards
S
HAL9000
Hi Simon,

You are querying from a view?

Can you run the SQL from SQL*Plus with "set autotrace on"? That will show execution stats, and give a clew about the performance differences!

"No Dave I haven't tried that. How would I do it?"

http://www.dba-oracle.com/t_dbms_stats.htm

Step 1: Create the stats_table:

exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats', - >
tblspace => 'SYSTEM');

Step 2: Gather the statistics with gather_system_stats. In this dbms_stats example, we compute histograms on all indexed columns:

DBMS_STATS.gather_schema_stats(
ownname=>’<schema>’,
estimate_percent=>dbms_stats.auto_sample_size
cascade=>TRUE,
method_opt=>’FOR ALL COLUMNS SIZE AUTO’)

Step 3: Export the stats to the prod_stats table using export_system_stats::

exec dbms_stats.export_system_stats(ownname => 'SYS', stattab => 'prod_stats');

Step 4: Export the stats to the prod_stats table using exp:

exp scott/tiger file=prod_stats.dmp log=stats.log tables=prod_stats rows=yes

Step 5: FTP to the production server:

ftp -i prodserv . . .

Step 6: Import the stats from the prod_stats.dmp table using the import (imp) utility:

imp scott/tiger file=prod_stats.dmp log=stats.log tables=prod_stats rows=yes


Also, make sure that all optimizer parms are identical, and that you have gathered system statistics.


"it does, and it works very, very fast...... but only on the 10gR2 database!"

That happens on every release, major changes to the SQL optimizer.
Simon Greener
Thanks

> You are querying from a view?

No, but there are lots of in-line views being used.

> Can you run the SQL from SQL*Plus with "set autotrace on"? That will show execution stats, and give a clew about the performance differences!

Doing so. Just waiting till it finishes before posting.

> Step 6: Import the stats from the prod_stats.dmp table using the import (imp) utility:

Thanks. Will do once query finishes.

> That happens on every release, major changes to the SQL optimizer.

Hmmm this has cost me days and days. Far more than an uninstall 11g and reinstall of 10g. The sql optimizer hints for 10g seem to summarise the approach fairly well. I will be interest to see what finally makes 11g run fast.

regards
S
HAL9000
Are you current with the 10g optimizer changes?

http://www.dba-oracle.com/t_slow_performan...ter_upgrade.htm
Simon Greener
Thanks for all your help. I am sorry that I have not gotten back to this forum earlier as I ended up spending two weeks on another contract.

But, in summary, the main problem turned out to be that I was using a PL/SQL table function that I wrote a few years ago which, after being recompiled for 11g, decided not to generate a limited set of vectors for a single sdo_geometry object, rather it decided to go into an infinite loop:

CODE
SELECT /*+USE_NL(p v)*/
       p.fid,
....
FROM mcw_sewer.ww_section p,
    TABLE(mcw_sewer.zoi.GetVector(
           SDO_LRS.convert_to_lrs_geom((SELECT /*+ INDEX(l ww_line_fid_attr) */
                                               SDO_AGGR_CONCAT_LINES(l.geom)
                                          FROM mcw_sewer.ww_line l
                                         WHERE l.FID_ATTR = p.FID
                                       ),
                                       p.input_depth,
                                       p.output_depth)
                                 )
         ) v
WHERE ...


I did not see this problem because I was using SQL Developer to code and test. It was only by returning back to good-old sqlplus to implement the suggested "set autotrace on" that I saw the SQL spewing onto the screen the same numbers! (Perhaps I should go back to using VI and SQLPLUS as my principle tools: I have moved to SQL Developer because of the good PL/SQL development tools and the built in EXPLAIN PLAN!)

I didn't fancy going back into the PL/SQL to find where the problem occurred (I would say a variable is not being correctly initialised and causing a loop to go infinite), rather I took a different tack that allowed me to isolate the offending elements of the PL/SQL until I had time to search for and correct the problem.

So, now the SQL runs as it originally did on 10gR2. However, on 10gR2 it still runs in 10minutes but on 11g I can't get it to run faster than 30 minutes. Yes, I need to follow up on the statistics suggestions but the customer is happy and that is the most important thing.

I know this is a bit belated, but I want to thank those who gave me the suggestion that, inadvertantly, helped me find the problem. Your input is GREATLY APPRECIATED.

regards
Simon
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.