Home » RDBMS Server » Performance Tuning » Query Optimization
Query Optimization [message #64703] Mon, 29 December 2003 21:20 Go to next message
Parag
Messages: 10
Registered: July 2002
Junior Member
Hi Guru's,
I have one query which is taking around one hour to
excute.Problem is Invoice_line table is having 1 million records. Indexing is properly done. Inovice_line table is partition table. Can you please give me some suggestion to tune this query.?

SELECT
count(1)
FROM
datap.ITEM_MASTER,
datap.DB_MASTER,
datap.FISCAL_WEEK,
datap.CUSTOMER_MASTER CUSTOMER_MASTER_SOLD,
datap.FISCAL_WEEK DUE_WEEK,
datap.INVOICE_LINE
WHERE
INVOICE_LINE.ITEM_ID = ITEM_MASTER.ITEM_ID
AND INVOICE_LINE.FISCAL_WEEK = FISCAL_WEEK.FISCAL_WEEK
AND INVOICE_LINE.DB_CODE = DB_MASTER.DB_CODE
AND CUSTOMER_MASTER_SOLD.CUST_ID=INVOICE_LINE.CUST_SOLD_ID
AND INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START and INVOICE_LINE.DUE_DATE < DUE_WEEK.
FISCAL_DT_END ;

Thanks in Advance ,
Parag
Re: Query Optimization [message #64704 is a reply to message #64703] Mon, 29 December 2003 23:36 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Please provide the following details:

1. Explain plan (from EXPLAIN PLAN command, TKPROF or SET AUTOTRACE ON)
2. Execution stats (from TKPROF or SET AUTOTRACE ON)
3. Index structures (from DBA_IND_COLUMNS)
4. Last analyzed date (from DBA_TABLES/ DBA_INDEXES)

Best regards.

Frank
Re: Query Optimization [message #64706 is a reply to message #64704] Tue, 30 December 2003 01:11 Go to previous messageGo to next message
Parag
Messages: 10
Registered: July 2002
Junior Member
Hi,
Thanks very much for replying.Can you please have look on below information. I don't have privileges for 'set autotrace on'
on production database , so Execution stat information is missing other statistics are from Production database. But i have
some Execution stat information from my Development database it is listed in step no 4. Once again thanks and waiting
for you reply.

Thanks & Regards,
Parag

1. EXPLAIN PLAN

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
SELECT STATEMENT
SORT AGGREGATE
NESTED LOOPS
NESTED LOOPS
HASH JOIN
INDEX FAST FULL SCAN PK_ITEM_MASTER
HASH JOIN
MERGE JOIN CARTESIAN
TABLE ACCESS FULL FISCAL_WEEK
BUFFER SORT
INDEX FULL SCAN PK_DB_MASTER

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
PARTITION RANGE ALL
TABLE ACCESS FULL INVOICE_LINE
INDEX UNIQUE SCAN PK_FISCAL_WEEK
INDEX UNIQUE SCAN PK_CUSTOMER_MASTER

2.INDEX INFORMATION
Table: INVOICE_LINE
COLUMN_NAME INDEX_NAME
CUST_BILL_ID I_INVOICE_BILL_TO
CUST_SOLD_ID I_INVOICE_SOLD_TO
DB_CODE I_INVOICE_DB_CODE_F_MANUF
DB_CODE I_INVOICE_ITEM
DB_CODE I_INVOICE_ORDER
DB_CODE I_INVOICE_WEEK_2
DB_CODE PK_INVOICE
DB_CODE SYS_C003802
DUE_DATE I_INVOICE_DUE_DATE
FISCAL_WEEK I_INVOICE_WEEK
FISCAL_WEEK I_INVOICE_WEEK_2
F_MANUF I_INVOICE_DB_CODE_F_MANUF
INV_LINE I_INVOICE_ORDER
INV_LINE PK_INVOICE
INV_LINE SYS_C003802
INV_NB PK_INVOICE
INV_NB SYS_C003802
ITEM_ID I_INVOICE_ITEM
ITEM_NUMBER I_INVOICE_ITEM
ORDER_NUMBER I_INVOICE_ORDER
ORDER_NUMBER PK_INVOICE
ORDER_NUMBER SYS_C003802

Table: ITEM_MASTER
COLUMN_NAME INDEX_NAME
DB_CODE IU_ITEM_MASTER_DB_CODE
DB_CODE I_ITEM_MASTER_DBCODE_ITEMNO
DB_CODE PK_ITEM_MASTER
HPL_CODE I_ITEM_HPL
ITEM_DESC1 I_ITEM_DESC1
ITEM_ID PK_ITEM_MASTER
ITEM_NUMBER I_ITEM_MASTER_DBCODE_ITEMNO
ITEM_NUMBER PK_ITEM_MASTER
PL_CODE I_ITEM_PL

Table: FISCAL_WEEK
FISCAL_MONTH I_FISCAL_MONTH
FISCAL_WEEK PK_FISCAL_WEEK
FISCAL_YEAR I_FISCAL_YEAR
REL_WEEK I_FISCAL_REL_WEEK

Table: DB_MASTER
DB_CODE PK_DB_MASTER

Table: CUSTOMER_MASTER
COLUMN_NAME INDEX_NAME
CUST_CODE IU_CUST_CODE
CUST_CODE PK_CUSTOMER_MASTER
CUST_ID PK_CUSTOMER_MASTER
DB_CODE IU_CUST_CODE
DB_CODE PK_CUSTOMER_MASTER

3)
Last Analyzed date for all Tables and Indexes : 28-DEC-03

4) From test Database

SQL>Select count(1) from INVOICE_LINE,FISCAL_WEEK DUE_WEEK
Where INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START
And INVOICE_LINE.DUE_DATE < DUE_WEEK.FISCAL_DT_END
And ROWNUM <20;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 MERGE JOIN (Cost=31 Card=2445907 Bytes=56255861)
4 3 SORT (JOIN) (Cost=26 Card=2068420 Bytes=18615780)
5 4 INDEX (FULL SCAN) OF 'I_DUE_DATE' (NON-UNIQUE) (Co
st=26 Card=2068420 Bytes=18615780)

6 3 FILTER
7 6 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'FISCAL_WEEK' (Cost=2 Car
d=473 Bytes=6622)

Statistics
----------------------------------------------------------
0 recursive calls
16 db block gets
5227 consistent gets
5939 physical reads
0 redo size
371 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
Re: Query Optimization [message #64707 is a reply to message #64706] Tue, 30 December 2003 02:57 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

Your production system does a FULL TABLE SCAN on INVOICE_LINE (+-1 mil rows), while the dev DB does a FULL INDEX SCAN (+- 2 mil rows???). I'm not sure if this is meaningful as it seems that you have different indexes on dev.

Try to specify the following hint to force the query to use the I_INVOICE_DUE_DATE index (as the dev DB uses I_DUE_DATE) and let us know if it helps:

SELECT /*+INDEX(INVOICE_LINE, I_INVOICE_DUE_DATE)*/ count(1) ...


Best regards.

Frank
Re: Query Optimization [message #64709 is a reply to message #64707] Tue, 30 December 2003 22:33 Go to previous messageGo to next message
Query optimization
Messages: 4
Registered: December 2003
Junior Member
Hi,
Thanks for the help. I tried to force the query to used index i_index_due_date, but of no use. Actual rows in
production db = 7186062.
test db =2072967.
I did not get about 2 million rows in DEV db.?
I created composite index on invoice_line table
i_indextest (TEM_ID,FISCAL_WEEK,DB_CODE,CUST_SOLD_ID,DUE_DATE)
Now the query is taking around 23 mins to exeute.
Explain plan after creating index i_indextest

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
NESTED LOOPS
HASH JOIN
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID ITEM_MASTER
INDEX RANGE SCAN I_ITEM_HPL
HASH JOIN
TABLE ACCESS FULL FISCAL_WEEK

OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
NESTED LOOPS
TABLE ACCESS FULL DB_MASTER
INDEX FAST FULL SCAN I_INDEXTEST
TABLE ACCESS BY INDEX ROWID CUSTOMER_MASTER
INDEX UNIQUE SCAN PK_CUSTOMER_MASTER
FILTER
SORT JOIN
TABLE ACCESS FULL FISCAL_WEEK

What else can be done to tune the query.
Re: Query Optimization [message #64710 is a reply to message #64709] Tue, 30 December 2003 22:41 Go to previous messageGo to next message
Query optimization
Messages: 4
Registered: December 2003
Junior Member
Actual query

SELECT
count(1)
FROM
ITEM_MASTER,
CUSTOMER_MASTER CUSTOMER_MASTER_SOLD,
DB_MASTER,
FISCAL_WEEK DUE_WEEK,
INVOICE_LINE,
FISCAL_WEEK
WHERE
( INVOICE_LINE.ITEM_ID = ITEM_MASTER.ITEM_ID )
AND ( INVOICE_LINE.FISCAL_WEEK = FISCAL_WEEK.FISCAL_WEEK )
AND ( INVOICE_LINE.DB_CODE = DB_MASTER.DB_CODE )
AND ( CUSTOMER_MASTER_SOLD.CUST_ID=INVOICE_LINE.CUST_SOLD_ID )
AND ( INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START and INVOICE_LINE.DUE_DATE < DUE_WEEK.FISCAL_DT_END )
AND (
ITEM_MASTER.HPL_CODE IN ('503', '595')
AND ( ITEM_MASTER.F_CURRENT = 'Y' )
AND (( FISCAL_WEEK.REL_YEAR = 0 )
OR ( FISCAL_WEEK.REL_YEAR = -1 ))
)
GROUP BY
ITEM_MASTER.HPL_CODE,
CUSTOMER_MASTER_SOLD.TRD_INTRACO,
DB_MASTER.REPORT_REGION_NAME,
ITEM_MASTER.ITEM_NUMBER,
ITEM_MASTER.ITEM_DESC1,
ITEM_MASTER.ITEM_DESC2,
CUSTOMER_MASTER_SOLD.CUST_NAME||' - '||CUSTOMER_MASTER_SOLD.CITY||', '||CUSTOMER_MASTER_SOLD.STATE,
DUE_WEEK.FISCAL_MONTH

Indexes are same in both Dev and production. I also
tried by creating bitmap index for the columns having
less distinct values.

Thanks & Regards,
Parag
Re: Query Optimization [message #64711 is a reply to message #64709] Tue, 30 December 2003 23:50 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Hi,

What do you mean it is of no use, have you at least checked that the query used the I_INDEX_DUE_DATE index?

> production db = 7186062.
According to your first mail, you have 1 mil rows!?!

> I did not get about 2 million rows in DEV db.?
I've mentioned that the FULL INDEX SCAN processed +- 2 million rows on your test/dev DB - look at the cardinality in the explain plan for your dev/test db!

Your best bet is to play with the query, forcing it to use different indexes and optimization plans.

Best regards.

Frank
Re: Query Optimization [message #64713 is a reply to message #64711] Wed, 31 December 2003 02:17 Go to previous messageGo to next message
Query optimization
Messages: 4
Registered: December 2003
Junior Member
Thanks for reply !!
I said not of use beacuse the query we are using in
Bussiness objects to generate report. Bussness objects
does not support HINTS (as query is dynamically generated).
Yes i had seen that the query is using i_invoice_due_date in the explain plan. Initially i was not sure about the number fo rows present in the
database, so i gave approximate number of rows.

If you see the query which i send in my second mail i.e

SQL>Select count(1) from INVOICE_LINE,FISCAL_WEEK DUE_WEEK
Where INVOICE_LINE.DUE_DATE >= DUE_WEEK.FISCAL_DT_START
And INVOICE_LINE.DUE_DATE < DUE_WEEK.FISCAL_DT_END
And ROWNUM <20;

I really dont have any idea about the card value may be it is the number of rows effected by join.
INDEX (FULL SCAN) OF 'I_DUE_DATE' (NON-UNIQUE) (Co
st=26 Card=2068420 Bytes=18615780), but the number
rows mention in my previous are exact

thanks,
Parag
Re: Query Optimization [message #64714 is a reply to message #64710] Mon, 05 January 2004 01:56 Go to previous messageGo to next message
Query optimization
Messages: 4
Registered: December 2003
Junior Member
Hi Frank,
Thanks for giving me very good idea of using Hint.
Now the query is taking only 9 secs to execute.
But i want to ask you. Why oracle is not using index
i_inovice_week_2 in my Production database,but in test
database it is using same index i_invoice_week_2.

Thanks & Regards,
Parag
Re: Query Optimization [message #64716 is a reply to message #64714] Tue, 06 January 2004 04:28 Go to previous message
neil
Messages: 15
Registered: June 2001
Junior Member
Parag,

In answer to your question on why your Prod DB isn't using an index but your Dev DB does.

The CBO gives costs to your SQL and from this decides whether accessing the data via one execution path is more or less expensive than another. A quick example, if you have an index on a very skewed column within a table (clustering_factor in xxx_indexes similar to number of rows in table) then doing a full table scan with multiblock read can work out cheaper to the CBO than doing an index scan. This may or may not be your problem but a couple of parameters you could try and play with to give more weight to the use of indexes are optimizer_index_caching (increase to 80 or 90) and optimizer_index_Cost_adj (reduce from 100). These can be set instance wide or just within a session.

Hope this helps.

Neil.
Previous Topic: Statistics description
Next Topic: partitioned tables
Goto Forum:
  


Current Time: Thu Oct 01 11:29:06 CDT 2020