This discussion is archived
4 Replies Latest reply: Feb 21, 2013 2:03 PM by 556109 RSS

Minimal lightweight configuration on Linux

556109 Newbie
Currently Being Moderated
Hi,

I have installed Oracle XE 10g on Linux (32-bit) with modest resources (i.e. 512MB RAM).
Everything seems to run well.i
I intention is to use this installation to run very basic integration tests only.
So, it won't be used in multi-user or production environment.

I imagine, it should be possible to tweak the XE configuration and decrease its footprint even more,
to instruct automatic resources management to not to be ready for 20 simultaneous connections
(as I understand that's the default), but 5, and such.

Could anyone advise on the following settings?

1) Is it possible to decrease memory allocation? I have learned a bit about SGA and PGA settings, but I;m not sure what's a reasonable limit here.
2) Is it possible to disable number of connections , as explained above.
3) Is it possible to completely disable unused components, like some application servers, Java applications, and such. I have already tried to set the HTTP port to 0,
but there may be is more aggressive policy possible.
4) Any chance to cut down the nubmer of spawned processes?

Currenly, I'm getting this:
# ps aux | grep oracle
oracle   14065  0.0  0.5  21652  5864 ?        Ss   22:35   0:00 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit
oracle   14161  0.0  0.9 370360 10204 ?        Ss   22:36   0:00 xe_pmon_XE
oracle   14163  0.0  0.8 369744  8828 ?        Ss   22:36   0:00 xe_psp0_XE
oracle   14165  0.0  1.7 369744 18132 ?        Ss   22:36   0:00 xe_mman_XE
oracle   14167  0.0  1.1 371812 12048 ?        Ss   22:36   0:00 xe_dbw0_XE
oracle   14169  0.0  2.6 385296 28232 ?        Ss   22:36   0:00 xe_lgwr_XE
oracle   14171  0.0  1.3 369752 14076 ?        Ss   22:36   0:00 xe_ckpt_XE
oracle   14173  0.0  3.9 370276 41392 ?        Ss   22:36   0:00 xe_smon_XE
oracle   14175  0.0  1.3 369744 14064 ?        Ss   22:36   0:00 xe_reco_XE
oracle   14177  0.0  1.9 371340 20476 ?        Ss   22:36   0:00 xe_cjq0_XE
oracle   14179  0.0  4.0 372488 42588 ?        Ss   22:36   0:00 xe_mmon_XE
oracle   14181  0.0  1.0 369744 10892 ?        Ss   22:36   0:00 xe_mmnl_XE
oracle   14183  0.0  0.8 370408  8960 ?        Ss   22:36   0:00 xe_d000_XE
oracle   14185  0.0  0.8 370356  8628 ?        Ss   22:36   0:00 xe_s000_XE
oracle   14187  0.0  0.8 370356  8632 ?        Ss   22:36   0:00 xe_s001_XE
oracle   14189  0.0  0.8 370356  8624 ?        Ss   22:36   0:00 xe_s002_XE
oracle   14191  0.0  0.8 370356  8632 ?        Ss   22:36   0:00 xe_s003_XE
oracle   14196  0.0  0.9 369744  9968 ?        Ss   22:36   0:00 xe_qmnc_XE
oracle   14223  0.0  1.0 369740 10992 ?        Ss   22:36   0:00 xe_q000_XE
oracle   14225  0.0  0.8 369740  9288 ?        Ss   22:36   0:00 xe_q001_XE
Basically, what can I do to make the Oracle XE 10g configuration on Linux as lightweight as possible?

I'd apreciate any hel,
Mateusz Loskot

Edited by: mloskot on Feb 21, 2013 6:25 PM

Edited by: mloskot on Feb 25, 2013 12:29 PM
  • 1. Re: Minimal lightweight configuration on Linux
    clcarter Expert
    Currently Being Moderated
    If you're worried about process counts, the shared server and dispatcher (d000 and s00<n>) can be stopped by disabling shared server settings, but that will break Apex. But if you're going lightweight, you're probably not worried about running web services. Also shut down job queue, and advanced queueing:
    alter system reset dispatchers scope=spfile;
    alter system reset shared_servers scope=spfile;
    alter system set job_queue_processes = 0 scope=both;
    alter system set aq_tm_processes = 0 scope=both;
    And shutdown/restart the instance.

    Most of the other processes can't be disabled. The memory monitor/management (mmon, mman) can't be disabled, at least I'm not away of a way to do so. Nor would I suggest trying it. The logwriter (lgwr), checkpoint (ckpt), process monitor (pmon) and system monitor are all vital to the heath of a working database, those have to be running or you won't have a running database.

    To control the allowed number of sessions and processes in the instance, the sessions and processes parameters control those.

    Don't go below about 40 on processes, a typical (well, not minimized) has around 20 or 25 background processes. By taking away the shared server settings, each session will require a dedicated server process, which probably counts toward the allowed processes as well.

    One of my XEs on linux has memory_target = 400M but its the 11g version, so the equivalent for 10g would be something like sga_target=300M and pga_aggregate_target=100M. But that's tiny. Don't expect to the instance to get lots of work done quickly with those values ;)
  • 2. Re: Minimal lightweight configuration on Linux
    556109 Newbie
    Currently Being Moderated
    clcarter wrote:
    If you're worried about process counts, the shared server and dispatcher (d000 and s00<n>) can be stopped by disabling shared server settings, but that will break Apex. But if you're going lightweight, you're probably not worried about running web services. Also shut down job queue, and advanced queueing:
    alter system reset dispatchers scope=spfile;
    alter system reset shared_servers scope=spfile;
    alter system set job_queue_processes = 0 scope=both;
    alter system set aq_tm_processes = 0 scope=both;
    And shutdown/restart the instance.
    I have applied these settings and the processes is this now,
    oracle    3404  0.0  0.5  21652  5840 ?        Ss   21:14   0:00 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit
    oracle    3409  0.0  0.9 345784 10132 ?        Ss   21:14   0:00 xe_pmon_XE
    oracle    3411  0.0  0.8 345168  8824 ?        Ss   21:14   0:00 xe_psp0_XE
    oracle    3413  0.0  1.2 345168 13332 ?        Ss   21:14   0:00 xe_mman_XE
    oracle    3415  0.0  1.0 347236 11404 ?        Ss   21:14   0:00 xe_dbw0_XE
    oracle    3417  0.0  2.6 360720 28240 ?        Ss   21:14   0:00 xe_lgwr_XE
    oracle    3419  0.0  1.3 345176 13868 ?        Ss   21:14   0:00 xe_ckpt_XE
    oracle    3421  0.0  3.8 345704 40804 ?        Ss   21:14   0:00 xe_smon_XE
    oracle    3423  0.0  1.3 345168 14040 ?        Ss   21:14   0:00 xe_reco_XE
    oracle    3425  0.1  3.8 346896 40404 ?        Ss   21:14   0:00 xe_mmon_XE
    oracle    3427  0.0  1.2 345168 13464 ?        Ss   21:14   0:00 xe_mmnl_XE
    So, I have disabled the shared server settings as you suggested. Is there any point in disabling the HTTP access with?
    EXEC DBMS_XDB.SETHTTPPORT(0);
    Most of the other processes can't be disabled. The memory monitor/management (mmon, mman) can't be disabled, at least I'm not away of a way to do so.
    Nor would I suggest trying it. The logwriter (lgwr), checkpoint (ckpt), process monitor (pmon) and system monitor are all vital to the heath of a working database, those have to be running or you won't have a running database.
    [...]
    I appreciate all these details, it's interesting for better understanding of what I'm dealing with.

    >
    One of my XEs on linux has memory_target = 400M but its the 11g version, so the equivalent for 10g would be
    something like sga_target=300M and pga_aggregate_target=100M. But that's tiny.
    Regarding memory, apparently, my installation defaults for SGA_TARGET_MAX was 280M. So, I have lowered it a bit to 256M:
    SQL>ALTER SYSTEM SET SGA_TARGET=256M SCOPE=BOTH
    
    SQL> show parameters pga;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    pga_aggregate_target                 big integer 94464K
    SQL> show parameters sga;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 280M
    sga_target                           big integer 256M
    As you see, the pga_aggregate_target is already lower than 100M.
    Can I lower it to 64M to not to damage the installation?
    Don't expect to the instance to get lots of work done quickly with those values ;)
    The tests I'm going to run a very basic short integration that mainly target execution of simple SQL commands, single connection and single table in use during testing.

    Thank you very much for these suggestions, it's all very informative!
  • 3. Re: Minimal lightweight configuration on Linux
    clcarter Expert
    Currently Being Moderated
    any point in disabling the HTTP access with?
    Won't hurt to disable HTTP, especially when its not being used. A general "best practice" with computers, if you don't use a service and its not needed, disable and turn it off. One less possible system exploit.
    SGA_TARGET_MAX ... lowered it a bit to 256M
    To see a change in the memory footprint, that requires a shutdown and startup. Usually that type of change is done to get more memory for the instance, not less. But note the sga_max_size parameter- if its not set, the sga_max_size value gets calculated based on all memory parameters, the log_buffer size, and a couple other things, and that is how much memory the instance "requests" from the host at startup.
    pga_aggregate_target lower it to 64M to not to damage...
    I'm not sure. Maybe even just 32M can work, depends on what goes on within the instance.

    A frequent mistake with tuning, making several changes all at once. If one change breaks or harms the instance, how can you be certain which change went the wrong direction? One change at a time is also a good "best practice".

    And mucking about with parameters can also be harmful, getting a bad parameter out of an spfile is not easy. If the instance won't start, changing or fixing a setting is nearly impossible.

    Its good to have an old style pfile for a "plan B" in case things go south, look in your alert log. Go to the bottom, and search backwards and find the most recent startup- there will be a stanza (section of lines) with all non-default parameters. Take just those lines and save them someplace, like in c:\temp\initXE.ora and:
    sqlplus /nolog
    ...
    conn /as sysdba
    ... ? connected to idle instance ... ???
    -- if not:
      shutdown immediate;
    -- or may have to crash the instance with
      shutdown abort;
    ... -- startup with the pfile backup
    startup pfile='c:\temp\initXE.ora';
    -- and build an spfile to overwrite the bad one
    create spfile from pfile='c:\temp\initXE.ora';
    ... -- and be sure to test it!
    shutdown immediate;
    ...
    startup;
    There's lots of good info in the 2 Day DBA guide http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/toc.htm and the full documentation sets (10g) at http://www.oracle.com/pls/db102/homepage or (11g) at http://www.oracle.com/pls/db112/portal.all_books can be quite helpful, the XE pages are just a small subset.
  • 4. Re: Minimal lightweight configuration on Linux
    556109 Newbie
    Currently Being Moderated
    Great notes, all clear now.
    Thanks for amazing help!

    Mat

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points