| SQL STATEMENT TUNING [message #320219] |
Wed, 14 May 2008 05:56  |
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 #320238 is a reply to message #320219] |
Wed, 14 May 2008 07:15  |
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 #320258 is a reply to message #320219] |
Wed, 14 May 2008 07:48 |
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 #320396 is a reply to message #320308] |
Thu, 15 May 2008 00:05 |
rleishman
Messages: 3728
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 |
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 #323236 is a reply to message #322997] |
Tue, 27 May 2008 19:03 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
