Vishal desai’s Oracle Blog

June 1, 2016

Load Balancing maintenance tasks on RAC nodes using dbms_scheduler

Filed under: Tools — vishaldesai @ 2:04 am

In shared environment, with instance caging turned on, I had to perform maintenance during downtime window to reorganize few tables that are populated by AQ. I wanted to load balance sub tasks, use all available resources and complete task quickly. Here is little snippet of code I wrote to load balance tasks across multiple nodes in RAC cluster.

Create job table and populate job table with sub tasks as shown below. I didn’t had huge variation in table sizes so order was  not that important. But if there is huge outlier table in list, put them first on the list otherwise you may end up with one job at the end that starts towards the end and run for long time.

drop table job_table purge;
create table job_table( task_no number primary key, task varchar2(4000),status char(1)) tablespace users;
--status null - work to do, I - in progress, S - Success, F - Failure

insert into job_table values(1   , 'begin execute immediate ''alter table  schema_name.table_name1  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=1   ; commit; end;','');
insert into job_table values(2   , 'begin execute immediate ''alter table  schema_name.table_name2  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=2   ; commit; end;','');
insert into job_table values(3   , 'begin execute immediate ''alter table  schema_name.table_name3  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=3   ; commit; end;','');
insert into job_table values(4   , 'begin execute immediate ''alter table  schema_name.table_name4  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=4   ; commit; end;','');
insert into job_table values(5   , 'begin execute immediate ''alter table  schema_name.table_name5  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=5   ; commit; end;','');
insert into job_table values(6   , 'begin execute immediate ''alter table  schema_name.table_name6  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=6   ; commit; end;','');
insert into job_table values(7   , 'begin execute immediate ''alter table  schema_name.table_name7  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=7   ; commit; end;','');
insert into job_table values(8   , 'begin execute immediate ''alter table  schema_name.table_name8  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=8   ; commit; end;','');
insert into job_table values(9   , 'begin execute immediate ''alter table  schema_name.table_name9  move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=9   ; commit; end;','');
insert into job_table values(10  , 'begin execute immediate ''alter table  schema_name.table_name10 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=10  ; commit; end;','');
insert into job_table values(11  , 'begin execute immediate ''alter table  schema_name.table_name11 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=11  ; commit; end;','');
insert into job_table values(12  , 'begin execute immediate ''alter table  schema_name.table_name12 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=12  ; commit; end;','');
insert into job_table values(13  , 'begin execute immediate ''alter table  schema_name.table_name13 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=13  ; commit; end;','');
insert into job_table values(14  , 'begin execute immediate ''alter table  schema_name.table_name14 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=14  ; commit; end;','');
insert into job_table values(15  , 'begin execute immediate ''alter table  schema_name.table_name15 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=15  ; commit; end;','');
insert into job_table values(16  , 'begin execute immediate ''alter table  schema_name.table_name16 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=16  ; commit; end;','');
insert into job_table values(17  , 'begin execute immediate ''alter table  schema_name.table_name17 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=17  ; commit; end;','');
insert into job_table values(18  , 'begin execute immediate ''alter table  schema_name.table_name18 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=18  ; commit; end;','');
insert into job_table values(19  , 'begin execute immediate ''alter table  schema_name.table_name19 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=19  ; commit; end;','');
insert into job_table values(20  , 'begin execute immediate ''alter table  schema_name.table_name20 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=20  ; commit; end;','');
insert into job_table values(21  , 'begin execute immediate ''alter table  schema_name.table_name21 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=21  ; commit; end;','');
insert into job_table values(22  , 'begin execute immediate ''alter table  schema_name.table_name22 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=22  ; commit; end;','');
insert into job_table values(23  , 'begin execute immediate ''alter table  schema_name.table_name23 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=23  ; commit; end;','');
insert into job_table values(24  , 'begin execute immediate ''alter table  schema_name.table_name24 move parallel 8 compress for oltp''; update job_table set status=''S'' where task_no=24  ; commit; end;','');

commit;

 

Change number of instances in your environment, number of sub tasks that you want to run simultaneously on each node and sleep interval and execute pl/sql block.

DECLARE
   --how many instances you want to use
   v_instances            NUMBER := 3;
   --jobs on each instance. Each job is a task from job_Table
   v_jobs                 NUMBER := 2;

   v_available_instance   NUMBER := NULL;
   v_available_jobs       NUMBER := NULL;
   v_job_name             varchar2(100);
   c                      number;

   CURSOR c1
   IS
      SELECT *
        FROM job_table
      ;
BEGIN
   FOR v1 IN c1
   LOOP
      --start find available instance

         DECLARE
            CURSOR c2
            IS
               SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

            v_running_instance   number;
            v_running_jobs       number;
         BEGIN
            OPEN c2;
            LOOP
               FETCH c2 INTO v_running_instance, v_running_jobs;

               
                  v_available_instance := v_running_instance;
                  v_available_jobs := v_running_jobs;
                  
                        v_job_name := DBMS_SCHEDULER.generate_job_name ('MYJOB_');
                          DBMS_SCHEDULER.create_job (
                             job_name              => v_job_name,
                             job_type              => 'PLSQL_BLOCK',
                             job_action            => v1.task,
                             comments              =>    'Instance '
                                                      || v_available_instance
                                                      || ' task no '
                                                      || v1.task_no,
                             number_of_arguments   => 0,
                             start_date            => SYSTIMESTAMP,
                             repeat_interval       => NULL,
                             end_date              => NULL,
                             enabled               => FALSE,
                             auto_drop             => TRUE );
                             
                          DBMS_SCHEDULER.set_attribute (
                            name      => v_job_name,
                            attribute => 'instance_id',
                            value     => v_available_instance);   

                         DBMS_SCHEDULER.enable (name => v_job_name);
                      
                          
                          UPDATE job_table
                             SET status = 'I'
                           WHERE task_no = v1.task_no;

                          COMMIT;
                          dbms_lock.sleep(2);
                
                --EXIT WHEN c2%NOTFOUND;
                EXIT;
            END LOOP;

            CLOSE C2;
         END;

        WHILE 1 > 0 loop
            dbms_lock.sleep(5);
            SELECT count(1) into c
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, v_jobs jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= v_instances) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;
                if c > 0 then
                exit;
                end if;
        end loop;
            
        
   END LOOP;
END;
/

 

Now sit back relax and use your favorite monitoring tool and output from following SQL statements until task is complete.

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like '%MYJOB%' order by actual_start_date desc;

select * from DBA_SCHEDULER_RUNNING_JOBS where job_name like '%MYJOB%';

select * from dba_scheduler_jobs where job_name like '%MYJOB%';
select * from job_table order by status;
select status,count(1) from job_table group by status;

exec dbms_scheduler.stop_job('MYJOB_11014');
exec dbms_scheduler.drop_job('MYJOB_10997');

 SELECT running_instance, jobs running_jobs
                 FROM (SELECT a.running_instance, a.jobs - b.jobs jobs
                         FROM (    SELECT ROWNUM running_instance, 1 jobs
                                     FROM DUAL
                               CONNECT BY LEVEL <= 3) a,
                              (  SELECT running_instance,
                                        COUNT (running_instance) jobs
                                   FROM DBA_SCHEDULER_RUNNING_JOBS
                                  WHERE job_name LIKE '%MYJOB%'
                               GROUP BY running_instance) b
                        WHERE a.running_instance = b.running_instance(+))
                WHERE jobs IS NULL OR jobs > 0;

I used similar approach for other long running tasks such as index rebuild, gathering statistics on tables and indexes. Other post tasks such as convert table , indexes degree back to 1 etc that does not take long time, were ran from regular sqlplus script.

March 10, 2015

SQL Plan Tree Visualization

Filed under: Performance Tuning, SQL, Tools, Troubleshooting — vishaldesai @ 8:22 pm

Real Time SQL Monitor Graphical plan from Cloud Control at times is not legible. Using google chart and visualization techniques that I learned from Accenture Enkitec’s and Iggy’s blog, I have create three scripts to quickly display plan in graphical Btree format. The one from real time sql monitoring displays few more metrics such as cardinality, IO and time statistics and I have added some color logic such that if time spend in one plan line id is more than 10% of total elapsed time then display Timeline in red otherwise green.

@getplan_cursor_tree.sql – Display graphical btree plan from cache based on SQL ID and plan_hash_value

@getplan_awr_tree.sql – Display graphical btree plan from AWR metadata based on SQL ID and plan_hash_value

@getplan_realsqlmonitor_tree.sql – Display graphical btree plan from Real Time SQL monitoring based on SQL ID, sql_exec_id etc.

Modify hard coded lines in script:

SPO logs\getplan_realsqlmonitor_tree_&tmenvs..html;

host start chrome C:\Users\U267399\Desktop\Tools\scripts\logs\getplan_realsqlmonitor_tree_&tmenvs..html

Sample output from OEM:

 image

 

Sample html output using getplan_realsqlmonitor_tree.sql: 

image

Full html file https://dl.dropboxusercontent.com/u/48791336/getplan_realsqlmonitor_tree_031615_170343.html

Download

References:

1) http://mauro-pagano.com/2015/03/10/trouble-reading-an-execution-plan-maybe-sqld360-can-help/

2) https://iggyfernandez.wordpress.com/2010/11/26/explaining-the-explain-plan-using-pictures/

November 5, 2014

SQL Performance changing over time

Filed under: Performance Tuning, SQL, Tools — vishaldesai @ 9:43 pm

Here is another visualization to analyze sql performance changing over period of time using Excel.

Input Screen:

image

Enter the details such as username, password, tns, Days, Interval and SQL ID and click on run button.

Output Screen:

image

As you can see from above, IO Wait times on 8/16 and 9/2 were higher compared to 8/19, 8/21 and 8/26. Number of executions were more or less same on those days. On 9/9 and 9/10, IO wait times were at peak but that could be due corresponding increase in number of executions.

image 

Data 2 worksheet will show more details about other SQL statistics. Units for other statistics are different so its difficult to show these statistics in two dimensional chart format.

Formatting for some combination by Day/Interval may not format excel chart properly. You may have to adjust chart manually to make it legible.

Download (Will be updated on 11/08)

October 3, 2014

Compare Oracle SQL Plan in Excel

Filed under: Performance Tuning, Tools — vishaldesai @ 2:36 pm

 

Input Screen:

image

 

 

Output Screen:

image

Download xlsm file, input source and target details, double click on list box to populate plan hash values and select one item and click on Run button to compare plan. The output screen will highlight first line (only first) where it finds difference in operation.

Download

Compare Oracle Database Parameters in Excel

Filed under: Performance Tuning, Tools — vishaldesai @ 2:13 pm

Input Screen:

image

Output Screen:

image

It will highlight differences in red. I have added logic to mark it blue if values are different for parameters instance_number, thread, undo_tablespace and local_listener. You can add more parameters to that logic for further customization.

Download xlsm file, add tns alias to tnsnames.ora file on your windows client and start comparing parameters.

Download

May 15, 2014

IO Waits, Average Wait Time and Histogram Heat maps using SQL Developer

Filed under: Tools, Troubleshooting — vishaldesai @ 11:00 pm

Jeff Smith pointed out David Mann’s blog in recent session on SQL Developer Reporting. Using David’s base code I have developed SQL developer heat map reports to visualize, troubleshoot and compare IO related waits.

 image

 

image

From above we can clearly visualize that on 11-MAY-2013 between 2:00 – 3:00 pm average wait time for log file parallel write event was 27.3 ms. The number of waits during same time slot was 10801 which is equivalent to 3 waits per second and that’s not significant. If intermittent slowness was reported by application team and if it was related to commit wait class, DBAs need to engage SA/SAN team to investigate this further.

Below are IO wait histogram reports for log file parallel write event on same database instance. For OLTP workload you want to see bright red boxes for log file parallel write between 1 and 16ms buckets for wait counts and average wait times. Such heat maps can also be used to do relative performance comparison by Day or Day/Hour.

image

 

image

 

Download (I will upload SQL developer report definition files early next week.)

Another example of vertical stacked bar chart.

IO Wait Histogram by Count – db file sequential read

image

 

IO Wait Histogram by Avg Wait Time(s) – db file sequential read

image

April 18, 2014

Database Time Viewer

Filed under: Tools — vishaldesai @ 2:31 pm

DBTimeViewer is a great standalone tool created by Dominic Giles to monitor Oracle Database Time. It requires simple configuration file update which includes database connection information such as username, password, hostname, port and service. In large enterprise environment with hundreds of thousands of databases it becomes little difficult to update database.xml file every time you have to connect to different database. I have created simple combination of batch and SQL script which will automatically create database.xml.

Scripts:

Create SQL and Batch scripts with proper path settings:

Script dbtimemonitor.sql

set head off
set feed off
set linesize 200
set pages 9999
set feedback off
set verify off
set pagesize 0


set term off

spool C:\<dbtime path>\databases.xml

select '<?xml version = ' || '''' || '1.0' || '''' || ' encoding = ' || '''' || 'UTF-8' || '''' || '?>'  from dual
union all
select '<WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor">' from dual
union all
select  '<MonitoredDatabase><ConnectString>//' || a.host_name || '<domain>:<port>/' || b.name || '</ConnectString><Comment></Comment><Username>' || '&1' || '</Username><Password>' || '&2' || '</Password></MonitoredDatabase>'  from gv$instance a, v$database b
union all
select '</WaitMonitor>' from dual;

spool off
set term on
exit


Script dbtime.bat

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3

sqlplus -s "%DBUser%/%DBPass%@%DBTNS%" @C:\<path>\dbtimemonitor.sql %1 %2

cd C:\<path>
dbtimemonitor.bat

Output:

C:\<path>>dbtime.bat <dbuser> <dbpassword> <dbtns>

Capture4

November 26, 2013

Compare IO outliers

Filed under: Performance Tuning, Tools, Troubleshooting — vishaldesai @ 9:47 pm

Oracle SQL Developer has nice reporting capability to plot charts. To compare IO outliers using db_hist_event_histogram between two periods, I have created couple of reports for quick comparison.

When you click on report, it will prompt you to select database and input event, period one start snap and end snap, period two start and end snap.

Compare IO wait count outliers by %

image

Compare IO wait count outliers by Value

image

Compare IO wait time outlier by %

image

IO latency for all snaps by Day

image

IO latency for last 24 hours

image

Download link for report definition file.

PS Current version of SQL developer supports only 5 different colors for pie charts so there is overlap of colors in first 3 charts.

October 7, 2013

SQL Developer Child Reports and Drill Down

Filed under: Tools, Troubleshooting — vishaldesai @ 10:36 pm

SQL Developer has some cool reporting capability. Using these features and some of my favorite scripts, I can now quickly troubleshoot Oracle Database related issue.

Example: For given database session, I can run Tanel’s asqlmon, look at historical SQL performance using Kerry’s AWR scripts, and drill down into IO latency with just point and click.

 

September 3, 2013

Oracle IO latency monitoring

Filed under: Performance Tuning, Tools — vishaldesai @ 6:13 pm

I have converted Kyle Hailey’s oramon, to SQL format so that it can be run from client sqlplus window.

Sample Screenshot:

image

Usage:

oraiomon.sql <interval> <samples>

Download

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 40 other followers