Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2012
    Posts
    29

    Unanswered: database connection and memory parameters

    I have 3 questions.

    I have 11.2.0.1 database on Windows 2008 R2 SP1.

    1) While connecting to the database as sysdba (sqlplus "/as sysdba"), it takes about 12 to 15 seconds
    Can you guys please help me what things I need to check/change so that connection will be faster?

    2) Also, I have read somewhere that changing the sga/memory parameters helps connecting to the database.
    is it true?

    3) This is not concerned with above 2 questions. What should be the memory parameters size for a 8 gb

    database?
    What I have is:
    Total System Global Area 1068937216 bytes
    Fixed Size 2182592 bytes
    Variable Size 1027605056 bytes
    Database Buffers 33554432 bytes
    Redo Buffers 5595136 bytes

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    How fast can you connect if you don't use "/ as sysdba" (i.e. if you specify the username& password)?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Aug 2012
    Posts
    29
    Hi,

    Even if i connect as other users, its still takes same time.

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Is this true if you connect from clients as well as the server? (I'm trying to work out if this could be a 'connection' issue or a 'server' issue)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    1) Run tnsping to check time to Listener
    2) Don't change anything until you know what is "taking long". You don't know if it has anything to do with mem. It probably does not.
    3) There is not much of a minimum regarding memory. Well actually 512MB SGA is the minimum in 10g. You SHOULD be using automatic memory management and have SGA_TARGET set to at least 512MB and SGA_MAX_SIZE set to at least 512MB (at least initially). Your memory requirement will be determined by how much your database is used.
    And no, more memory does not mean more performance. There is a sweet spot that you have to find. You will need to use STATSPACK or AWR to monitor the different ratios and adjust accordingly.

  6. #6
    Join Date
    Aug 2012
    Posts
    29
    1) tnsping connects in 20ms.
    2) I am not changing anything until I am sure.
    3) I have not set SGA_TARGET and PGA_AGGREGATE_TARGET parameters as I have set MEMORY_TARGET = 1G

  7. #7
    Join Date
    Aug 2012
    Posts
    29
    Is this true if you connect from clients as well as the server? (I'm trying to work out if this could be a 'connection' issue or a 'server' issue)
    From client side, I can connect normally. Issue is from server side only.

  8. #8
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    How long does it take to connect over TNS on the server ?

    sqlplus sys/<password>@<connect-string> as sysdba
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  9. #9
    Join Date
    Aug 2012
    Posts
    29
    it takes just about the same time .... 10 to 15 secs

  10. #10
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I remember encountering something like this before. My case had to do with the sqlnet.ora file that was configured to use LDAP or some other connection type other than TNSNAMES. It was taking a long time trying to resolve an LDAP name, would eventually timeout and would then try the TNSNAME at which point the connection succeeded.

    Check your SQLNET.ora.

  11. #11
    Join Date
    Aug 2012
    Posts
    29
    my sqlnet.ora is as simple as this:

    SQLNET.AUTHENTICATION_SERVICES = (NTS)

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •