Home » RDBMS Server » Performance Tuning » Query Very Slow
| Query Very Slow [message #156272] |
Tue, 24 January 2006 05:48  |
mdamjad
Messages: 45
Registered: May 2005
|
Member |
|
|
I am using Win2k Prof and oracle 8i and Forms 4.5 The following query woks slow on oracle Server and works fast on oracle client .
If ORDER BY clause is removed the query works fast both sides
Index is not created for any table.
The tables are
Table Name :- product_detail;
COMP_CODE VARCHAR2(10) -- Reference Key
PROD_CODE VARCHAR2(10)
DESCR VARCHAR2(25)
PACKING VARCHAR2(10)
BATCH_NO VARCHAR2(15)
EXP_DATE DATE
PTR NUMBER(8,2)
MRP NUMBER(8,2)
QTY NUMBER(6)
FQTY NUMBER(6)
MIN_STK NUMBER(
SALE VARCHAR2(2)
PBAL NUMBER(10,2)
SQTY NUMBER(9,3)
SAMT NUMBER(10,2)
PQTY NUMBER(9,3)
PAMT NUMBER(10,2)
SRQTY NUMBER(9,3)
SRAMT NUMBER(10,2)
PRQTY NUMBER(9,3)
PRAMT NUMBER(10,2)
LSDATE DATE
LPDATE DATE
TODATE DATE
COMPOS VARCHAR2(100)
NO NUMBER(4)
WAY VARCHAR2(1)
Table Name :- prod_mast;
COMPCODE VARCHAR2(10) Primary Key
NAME VARCHAR2(60)
select prod_code,descr,a.packing,a.comp_code,b.name,a.qty ,to_char(lsdate,'dd-mm-yyyy') ldate,
round(sysdate-lsdate) days ,sale from product_detail a,prod_mast b
where a.comp_code=b.compcode and a.qty>0 order by descr,b.name
|
|
|
|
|
|
| Re: Query Very Slow [message #156359 is a reply to message #156272] |
Tue, 24 January 2006 23:10  |
mdamjad
Messages: 45
Registered: May 2005
|
Member |
|
|
create index indxdec on product_detail;
create index indxname on product_detail;
is the above method correct to create index . If it is wrong please give method to create index .
Thakyou
|
|
|
|
| Re: Query Very Slow [message #156368 is a reply to message #156359] |
Wed, 25 January 2006 01:03 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
create index iname on tname(colname [,colname...])
Check the doco next time.
I'm afraid I disagree with the other responses. Indexes on descr and name will not help.
The reason is that the SQL is joining the two tables in their entirity (except for those with qty<=0, which are probably in the minority). When you are joining a large proportion of data in two tables, indexes almost always slow you down.
The best result for this query is probably a HASH join, with Full Table Scans on both tables.
There are two reasons why it runs faster without the ORDER BY:
1. The ORDER BY causes a SORT - sorts take time.
2. With the ORDER BY, Oracle has to read EVERY row before it can return even one. Without the ORDER BY, it can return each row as soon as it is found. So it may just seem to be faster because the first row is returned quickly. The real test is how long it takes to return the LAST row.
I suggest you get an EXPLAIN PLAN for the query. Make sure it is performing a HASH join. If it is, that's as good as it is going to get. If it is performing a NESTED LOOP join, analyze the tables and try again. If still no joy, lookup the Oracle Tuning manual and search for USE_HASH.
_____________
Ross Leishman
|
|
|
|
|
|
| Re: Query Very Slow [message #156540 is a reply to message #156368] |
Wed, 25 January 2006 11:17 |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I ran a little test, shown below, and it confirmed what Ross said. When you anlyze, so that the cost-based optimizer has statistics that it can use to determine the best execution plan, it uses a hash join and full table scans. Otherwise, the rule-based optimizer may select a less efficient execution plan. Also, creating indexes as I erroneously suggested did not change the plan or improve performance of the sort, with or without statistics.
-- set up test data:
scott@ORA92> CREATE TABLE prod_mast AS
2 SELECT object_id AS compcode,
3 object_name AS name
4 FROM all_objects
5 /
Table created.
scott@ORA92> ALTER TABLE prod_mast
2 ADD CONSTRAINT prod_mast_pk
3 PRIMARY KEY (compcode)
4 /
Table altered.
scott@ORA92> CREATE TABLE product_detail AS
2 SELECT object_id AS comp_code,
3 data_object_id AS qty,
4 created AS lsdate,
5 status AS descr,
6 temporary AS prod_code,
7 generated AS packing,
8 secondary AS sale
9 FROM user_objects
10 WHERE object_id IN
11 (SELECT compcode
12 FROM prod_mast)
13 /
Table created.
scott@ORA92> ALTER TABLE product_detail
2 ADD CONSTRAINT product_detail_fk
3 FOREIGN KEY (comp_code)
4 REFERENCES prod_mast (compcode)
5 /
Table altered.
-- no statistics, no extra indexes:
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT a.prod_code,
2 a.descr,
3 a.packing,
4 a.comp_code,
5 b.name,
6 a.qty,
7 TO_CHAR (a.lsdate, 'dd-mm-yyyy') AS ldate,
8 ROUND (SYSDATE - a.lsdate) AS days,
9 a.sale
10 FROM product_detail a,
11 prod_mast b
12 WHERE a.comp_code = b.compcode
13 AND a.qty > 0
14 ORDER BY a.descr, b.name
15 /
P DESCR P COMP_CODE NAME QTY LDATE DAYS S
- ------- - ---------- ------------------------------ ---------- ---------- ---------- -
N VALID N 119868 AGG_RESULTS 119868 08-12-2005 48 N
...
N VALID N 85193 Z 85193 15-05-2005 255 N
181 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_DETAIL'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_MAST'
5 4 INDEX (UNIQUE SCAN) OF 'PROD_MAST_PK' (UNIQUE)
scott@ORA92> SET AUTOTRACE OFF
-- no statistics, with extra indexes:
scott@ORA92> CREATE INDEX product_detail_idx
2 ON product_detail (descr)
3 /
Index created.
scott@ORA92> CREATE INDEX prod_mast_idx
2 ON prod_mast (name)
3 /
Index created.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT a.prod_code,
2 a.descr,
3 a.packing,
4 a.comp_code,
5 b.name,
6 a.qty,
7 TO_CHAR (a.lsdate, 'dd-mm-yyyy') AS ldate,
8 ROUND (SYSDATE - a.lsdate) AS days,
9 a.sale
10 FROM product_detail a,
11 prod_mast b
12 WHERE a.comp_code = b.compcode
13 AND a.qty > 0
14 ORDER BY a.descr, b.name
15 /
P DESCR P COMP_CODE NAME QTY LDATE DAYS S
- ------- - ---------- ------------------------------ ---------- ---------- ---------- -
N VALID N 119868 AGG_RESULTS 119868 08-12-2005 48 N
...
N VALID N 85193 Z 85193 15-05-2005 255 N
181 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_DETAIL'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PROD_MAST'
5 4 INDEX (UNIQUE SCAN) OF 'PROD_MAST_PK' (UNIQUE)
scott@ORA92> SET AUTOTRACE OFF
-- with statistics, with extra indexes:
scott@ORA92> ANALYZE TABLE prod_mast
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> ANALYZE TABLE product_detail
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT a.prod_code,
2 a.descr,
3 a.packing,
4 a.comp_code,
5 b.name,
6 a.qty,
7 TO_CHAR (a.lsdate, 'dd-mm-yyyy') AS ldate,
8 ROUND (SYSDATE - a.lsdate) AS days,
9 a.sale
10 FROM product_detail a,
11 prod_mast b
12 WHERE a.comp_code = b.compcode
13 AND a.qty > 0
14 ORDER BY a.descr, b.name
15 /
P DESCR P COMP_CODE NAME QTY LDATE DAYS S
- ------- - ---------- ------------------------------ ---------- ---------- ---------- -
N VALID N 119868 AGG_RESULTS 119868 08-12-2005 48 N
...
N VALID N 85193 Z 85193 15-05-2005 255 N
181 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=19 Bytes=1102)
1 0 SORT (ORDER BY) (Cost=20 Card=19 Bytes=1102)
2 1 HASH JOIN (Cost=18 Card=19 Bytes=1102)
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_DETAIL' (Cost=2 Card=19 Bytes=589)
4 2 TABLE ACCESS (FULL) OF 'PROD_MAST' (Cost=15 Card=29645 Bytes=800415)
scott@ORA92> SET AUTOTRACE OFF
-- with statistics, without extra indexes:
scott@ORA92> DROP INDEX product_detail_idx
2 /
Index dropped.
scott@ORA92> DROP INDEX prod_mast_idx
2 /
Index dropped.
scott@ORA92> ANALYZE TABLE prod_mast
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> ANALYZE TABLE product_detail
2 COMPUTE STATISTICS
3 FOR TABLE
4 FOR ALL INDEXES
5 FOR ALL INDEXED COLUMNS
6 /
Table analyzed.
scott@ORA92> SET AUTOTRACE ON EXPLAIN
scott@ORA92> SELECT a.prod_code,
2 a.descr,
3 a.packing,
4 a.comp_code,
5 b.name,
6 a.qty,
7 TO_CHAR (a.lsdate, 'dd-mm-yyyy') AS ldate,
8 ROUND (SYSDATE - a.lsdate) AS days,
9 a.sale
10 FROM product_detail a,
11 prod_mast b
12 WHERE a.comp_code = b.compcode
13 AND a.qty > 0
14 ORDER BY a.descr, b.name
15 /
P DESCR P COMP_CODE NAME QTY LDATE DAYS S
- ------- - ---------- ------------------------------ ---------- ---------- ---------- -
N VALID N 119868 AGG_RESULTS 119868 08-12-2005 48 N
...
N VALID N 85193 Z 85193 15-05-2005 255 N
181 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=19 Bytes=1102)
1 0 SORT (ORDER BY) (Cost=20 Card=19 Bytes=1102)
2 1 HASH JOIN (Cost=18 Card=19 Bytes=1102)
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_DETAIL' (Cost=2 Card=19 Bytes=589)
4 2 TABLE ACCESS (FULL) OF 'PROD_MAST' (Cost=15 Card=29645 Bytes=800415)
scott@ORA92> SET AUTOTRACE OFF
|
|
|
|
| Re: Query Very Slow [message #156547 is a reply to message #156272] |
Wed, 25 January 2006 12:17 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Just wanted to add that a hash join does not require full table scans. But I see the FTS with the hash join way more often than I don't.
Also I'm moving this to performance tuning forum, as this is the exact type of question for that forum, and I'd love to see more traffic and contributers on it. I have been, and will continue to be for the foreseeable future, less active in posting than I was last year.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 18:35:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
