SQL: Tuning Queries

[venur]

Hi,

I am little confisued, as my query taking a lot of time to execute. I have created indexes too. But still I am not happy with time its taking to execute.

My query :

SELECT
A.SESSIONLOGID , A.LOGINTIME , A.LOGOUTTIME
, D.TABLENAME, D.LIMIT,
COUNT(B.PLAYERINMONEYID) NUMBER_OF_GAMES ,
SUM(((((floor((((F.STOPTIME-F.STARTTIME)*24*60*60) -
floor(((F.STOPTIME-F.STARTTIME)*24*60*60)/3600)*3600)/60))*60) +
(round((((F.STOPTIME-F.STARTTIME)*24*60*60) -
floor(((F.STOPTIME-F.STARTTIME)*24*60*60)/3600)*3600 -
(floor((((F.STOPTIME-F.STARTTIME)*24*60*60) -
floor(((F.STOPTIME-F.STARTTIME)*24*60*60)/3600)*3600)/60)*60))))))/60) TOTAL_TIME,
A.USERID,SUM(B.PLAYERRAKE) TOTAL_RAKE,SUM(B.BETAMOUNT) STAKES, D.LOWSTAKE || '/' || D.HIGHSTAKE HIGHLOW
FROM
PLAYERGAMELOG_R B
, SESSIONLOG A
, GAMEPOKERLOG C
, GAMETABLE D
, GAMELOG F
, PLAYERINMONEY E
WHERE
(E.SESSIONLOGID = A.SESSIONLOGID)
AND (B.GAMEPOKERLOGID = C.GAMEPOKERLOGID)
AND (C.POKERGAMETABLEID = D.POKERGAMETABLEID)
AND (C.GAMELOGID = F.GAMELOGID)
AND (E.PLAYERINMONEYID = B.PLAYERINMONEYID)
GROUP BY
A.SESSIONLOGID
, D.TABLETYPE
, A.LOGINTIME
, A.LOGOUTTIME
, D.TABLENAME
, D.LIMIT
, A.USERID
, D.LOWSTAKE
, D.HIGHSTAKE;


Total Records APPX:
A = 470000
B = 1500000
C = 80000
F = 2000000

It would be helpful if some one can tell me on what coulmns I should have indexes that makes my query run faster.

Thanks in Advance
venu

 

[sem]

As I suppose you're not very strong in performance tuning, first of all I'd suggest you to gather statistics and let Oracle cost-base optimizer do its work without any other intrudence. Then, in case it can't manage, send here table structures/indexes as well as execution plan of your statement.

Regards, Dima

 

[parbhani]

Hi there ,

I would suggest an experiment , as a first attempt .

- Table C has the least number of rows
- We are taking a complete join , means we want to select all rows from the participating tables , satisfing the conditions of join .
- you must have indexes on columns taking part in the 'where' clause and who are on the left hand side of '=' sign
- you have to try that , a table with considarable less number of rows should become a drive table , say for e.g. C
- try to get full table scan on 'C' even if there is an index , using hints
- A dummy condition can be put there on C like , "C.GAMELOGID > 0 " , if its not violating the logic of the system or query

Have a cool thaught and see what you can do.
I am not trying to re-write the SQL here , because that u can do better , I know.