| pl/sql [message #522276] |
Wed, 07 September 2011 17:20  |
zodiacsom
Messages: 37
Registered: December 2009
Location: pune
|
Member |
|
|
Hi all ..
i am executing the following procedure it took more than 2hr to complete .... please help to imporve perfomance
PROCEDURE CREATE_BAL_RES_MEDICAL
AS
l_key NUMBER(10,0);
c_key NUMBER(10,0);
lcat NUMBER(10,0);
loss_res NUMBER(10,2);
CURSOR Cursor1 IS
select A.XREF_KEY,B.XREF_res2-A.XREF_res2 dif from clmxref a join exist_clmxref
b on B.XREF_KEY = A.XREF_KEY
where B.XREF_res2 <> A.XREF_res2;
BEGIN
OPEN Cursor1;
LOOP
FETCH Cursor1 INTO c_key,loss_res;
EXIT WHEN Cursor1%NOTFOUND;
SELECT COALESCE(MAX(L.loss_key) + 1, 1) INTO l_KEY FROM LOSS L;
Select id into lcat from claim c join rv4_loss_category l on c.clm_type=l.li
neofcoverage where claim_key=c_key and bucket=2;
INSERT INTO loss (loss_key,loss_claim, loss_user, loss_type, loss_enterdate,
loss_date, loss_category, loss_amt, loss_paytype,
loss_enteruser, loss_category_nbr, ref_losscategory)
VALUES (l_key,c_key, 'SYSTEM', 'R', SYSDATE,SYSDATE,'MEDICAL',loss_res,'A','
SYSTEM',2, lcat);
COMMIT;
END LOOP;
CLOSE Cursor1;
END;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: pl/sql [message #522398 is a reply to message #522395] |
Fri, 09 September 2011 04:29  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Fri, 09 September 2011 10:13
Please follow posting guidelines and always include starting and closing tags for giving your coding
part in orafaq.
Please follow guidelines given by Michel.
If you're going to tell people to use code tags it helps to post a link to the page that shows how. However since Michel has already done this that would be unecessary repition.
I don't know about your schema table size and i have no table records as well,But i can give you
best solution upto my best level for your procedure:
msol25 wrote on Fri, 09 September 2011 10:13
A) ignore usage of max function in executable section of subprograms(Like Procedure,functions etc)
Why?
msol25 wrote on Fri, 09 September 2011 10:13
B) Always try to execute your query in cursor only.
Why? And all sql statements are cursors. Just most of them are implicit cursors. I assume you mean explicit cursors.
msol25 wrote on Fri, 09 September 2011 10:13
C) But you may need to execute query in executable section of subprogram,so tune it properly before usage into executable section of subprogram.
Can you execute queries outside of executable sections? What does that even mean?
msol25 wrote on Fri, 09 September 2011 10:13
Tune your query like:
explain plan for
select sum(col1)
,col_2
from tab
group by col_2;
How does that tune a query? It gives some info you need but it doesn't tune it.
|
|
|
|
| Re: pl/sql [message #522400 is a reply to message #522396] |
Fri, 09 September 2011 04:31 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Fri, 09 September 2011 10:16Zodiac,
Please ignore usage of Aggregate Function(like Max) into executable section of Subprogram because aggregate functions are very costly.
That's really bad advice. Oracle can do aggregates very efficiently, and even if they are expensive, if you need them to get the answer then you need them.
How else would you do it? Other than using a sequence instead like I already suggested.
|
|
|
|
| Re: pl/sql [message #522405 is a reply to message #522398] |
Fri, 09 September 2011 04:49 |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Thanks Cookie for your query,
Please find complete steps for tuning a particular query:
Step 1: First check your explain plan using:
explain plan for
select sum(col1)
,col_2
from tab
group by col_2;
Step 2: Then execute Query :
select *
from table(dbms_xplan.display);
Step 3: If you find full table scan for that table i.e. your indexes not working for retrieving
20-30% records from table,then use this query for those specific tables like :
begin
dbms_stats.gather_table_stats
(
ownname => 'ABCD',
tabname => 'TAB',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
cascade => true,
degree => 7
);
end;
Step 4: After doing this activity then execute steps 1 and 2 :
explain plan for
select sum(col1)
,col_2
from tab
group by col_2;
Step 5: Run:
select *
from table(dbms_xplan.display);
In this you can find cost of query and you can find index is working properly or not.If not as per
your expectation then use hint using query:
select /*+ index(t,idx) */ sum(t.col1)
,t.col_2
from tab t
group by col_2;
Many Thanks for your query cookie,If you can add anything according to your experience then please
add into given steps.
[Updated on: Fri, 09 September 2011 04:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: pl/sql [message #522433 is a reply to message #522405] |
Fri, 09 September 2011 08:00 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Fri, 09 September 2011 10:49Thanks Cookie for your query,
Wasn't so much a query as me pointing out that your explanation was woefully incomplete.
msol25 wrote on Fri, 09 September 2011 10:49
Step 3: If you find full table scan for that table i.e. your indexes not working for retrieving
20-30% records from table,then use this query for those specific tables like :
Where did the figure of 20-30% come from? And your example query will retrieve 100% but never mind.
msol25 wrote on Fri, 09 September 2011 10:49
Many Thanks for your query cookie,If you can add anything according to your experience then please
add into given steps.
My suggestions for this particular problem are above. In more general terms - whole books have been written on this subject, do you really think it can be summed up in a short post?
In addition - you've ignored all my other points
|
|
|
|
|
|
|
|
|
|
| Re: pl/sql [message #522475 is a reply to message #522395] |
Sat, 10 September 2011 07:53 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Fri, 09 September 2011 19:16
If you are not satisfied with my give solution,but i can explain as per your raised issues.
There appear to be some words missing from that sentence.
msol25 wrote on Fri, 09 September 2011 19:16
aggregate functions is always costly because no index can work with aggregate function and it will always work with full table scan.
That's absolute rubbish.
msol25 wrote on Fri, 09 September 2011 19:16
I am talking only for implicit cursor but not about explicit cursor and I am not telling not use aggregate function
in executable section.
Again - why do you keep going on about executable sections? Where do you expect them to get the aggregate? In the declare section?
msol25 wrote on Fri, 09 September 2011 19:16
If possible then try to resolve using single efficient query rather than writing small queries
each time for each value.
So when you wrote this:
msol25 wrote on Fri, 09 September 2011 10:13
B) Always try to execute your query in cursor only.
You meant this:
B) Always try to execute your query in a single cursor only.
Review what you write before you post it and the rest of us might actually be able to understand
msol25 wrote on Fri, 09 September 2011 19:16
I want to say i.e. In most of cases we are working with relational databases and maintaining integrity constraints as well,so why
we should not bother to write single query by using integrity constraints.
What have integrity constraints got to do with anything?
msol25 wrote on Fri, 09 September 2011 19:16
why database is not having flow control for querrying on multiple tables.
What does that even mean?
msol25 wrote on Fri, 09 September 2011 19:16If Optimizer is correct always then,why oracle experts in orafaq giving solution to use hints in some specific cases.?
Cookie Keep in mind Optimizer is not god and it can be wrong and Optimizer uses Krushkal algorithm for calculating cost of
query and as per cost based algorithms(like krushkal algorithm) you can only find cost of path but not calculating traffic on that path.
Who said it was always right? It's almost always right though, especially in the most recent versions as lots of work has been done on it. You should never need an index hint for a simple query. And the queries in this problem are simple. If there is a problem here due to indexes it will most likely be because the necessary index doesn't exist.
msol25 wrote on Fri, 09 September 2011 19:16
Sometime we use hints by keeping in mind i.e. route can be busy so we need to use different path using hint.
Route can be busy? No idea what that means either.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
