| Problem with order by clause [message #171103] |
Mon, 08 May 2006 07:15  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
I have one query which has order by clause. If i run the query without order by clause it executes in mins but when i add that order by clause it takes hrs to complete & when i check the v$session_longops its message shows Table Scan: PAY_T: 78199 out of 126769 Blocks done and time_remaining is 21600.
Is it sort that is taking time or table scan? when i remove the order by clause this same table scan completes in mins. Why this is so?
Workarea_size_policy is set to AUTO and pga_aggregate_size is 4GB.Is 4GB not sufficent? How do i know how much i have to increase the size of pga_aggreage_size?
Sometimes tablescan is so fast that it completes first 120000 blocks in few mins but to scan last few blocks it takes hrs. Why it the reason for this? Is it because of I/O's have become very slow?
Please reply asap.
Thanks & Regards
Chandan Singh
|
|
|
|
|
|
| Re: Problem with order by clause [message #171307 is a reply to message #171104] |
Tue, 09 May 2006 06:31  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
| Quote: |
A few questions:
- Do you use a tool like TOAD?
- Do you measure the time needed to return ALL rows or only the first few rows
- How many rows will the query return?
|
-Yes, i am running query thru toad. 
-Query with order by clause was runing for 3 days and after that we have to terminate the session. Now, again i have executed the query without order by clause using set autotrace traceonly explain statistics. Its executing for almost 7 hrs and still going on. Initially in v$session_longops it showed table scan for AU_PYIF_T but after that it showed nothing.
-Haven't got the results yet but it will return more than I million rows.
Below is the size of tables.
TABLE_NAME NUM_ROWS
AU_PYIF_CC_T 6000000
AU_PYIF_T 6343345
ACCT_T 6953423
SER_T 8788390
EVT_AUD_T 11943231
EVT_T 641620480
if anybody can suggest on this how can i improve the performance.
SELECT
ACCT_T.ACCT_NO,
AU_PYIF_T.AU_PAR_OBJ_RV,
EVT_T_2.SER_OBJ_ID,
EVT_T.PROG_NAME,
SER_T.LOG,
EVT_T.CREAT,
AU_PYIF_T.AU_PAR_OBJ_ID,
EVT_AUD_T.OBJ_ID,
EVT_AUD_T.OBJ_RV,
EVT_AUD_T.OBJ_TY,
AU_PYIF_T_PRV.AU_PAR_OBJ_ID,
AU_PYIF_T_PRV.AU_PAR_OBJ_RV,
AU_PYIF_CC_T.DB_NM,
AU_PYIF_CC_T_PRV.DB_EP,
AU_PYIF_CC_T_PRV.DB_NM,
AU_PYIF_CC_T.DB_EP
FROM
EVT_AUD_T EVT_AUD_T,
EVT_T EVT_T,
AU_PYIF_T AU_PYIF_T,
EVT_T EVT_T_2,
ACCT_T ACCT_T,
SER_T SER_T,
AU_PYIF_CC_T AU_PYIF_CC_T,
AU_PYIF_T AU_PYIF_T_PRV,
AU_PYIF_CC_T AU_PYIF_CC_T_PRV
WHERE
EVT_AUD_T.OBJ_ID = EVT_T.POID_ID
AND EVT_AUD_T.OBJ_ID = AU_PYIF_T.AU_PAR_OBJ_ID
AND EVT_T.SESS_OBJ_ID = EVT_T_2.POID_ID
AND EVT_T.SESS_OBJ_TYP = EVT_T_2.POID_TYP
AND EVT_T.ACCT_OBJ_ID = ACCT_T.POID_ID
AND EVT_T_2.SER_OBJ_ID0 = SER_T.POID_ID
AND AU_PYIF_T.POID_ID = AU_PYIF_CC_T.OBJ_ID0
AND AU_PYIF_T.AU_PAR_OBJ_ID = AU_PYIF_T_PRV.AU_PAR_OBJ_ID
AND AU_PYIF_T_PRV.POID_ID0 = AU_PYIF_CC_T_PRV.OBJ_ID
AND EVT_AUD_T.OBJ_TYP = '/payinfo/cc'
AND EVT_AUD_T.OBJ_ID = AU_PYIF_T.AU_PAR_OBJ_ID
AND AU_PYIF_T.AU_PAR_OBJ_ID = AU_PYIF_T_PRV.AU_PAR_OBJ_ID
AND ((AU_PYIF_T.AU_PAR_OBJ_RV = 0 AND AU_PYIF_T_PRV.AU_PAR_OBJ_RV = 0)
OR AU_PYIF_T.AU_PAR_OBJ_RV > AU_PYIF_T_PRV.AU_PAR_OBJ_RV)
-----ORDER BY ACCT_T.ACCT_NO, AU_PYIF_T.AU_PAR_OBJ_ID
Execution Plan(without order by clause)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81188 Card=1512 Bytes=427896)
1 0 NESTED LOOPS (Cost=81188 Card=1512 Bytes=427896)
2 1 NESTED LOOPS (Cost=80810 Card=1512 Bytes=397656)
3 2 NESTED LOOPS (Cost=80432 Card=1512 Bytes=367416)
4 3 NESTED LOOPS (Cost=80054 Card=1512 Bytes=323568)
5 4 NESTED LOOPS (Cost=79676 Card=1512 Bytes=279720)
6 5 NESTED LOOPS (Cost=49320 Card=121423 Bytes=16270682)
7 6 HASH JOIN (Cost=18964 Card=121423 Bytes=8499610)
8 7 HASH JOIN (Cost=15073 Card=535178 Bytes=18196052)
9 8 TABLE ACCESS (FULL) OF 'AU_PYIF_T' (Cost=4741 Card=6343873 Bytes=107845841)
10 8 TABLE ACCESS (FULL) OF 'AU_PYIF_T' (Cost=4741 Card=6343873 Bytes=107845841)
11 7 PARTITION RANGE (ALL)
12 11 TABLE ACCESS (FULL) OF 'EVT_AUD_T' (Cost=2894 Card=853112 Bytes=30712032)
13 6 PARTITION RANGE (ITERATOR)
14 13 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'EVT_T' (Cost=1 Card=1 Bytes=64)
15 14 INDEX (UNIQUE SCAN) OF 'I_EVT__ID' (UNIQUE)
16 5 PARTITION RANGE (ITERATOR)
17 16 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'EVT_T' (Cost=1 Card=1 Bytes=51)
18 17 INDEX (UNIQUE SCAN) OF 'I_EVT__ID' (UNIQUE)
19 4 TABLE ACCESS (BY INDEX ROWID) OF 'AU_PYIF_CC_T' (Cost=1 Card=1 Bytes=29)
20 19 INDEX (RANGE SCAN) OF 'AU_PYIF_CC_T' (NON-UNIQUE)
21 3 TABLE ACCESS (BY INDEX ROWID) OF 'AU_PYIF_CC_T' (Cost=1 Card=1 Bytes=29)
22 21 INDEX (RANGE SCAN) OF 'AU_PYIF_CC_T' (NON-UNIQUE)
23 2 TABLE ACCESS (BY INDEX ROWID) OF 'ACCT_T' (Cost=1 Card=1 Bytes=20)
24 23 INDEX (UNIQUE SCAN) OF 'I_ACCT__ID' (UNIQUE)
25 1 TABLE ACCESS (BY INDEX ROWID) OF 'SER_T' (Cost=1 Card=1 Bytes=20)
26 25 INDEX (UNIQUE SCAN) OF 'I_SER__ID' (UNIQUE)
Execution Plan(with order by clause)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81249 Card=1512 Bytes=427896)
1 0 SORT (ORDER BY) (Cost=81249 Card=1512 Bytes=427896)
2 1 NESTED LOOPS (Cost=81188 Card=1512 Bytes=427896)
3 2 NESTED LOOPS (Cost=80810 Card=1512 Bytes=397656)
4 3 NESTED LOOPS (Cost=80432 Card=1512 Bytes=367416)
5 4 NESTED LOOPS (Cost=80054 Card=1512 Bytes=323568)
6 5 NESTED LOOPS (Cost=79676 Card=1512 Bytes=279720)
7 6 NESTED LOOPS (Cost=49320 Card=121423 Bytes=16270682)
8 7 HASH JOIN (Cost=18964 Card=121423 Bytes=8499610)
9 8 HASH JOIN (Cost=15073 Card=535178 Bytes=18196052)
10 9 TABLE ACCESS (FULL) OF 'AU_PYIF_T' (Cost=4741 Card=6343873 Bytes=107845841)
11 9 TABLE ACCESS (FULL) OF 'AU_PYIF_T' (Cost=4741 Card=6343873 Bytes=107845841)
12 8 PARTITION RANGE (ALL)
13 12 TABLE ACCESS (FULL) OF 'EVT_AUD_T' (Cost=2894 Card=853112 Bytes=30712032)
14 7 PARTITION RANGE (ITERATOR)
15 14 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'EVT_T' (Cost=1 Card=1 Bytes=64)
16 15 INDEX (UNIQUE SCAN) OF 'I_EVT__ID' (UNIQUE)
17 6 PARTITION RANGE (ITERATOR)
18 17 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'EVT_T' (Cost=1 Card=1 Bytes=51)
19 18 INDEX (UNIQUE SCAN) OF 'I_EVT__ID' (UNIQUE)
20 5 TABLE ACCESS (BY INDEX ROWID) OF 'AU_PYIF_CC_T' (Cost=1 Card=1 Bytes=29)
21 20 INDEX (RANGE SCAN) OF 'AU_PYIF_CC_T' (NON-UNIQUE)
22 4 TABLE ACCESS (BY INDEX ROWID) OF 'AU_PYIF_CC_T'(Cost=1 Card=1 Bytes=29)
23 22 INDEX (RANGE SCAN) OF 'AU_PYIF_CC_T' (NON-UNIQUE)
24 3 TABLE ACCESS (BY INDEX ROWID) OF 'ACCT_T' (Cost=1 Card=1 Bytes=20)
25 24 INDEX (UNIQUE SCAN) OF 'I_ACCT__ID' (UNIQUE)
26 2 TABLE ACCESS (BY INDEX ROWID) OF 'SER_T' (Cost=1 Card=1 Bytes=20)
27 26 INDEX (UNIQUE SCAN) OF 'I_SER__ID' (UNIQUE) |
|
|
|
| Re: Problem with order by clause [message #171471 is a reply to message #171307] |
Wed, 10 May 2006 02:42 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The NESTED LOOPS joins are killing you. It would probably be better if all of the joins were hash joins. The problem is that hash joins take up temp space, and Oracle has probably ascertained that there is not enough temp space to hash join the biggest tables.
You could try adding USE_HASH hints to force hash joins. If it fails (unable to allocate TEMP segment ...) then you could try USE_MERGE hints - sort-merge joins use a bit less temp space.
SELECT /*+ USE_HASH(EVT_AUD_T EVT_T AU_PYIF_T EVT_T_2 ACCT_T SER_T AU_PYIF_CC_T AU_PYIF_T_PRV AU_PYIF_CC_T_PRV)*/ ...
FROM ....
I'm also concerned about the INDEX RANGE SCANs. You are joining to some pretty big tables without using a unique/primary key. Can you guarantee that only a very small number of rows will match in these tables (pref. 0 or 1). If not, your result set could be exploding cartesian-product-like.
If hash or sort-merge joins fail, then you can either give up and go home, or change the underlying structure of the data. Possibilities include (from easiest to hardest)
- Create indexes that include every column that the SQL returns - this way Oracle does not need the TABLE ACCESS steps of the plan which is what is slowing it down.
- Ensure the partitioned tables are joined on the partition-key. Oracle can perform partition-wise joins, which are less intensive in their use of temp space.
- Do something involving IOTs or clusters. I'm not even going to describe it here cos it's way to dangerous to accept advice like this from someone you does not understand your data structures.
Ross Leishman
|
|
|
|
| Re: Problem with order by clause [message #171614 is a reply to message #171471] |
Wed, 10 May 2006 09:56 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi Ross,
Sorry to interrupt here.
However reading this i have a query here.
"You could try adding USE_HASH hints to force hash joins. If it fails (unable to allocate TEMP segment ...) then you could try USE_MERGE hints - sort-merge joins use a bit less temp space.
"
Since sort-merge sorts both tables and merges the result sets to answer the query,aren't there chances that it may take more temp space?
Also the primary or unique may be doing here range scan because of < operator.
I may be wrong but just clearing my concept here.
For fast retrieval of first vey few rows Nested Loop is feasible. But other times when we are thinking of total result set retrieval at optimum time can we say that Hash Joins are better than Nested Loop Joins and then Sort-Merge joins?
Thanks
Pratap
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
