Home » RDBMS Server » Performance Tuning » How to reduce the execution timing of SQL Query (SQL*Plus: Release 3.3.4.0.0, Oracle7:7.3.4.0.0)
How to reduce the execution timing of SQL Query [message #309594] Fri, 28 March 2008 01:42 Go to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

Can any one help me out , I have a SQL query & i want to reduce the Execution timing og this:

select file_no, ets_date, load_code, disch_code, container 
from file_table where status = 'O' 
and direction = 'EO' order by file_no desc;


I have indexing on column

a) direction
b) status
c) file_no

and the execution timing of this is 3.10sec.

Thanx in advance.
Re: How to reduce the execution timing of SQL Query [message #309599 is a reply to message #309594] Fri, 28 March 2008 01:48 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please provide the following Details:-

1)Table Structures
2)Explain Plan for your query
3)Rows Count For Each Table
4)Statistics available or not
Re: How to reduce the execution timing of SQL Query [message #309602 is a reply to message #309594] Fri, 28 March 2008 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Oracle SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel
Re: How to reduce the execution timing of SQL Query [message #309623 is a reply to message #309599] Fri, 28 March 2008 03:41 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,
Thanks for replying.
a) Table structure
SQL> desc file_table ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 FILE_NO                                   NOT NULL VARCHAR2(12)
 DIRECTION                                          VARCHAR2(2)
 ORIGIN_CODE                                        VARCHAR2(4)
 ORIGIN_NAME                                        VARCHAR2(20)
 LOAD_CODE                                          VARCHAR2(4)
 LOAD_NAME                                          VARCHAR2(20)
 DISCH_CODE                                         VARCHAR2(4)
 DISCH_NAME                                         VARCHAR2(20)
 DEST_CODE                                          VARCHAR2(4)
 DEST_NAME                                          VARCHAR2(20)
 ETS_DATE                                           DATE
 ETA_DATE                                           DATE
 
 CONTAINER                                          VARCHAR2(11)
 CONT_SIZE                                          VARCHAR2(3)
 LINE_BOOKING_NO                                    VARCHAR2(16)
  STATUS                                             VARCHAR2(1)

b) This query is used in LOV of file_no.
c)Total no of rows selected is : 10017 rows selected.
d)Data of these column:

SQL> select file_no, ets_date, load_code, disch_code, container 
     from file_table where status = 'O' 
     and direction = 'EO' order by file_no desc;

  FILE_NO      ETS_DATE  LOAD DISC CONTAINER
------------ --------- ---- ---- -----------
703063170    21-MAR-07 NYC  LAM  NYKU2397112
703044237    28-MAR-07 NYC  FLX  GSTU9563649
702903129    14-FEB-07 NYC  CPH  1
702063WHS    01-FEB-07 NYC  NYC  IN/OUT
701834023    13-AUG-07 OAK  ROT  NYKU2455230
70103098     15-SEP-07 PEV  RIH  709103098
610063PDM    01-OCT-06 NYC  NYC  OCT/06 PDM
609023ERR    01-SEP-07 ATL  ATL  X
608904001    01-AUG-06 NYC  ALX  TBA
608104064    28-AUG-06 HOU  PTE  MAEU7804775
1606220      11-SEP-07 HOU  THA  EMCU9432640

FILE_NO      ETS_DATE  LOAD DISC CONTAINER
------------ --------- ---- ---- -----------
1605940      29-AUG-07 HOU  HAM  1
1600133      26-JUL-07 OAK  DAL  NYK5954504
1589700      03-JUL-07 LAX  LAM  TRLU6504562
1202827      22-DEC-07 ABY  ABO  SXD2212
071084003    01-OCT-07 HOU  FPT  MSCU2986064
070644297    02-JUL-07 NFK  BHV  TGHU8343240
036VF6                           036VF6
Re: How to reduce the execution timing of SQL Query [message #309626 is a reply to message #309623] Fri, 28 March 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajatratewal wrote on Fri, 28 March 2008 07:48
Please provide the following Details:-

1)Table Structures
2)Explain Plan for your query
3)Rows Count For Each Table
4)Statistics available or not


Regards
Michel

Re: How to reduce the execution timing of SQL Query [message #309670 is a reply to message #309599] Fri, 28 March 2008 06:52 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

Row count foe each table

File_no -----> 393810
Date -----> 376522
load_code -----> 389275
dis_code -----> 389257
container -----> 319524


Statistics not available.
Re: How to reduce the execution timing of SQL Query [message #309675 is a reply to message #309670] Fri, 28 March 2008 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajatratewal wrote on Fri, 28 March 2008 07:48
Please provide the following Details:-

1)Table Structures
2)Explain Plan for your query
3)Rows Count For Each Table
4)Statistics available or not


Regards
Michel

Re: How to reduce the execution timing of SQL Query [message #309676 is a reply to message #309675] Fri, 28 March 2008 07:36 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
hi,

please explain in brief , exactly what information u required.
Re: How to reduce the execution timing of SQL Query [message #309686 is a reply to message #309675] Fri, 28 March 2008 08:07 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,
this query is basically used as a query in LOV of File_no.
which select file no from LOV
Re: How to reduce the execution timing of SQL Query [message #309695 is a reply to message #309676] Fri, 28 March 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is explain in the links I posted as well as you must not use IM/SMS speak.

Regards
Michel
Re: How to reduce the execution timing of SQL Query [message #309730 is a reply to message #309695] Fri, 28 March 2008 10:56 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
If by LOV you mean an Oracle Form's List of Values, please consider this:

1. If the query is returning 10k lines, check the properties of both the LOV and the associated Record Group, and reduce the amount of records fetched to something "acceptable". (< 100 for example)
2. You may want to set off the LOV property "Automatic Refresh". (Read the documentation related to this).
3. Please post the explain plan of your query.

I hope this helps.

[Updated on: Fri, 28 March 2008 10:59]

Report message to a moderator

Re: How to reduce the execution timing of SQL Query [message #312280 is a reply to message #309730] Tue, 08 April 2008 07:07 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
HI,

very sorry for giving answer very late.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=69 Card=15
Bytes=840)

1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 MAT_VIEW ACCESS (FULL) OF 'MV_FILE_TABLE1' (MAT_VIEW)
(Cost=69 Card=101 Bytes=5656)
Re: How to reduce the execution timing of SQL Query [message #312871 is a reply to message #309594] Thu, 10 April 2008 00:26 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

select file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;



In this query...

1 Supppose If the column 'status' contains 10 unique records out of total 100 records then create a BIT MAP INDEX on this column. If doesnt then create a normal index.

2. same If the column 'direction ' contains 10 unique records out of total 100 records then create a BIT MAP INDEX on this column. If doesnt then create a normal index.


If your using a bit map index then use query

BIT_status is bitmap index on status column and BIT_direction is bitmap index on direction column on table file_name

select /*+INDEX_COMBINE(file_table BIT_status BIT_direction )*/file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;


if your using a composite index suppose 'ind_dir_stat' for 'direction and status column on table file_name then use the query


select /*+ INDEX_FFS ( file_table ind_dir_stat ) */file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;


I think it would work out faster...





[Updated on: Thu, 10 April 2008 00:30]

Report message to a moderator

Re: How to reduce the execution timing of SQL Query [message #312905 is a reply to message #312871] Thu, 10 April 2008 01:39 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

When i issue a command to create a bit map index then it shows me an error:

SQL> create BITMAP index
  2  emp_bit_idx
  3  on emp(ename);
create BITMAP index
*
ERROR at line 1:
ORA-00439: feature not enabled: Bit-mapped indexes


after that i issued a command:
SQL> select parameter, value from v$option;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------
Partitioning                                                     FALSE
Objects                                                          TRUE
Real Application Clusters                                        FALSE
Advanced replication                                             FALSE
Bit-mapped indexes                                               FALSE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Parallel backup and recovery                                     FALSE

SQL> select * from v$version;

BANNER
----------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production




Is BIT MAP INDEXING IS for the above mentioned version of oracle ?
Can i set the value of parameter(Bit map indexing) to TRUE ?

Please help me for this

Thanks & Regards
Re: How to reduce the execution timing of SQL Query [message #312941 is a reply to message #312905] Thu, 10 April 2008 03:05 Go to previous messageGo to next message
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
Junior Member

hi,

I am using bit map index in oracle 9i & this is possible in 10 g also.

you should use BIT MAP index on those column only which have distinct values 6-10 out of 1000 total records on average.

Re: How to reduce the execution timing of SQL Query [message #312944 is a reply to message #312941] Thu, 10 April 2008 03:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
singh.neerajin wrote on Thu, 10 April 2008 18:05

you should use BIT MAP index on those column only which have distinct values 6-10 out of 1000 total records on average.


Is that it? I would have thought there were some other considerations that would be worth mentioning before the OP goes and crashes an OLTP application with bitmap indexes.

In almost all cases, a single b-tree index will out-perform bitmap indexes. Bitmap indexes should only be used in DSS systems that permit ad-hoc unpredictable access paths.


If you are selecting <10% of the table, you may get a performance improvement from a single index on (status, direction). If you are selecting >10%, then an index probably will not help.

If you need to improve performance further, you may add file_no as a third column in this index. It is possible for Oracle to use the index to satisfy the sort, although you will have difficulty getting this to work for ORDER BY DESC. You may need to create a Descending Key index; and you may need to add hints to the query. It may take some effort - don't bother if the performance of the initial suggestion is adequate.

If you are selecting a lot of rows (1-10%) then you may want to add the other selected columns to the index (ets_date, load_code, disch_code, container). This will take up a LOT more space and make the index less effective for other queries, but it will avoid a table lookup for THIS query. Only do this if you are DESPERATE for more speed.

Ross Leishman
Previous Topic: Order by with Nulls -- Performance
Next Topic: Cursor_Sharing settings not working (merged)
Goto Forum:
  


Current Time: Mon Dec 05 20:47:47 CST 2016

Total time taken to generate the page: 0.17899 seconds