| sql tuning [message #217599] |
Fri, 02 February 2007 23:36  |
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
|
Member |
|
|
Hi ,
My table "fund_transactions" is having more then 40000000 records. I tried with my best to execute this query , but take lot of time even after analyzing the table and indexes are not helpful. Please can any one help me for running this query faster.
thanks
select count(1)
from ( select /*+ index (I0000667, LISS_FTR_STATUS,
ON_ALL_FUND_COLMS_INDX) */
fk_policiespol_id, Transaction0
from Fund_transactions
where event_type = 12
AND Status = 1
AND units > 0
);
Ram Pratap Singh
|
|
|
|
| Re: sql tuning [message #217614 is a reply to message #217599] |
Sat, 03 February 2007 02:48  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
question:
Do you really need to know how many records this inner query returns, or do you use this query to determine if there is a record matching the inner query?
Furthermore:
show us an explain plan, the index plan, etc.
|
|
|
|
|
|
| Re: sql tuning [message #217631 is a reply to message #217614] |
Sat, 03 February 2007 09:25 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Frank wrote on Sat, 03 February 2007 09:48 | question:
Furthermore:
show us an explain plan, the index plan, etc.
|
And please get rid of this:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
This is a VERY bad habit
|
|
|
|
| Re: sql tuning [message #217634 is a reply to message #217599] |
Sat, 03 February 2007 09:40 |
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
|
Senior Member |
|
|
1) How many distinct values fo you have for these columns
event_type , Status and units ?
If I am guessing right ( Aha! ) , I will define separate bitmap indexes on those columns .
2) Will change the query to
create global temporary table foo as (cnr number, pol_id whatever type )
insert into foo
select count(distinct Transaction0) cnt , pol_id
from Fund_transactions ,aviva_aff_liss_intf_arp_new r1
where FK_POLICIESPOL_ID = r1.pol_id
and event_type = 12
AND Status = 1
AND units !=
3) Now define a PK on foo with pol_id as pk column.
4)
Instead of cursor looop for update ,do an inline view update between foo and aviva_aff_liss_intf_arp_new .
-----
I am making a few assumptions here . Let us know how it goes.
If you have frequent updates on Fund_transactions , I will drop the bitmaps after the process are done. You can always recreate them nologginh when you need them.
Srini
|
|
|
|
| Re: sql tuning [message #217637 is a reply to message #217599] |
Sat, 03 February 2007 10:23 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Can you try following:
1. CREATE INDEX ... ON Fund_transactions
( FK_POLICIESPOL_ID , event_type ,
status, units, Transaction0 ) NOLOGGING ;
2. I don't see any reason for additional cursor in your SP:
create or replace procedure update_intf is
-- Commented: cursor c1 is select pol_id
-- Commented: from aviva_aff_liss_intf_arp_new;
Begin
-- Commented: FOR R1 IN C1 LOOP
UPDATE aviva_aff_liss_intf_arp_new r1
SET MONTHELAPSED = ( select /* No hint needed */
count( distinct Transaction0)
from Fund_transactions
where FK_POLICIESPOL_ID = r1.pol_id
and event_type = 12
AND Status = 1
AND units != 0 -- may have in minus
)
; /* NEW LINE */
-- Commented: WHERE POL_ID = r1.POL_ID;
COMMIT;
-- Commented: END LOOP;
-- Commented: EXCEPTION
-- Commented: WHEN OTHERS THEN
-- Commented: DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
3. Verify with EXPLAIN that the new index is used for subselect.
4. Post the results ( with TKPROF).
HTH.
|
|
|
|
|
|
|
|
| Re: sql tuning [message #217676 is a reply to message #217668] |
Sun, 04 February 2007 09:47 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| Quote: | one more please. what are the disadvantages of using
exception
when others
dbms_output.put_line()
|
Disadvantages:
- dbms_output only works if you either log the output to a file or run interactive.
- It doesn't add a bit of functionality; if you do NOT catch the exception, you will also get a line with the error message.
- It obfuscates the line where the error occurred
- To the calling procedure, it hides the fact that an error occurred
I have a question for you: what is the (supposed) advantage? Why did you do it in the first place?
|
|
|
|
|
|
| Re: sql tuning [message #217898 is a reply to message #217862] |
Tue, 06 February 2007 00:43 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
What happens if you do NOT add the exception handler? Do you not see both the error AND the line where it was raised?
Much more info, especially in development phase!
|
|
|
|
| Re: sql tuning [message #315763 is a reply to message #217628] |
Tue, 22 April 2008 13:09 |
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
|
Junior Member |
|
|
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
If you do this, make sure you re-raise the error:
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END; |
|
|
|
|
|
 Re: sql tuning [message #316949 is a reply to message #217898] |
Mon, 28 April 2008 23:05 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
good point - one i always try to make to junior programmers -
oracle provides the error message and line # of executing code stmt of abend.
all too often i have to remove a global exception handler in someone's code so that i can find the actual code statement causing the abend. (instead of being lead to the 'where others' clause).
"Tell me more than what Oracle is going to tell me, or dont tell me anything!".
I like to wrap error handlers around just about anything i dont want to cause my process to stop from; and in all cases, raise to the outermost block where the WHEN OTHERS will call a generic error mod that deals with how to handle processing from this point on.
In regards to dbms_output.put_line - even that can be a pest when the buffer overflows. And being a lazy programmer, thats too many letters to type! I like my common utility routines like PR() - which invoked dbms_output, truncates strings > 255 - and on buffer overflow gives the me the choice as to whether I want to "flush the buffer" and receive all new output from that point or just stop outputting and give me everything up to that point.
Felt the need to comment because I just had to do some of this debug-via-removing-exception-handling today!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
