We bumped a Linux 11.2.0.4 database up to a 12 gigabyte SGA and the login time went up to about 2.5 seconds. Then a Linux admin configured 12 gigabytes of HugePages to fit the SGA and login time went down to .13 seconds. Here is how I tested the login time. E.sql just has the exit command in it so this logs in as SYSDBA and immediately exits:
$ time sqlplus / as sysdba < e.sql ... edited out for space ... real 0m0.137s user 0m0.007s sys 0m0.020s
So, then the question came up about our databases with 3 gig SGAs without HugePages. So I tested one of them:
real 0m0.822s user 0m0.014s sys 0m0.007s
Same version of Oracle/Linux/etc. Seems like even with a 3 gig SGA the page table creation is adding more than half a second to the login time. No wonder they came up with HugePages for Linux!
Bobby
Interesting observations, thank you Bobby.
Thinking back a bit on my home lab, the login time sped up quite a bit when I finally got around to configuring HugePages on 11g/12c RAC.
Until now I had not made the connection between HugePages and login times. Login times are now quite fast.
time sqlplus jkstill/XXX@p1 <<EOF
select sysdate from dual;
EOF
…
real 0m0.080s
user 0m0.000s
sys 0m0.023s
Thanks for your comment Jared. I was surprised at how clear of a connection there was between HugePages and login time. I knew that HugePages was a good thing to do but did not realize that the benefit would be so visible.
Pingback: Huge Pages Speeds Startup of Web Servers | Bobby Durrett's DBA Blog