Re: To estimate maximum active sessions on my oracle database is reasonable to the approach?

Hi, Quanwen Zhao

>> because an AAS of 1 is equivalent to 100% of a CPU core

I don't think AAS has any relationship to the number of CPU cores or the utilization of CPU core ( 100% or 0%). AAS = Average Active SessionsAAS = DB Time/Elapsed Time(wall clock)DB Time = CPU Time + non-idle wait time ie if wall clock is 15 min,  accumulated DB time from all sessions during that 15 min time period is 150 min, then AAS= 10 , it does not matter how many CPU cores you have. When you have 8 CPU cores and you  observe you have  AAS=11 or  count=11 ( from  select count(*) from v$session where state='ACTIVE', I imagine this count as AAS in 1 second wall clock time, instantaneous AAS ), From what you described,   you seemed interpret this as  you must have 8 sessions on CPU 100%. This is not true completely. It can be one active session  hold a DML lock on a table row, all other 9 active sessions waiting for this lock. nothing to do with CPU count or utilization. ( This is what Jonathon Lewis's example tells us.  sorry I cannot include Jonathon's  reply  in my reply because  I use different emails to receive and send to oracle-l)

If your intended purpose is to estimate what are the maximum or reasonable AAS your database can have. I think only empirical approach makes sense. if you have CPU=8 then monitor from AAS=2xCPU= 16; if your CPU=192, I don' think monitor from 382 makes sense, instead monitor from 20. Then correlating with application metrics. In one of our production db, we have 24 CPUs, and I receive average active session alert greater than 200 from time to time, the system does not scale well obviously but no one cares :)  no complains, app team seems happy with what the database deliver to them.  So I think AAS is a good metrics to monitor db but need to correlate with other metrics. ( Look at OEM performance page, AAS with wait event in the same graph, this is ideal) Best regards,
Denis

--
http://www.freelists.org/webpage/oracle-l