Home » RDBMS Server » Performance Tuning » SQL STATEMENT TUNING (ORACLE 8I)
SQL STATEMENT TUNING [message #320219] Wed, 14 May 2008 05:56 Go to next message
chinmayikkalki
Messages: 11
Registered: April 2008
Junior Member
HI RESPECTED EXPERTS,
I NEED TO TUNE THE FOLLOWING QUERY.
PLEASE REPLY IF U HAVE SUGGESTIONS.
THERE ARE ONLY 360 ROWS IN THAT TABLE STILL IT IS TIME CONSUMING. i M NOT GETTING THAT PARRALLEL_TO_SERIAL IN THE EXPLAIN PLAN.
PLEASE HELP.

SELECT  "F"."DCT_PORTFOLIO"."PORTFOLIO_ID" ,           
  "F"."DCT_PORTFOLIO"."PORTFOLIO_NAME"     
FROM "F"."DCT_PORTFOLIO" WHERE(F."DCT_PORTFOLIO"."STATUS" =
  'A' );   


Elapsed: 00:00:01.32

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=120 Bytes=492
          0)

   1    0   TABLE ACCESS* (FULL) OF 'DCT_PORTFOLIO' (Cost=2 Card=120 B :Q126460 ytes=4920)        00

   1 PARALLEL_TO_SERIAL            SELECT /*+ Q12646000 NO_EXPAND ROWID(A1) */
                                   A1."PORTFOLIO_ID",A1."PORTFOLIO_NAME



Statistics
----------------------------------------------------------
         19  recursive calls
         15  db block gets
         36  consistent gets
          0  physical reads
        684  redo size
      21881  bytes sent via SQL*Net to client
       2978  bytes received via SQL*Net from client
         25  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
347	rows processed


Re: SQL STATEMENT TUNING [message #320227 is a reply to message #320219] Wed, 14 May 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't post in UPPER CASE
2/ ALTER TABLE ... NOPARALLEL;

Regards
Michel
Re: SQL STATEMENT TUNING [message #320238 is a reply to message #320219] Wed, 14 May 2008 07:15 Go to previous messageGo to next message
chinmayikkalki
Messages: 11
Registered: April 2008
Junior Member
Hi experts,
Yes i did alter table ... noparallel and execution plan is chaned but is it possible to reduce the elapsed time below 1 second. There are 69 columns in that table,5/6 indexes,2 pk columns,total 360 rows from which it selects 347 rows.

select  "F"."dct_portfolio"."portfolio_id" ,
  "F"."dct_portfolio"."portfolio_name"
from  "F"."dct_portfolio" 
where (F."dct_portfolio"."status"='A')
/

347 rows selected.

Elapsed: 00:00:01.32

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=347 Bytes=142
          27)

   1    0   TABLE ACCESS (FULL) OF 'DCT_PORTFOLIO' (Cost=2 Card=347 By
          tes=14227)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
      28853  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        347  rows processed

Re: SQL STATEMENT TUNING [message #320253 is a reply to message #320238] Wed, 14 May 2008 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase "arraysize"

Regards
Michel
Re: SQL STATEMENT TUNING [message #320258 is a reply to message #320219] Wed, 14 May 2008 07:48 Go to previous messageGo to next message
chinmayikkalki
Messages: 11
Registered: April 2008
Junior Member
Dear Experts,

I increased the array size.
Actually the table has 69 columns and 360 rows from which the query fetched only 2 columns and 347 rows and using full table scan.
I think selecting 2 columns from 69 columns is the bottleneck.
May i know your suggestion plz.

Regards,
Chinmay.

Re: SQL STATEMENT TUNING [message #320308 is a reply to message #320258] Wed, 14 May 2008 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your value of arraysize?
Note arraysize not linesize.

Regards
Michel
Re: SQL STATEMENT TUNING [message #320396 is a reply to message #320308] Thu, 15 May 2008 00:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It only did 2 round trips, so the arraysize can't be too small.

It's also only performing 4 db block gets and 13 consistent gets. So there doesn't seem to be a HWM problem, and the 69 columns does not seem to be generating huge amounts of IO.

You may have a slow network, or the database server may be very very busy.

Try this:
select  "F"."dct_portfolio"."portfolio_id" ,
  "F"."dct_portfolio"."portfolio_name"
from  "F"."dct_portfolio" 
where (F."dct_portfolio"."status"='A')
and rownum > 1


This will acces the data from disk, but will not return it over the network. It should give us more information.

Also check the load on your server.

Ross Leishman
Re: SQL STATEMENT TUNING [message #320870 is a reply to message #320219] Fri, 16 May 2008 08:57 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ross, should that last line of yours been rownum < 1?

As for the analysis, I pretty much agree with Ross. Possibly a terribly slow disk, or a RAID 5 array with a bad disk?

The only thing I can suggest would be to create an index on the 2 columns you are selecting and the status column. Basically do a FFS of the 3 column index rather than a FTS of the table.

But still, you are only reading 13 blocks...how much better can you get. I wonder if the parsing is a big chunk of that time.

Would all of those quotes slow down the query parser?
Re: SQL STATEMENT TUNING [message #321215 is a reply to message #320870] Mon, 19 May 2008 08:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
smartin wrote on Fri, 16 May 2008 23:57
Ross, should that last line of yours been rownum < 1?



Nope. See here for my reasoning.

Ross Leishman
Re: SQL STATEMENT TUNING [message #322997 is a reply to message #320396] Tue, 27 May 2008 00:49 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Ross,
What does network load means here. Please let me know as I also stuck in a similar situation.

Thanks,
Mahi
Re: SQL STATEMENT TUNING [message #323236 is a reply to message #322997] Tue, 27 May 2008 19:03 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Networks can only send so much data between computers at a time. If someone else is sending a lot of data, and you want to send a lot of data, then you will slow each other down.

Ross Leishman
Previous Topic: using sequences in dml
Next Topic: performance issue
Goto Forum:
  


Current Time: Sat Dec 10 22:42:59 CST 2016

Total time taken to generate the page: 0.22733 seconds