Home » SQL & PL/SQL » SQL & PL/SQL » sql tuning
sql tuning [message #217599] Fri, 02 February 2007 23:36 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #217628 is a reply to message #217614] Sat, 03 February 2007 07:07 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi Frank,

Here is my programme

create or replace procedure update_intf is
cursor c1 is select pol_id from aviva_aff_liss_intf_arp_new;


Begin
FOR R1 IN C1 LOOP
UPDATE aviva_aff_liss_intf_arp_new
SET MONTHELAPSED = ( select /*+ index (I0000667, LISS_FTR_STATUS, ON_ALL_FUND_COLMS_INDX, idx_units) */ -- it may have dulicate records wich i want to omit
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
)
WHERE POL_ID = r1.POL_ID;

COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


thanks
Re: sql tuning [message #217631 is a reply to message #217614] Sat, 03 February 2007 09:25 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #217668 is a reply to message #217631] Sun, 04 February 2007 06:33 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi Franks,

Thanks for advice. Its working fine , My query was resulting into full table scan, after seeing explain plan its working fine.

Thanks again.

one more please. what are the disadvantages of using
exception
when others
dbms_output.put_line()

please advice if any other method to track exceptions.


Ram pratap Singh
Re: sql tuning [message #217671 is a reply to message #217637] Sun, 04 February 2007 06:49 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

Thanks, its working fine now after forcing a index to use . cursor was only know the speed of loop.

Re: sql tuning [message #217676 is a reply to message #217668] Sun, 04 February 2007 09:47 Go to previous messageGo to next message
Frank
Messages: 7880
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 #217862 is a reply to message #217676] Mon, 05 February 2007 22:06 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi Frank,

Thanks for your advice. I feel "dbms_output.put_line" is use full when my procedure is in initia stage( being exeucted from sql prompt only) and I am not sure what run time error may occure. To trace all these things, I used it. Certainly after successful execution of sp this code will not be used.


Thanks again,

Ram Pratap Singh
Re: sql tuning [message #217898 is a reply to message #217862] Tue, 06 February 2007 00:43 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #315772 is a reply to message #315763] Tue, 22 April 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think was still hearing more than 1 year after.

Regards
Michel
icon14.gif  Re: sql tuning [message #316949 is a reply to message #217898] Mon, 28 April 2008 23:05 Go to previous message
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!


Previous Topic: reffering to a column from parent query in inner query
Next Topic: Help
Goto Forum:
  


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

Total time taken to generate the page: 0.33535 seconds