Following Steps to perform. SYSAUX tablespace


SELECT *  FROM dba_jobs WHERE SCHEMA_USER='SYSMAN';

SELECT *  FROM SYSMAN.mgmt_metrics_1hour;

SELECT *  FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));

DELETE FROM SYSMAN.MGMT_SEVERITY WHERE LOAD_TIMESTAMP<TRUNC(ADD_MONTHS(SYSDATE,-2));
COMMIT;

SELECT trigger_name FROM dba_triggers WHERE trigger_name LIKE '%SEV%DEL%';

spool c:\del_severity.sql
SELECT 'exec sysman.em_severity.delete_current_severity('''|| target_guid || ''','''|| metric_guid ||''','''|| key_value ||''');' FROM sysman.MGMT_SEVERITY;
spool off
@c:\del_severity.sql
commit;

-------------
EXEC em_severity.delete_current_severity('142E273EE3BDA54ECF9C42EF7CCB7616','6E65075DA52ACA744B4B8C3FCB018289','/billing01');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:03:38 2011/115843');
COMMIT;

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','10633143D11A8FCF6CB1A732CEE5352A','Sat Dec 31 20:47:33 2011/134506');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','3E6F70DB22758B7B9756EF342180E7BB','SYSAUX');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','ARBORAD');

EXEC em_severity.delete_current_severity('85DE1181E5B760BBB8F70F97FFBD7070','F95BA0D95585002889E1ABF92B2DA7C3','SYSMAN');

COMMIT;
-----------------------------------
SELECT s.target_guid,
           s.metric_guid,
           s.key_value
       FROM mgmt_targets t JOIN mgmt_current_severity s
           ON s.target_guid != t.target_guid
      WHERE LOWER(t.target_name) LIKE '%myhost%';
     

SELECT DBMS_STATS.get_stats_history_retention FROM dual;
EXEC DBMS_STATS.alter_stats_history_retention(15);
EXEC DBMS_STATS.PURGE_STATS(SYSDATE-15);

col Mb FORM 9,999,999
col SEGMENT_NAME FORM a40
col SEGMENT_TYPE FORM a6
SET lines 120
SELECT SUM(bytes/1024/1024)Mb,segment_name,segment_type FROM dba_segments
 WHERE  tablespace_name='SYSAUX'
AND segment_name LIKE 'WRI$_OPTSTAT%'
AND segment_type='TABLE'
GROUP BY segment_name,segment_type ORDER BY 1 ASC;


spool c:\rebuild_index.sql
SELECT 'alter index '||segment_name||' rebuild online parallel (degree 14);' FROM dba_segments WHERE tablespace_name='SYSAUX'
AND segment_name LIKE '%OPT%' AND segment_type='INDEX';
spool off
@c:\rebuild_index.sql


SELECT *  FROM dba_indexes WHERE owner='SYS' AND status!='VALID';

Clear Table records after startup database

If user does not logout properly / kill task / services restarted then record of that user will present in tblrklogindetails table.

We can create trigger that will delete all records if database service will be restarted

create or replace trigger tg_clear_logindetails

    after startup

     on database

  declare

     j   integer;

    pragma autonomous_transaction;

  begin

    dbms_job.submit (j,  'begin execute immediate ''truncate table ldbo.tblrklogindetails''; end;');

   commit;

 end tg_clear_logindetails;

 /

If there is no records in the table then following Error message will popup.

Please change this message to some user friendly message

“Session is Disconnected from a server. Please Login again”

Or something else

In web application , client makes connection with server using IIS manager.

1)      User login into rk application and fire report

2)      Oracle service is restart in between. Oracle Not connected Error has started because connection is break between client and server.

3)      Error is coming until we have not logout and login again into application.

4)      Iisreset help to reconnect client server without logout application.

Resolution

When database is shutdown, application should automatically shutdown when showing oracle not connected .

Tblrklogindetails records should be clear.

Schedule Job for specific time interval

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'Test',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_rkexecutejob(13,'' ''); END;',
    start_date      => '01-APR-12 09.00.00 AM ASIA/CALCUTTA',
    repeat_interval=> 'freq=minutely;bysecond=0;byminute=00,15,30,45;byhour=9,10,11,12,13,14,15,16;byday=MON,TUE,WED,THU,FRI',
    enabled         => TRUE,
    comments        => 'JOB for rk test');
END;
/

The repaet_interval paramter of the scheduler configuration should have a freq value of minutely interval value of 5 and byhourly value of 9-17.

BEGIN

  DBMS_SCHEDULER.create_job (

    job_name        => 'analyze_queue',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN dbms_stats.gather_table_stats(''LDBO'',''TBLRKQUEUE'' ,force=>TRUE); END;',

    start_date      => '01-APR-12 10.00.00 AM ASIA/CALCUTTA',

    repeat_interval=> 'freq=minutely;interval=5;byhour=9,10,11,12,13,14,15,16,17;byday=MON,TUE,WED,THU,FRI',

    enabled         => TRUE,

    comments        => 'JOB to gather Queue Table statistics');

END;

/

IIS Performance Tuning

1)     Recycling Application Pool

Daily they have to restart IIS to clear the application pool.

An application pool is a set of one or more applications assigned to a set of one or more worker processes.

It is useful to recycle periodically because web apps might have a memory leaks or other conditions that get worse over time.

Schedule a recycle application pool once a day on off-peak hours. 

2)     Dedicated Application Pool for different applications

3)     Web Garden on IIS (Max Worker process) Allocate worker process to different CPU core

By default each Application Pool runs with a Single Worker Process (W3Wp.exe). We can assign multiple Worker Processes With a Single Application Pool. An Application Poll with multiple Worker process is called "Web Gardens". Many worker processes with the same Application Pool can sometimes provide better throughput performance and application response time. And each worker process should have their own Thread and Own Memory space.

Application Pool > Max Worker process

 IIS has a Maximum Worker Process setting per App Pool. For my AppPool, I increased this value to 16 on our 8 core (16 w/ hyperthreading) Win2008 Xeon server in hopes of getting more parallel processing of client requests for our IIS hosted WCF Service (net.tcp, .net 4.0). I am not sure that this is having the desired affect as I don't see 16 worker processes in task manager despite a heavy load from my multi-threaded test client.

Web gardens was designed for one single reason – Offering applications that are not CPU-bound but execute long running requests the ability to scale and not use up all threads available in the worker process.

The examples might be things like -

Applications that make long running database requests (e.g. high computational database transaction)

Applications that have threads occupied by long-running synchronous and network intensive transactions

Max Worker Process=10

Max Worker Process=no of CPU

4)     Limit the queue length for application pools

When running an IIS server in worker process isolation mode, limit the queue length for application pools to prevent large numbers of requests from queuing and overloading the IIS web server.
Adding a new request to the queue can result in exceeding the maximum queue length. In turn, the IIS server may reject the requests and send a 503 error to the client. Keep the queue length shorter to boost IIS performance.

5)     IIS HTTP compression

Enable HTTP compression on the Microsoft IIS server to use bandwidth more effectively. This results in faster response times between the IIS web server and compression-enabled browsers.

IIS can provide three compression options, including static files only, dynamic application responses, and both static and dynamic application responses.

Enable dynamic compression for the server when bandwidth is an issue as indicated by IIS server monitoring, but remember that compression consumes CPU time and memory resources significantly.

Sites > Compression>

6)      Use the Expires header in IIS7

Configure the HTTP Expires header to improve IIS server performance. Generally, continuous HTTP requests sent from new website visitors to display pages creates a massive load on the IIS server.

Configuring the Expires header allows caching pages (and page elements such as images, scripts, css, etc.) on the client side and helps avoid unnecessary HTTP requests.

In IIS 7, Expires headers can be enabled in the system.webServer section of the web.config file. Using the Expires header, you can determine how long files can be cached on the client side. This may be a certain number of hours or days in the future or a fixed date.

7)     Windows 2008 Network Settings

http://support.microsoft.com/kb/951037

8)     Change max concurrent requests per CPU setting

Max Concurent Request is calculated by following formula

(MaxWorkerThreads * no Of CPU) – (MinFreeThreads * no Of CPU)

This is 12 by default on a single-proc machine.

So performance does improve by adding more CORE/CPU(hardware) to your system

So If you have Two Processor with below configuration

maxWorkerThreads               100

minFreeThreads                      88

Than MaxConcurent Request would be

(100*2) - (88*2) =24 Concurent Request

If you add two more Core than

Than MaxConcurent Request would be

(100*4) - (88*4) =48 Concurent Request.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ASP.NET\2.0.50727\MaxConcurrentRequestsPerCPU

Set this to a value higher than 12 (12 is the default)

[DWORD]

Change the maxConcurrentRequestsPerCPU setting from 12 to 5000. This allowed IIS to process more requests concurrently.