Home » RDBMS Server » Performance Tuning » Query is running long ( 10.2.0.2.0 , sun solris)
| Query is running long [message #296860] |
Tue, 29 January 2008 06:18  |
amarg.dba@gmail.com
Messages: 3
Registered: January 2008
|
Junior Member |
|
|
Hi Guru's,
I have a problem i trid tuning this by 2 method's but i need your help on this.. below is the query..
SELECT
SUM ( AL1.RENTAL_COUNT ),
SUM ( AL1.TIME_AND_MILEAGE_AMT_NET_USD ),
AL2.MONTH_DESC,
AL2.YEAR_DESC,
AL2.MONTH_OF_YEAR,
AL5.DISTRIBUTION_CHANNEL_DESC,
AL5.CHANNEL_GROUP,
AL3.RENTAL_LOC_MNEMONIC,
AL3.RENTAL_LOC_DESC,
AL1.DRIVER_LICENSE_COUNTRY_CODE,
SUM ( AL1.LENGTH_OF_RENTAL_ACTUAL ),
SUM ( AL1.GPS_CHG_AMOUNT_USD ),
SUM ( AL1.INS_LDW_AMT_USD )
FROM
EISUSER.EIS_FACT AL1,
EISUSER.EIS_TIME AL2,
EISUSER.EIS_LOC AL3,
EISUSER.EIS_DISTRIBUTION_CHANNEL AL5
WHERE
(AL1.ENTERING_CHECKIN_DATE=AL2.DATE_VALUE
AND AL1.DW_CO_LOC_ID=AL3.RENTAL_DW_LOC_ID
AND AL1.DISTRIBUTION_CHANNEL=AL5.DISTRIBUTION_CHANNEL_ID)
AND (AL1.NON_REV_IND='NONE' AND AL2.YEAR_DESC IN ('2006', '2007')
AND AL3.RENTAL_LOC_MNEMONIC IN ('HNL', 'IAH', 'JFK', 'LAX', 'MCO', 'MIA', 'ORD', 'SAN', 'SFO', 'YYZ'))
GROUP BY
AL2.MONTH_DESC,
AL2.YEAR_DESC,
AL2.MONTH_OF_YEAR,
AL5.DISTRIBUTION_CHANNEL_DESC,
AL5.CHANNEL_GROUP,
AL3.RENTAL_LOC_MNEMONIC,
AL3.RENTAL_LOC_DESC,
AL1.DRIVER_LICENSE_COUNTRY_CODE
the xplain plan has been attached tooo..
I analyzed the tables as well as indexes also..i tried giving hints on indexes but no chance....
Please any one help me regarding this query .
Attached is the xplain plan..please help me it is urgent
-
Attachment: index.html
(Size: 58.11KB, Downloaded 1194 times)
[Updated on: Tue, 29 January 2008 06:33] Report message to a moderator |
|
|
|
|
|
| Re: Query is running long [message #296899 is a reply to message #296885] |
Tue, 29 January 2008 07:30  |
amarg.dba@gmail.com
Messages: 3
Registered: January 2008
|
Junior Member |
|
|
Hi Michel,
I think now you can see my query and xplain plan, I tried giving the hints like index , but my query is hanging..please help me out with your valuable ideas...i'm an 2 year exp in dba and this is my first query tuning in life and i tried by giving index hint,all_rows,first_rows
How to imporve my query...i need your sugeestions..
SQL Statement:
SELECT sum(al1.rental_count), sum(al1.time_and_mileage_amt_net_usd),
al2.month_desc, al2.year_desc, al2.month_of_year,
al5.distribution_channel_desc, al5.channel_group,
al3.rental_loc_mnemonic, al3.rental_loc_desc,
al1.driver_license_country_code, sum(al1.length_of_rental_actual),
sum(al1.gps_chg_amount_usd), sum(al1.ins_ldw_amt_usd)
FROM eisuser.eis_fact al1, eisuser.eis_time al2, eisuser.eis_loc al3,
eisuser.eis_distribution_channel al5
WHERE al1.entering_checkin_date = al2.date_value
AND al1.dw_co_loc_id = al3.rental_dw_loc_id
AND al1.distribution_channel = al5.distribution_channel_id
AND al1.non_rev_ind = 'NONE'
AND al2.year_desc IN ('2006', '2007')
AND al3.rental_loc_mnemonic IN ('HNL', 'IAH', 'JFK', 'LAX', 'MCO', 'MIA',
'ORD', 'SAN', 'SFO', 'YYZ')
GROUP BY al2.month_desc, al2.year_desc, al2.month_of_year,
al5.distribution_channel_desc, al5.channel_group,
al3.rental_loc_mnemonic, al3.rental_loc_desc,
al1.driver_license_country_code
Optimizer Mode Used:
COST ALL ROWS (optimizer: CHOOSE)
Total Cost:
1,334
Execution Steps:
Step # Step Name
27 SELECT STATEMENT
26 PX COORDINATOR
25 SYS.:TQ10005 PX SEND [QC (RANDOM)]
24 SORT [GROUP BY]
23 PX RECEIVE
22 SYS.:TQ10004 PX SEND [HASH]
21 HASH JOIN [BUFFERED]
4 BUFFER [SORT]
3 PX RECEIVE
2 SYS.:TQ10001 PX SEND [HASH]
1 EISUSER.EIS_DISTRIBUTION_CHANNEL TABLE ACCESS [FULL]
20 PX RECEIVE
19 SYS.:TQ10003 PX SEND [HASH]
18 HASH JOIN
8 PX RECEIVE
7 SYS.:TQ10002 PX SEND [HASH]
6 PX BLOCK [ITERATOR]
5 EISUSER.EIS_TIME TABLE ACCESS [FULL]
17 BUFFER [SORT]
16 PX RECEIVE
15 SYS.:TQ10000 PX SEND [HASH]
14 EISUSER.EIS_FACT TABLE ACCESS [BY GLOBAL INDEX ROWID]
13 NESTED LOOPS
11 INLIST ITERATOR
10 EISUSER.EIS_LOC_CCRG TABLE ACCESS [BY INDEX ROWID]
9 EISUSER.EIS_LOC_RNT_LOC_MNEMONIC INDEX [RANGE SCAN]
12 EISUSER.EISFACT_DW_CO_LOC_ID_IX INDEX [RANGE SCAN]
Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
1 This plan step retrieves all rows from table EIS_DISTRIBUTION_CHANNEL. 3 17 0.515
2 This plan step has no supplementary description information. 3 17 0.515
3 This plan step has no supplementary description information.
4 This plan step sorts the buffer row source. -- -- --
5 This plan step retrieves all rows from table EIS_TIME. 3 730 17.822
6 This plan step has no supplementary description information.
7 This plan step has no supplementary description information. 3 730 17.822
8 This plan step has no supplementary description information.
9 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index EIS_LOC_RNT_LOC_MNEMONIC. 2 10 --
10 This plan step retrieves rows from table EIS_LOC_CCRG through ROWID(s) returned by an index. 4 10 0.313
11 This plan step loops through the query's IN list predicate, executing its child step for each value found.
12 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index EISFACT_DW_CO_LOC_ID_IX. 2 71,748 --
13 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 1,327 553,497 43,241.953
14 This plan step retrieves rows from table EIS_FACT through ROWID(s) returned by a global partitioned index. 134 55,994 2,624.719
15 This plan step has no supplementary description information. 134 55,994 2,624.719
16 This plan step has no supplementary description information.
17 This plan step sorts the buffer row source. -- -- --
18 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 1,330 317,153 32,520.571
19 This plan step has no supplementary description information. 1,330 317,153 32,520.571
20 This plan step has no supplementary description information.
21 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 1,332 317,153 42,121.883
22 This plan step has no supplementary description information. 1,332 317,153 42,121.883
23 This plan step has no supplementary description information.
24 This plan step accepts a set of rows from its child node, and sorts them into groups based on the columns specified in the query's GROUP BY clause. 1,334 317,153 42,121.883
25 This plan step has no supplementary description information. 1,334 317,153 42,121.883
26 This plan step has no supplementary description information.
27 This plan step designates this statement as a SELECT statement. 1,334 317,153 42,121.883
|
|
|
|
|
|
|
|
| Re: Query is running long [message #296921 is a reply to message #296910] |
Tue, 29 January 2008 08:37 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Read the forum guide, it will be useful for you as you will learn that posting in CAPITALS is shouting against your correspondant and this is not the best way to get help.
You will also learn how to format your post and how to use the "Preview Message" button that you will find not so far from the "Submit Reply" one that you well found.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 05:08:07 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
