Performance Tuning: PROCESSES

      The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to Oracle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user process. The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes for the database instance.

If you plan on running 50 user processes, a good estimate would be to set the PROCESSES initialization parameter to 70.

To count current number of process on database:
SQL> select count(*) from v$process;

  COUNT(*)
----------
       368

To display current value of process on database:
SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     6000
SQL>


To display current value of process on os:
$ ulimit -u
131072

To count current number of process on os:
ps -ef|wc -l
1299

To set value in database:
SQL> alter system set process=6000 scope=spfile;

To set value in OS:
vi /etc/security/limits.conf
orausr     soft    nproc       131072
orausr     hard   nproc       131072

Performance Tuning: Session_max_open_files

Performance Tuning: Session_max_open_files

        Specifies the maximum number of BFILEs that can be opened in any session. The maximum value for this parameter depends on the equivalent parameter defined for the underlying operating system. The range of value is 1 to either 50 or the value of MAX_OPEN_FILES defined at the operating system level, whichever is less

To display current value on database:
SQL> show parameter Session_max_open_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_max_open_files               integer     500
SQL>

To display current value on OS per user:
$ ulimit -Hn
65536
$ ulimit -Sn
65536

To set value in database:
SQL> alter system set session_max_open_files =500 scope=spfile;


To set value in OS:
vi /etc/security/limits.conf
orausr     soft    nofile       65536
orausr     hard   nofile       65536

To count number of data file on database:
SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
       180

SQL>

We can increase values of session_max_open_files whenever number of database files get increase.

Performance Tuning: db_keep_cache_size

Performance Tuning-db_keep_cache_size

         Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer.
According to Oracle documentation, a good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool.

To find the size of a particular table
select (x.blocks *8)/(1024) "Size in MB" from user_tables x where table_name = 'TBLMENU';


By selecting data from view v$db_cache_advice we can decide how much cache size should be set to db_keep_cache_size. Estimated Physical Read Factor equal to 1 is represent current value of db_keep_cache_size (2048 MB).

In this example, the output shows that if the cache was 384 MB, instead of the current size of 2048 MB—the estimated number of physical reads would increase by a factor of 2.01, or 101%. Hence, it is not advisable to decrease the cache size to 384MB.
However, increasing the cache size to 3840MB may potentially decrease reads by a factor of .87, or 13%. If an additional 1792MB memory is available on the system and the value of the SGA_MAX_SIZE parameter allows for the increment, it is advisable to increase the keep buffer cache pool (db_keep_cache_size) size to 3840MB.

To load a table tblMenu in KEEP buffer cache, we need to issue command as below:
ALTER TABLE TBLMENU STORAGE(BUFFER_POOL KEEP);

To unload a table tblMenu from buffer, we need to issue the following command:
ALTER TABLE TBLMENU STORAGE(BUFFER_POOL DEFAULT);

To check whether a table is keep in buffer, we need to issue the following command:
select s.segment_name,s.buffer_pool from dba_segments s where s.segment_name='TBLMENU';
SEGMENT_NAME    BUFFER_POOL
TBLMENU                 KEEP

Restore Duration Time for Database

     The query below is to show the duration of Restore Time for a Database.  It is very useful for a big database restoration that we need to know how long the restoration is completed. Please notice this script for your daily DBA jobs :). Please set the value to database which you want to know restoration duration time --SET @databaseName = 'TM201707'

/*

Query - Restore Duration Time for Database

Uses MSDB..RestoreHistory table for Start time and ErrorLog for Finish Time

*/

-- Name of the Database you want restore name of:
DECLARE @databaseName VARCHAR(50)
SET @databaseName = 'TM201707'



DECLARE @TSQL NVARCHAR(2000)
DECLARE @lC INT
DECLARE @ErrorLogStart DATETIME
DECLARE @CurrentLogStart DATETIME
SET @CurrentLogStart = GETDATE()

-- Error Message for ErrorLog search
DECLARE @errorLogResult VARCHAR(4000)
SET @errorLogResult = 'Restore is complete on database ''' + @databaseName
    + '''.  The database is now available.'

-- Results Table
CREATE TABLE #Restore
    (
      [database] VARCHAR(50) ,
      StartTime DATETIME ,
      EndTime DATETIME
    )

INSERT  INTO #Restore
        ( [database] )
VALUES  ( @databaseName )

-- Get start time by looking msdb..restorehistory and selecting the first record with your db
UPDATE  #Restore
SET     StartTime = ( SELECT TOP ( 1 )
                                restore_date
                      FROM      msdb.dbo.restorehistory
                      WHERE     destination_database_name = @databaseName
                    )
WHERE   [database] IS NOT NULL

-- Get end time by loading the errorlog into a temp table and filtering to find the restore command @errorLog Result


-- SET ErrorLogStart Date to Restore Start Time
SET @ErrorLogStart = ( SELECT   StartTime
                       FROM     #Restore
                       WHERE    [database] = @databaseName
                     )

CREATE TABLE #TempLog
    (
      LogDate DATETIME ,
      ProcessInfo NVARCHAR(50) ,
      [Text] NVARCHAR(MAX)
    )

CREATE TABLE #logF
    (
      ArchiveNumber INT ,
      LogDate DATETIME ,
      LogSize INT
    )

INSERT  INTO #logF
        EXEC sp_enumerrorlogs
SELECT  @lC = MIN(ArchiveNumber)
FROM    #logF

WHILE @lC IS NOT NULL
    BEGIN
        IF EXISTS ( SELECT  1
                    FROM    #TempLog )
            BEGIN
                SET @CurrentLogStart = ( SELECT TOP ( 1 )
                                                LogDate
                                         FROM   #TempLog
                                         ORDER BY LogDate
                                       )
            END
        IF ( @CurrentLogStart > @ErrorLogStart )
            BEGIN
                INSERT  INTO #TempLog
                        EXEC sp_readerrorlog @lC
                SELECT  @lC = MIN(ArchiveNumber)
                FROM    #logF
                WHERE   ArchiveNumber > @lC
            END
        ELSE
            BEGIN
                BREAK
            END
    END


   
UPDATE  #Restore
SET     EndTime = ( SELECT TOP ( 1 )
                            LogDate
                    FROM    #TempLog
                    WHERE   ProcessInfo = 'Backup'
                            AND [Text] LIKE @errorLogResult
                  )
WHERE   [database] IS NOT NULL



-- Return the Restore information
SELECT  [database] ,
        StartTime ,
        EndTime ,
        DATEDIFF(MINUTE, StartTime, EndTime) AS 'Restore Duration in Minutes' ,
        DATEDIFF(SECOND, StartTime, EndTime) AS 'Restore Duration in Seconds'
FROM    #Restore

-- Clean up
DROP TABLE #Restore
DROP TABLE #TempLog
DROP TABLE #logF

Sample Output:

Reference: https://sqlnotesfromtheunderground.wordpress.com/2014/10/02/restore-duration-time-for-database/