performance issue's 1

[PappaG]

Hi All

Can anyone make this run better, tried running today and i had DB guys calling me throwing a hairy fit. I know my end it appeared to eat up all the availible space on the temp table but the DB guys said it was bringing the system to its knee's.

SELECT 
	T.DATE_YYYYMMDD, 
	T.HOUR_HH24, 
	R.BRAND, 
	Sum(V.N_ENTERED) AS SumOfN_ENTERED,
	Sum(V.N_DISTRIBUTED) AS SumOfN_DISTRIBUTED,
	Sum(V.N_DISTRIB_IN_TR) AS SumOfN_DISTRIB_IN_TR,
	Sum(V.N_ANSWERED) AS SumOfN_ANSWERED,
	Sum(V.N_ABANDONED) AS SumOfN_ABANDONED,
	Sum(V.N_ABANDONED_IN_TR) AS SumOfN_ABANDONED_IN_TR,
	Sum(VAB.N_ABANDONED_R) AS SumOfN_ABANDONED_R,
	Sum(VAB.N_ABANDONED_Q) AS SumOfN_ABANDONED_Q,
	Sum(VR.N_ENTERED_RETRY) AS SumOfN_ENTERED_RETRY,
	Sum(VR.N_ANSWERED_RETRY) AS SumOfN_ANSWERED_RETRY,
	Sum(V.T_DISTRIBUTED) AS SumOfT_DISTRIBUTED,
	Sum(V.T_ANSWERED) AS SumOfT_ANSWERED,
	Sum(V.T_ABANDONED) AS SumOfT_ABANDONED
FROM ((((DATAMART01.T_QUEUE_HOUR T
	INNER JOIN DATAMART01.V_QUEUE_HOUR V ON T.TIME_KEY = V.TIME_KEY) 
	INNER JOIN DATAMART01.V_Q_AB_HOUR VAB ON T.TIME_KEY = VAB.TIME_KEY) 
	INNER JOIN DATAMART01.V_Q_RETRY_HOUR VR ON T.TIME_KEY = VR.TIME_KEY) 
	INNER JOIN DATAMART01.O_QUEUE_HOUR O ON (V.OBJECT_ID = O.OBJECT_ID) 
	AND (VAB.OBJECT_ID = O.OBJECT_ID) 
	AND (VR.OBJECT_ID = O.OBJECT_ID)) 
	INNER JOIN ROUTING01.V_ROUTING R ON O.PRESENTATION_NAME = CONCAT(R.PRODUCT,'@NIVR')
GROUP BY T.DATE_YYYYMMDD, T.HOUR_HH24, R.BRAND
HAVING (((T.DATE_YYYYMMDD) Like '200705%'));

 

[SantaMufasa]

Here is what I would do to ensure that the query performed as fast as possible:[ul][li]Confirm that STATISTICS have been gathered recently on the tables involved.[/li][li]Ensure that columns that you use for joining are indexed.[/li][li]Replace "HAVING ((T.DATE_YYYYMMDD) Like '200705%'))" with "WHERE T.DATE_YYYYMMDD between '20070501' and '20070531'". ("WHERE" should appear immediately following all of your "FROM" clauses.) The reason this will be faster is a) the BETWEEN can use indexes that reside on DATE_YYYYMMDD, and b) WHERE will get rid of unwanted rows earlier.[/li][/ul]Let me know if any of this helps improve performance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[PappaG]

Cheers SantaMufasa

Think the only one i can do something with will be the dates, i only have read access to the database and our providers are talking about indexing it better but don't think there is much setup at the moment. I will try there where clause and hope no one has a hairy fit about it...lol

 

[SantaMufasa]

Frankly, Pappa, you can also confirm the freshness of the statistics by querying the "LAST_ANALYZED" column of ALL_TABLES (which every user should have query access against).

You also have access to ALL_TAB_COLUMNS, which tells which columns are indexed on which tables.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[PappaG]

Thanks again SandtaMufasa

The "where" and "between" was enough cut it down to 15 days data at a time instead of a complete month warned the DB guy i would be running it who allocated the temp table some extra space and no hairy fit. I will look at your other comments but already got the job done......have a star for your effort