Home » RDBMS Server » Performance Tuning » Newbie: SQL runs slow on 10g, not on 9i
Newbie: SQL runs slow on 10g, not on 9i [message #285335] Tue, 04 December 2007 04:50 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

Using a application for handeling orders and supplie status.

If I use a 9i DB it only takes a few seconds to create a new order entry. But when Im using 10g it takes several minutes, up to 10min.
I have a used SQL-tracing and I have a trace-file for the session which causes the problem. 12mb of size.

But what to do with it? There are a lot of waits in it:

PARSING IN CURSOR #1 len=391 dep=0 uid=2852 oct=3 lid=2852 tim=5275963435 hv=1570671080 ad='9a13edf8'
SELECT "ORDER_MESSAGE"."INTERNAL_ORDER_ID",
"ORDER_MESSAGE"."MESSAGE_PICKER",
"ORDER_MESSAGE"."MESSAGE_RECEIVER_BUYER",
"ORDER_MESSAGE"."MESSAGE_DELIVERY",
"ORDER_MESSAGE"."MESSAGE_SHIPPER",
"ORDER_MESSAGE"."MESSAGE_BUYER"
FROM "ORDER_MESSAGE"
WHERE "ORDER_MESSAGE"."INTERNAL_ORDER_ID" = :ldec_internal_id
END OF STMT
PARSE #1:c=0,e=294,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5275963432
WAIT #1: nam='library cache lock' ela= 212 handle address=8794391258112 lock address=8794563271432 100*mode+namespace=301 obj#=-1 tim=5275963882
EXEC #1:c=0,e=1351,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5275964876
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5275964937

After this there a several thousands of this kind of lines (WAIT #1).

So, what do I do with this information?

Plz help a newbie! Smile

[Updated on: Tue, 04 December 2007 04:51]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #285370 is a reply to message #285335] Tue, 04 December 2007 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use tkprof, it will compile it for you.

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #285422 is a reply to message #285335] Tue, 04 December 2007 08:48 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi again and thanks for your help.

I now have a file from TKPROF. I have found some strange SQL that takes up to 3min to complete. Se below:

SELECT "ORDER_LINE_EDI_X"."INTERNAL_ORDER_ID" ,
"ORDER_LINE_EDI_X"."ORDER_LINE_NO" , "ORDER_LINE_EDI_X"."DIS_TAG"
, "ORDER_LINE_EDI_X"."CONTENT"
FROM
"ORDER_LINE_EDI_X" WHERE ( "ORDER_LINE_EDI_X"."INTERNAL_ORDER_ID" =
:adec_internal_order_id )


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 1 111.62 174.66 325724 332438 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 111.62 174.66 325724 332438 0 0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 2852

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL ORDER_LINE_EDI_X (cr=332438 pr=325724 pw=0 time=174662587 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file scattered read 13019 0.34 66.32
gc cr multi block request 6720 0.00 0.53
db file parallel read 42 0.12 0.63
gc current block 2-way 31 0.00 0.01
db file sequential read 3 0.00 0.00
SQL*Net message from client 1 0.00 0.00

So, are there any good link for me to read so I now how to improve the SQL? We are bind variables. Maybe I should tune the memory settings for the instance? Only set the SGA_target?

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #285423 is a reply to message #285422] Tue, 04 December 2007 08:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
When was the last time the statistics updated?
What statements did you use to update statistics?
Can you collect new stats on the concerned table (and its indexes) and try again?
Re: Newbie: SQL runs slow on 10g, not on 9i [message #285424 is a reply to message #285422] Tue, 04 December 2007 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to Identify Performance Problem and Bottleneck

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #286055 is a reply to message #285335] Thu, 06 December 2007 06:42 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I have found that when running on a 9i DB the SQL uses indexes but on 10g it dont. Is there an easy newbie-solution for this? Embarassed
Re: Newbie: SQL runs slow on 10g, not on 9i [message #286204 is a reply to message #286055] Thu, 06 December 2007 19:46 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes there is. Use DBMS_STATS.GATHER_TABLE_STATS to analyse the table, then run it again.

Ross Leishman
Re: Newbie: SQL runs slow on 10g, not on 9i [message #286407 is a reply to message #286204] Fri, 07 December 2007 05:03 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I googled and looked at OTN, but havent found anything good about how to use DBMS_STATS.GATHER_TABLE_STATS.
Can anyone give me some pointers?
Re: Newbie: SQL runs slow on 10g, not on 9i [message #286424 is a reply to message #286407] Fri, 07 December 2007 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about documentation?
There is a book with (almost) all PL/SQL packages.
What about http://otn.oracle.com/pls/db102/db102.federated_search
I don't you really search.

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288593 is a reply to message #285335] Tue, 18 December 2007 02:33 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi again!

I have run DBMS_STATS.GATHER_TABLE_STATS on the tables. So all tables are analyzed. The indexs exists. But the following query takes almsot three minutes:

SELECT ORDER_LINE_EDI_X.INTERNAL_ORDER_ID,
ORDER_LINE_EDI_X.ORDER_LINE_NO, ORDER_LINE_EDI_X.DIS_TAG,ORDER_LINE_EDI_X.CONTENT
FROM
ORDER_LINE_EDI_X WHERE (ORDER_LINE_EDI_X.INTERNAL_ORDER_ID=
:adec_internal_order_id);

I have an explan plan and its using its index. But when running from our application it looks like this:

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL ORDER_LINE_EDI_X (cr=332438 pr=332432 pw=0 time=175728015 us)

Optimizer mode: ALL_ROWS
I have change the optimizer mode (first, choose, rule) and re-ran the SQL and its still slow.

And this is from the explain plan:

Step # Step Name
3 SELECT STATEMENT
2 L2000.ORDER_LINE_EDI_X TABLE ACCESS [BY INDEX ROWID]
1 L2000.PK_ORDER_LINE_EDI_X INDEX [RANGE SCAN]

So, any ideas why the SQL is not using the index when running from our application? But when running the SQL in explain plan the index is being used.
What would be the next step for a newbie?

[Updated on: Tue, 18 December 2007 02:36]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #288597 is a reply to message #288593] Tue, 18 December 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because run time estimates it is better to use FTS or because it reuses a plan from a previous query.
Did you also gather statistics on indexes?

Regards
Michel

[Updated on: Tue, 18 December 2007 02:49]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #288603 is a reply to message #288597] Tue, 18 December 2007 03:03 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

"Did you also gather statistics on indexes?"
I ran the following:

dbms_stats.gather_index_stats
and
dbms_stats.gather_table_stats

So, I guess, yes, I have. Smile

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288607 is a reply to message #288603] Tue, 18 December 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't guess, verify.

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288614 is a reply to message #288607] Tue, 18 December 2007 03:24 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Statistics
Last Analyzed 13-Dec-2007 17:18:45
Blevel 3
Distinct Keys 88542219
Clustering Factor 1936371
Leaf Blocks 330450
Average Leaf Blocks Per Key 1
Average Data Blocks Per Key 1
Number of Rows 88542219
Sample Size 317246

Thats for the index. So, yes, I have.

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288619 is a reply to message #288614] Tue, 18 December 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you see the clustering factor?
What is the number of blocks and rows in the table?
What is the index definition?
Is it unique or not?
It seems (Distinct Keys = Number of Rows)
but in this case how explain plan can give RANGE SCAN? Surely because INTERNAL_ORDER_ID is not the only column of the index.

BUT WHY HAVE I TO GUESS ALL THAT? WHY DON'T YOU POST ALL THE INFORMATION?


Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288638 is a reply to message #288619] Tue, 18 December 2007 04:09 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

Well, I did post the info found under the "statistics tab" in OEM. I thougth that would be enough. I applogies for my behavior.
This is all the info I found:

General
Name PK_ORDER_LINE_EDI_X
Schema L2000
Tablespace USER_INDEX
Index Type Normal
Status VALID

Indexed Table Object
Index On Table
Schema L2000
Name ORDER_LINE_EDI_X

Index Columns

Column Name Data Type Sorting Order
INTERNAL_ORDER_ID NUMBER ASC
ORDER_LINE_NO NUMBER ASC
DIS_TAG VARCHAR2 ASC

Storage

Tablespace
Name USER_INDEX

Extent Management Local

Segment Management Automatic

Allocation Type SYSTEM

Logging Yes

Space Usage
Free Space (PCTFREE)(%) 10

Number of Transactions
Initial 2
Maximum 255

Buffer Pool
Buffer Pool DEFAULT

Options
Index Options UNIQUE

***************************************************************
Are there any more info to be found?
Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288644 is a reply to message #288638] Tue, 18 December 2007 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288645 is a reply to message #288638] Tue, 18 December 2007 04:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT ORDER_LINE_EDI_X.INTERNAL_ORDER_ID, 
ORDER_LINE_EDI_X.ORDER_LINE_NO, ORDER_LINE_EDI_X.DIS_TAG,ORDER_LINE_EDI_X.CONTENT 
FROM
ORDER_LINE_EDI_X WHERE (ORDER_LINE_EDI_X.INTERNAL_ORDER_ID= 
:adec_internal_order_id);

I have an explan plan and its using its index. But when running from our application it looks like this:

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL ORDER_LINE_EDI_X (cr=332438 pr=332432 pw=0 time=175728015 us)


This query uses a Bind Variable. Oracle does not know what value you are going to provide for the bind variable, so it doesn't know whether it will return a few rows or a lot.

When you run it through Explain Plan (and it shows an index), I bet you are replacing the bind variable with a constant value. Now Oracle KNOWS the value and can make a more informed decision.

If you left the bind variable in when you used Explain Plan, you would see the FTS.

The fact that Oracle chooses a FTS probably means that SOME values would be better off with a FTS and SOME values would be better with an index. This is skewed data. Do you have some values that repeat a lot?

You can help Oracle out by telling it how many rows YOU think it will return using the CARDINALITY hint.
SELECT /*+CARDINALITY(ORDER_LINE_EDI_X, 100)*/
ORDER_LINE_EDI_X.INTERNAL_ORDER_ID, 
ORDER_LINE_EDI_X.ORDER_LINE_NO, ORDER_LINE_EDI_X.DIS_TAG,ORDER_LINE_EDI_X.CONTENT 
FROM
ORDER_LINE_EDI_X WHERE (ORDER_LINE_EDI_X.INTERNAL_ORDER_ID= 
:adec_internal_order_id);

When it has better information, Oracle will make better decisions.

Ross Leishman
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288659 is a reply to message #288644] Tue, 18 December 2007 04:36 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
 
General
Name		PK_ORDER_LINE_EDI_X
Schema		L2000
Tablespace	USER_INDEX
Index Type	Normal
Status		VALID
	
Indexed Table Object
Index On	Table
Schema		L2000
Name		ORDER_LINE_EDI_X
	
Index Columns
	
Column Name	     Data Type	       Sorting Order
INTERNAL_ORDER_ID	NUMBER	         ASC
ORDER_LINE_NO	        NUMBER	         ASC
DIS_TAG	                VARCHAR2         ASC
	
Storage
	
Tablespace
Name		        USER_INDEX
		
Extent Management	Local
		
Segment Management	Automatic
		
Allocation Type		SYSTEM
		
Logging		        Yes
	
Space Usage
Free Space (PCTFREE)(%)		10
	
Number of Transactions
Initial		2
Maximum		255

Buffer Pool		DEFAULT
	
Index Options		UNIQUE
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288672 is a reply to message #288645] Tue, 18 December 2007 04:57 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi and thanks for your reply.

Im using SQL Scratch Pad and I have not givin the BV any value. But it still uses the index. But if I try to execute the SQL with the button 'execute' I get an error that a value is missing for the index. (in or out parameter is missing for index).
Yes, INTERNAL_ORDER_ID repeats alot.

I tried using your code with Cardinality in SQL Developer. And it asks for a value for the Bind V. I dont give any and it returns zero rows.

So, what to do now? Should the developer start using cardinality in their code?
Thanks for helping a newbie.

Regards
H

[Updated on: Tue, 18 December 2007 06:20]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #288831 is a reply to message #288672] Tue, 18 December 2007 21:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You don't supply a value to view the plan, but you do supply a value when you run it.

Put in a value when you run it with the CARDINALITY hint and see if it is faster.

Ross Leishman
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288947 is a reply to message #288831] Wed, 19 December 2007 02:12 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi and thanks for helping!

The problem is that this particular SQL-query is part of many. So when I run only this query it runs very fast. I dont have access to the application (where to make the changes).

I tried your code and gave the BV a value (that I now exists). It gives me:

ORA-01722: invalid number
01722.0000 -invalid number
cause
action
error at line:1

So, line 1 looks like this:
SELECT /*+CARDINALITY(ORDER_LINE_EDI_X, 100)*/

Is that the correct syntax?

Regards
Peter
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288951 is a reply to message #288947] Wed, 19 December 2007 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A space is required after /*+ and before */

Regards
Michel
Re: Newbie: SQL runs slow on 10g, not on 9i [message #288954 is a reply to message #288951] Wed, 19 December 2007 02:30 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!
Now the code looks like this:
SELECT /*+ CARDINALITY(ORDER_LINE_EDI_X, 100) */

But the error still occurs.

Regards
Peter
Re: Newbie: SQL runs slow on 10g, not on 9i [message #289153 is a reply to message #288954] Wed, 19 December 2007 21:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is that the only query in your editor? Any other comment leaders '/*' or '*/' lying about?

Try replacing the bind variable with a constant to see if you can get it working. Then change it back later when you've found the problem.

Paste the ENTIRE query here if you still can't get it working.

Ross Leishman
Re: Newbie: SQL runs slow on 10g, not on 9i [message #289205 is a reply to message #289153] Thu, 20 December 2007 03:33 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

The query works well when I supply a constant. (used in SQL Scratch Pad)

But when tries to run it with a Bind Var get an error that the index are missing a value.

When I use the application there are many SQL-queries being run. But two of them dont use index. One of them have I posted here on the board.
We have another DB that are 10g and the same application runs fine against that one (doing the same).

I have checked that the indexes have statistics and are analyzed.
The optimizer_mode is the same on both DBs.
So, how do I make the query to use the index?

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #289555 is a reply to message #289205] Mon, 24 December 2007 00:19 Go to previous messageGo to next message
jeffrey_hu
Messages: 2
Registered: December 2007
Junior Member
Please check those oracle parameters:
Parameter_name Type Suggestion_value
optimizer_index_caching integer 90
otimizer_index_cost_adj integer 1
optimizer_mode string CHOOSE

when i increase the otimizer_index_cost_adj value, i found some query don't use index, so i set this value equal 1.

maybe can help you.
Re: Newbie: SQL runs slow on 10g, not on 9i [message #291922 is a reply to message #289555] Mon, 07 January 2008 04:33 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I have changed the value of optimizer_index_cost_adj but that didnt help.

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #293879 is a reply to message #285335] Tue, 15 January 2008 07:34 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Try with this

optimizer_index_cost_adj = 10

else finally check with this...

ALTER SESSION SET optimizer_features_enable = '9.2.0'



- Atul

[Updated on: Tue, 15 January 2008 07:35]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #293884 is a reply to message #293879] Tue, 15 January 2008 07:56 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I have tried different values for optimizer_index_cost_adj. Nothing have helped. Changing optimizer_features_enable feels like step back. Why upgrade if we cant use 10g features (a better optimizer)?

Regards
H
Re: Newbie: SQL runs slow on 10g, not on 9i [message #294600 is a reply to message #285335] Fri, 18 January 2008 02:31 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

A collegue of mine have find this:


This led me to believe it had something to do with the bind variables so I focussed on this area this morning and changed a few more parameters. Again to no effect. But it did help me find another way to check what Oracle is doing. And to my surprise I noticed that somehow the queries executed in the database were 'rewritten'. Unfortunately this rewritten statement is not shown in the trace file so I did not notice it before. The rewritten statement somehow adds a to_binary_double function around a column of the number type. But that automatically invalidates the use of indexes. The workaround I implemented is that I now created so called function based indexes. So if normally table orders has an index on internal_order_id, I now created a function based index to_binary_double(interrnal_order_id) for orders as well. Now it can use the index again.


Any one knows WHY Oracle does this?

Regards
H

[Updated on: Fri, 18 January 2008 02:33]

Report message to a moderator

Re: Newbie: SQL runs slow on 10g, not on 9i [message #294789 is a reply to message #294600] Sat, 19 January 2008 04:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Because that is the datatype of the bind variable in your client program?
Previous Topic: Delete data and transaction log problem
Next Topic: DBMS_STATS.gather_table_stats
Goto Forum:
  


Current Time: Thu Dec 08 18:23:28 CST 2016

Total time taken to generate the page: 0.10717 seconds