Home » RDBMS Server » Performance Tuning » SQL query performance issue
SQL query performance issue [message #347319] |
Thu, 11 September 2008 05:17 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kirtani
Messages: 8 Registered: August 2008
|
Junior Member |
|
|
SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init init,xxe_f749_init_history history
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number
FROM xxe_f749_init_history
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND SUBSTR(resolution_time(history.init_number),1,1)='0'
Hi All,
Above pasted is a query i am facing performance issue with.
Query takes in all 40 min to execute.If i remove the last AND clause(SUBSTR(resolution_time(history.init_number),1,1)='0') it executes in a minutes time.
Any help on this would of great help.
Regards
[Updated on: Thu, 11 September 2008 05:20] Report message to a moderator
|
|
|
|
|
Re: SQL query performance issue [message #347334 is a reply to message #347322] |
Thu, 11 September 2008 05:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kirtani
Messages: 8 Registered: August 2008
|
Junior Member |
|
|
[COLOR=darkred]SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init init,xxe_f749_init_history history
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number
FROM xxe_f749_init_history
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND SUBSTR(resolution_time(history.init_number),1,1)='0'
In the above code i am also selecting the output of resolution_time function.Output from the function doesnt takes time.Issue comes when i put a check(AND clause) on the output of the function
|
|
|
Re: SQL query performance issue [message #347340 is a reply to message #347334] |
Thu, 11 September 2008 05:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dwarak.k
Messages: 61 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Can you try some thing like this
I am not sure this would help.just believing the function would be executed once instead of twice
select * from (SELECT history.init_number,history.current_administrator,init.CLASS,SUBSTR(resolution_time(history.init_number),1,1) as res_time
FROM xxe_f749_init init,xxe_f749_init_history history
WHERE history.Init_Number=init.Init_Number
AND (history.create_date_sec,history.init_number) IN (
SELECT MAX(create_date_sec),init_number
FROM xxe_f749_init_history
GROUP BY init_number)
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST')
where res_time ='0'
[Updated on: Thu, 11 September 2008 05:49] Report message to a moderator
|
|
|
|
Re: SQL query performance issue [message #347364 is a reply to message #347341] |
Thu, 11 September 2008 06:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
dwarak.k
Messages: 61 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
You can use alias name if it is part of the inline view.
select * from
(select employee_id , salary+1000 as revised_sal
from employees)
where revised_sal < 3500
EMPLOYEE_ID REVISED_SAL
127 3400
128 3200
132 3100
135 3400
136 3200
Post the error you encounter and the code you are trying
[Updated on: Thu, 11 September 2008 06:48] Report message to a moderator
|
|
|
|
Re: SQL query performance issue [message #347416 is a reply to message #347334] |
Thu, 11 September 2008 10:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The function in the where clause may well be executed for each row of the table. The row in the Select clause will only be executed for the rows that meet all of the other criteria.
You said yourself that removing the function from the Where clause speeds up the query by a actor of 40 - what more would it take to make you suspect that this funtion is a large part of the problem?
|
|
|
Re: SQL query performance issue [message #347464 is a reply to message #347416] |
Thu, 11 September 2008 13:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Commit JR's comment to memory -
Quote: | what more would it take to make you suspect that this funtion is a large part of the problem?
|
Isolating what criteria in a query is the root of a problem is the whole battle and you're at this point.
Provide the info Michel and JR requested:
Quote: | The function RESULUTION_TIME is where all the time is being consumed.
Can you post the code of that function?
|
Ater reading the forum post format guidelines -
Quote: | provide the requested and usual information (like execution plans).
|
My suggestion is to then measure what overhead is from the
SUBSTR & User Function vs the change in execution plan as a whole.
ie - You could remove SUBSTR & Resolution_History and test just the overhead of the extra criteria, and with the explain plans, weight the addition of a functional based index (if feasible) for that field against modification of the query for access approach.
Regards,
Harry
|
|
|
|
|
|
|
Re: SQL query performance issue [message #348399 is a reply to message #347319] |
Tue, 16 September 2008 11:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
oracle global temporary tables are ... ( can say that word in public)
if I need to use temporary table in lots of stored procedures, or scripts i need to create zilion global temporary tables for my needs, or i need to create 'UNIVERSAL' global temporary table with large column count, and diferent column types.
but if i need in one sript use 10 temporary tables (using temporary tables in lots of times helps to speed up query meny times) so i need minimum 10 global temporary tables....
so MS SQL LOCAL and GLOBAL temporary tables are BETTER!!! and you have any arguments about that.
|
|
|
|
Re: SQL query performance issue [message #348403 is a reply to message #347319] |
Tue, 16 September 2008 11:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
you need read less 'oracle' books.
oracle sub selects are nightmare, you need to use lots of hints like no_unnest ant etc, and it WONT WORK FAST like it can work if I use temporary tables.
oracle concept is wrong in many cases, but wrong concept makes BIG MONEY for conlulting, suppurt, powerfull servers, and writing books about nothing that helps.
if you do not know how to work with local temporary tables, and how it is easy, and how it speed up query performance, so beter you do not say a word.
in my case, i have writen seect from 90 tables (not distinct table names) or 30 tables/subselects
it is so HARD to tune that query...
so if I use 10-20 local temporary tables (with tables prefiltering, online temporary table indexation on needed colums) , I speeded up query about 100 times (included all indexation and temporary table creation time), and server has a minimum load at script execution time.
[Updated on: Tue, 16 September 2008 11:46] Report message to a moderator
|
|
|
|
Re: SQL query performance issue [message #348413 is a reply to message #347319] |
Tue, 16 September 2008 11:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
I havent seen any your posts, where I can see that you know how to write query in oracle.
if you working with ~20GB database, that explains all....
And do not tell me that oracle official bull shit, that temporary tables is better do not use.
there is 1000 examples that they wrong.
My time costs money, and if I need 10 times to write query, which is 10 times slower (then others write te same query it runs 100 times slower) than in MS SQL SERVER so what I can say?
I want to not use hints, but oracle optimiser is so dumb, so i need to show to him how query must be executed.
statistics do not helps and other things do not helps...
it uses indexes whent it do not need to do that, and when it is need to use indexes it do not use them...
so oracle is BIG BUG.
[Updated on: Tue, 16 September 2008 12:04] Report message to a moderator
|
|
|
|
Re: SQL query performance issue [message #348436 is a reply to message #348417] |
Tue, 16 September 2008 14:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
Quote: | If you need hints then you don't know how to write a query
|
Hi all,
Is that always true? I have found some performance issues while tuning queries with joins.I initially tried creating index.But creating index for my query was not a solution as i cvould see some of queries giving worst perfortmance..and that was resolved using hints.
sometime optimizer can't choose the best plan and hints are used if you know more about your data than oracle then hints an be used.Thats what my understanding is. Is that always true that where there is performnace issue for the skewed data there is no need to use hint.
Your suggesions will help...
Regards,
OLI
Regards,
Oli
[Updated on: Tue, 16 September 2008 14:29] Report message to a moderator
|
|
|
Re: SQL query performance issue [message #348445 is a reply to message #347319] |
Tue, 16 September 2008 15:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Quote: | but oracle optimiser is so dumb, so i need to show to him how query must be executed.
statistics do not helps and other things do not helps...
it uses indexes whent it do not need to do that, and when it is need to use indexes it do not use them...
so oracle is BIG BUG.
|
Perhaps it is just that your goals and Oracle's are out of synch. I have been in this situation recently.
Before drawing this conclusion, it is worth investigating
why Oracle makes different decisions than the yours (when you
plan provides optimal results).
My DEV environ is tuned for OLTP so i re-adjust the weights,
and expected buffering in my session to "resync" my goals with
Oracle.
(I feel obliged to give credit to rleishman for giving me the 'goal' perspective for these situations).
My high volume queries always start with
ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 0;
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;
After that, when I want to hash join so does Oracle -
and when we disagree I usually come up short.
Regards
Harry
|
|
|
Re: SQL query performance issue [message #348453 is a reply to message #347319] |
Tue, 16 September 2008 16:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
satm2008
Messages: 10 Registered: September 2008 Location: Toronto, Canada
|
Junior Member |
|
|
Let me put it this way. First you placed the tables in wrong (ie, against right-to-left) manner. In ORACLE, the driver table appears right and the child/driven table appears left. And second, the search-criteria normally begins at bottom-to-top so you should pick the matching/required parents records first and then let it check child against already-picked-parent records. In that manner, the search would be quicker and with better performance. Lets try it out with the following and find out the outcome.
And I recommend (though am not sure if it is proven), to put checked-against-checking as in
WHERE Child = Parent
AND Parent = ‘What_I_am_looking for’ ;
This would be easier for index based column search thus better performance.
Try the following and post the outcome.
SELECT history.init_number,
history.current_administrator,
init.CLASS,
SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init_history history, -- DETAIL, PLACE IT LEFT
xxe_f749_init init -- DRIVER, PLACE IT RIGHT MOST
WHERE
(history.create_date_sec, history.init_number)
IN (
SELECT MAX(create_date_sec),
init_number
FROM xxe_f749_init_history
GROUP BY init_number )
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND SUBSTR(resolution_time(history.init_number),1,1)='0'
AND history.Init_Number=init.Init_Number -- now find matching child for that parent
AND SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND init.CLASS IN ('1/A','2/B','3/C','4/D') –- place all parent-checks first
;
Good luck
[Updated on: Tue, 16 September 2008 16:42] Report message to a moderator
|
|
|
|
Re: SQL query performance issue [message #348482 is a reply to message #347319] |
Tue, 16 September 2008 23:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
harrysmall3, atm I have iteresting problem
I have staging area (transportable tablespace from OLTP system with tables and indexes and etc).
selecting from staging area tables and joining them i need to use hash join, because nested loops and index range scans have huge performance hit.
so system parameter OPTIMIZER_INDEX_COST_ADJ is set to 100.
but then optimizer wont use my bitmap indexes in DW tables, it start use it if OPTIMIZER_INDEX_COST_ADJ = 30, so I cant use the alter session statement because in query is used OLTP staging area tables and DW tables together.
I need to use hints to show how to use one and other table for optimiser.
this problem is show up not the long time ago, so there is posibility that DBA do something with server.
DBA is black box, I cant tune queries for performance without hints, because execution plans dramaticaly changing every week.
|
|
|
Re: SQL query performance issue [message #348495 is a reply to message #347319] |
Tue, 16 September 2008 23:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
table placing is critical only in RBO, in CBO optimizer replacing tables like he want why making execution plan.
please try this queries
SELECT history.init_number,
history.current_administrator,
init.CLASS,
SUBSTR(resolution_time(history.init_number),1,1)
FROM xxe_f749_init init
INNER JOIN xxe_f749_init_history ON history.Init_Number=init.Init_Number
AND history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE','ALPHA MAINT ONSITE')
AND SUBSTR(resolution_time(history.init_number),1,1)='0'
LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
init_number in_no
FROM xxe_f749_init_history
GROUP BY init_number) ON history.create_date_sec=max_sec
AND in_no=history.init_number
WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND in_no IS NOT NULL
SELECT history.init_number,
history.current_administrator,
init.CLASS,
history.r_time
FROM xxe_f749_init init
INNER JOIN ( SELECT init_number,
current_administrator,
SUBSTR(resolution_time(history.init_number),1,1) r_time
FROM xxe_f749_init_history
WHERE history.init_status NOT IN ('Resolved','Closed','Dormant')
AND UPPER(history.competence_queue) IN ('ALPHA MAINT OFFSHORE',
'ALPHA MAINT ONSITE')) history ON history.Init_Number=init.Init_Number
AND r_time='0'
LEFT JOIN (SELECT MAX(create_date_sec) max_sec,
init_number in_no
FROM xxe_f749_init_history
GROUP BY init_number) ON history.create_date_sec=max_sec
AND in_no=history.init_number
WHERE SUBSTR(trim(init.Init_Summary),1,8 ) != 'PROBLEM:'
AND UPPER(init.Init_Type) IN ('ERROR REPORT','QUERY/HELP')
AND init.command !='AMST'
AND init.CLASS IN ('1/A','2/B','3/C','4/D')
AND in_no IS NOT NULL
[Updated on: Wed, 17 September 2008 00:10] Report message to a moderator
|
|
|
Re: SQL query performance issue [message #348785 is a reply to message #347319] |
Wed, 17 September 2008 23:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Kriptas, concerning your question regarding
Quote: | but then optimizer wont use my bitmap indexes in DW tables
|
When you forced the query to use the bitmap index was there a performance gain?
Can you post the query and the explain plans for both execution variations? I've been hit with that disappointment myself -
thinking I've found the perfect bitmap index scenario and having Oracle not take that route.
Just take it the step further and test the actual performance with the bitmap index before forming conclusion of its merit, even when it appears to be the logical choice.
If your environment is changing a lot, definitely of merit to
use optimizer hints to lock your query into the best plan once you have found it. Then run the situation by your DBA's to get a better understanding of the system state and what issues they may be tackling with tuning transitions.
Always good for working relationships to share this information;
document the changes in the Access path's that Oracle has been switching around.
It could be very useful information for the DBA's in whatever the bigger scenario is that they are working on.
Presenting them with your analysis in conjunction with an inquiry on system status will probably get you a more detailed explanation in return.
Best Regards,
Harry
|
|
|
|
Re: SQL query performance issue [message #348814 is a reply to message #347319] |
Thu, 18 September 2008 00:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
the query is simple
select *
from sdwh_blnc a
where a.sal_dt=to_date('2008-09-01','yyyy-MM-dd')
table sdwh_blnc has 2 000 M records, it is huge table, about 500gb of size.
it is range partitioned by sal_dt.
one partition is designated for one month records(for 30 sal_dt).
one partition has about 50 M records, one sal_dt has about 1,5M records.
there is local bitmap index on sal_dt.
for few months there was all ok, optimiser used bitmap index and returned records very fast.
and now it use full partition scan, and performance downgraded dramaticaly.
if in one query I join accounts table with this query as subselect it return me result in 1,5 hours, before that (and if I use hints now) it run time was 1 minute.
i tried to set system parameter optimizer_index_cost_adj=50 (before it was 100) but it helped only for a week, no I need set that parameter to 30 to have the same results...
|
|
|
|
Re: SQL query performance issue [message #348833 is a reply to message #348818] |
Thu, 18 September 2008 01:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
anacedent, I do not understand your post...
oracle version 10g. in that table only newes partition indexes are rebuild after new day records are inserted.
so why selecting from old partitions where indexes and statistics are the same as 2 months ago is different now.
|
|
|
Re: SQL query performance issue [message #349084 is a reply to message #347319] |
Thu, 18 September 2008 17:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
Kriptas I think Anacedant is indicating that there may be more
degeneration with bitmap indexes over btree indexes and merit a rebuild more frequently.
I was just reading some bitmap articles and commentary and there appears to be a lot of "back and forth" debating on
the merits of bitmap indexes across a wide range of index related topics.
Here is a link to an article from Jonathon Lewis on the topic
of index degeneration in general
http://www.dbazine.com/oracle/or-articles/jlewis14
As well, one on bitmap usage where he questions some of the
adages on what makes a good candidate for a bitmap
http://jonathanlewis.wordpress.com/2006/11/29/bitmap-indexes/
Everything with a grain of salt until tested for oneself.
I would try out rebuilding the bitmap and regathering statistics just to see if it is a factor or not.
Regards
Harry
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:34:14 CDT 2024
|