Home » RDBMS Server » Performance Tuning » Slow Performance (Oracle 11g Windows2000 Server)
Slow Performance [message #422689] Fri, 18 September 2009 06:41 Go to next message
IMGS_Oracle
Messages: 2
Registered: September 2009
Junior Member
Hi

We upgraded to Oracle 11g from Oracle 10g, and we are having slow performance problem, a query in 10g used to take about 7 seconds the same query in 11g is taking 2.5 min

This is the trace


TKPROF: Release 11.1.0.6.0 - Production on Fri Sep 18 12:23:59 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: fgs11gs_ora_2848.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

error connecting to database using: FISD/FISD
ORA-12560: TNS:protocol adapter error

EXPLAIN PLAN option disabled.
********************************************************************************

SQL ID : cyxpz2rbwnhtw
select mdsys.sdo_cs.disambiguate_proj_name(srid, wktext), srid
from
mdsys.cs_srs where srid = 8307


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 509 0.01 0.01 0 0 0 0
Execute 509 0.00 0.02 0 0 0 0
Fetch 510 0.37 0.39 0 3478 0 510
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1528 0.39 0.42 0 3478 0 510

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 102 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID SDO_CS_SRS (cr=3 pr=0 pw=0 time=0 us cost=2 size=448 card=1)
1 INDEX UNIQUE SCAN SYS_C004898 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 62791)

********************************************************************************

SQL ID : 8219npg1fjv1f
SELECT REGEXP_REPLACE( :B2 , 'PROJECTION \["[^"]+"\]', 'PROJECTION ["' ||
OPS.COORD_OP_NAME || '"]')
FROM
MDSYS.SDO_COORD_REF_SYS CRS, MDSYS.SDO_COORD_OPS OPS WHERE CRS.SRID = :B1
AND OPS.COORD_OP_ID = CRS.PROJECTION_CONV_ID


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 509 0.00 0.02 0 0 0 0
Fetch 509 0.01 0.00 0 1527 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1018 0.01 0.02 0 1527 0 0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 102 (recursive depth: 2)
********************************************************************************

SQL ID : d26kddnrwfzpk
SELECT SDO_RELATION
FROM
SDO_RELATEMASK_TABLE WHERE SDO_MASK = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 509 0.00 0.01 0 0 0 0
Fetch 509 0.00 0.00 0 1018 0 509
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1018 0.00 0.02 0 1018 0 509

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 102 (recursive depth: 1)
********************************************************************************

SQL ID : f45b0zrkfxh20
SELECT DIMINFO
FROM
ALL_SDO_GEOM_METADATA WHERE OWNER = :own AND TABLE_NAME = :tab AND
COLUMN_NAME = :col


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 508 0.10 0.10 0 0 0 0
Fetch 508 108.29 109.15 0 991108 0 508
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1016 108.40 109.25 0 991108 0 508

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 102 (recursive depth: 1)
********************************************************************************

SQL ID : 0000000000000
ALTER SESSION SET SQL_TRACE=FALSE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 102



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 509 0.01 0.01 0 0 0 0
Execute 2035 0.10 0.16 0 0 0 0
Fetch 2036 108.68 109.56 0 997131 0 1527
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4580 108.81 109.73 0 997131 0 1527

Misses in library cache during parse: 0

2036 user SQL statements in session.
0 internal SQL statements in session.
2036 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: fgs11gs_ora_2848.trc
Trace file compatibility: 11.01.00
Sort options: default

1 session in tracefile.
2036 user SQL statements in trace file.
0 internal SQL statements in trace file.
2036 SQL statements in trace file.
5 unique SQL statements in trace file.
13993 lines in trace file.
111 elapsed seconds in trace file.


Any advice?

Report message to a moderator

Re: Slow Performance [message #422815 is a reply to message #422689] Sat, 19 September 2009 15:52 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Nothing in that trace takes 2.5 min.

Report message to a moderator

Re: Slow Performance [message #422852 is a reply to message #422689] Sun, 20 September 2009 19:12 Go to previous messageGo to next message
linlasj
Messages: 98
Registered: August 2005
Location: Linköping
Member
IMGS_Oracle wrote on Fri, 18 September 2009 13:41
Hi

We upgraded to Oracle 11g from Oracle 10g, and we are having slow performance problem, a query in 10g used to take about 7 seconds the same query in 11g is taking 2.5 min

This is the trace


TKPROF: Release 11.1.0.6.0 - Production on Fri Sep 18 12:23:59 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Trace file: fgs11gs_ora_2848.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

error connecting to database using: FISD/FISD
ORA-12560: TNS:protocol adapter error


Any advice?
I am worried about the line "error connecting to database using: FISD/FISD
ORA-12560: TNS:protocol adapter error
" in the trace file above. However, it might not be relevant. Still worried...

Report message to a moderator

Re: Slow Performance [message #422862 is a reply to message #422852] Sun, 20 September 2009 23:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
linlasj wrote on Mon, 21 September 2009 02:12
Any advice?
I am worried about the line "error connecting to database using: FISD/FISD
ORA-12560: TNS:protocol adapter error
" in the trace file above. However, it might not be relevant. Still worried..

Just EXPLAIN option was given on TKPROF command line and database is not reachable at this time.
No relation with what's inside the trace file.

Regards
Michel

Report message to a moderator

Re: Slow Performance [message #423511 is a reply to message #422689] Thu, 24 September 2009 21:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
hmm.. how about posting some sqlplus runtime stats. Something simple like SET AUTOTRACE ON, run the sql, dump the plan. Just for those of us who do not do a lot of TKPROF work.

If I was to guess, and I am, I'd say the following:

1) my money is on the disambiguator function call

2) next the regular expression (though I hear these are faster in 11g than 10g (thanks Michel))

3) scary metadata makes me think "watch your step, slippery when generic table design at work".

4) the usual suspects, (no stats, missing indexes)

Good luck, Kevin

Report message to a moderator

Re: Slow Performance [message #496793 is a reply to message #423511] Wed, 02 March 2011 07:54 Go to previous messageGo to next message
martateixeira
Messages: 2
Registered: March 2011
Location: São Paulo
Junior Member
Hello!
How did you fix this problem?
After upgrade to Oracle 11g, we are having slow performance and apparently is the problem.
Thanks,
Marta

Report message to a moderator

Re: Slow Performance [message #498595 is a reply to message #496793] Thu, 10 March 2011 07:17 Go to previous messageGo to next message
marcodba
Messages: 5
Registered: August 2006
Junior Member
I guess you are focusing on this query:
SQL ID : f45b0zrkfxh20
SELECT DIMINFO
FROM
ALL_SDO_GEOM_METADATA WHERE OWNER = :own AND TABLE_NAME = :tab AND
COLUMN_NAME = :col


Could you provide us with the execution plan of this query on your 11g database?

Report message to a moderator

Re: Slow Performance [message #498601 is a reply to message #498595] Thu, 10 March 2011 07:57 Go to previous message
martateixeira
Messages: 2
Registered: March 2011
Location: São Paulo
Junior Member
Hello!

We found a bug reported in metalink:

Slow Performance when using SDO_RELATE/SDO_FILTER [ID 1268383.1]

we are doing some performance tests yet, but it's working fine for while.

Thanks,
Marta

Report message to a moderator

Previous Topic: Query tuning
Next Topic: prev_hash_value not changing in v$session
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 14:10:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.