Wednesday, May 9, 2012

Analyze Queue Table (locked table)




-----------------------------------------------------------Analyze perticular table------------------------------------------

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 01.00.00 PM ASIA/CALCUTTA',
    repeat_interval=> 'FREQ=HOURLY;INTERVAL=1',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'JOB to gather Queue Table statistics');
END;
/

exec DBMS_SCHEDULER.RUN_JOB('analyze_queue');

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------


create or replace procedure sp_analyzequeue as
BEGIN
FOR cur_rec IN (select owner,table_name from dba_tab_statistics where owner='LDBO' and stattype_locked is not null) LOOP
BEGIN
dbms_stats.gather_table_stats('' || cur_rec.owner || '' , ''|| cur_rec.table_name || '',force=>TRUE);
END;
  END LOOP;
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'analyze_queue',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_analyzequeue; END;',
    start_date      => '01-APR-12 02:00.00.00 AM ASIA/CALCUTTA',
    repeat_interval => 'freq=DAILY',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Analyze queue tables');
END;
/

exec DBMS_SCHEDULER.RUN_JOB('analyze_queue');

select table_name,num_rows,to_char(last_analyzed,'DD-Mon-YYYY HH24:MI:SS') last_anaylze from user_tables where table_name in (select table_name from dba_tab_statistics where owner='LDBO' and stattype_locked is not null);


Tuesday, May 8, 2012

Oracle 11g2 Database Replay


Oracle 11g2 Database Replay Features
Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes
Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.
Database Replay performs workload capture of external client workload at the database level and has negligible performance overhead. Capturing the production workload eliminates the need to develop simulation workloads or scripts, resulting in significant cost reduction and time savings. By using Database Replay, realistic testing of complex applications that previously took months using load simulation tools can now be completed in days. This enables you to rapidly test changes and adopt new technologies with a higher degree of confidence and at lower risk.
You can use Database Replay to test any significant system changes, including:
Database and operating system upgrades
Configuration changes, such as conversion of a database from a single instance to an Oracle Real Application Clusters (Oracle RAC) environment
Storage, network, and interconnect changes
Operating system and hardware migrations

You can use Database Replay to capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to test the effects of a system change without affecting the production system.
Database Replay supports workload capture on a system running Oracle Database 10g Release 2 and newer releases. In order to capture a workload on a system running Oracle Database 10g Release 2, the database version can be 10.2.0.4 or higher. Workload replay is only supported on systems running Oracle Database 11g Release 1 and newer releases.
Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory



 Oracle 11g2 Database Replay Steps
 
Create Tablespace “USERS”   SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/tstd11s/data/user01.dbf’ size 500m autoextend on maxsize 1g;  Create DIRECTORY to store Replay related files. 
 SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’;  Start Capture process 
 SQL>
BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => ‘tst_capture_7′, 

                                       dir
      => ‘dbreplay_CAPTURE_DIR’,                                       duration => NULL);
END;
 
These below activities will be capture in replay process 
 SQL>CREATE USER dbreplay_test IDENTIFIED BY dbreplay_test  QUOTA UNLIMITED ON users;SQL>GRANT CONNECT, CREATE TABLE TO dbreplay_test;SQL>CONN dbreplay_test/dbreplay_test 
SQL>CREATE TABLE dbreplay_test_tab ( 
  id
           NUMBER,
  description
  VARCHAR2(50),  CONSTRAINT dbreplay_test_tab_pk PRIMARY KEY (id)
) tablespace users; 

SQL>
BEGIN
  FOR i IN 1 .. 100000 LOOP    INSERT INTO dbreplay_test_tab (id, description)    VALUES (i, ‘Description for ‘ || i);
  END 
LOOP;  COMMIT;
END;
To stop the replay process 
 SQL>CONN / AS SYSDBA
SQL>
BEGIN
  DBMS_WORKLOAD_CAPTURE.finish_capture;
END;

To find a Capture ID, Name 
 SQL>conn / as sysdbaSQL> SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info(‘dbreplay_CAPTURE_DIR’)
FROM
   dual;
DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(‘dbreplay_CAPTURE_DIR’)
—————————————————————
                                                             8
SQL> COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;
         8 tst_capture_7 
     
 
To get report 
 DECLARE
  l_report
  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 8,format
     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
To export the replay work 
 BEGIN  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 8);
END;
 – At Test Database —– 
 To create tablespace “USER” 
 SQL>CREATE TABLESpace users DATAFILE ‘/u01/oracle/dbdata/dbat01/data/dbat01_user01.dbf’ size 500m autoextend on maxsize 1g;  Create DIRECTORY to copy replay files 
 SQL>CREATE OR REPLACE DIRECTORY dbreplay_capture_dir AS ‘/u01/oracle/dbdata/dbreplay_capture/’;  To initial and prepare 
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture(‘dbreplay_CAPTURE_DIR’);    DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => ‘tst_capture_7′,
                                          replay_dir
  => ‘dbreplay_CAPTURE_DIR’);    DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
 
To calibrate the replay   #wrc mode=calibrate replaydir=/u01/oracle/dbdata/dbreplay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 15:43:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Report for Workload in: /u01/oracle/dbdata/dbreplay_capture
———————–
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 5

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

/u01/oracle/dbdata/dbreplay_capture/cap
  
To Start Replay process at Test Database 
 #wrc system/xxxx@dbat01  mode=replay replaydir=/u01/oracle/dbdata/dbreplay_capture
Workload Replay Client: Release 11.2.0.1.0 – Production on Tue Jan 18 16:02:48 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Wait for the replay to start (16:02:51)
…….
 
Open another new session at test Database 
————-
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>
BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;

PL/SQL procedure successfully completed.
————–Back to 1st session at test Database 

…….
Wait for the replay to start (16:02:51)

Replay started (16:33:26)

Replay finished (17:10:39)
  
To check replay work at test enviroment  
 
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = ‘dbreplay_TEST’;

TABLE_NAME
——————————
dbreplay_TEST_TAB

SQL> SELECT COUNT(*) FROM dbreplay_test.dbreplay_test_tab;

  COUNT(*)
———-
    100000

 
SQL> set pagesize 0 long 30000000 longchunksize 3000
SQL> set line 2000
SQL> select dbms_workload_replay.report(8,’TEXT’) from dual;
DB Replay Report for test_capture_8
—————————————————————————
| DB Name | DB Id
     | Release    | RAC | Replay Name
    | Replay Status |
—————————————————————————
| DBAT01
  | 313434807 | 11.2.0.1.0 | NO  | test_capture_3 | COMPLETED
     |
—————————————————————————

Replay Information
——————————————————————————————-|
   Information    | Replay                               | Capture
                              |
——————————————————————————————-| Name
             | test_capture_3                       | test_capture_3
                       |
——————————————————————————————-| Status
           | COMPLETED                            | COMPLETED
                            |
——————————————————————————————-| Database Name
    | DBAT01                               | tstd11s
                              |
——————————————————————————————-| Database Version | 11.2.0.1.0
                           | 11.2.0.1.0
                           |
——————————————————————————————-| Start Time
       | 18-01-11 21:33:26                    | 18-01-11 15:46:02
                    |
——————————————————————————————-| End Time
         | 18-01-11 22:07:49                    | 18-01-11 16:20:29
                    |
——————————————————————————————-| Duration
         | 34 minutes 23 seconds                | 34 minutes 27 seconds
                |
——————————————————————————————-| Directory Object | dbreplay_CAPTURE_DIR
                | dbreplay_CAPTURE_DIR
                |
————————————————————————————————–
| Directory Path
   | /u01/oracle/dbdata/dbreplay_ca | /u01/oracle/dbdata/db_replay_ca |
|
                  | pture/                               | pture/
                               |
————————————————————————————————–

Replay Options
———————————————————
|
       Option Name       | Value
                       |
———————————————————
| Synchronization
         | SCN
                         |
———————————————————
| Connect Time
            | 100%
                        |
———————————————————
| Think Time
              | 100%
                        |
———————————————————
| Think Time Auto Correct | TRUE 
                       |
———————————————————
| Number of WRC Clients
   | 1 (1 Completed, 0 Running ) |
———————————————————

Replay Statistics
—————————————————————
|
        Statistic        | Replay           | Capture
        |
—————————————————————
| DB Time
                 |
   76.383 seconds | 56.847 seconds |
—————————————————————
| Average Active Sessions |
              .04 |
            .03 |
—————————————————————
| User calls
              |               95 |
             95 |
—————————————————————
| Network Time
            |    1.101 seconds | .
              |
—————————————————————
| Think Time
              | 1992.558 seconds | .
              |
—————————————————————

Replay Divergence Summary
——————————————————————-
|
                Divergence Type
                | Count | % Total |
——————————————————————-
| Session Failures During Replay
                |     0 |
    0.00 |
——————————————————————-
| Errors No Longer Seen During Replay
           |     0 |
    0.00 |
——————————————————————-
| New Errors Seen During Replay
                 |     0 |
    0.00 |
——————————————————————-
| Errors Mutated During Replay
                  |     1 |
    1.05 |
——————————————————————-
| DMLs with Different Number of Rows Modified
   |     0 |
    0.00 |
——————————————————————-
| SELECTs with Different Number of Rows Fetched |
     4 |
    4.21 |
——————————————————————-

——————————————————————————————-Workload Profile Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Service/Module/Action
————————————————–
| No data exists for this section of the report. |
————————————————–
Top SQL with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Top Sessions with Top Events
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Divergence Session Failures By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
Error Divergence By Application
——————————————————————————————-| Service
   | Module    | Action    | Capture   | Replay
    | Count | First Occ | Last Occu |
| Name
      | Name      | Name      | Error     | Error     |       | urrence   | rrence
    |
——————————————————————————————- SYS$USERS | SQL*Plus
  | UNNAMED   | ORA-00904 | ORA-15566 | 1
     | 2011-01-1 | 2011-01-1 |
|
           |           |           |           |           |
       | 8T16:38:0 | 8T16:38:0 |
|
           |           |           |           |           |
       | 5.689633- | 5.689633- |
|
           |           |           |           |           |       | 05:00     | 05:00
     |
——————————————————————————————-
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Session
——————————————————————————————-
| Session ID | Session
      | Capture      | Replay Error | Count | First        | Last
         |
|
            | Serial       | Error        |              |       | Occurrence   | Occurrence
   |
——————————————————————————————-
| 34
         | 293          | ORA-00904    | ORA-15566    | 1
     | 2011-01-18T1 | 2011-01-18T1 |
|
            |              |              |              |
       | 6:38:05.6896 | 6:38:05.6896 |
|
            |              |              |              |       | 33-05:00     | 33-05:00
     |
——————————————————————————————-DML Data Divergence By Application
————————————————–
| No data exists for this section of the report. |
————————————————–
By SQL
————————————————–
| No data exists for this section of the report. |
————————————————–
By Divergence magnitude
————————————————–
| No data exists for this section of the report. |
————————————————–
SELECT Data Divergence By Application
——————————————————————————————-| Service
  | Module   | Action   | Avg Rows | Avg      | Number
   | Count | First Oc | Last Occ |
| Name
     | Name     | Name     | Affected | Absolute | of       |       | currence | urrence
  |
|
          |          |          |          | Rows     | Distinct |       |          |
          |
|
          |          |          |          | Affected | Sessions |       |          |
          |
——————————————————————————————-| SYS$USER | sqlplus@ | UNNAMED
  | -3       | 3        | 2        | 4
     | 2011-01- | 2011-01- |
| S
        | artp6    |          |          |          |          |
       | 18T16:38 | 18T16:56 |
|
          | (TNS     |          |          |          |          |
       | :48.0546 | :08.9631 |
         | V1-V3)   |          |          |          |          |
       | 23-05:00 | 96-05:00 |
——————————————————————————————-By Divergence magnitude
——————————————————————
| Max divergence magnitude | Divergence distribution (%) | Count |
——————————————————————
| no rows affected
         | 100                         | 4
     |
——————————————————————
Replay Clients Alerts
————————————————–
| No data exists for this section of the report. |
————————————————–
Replay Filters
————————————————–
| No data exists for this section of the report. |
————————————————–

End of Report. 
 

Followers