I have a query which is running really slow and want to know if I can use a function based index on ACC_DT. I have few indexes on the other columns in this table but its not helping in increasing the performance
AND ( ( P1.YR ) = '1990' AND
( TO_CHAR(P1.act_dt,'MM') ) = '12' ) and.....
is p1.yr a numeric column or a character column? If it is a numeric column (which it ought to be as you are storing a number in it) than drop the quotes around 12. It will prevent the usage of an index and slow down the query because Oracle needs to do a type conversion.
Second: you would need to create the index on TO_CHAR(act_dt,'MM'), not on act_dt itself, e.g
CREATE INDEX idx_act_dt ON p1 ( TO_CHAR(act_dt,'MM') )
You could also try using extract(month from p1.act_dt) = 12 instead, that might be quicker.
I have few indexes on the other columns in this table but its not helping in increasing the performance
Check your execution plan with EXPLAIN PLAN so that you know for sure where the performance is lost. That's the only way to know whether an index is used or not and why the query is slow.