| Newbie: SQL runs slow on 10g, not on 9i [message #285335] |
Tue, 04 December 2007 04:50  |
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! 
[Updated on: Tue, 04 December 2007 04:51] Report message to a moderator |
|
|
|
|
|
| Re: Newbie: SQL runs slow on 10g, not on 9i [message #285422 is a reply to message #285335] |
Tue, 04 December 2007 08:48  |
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 #288593 is a reply to message #285335] |
Tue, 18 December 2007 02:33 |
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 #288614 is a reply to message #288607] |
Tue, 18 December 2007 03:24 |
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 #288638 is a reply to message #288619] |
Tue, 18 December 2007 04:09 |
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 #288645 is a reply to message #288638] |
Tue, 18 December 2007 04:25 |
rleishman
Messages: 3728
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 |
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 |
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 #288947 is a reply to message #288831] |
Wed, 19 December 2007 02:12 |
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 #289153 is a reply to message #288954] |
Wed, 19 December 2007 21:26 |
rleishman
Messages: 3728
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 |
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 |
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 #294600 is a reply to message #285335] |
Fri, 18 January 2008 02:31 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
