ORACLE PARALLEL DML

ORACLE PARALLEL DML

ORACLE PARALLEL DML
There is a myth that if the table is not partitioned, then parallel DML will not work. This is no
longer true. Several years ago I had updated 35 million rows table (size 40GB) using parallel
DML option in 8i. The parallel update ran 6X faster than procedural update using PL/SQL.

In this paper, you will learn how to update a BIG Table using parallel DML option.

All the tests given below are conducted in 10gR2.

First, I created a table, T1 using ALL_OBJECTS. The table has 10,000 rows.

One of the quickest way to find out the effectiveness (throughput) of parallelism in your system
is to run a query (SELECT) using parallel option, and measure the throughput by querying
V$PQ_TQSTAT table. (Note: V$PQ_TQSTAT will be renamed to V$PX_TQSTAT in 11g).
alter session set db_file_multiblock_read_count = 128;

select /*+ FULL(t1) parallel(t1,8) */ * from tamil.t1 ;

select process, num_rows, bytes from v$pq_tqstat order by process;

PROCESS NUM_ROWS BYTES
---------- ---------- ----------
P000 1300 664452
P001 1300 664551
P002 1300 664551
P003 1300 664551
P004 1200 613428
P005 1200 613428
P006 1200 613428
P007 1200 613428
QC 10000 5111817 -- Query coordinator
If you see the number of rows evenly distributed among the parallel slave processes, then it
indicates that your system is well configured for parallel processing.
Case # 1 - Simple update on NON-PARTITIONED Table
I have built a table, T1 that has 3.11 Million rows.

SQL> desc t1
Name Null? Type
-------------------------- -------- ------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from t1;

COUNT(*)
----------
3111872

SQL> select table_name, num_rows, blocks, empty_blocks,
last_analyzed from user_tables
where table_name= 'T1' ;

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS LAST_ANALYZ
------------ ---------- ---------- ------------ -----------
T1 3116183 43085 0 05-MAR-2007
[I used DBMS_STATS.GATHER_TABLE_STATS procedure to gather table statistics with estimate
percent NULL(meaning 100 percent), but it reported more number of rows as you see in the
USER_TABLES].
Let me do the first test.

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

Elapsed: 00:00:00.01

SQL> update tamil.t1 set object_name = lower(object_name);
3111872 rows updated.

Elapsed: 00:09:34.33
SQL> commit;

Commit complete.

Elapsed: 00:00:00.13

A single process ran for 9 Min and 34 seconds.
Case # 2 Parallel Update on NON-PARTITIONED TABLE
I have shutdown the instance and restared.

SQL> alter session enable parallel dml ;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session force parallel dml parallel 8;

Session altered.

Elapsed: 00:00:00.01

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

Elapsed: 00:00:00.00

SQL> update /*+ full(t1) parallel(t1,8) */
2 tamil.t1 set object_name = lower(object_name);
3111872 rows updated.

Elapsed: 00:00:28.03
SQL> commit;

Commit complete.

Elapsed: 00:00:01.12
Conclusion:
The parallel update took just 28 seconds versus 9 min 34 seconds in a single process update.
By setting highest value to DB_FILE_MULTIBLOCK_READ_COUNT parameter, "PARALLEL DML"
and "FORCE PARALLEL DML" with correct degree of parallelism at the session level, you can
update a big table in a shorter time.

Do not attempt to process in parallel on a small table. In fact, it will run longer than what a single
process will take.

Agent Configuration Assistant Failed

Agent Configuration Assistant Failed

Today I hit the following error while trying to perform a standalone Grid Control Agent install:




There could be a number of reasons as to why the agent configuration assistant would fail while you are trying to install the Grid Control Agent. The commands which failed are listed in the $AGENT_HOME/cfgtoollogs/configToolsFailedCommands file.


[oracle@myserver agent10g] cat cfgtoollogs/configToolFailedCommands
# Copyright © 1999, 2009, Oracle. All rights reserved.
oracle.sysman.emcp.agent.AgentPlugIn


If you search metalink with “oracle.sysman.emcp.agent.AgentPlugIn” you will get a number of hits but the 2 most useful ones I have looked at are:

Subject:
Troubleshooting the error oracle.sysman.emcp.agent.AgentPlugIn raised by the Agent Configuration Assistant (AgentCA)
Doc ID:
740628.1


Subject:
Troubleshooting the 'oracle.sysman.emcp.agent.AgentPlugIn has failed' error
Doc ID:
734981.1


In my particular case, the agent failed because I entered an incorrect agent password during the install. Unfortunately there is no way to re-enter the password within OUI at this point so login to your server and follow the steps below:

1. cd $AGENT_HOME/bin
2. ./emctl stop agent
3. ./emctl unsecure agent
4. ./emctl secure agent
   
   • At this point you will be prompted for the agent registration password. If you input the incorrect password it will fail. Run this again until you enter the correct password.
   
   
5. ./emctl start agent

Now you should be able to login to your Grid Control console and see this target.

Setting EM Blackouts from the GUI and Command Line

Setting EM Blackouts from the GUI and Command Line

Oracle Enterprise Manager provides you with the ability to monitor your environments and alert you once specified thresholds have been reached. Blackouts allow you to suspend monitoring so you do not get notified. This is useful for scheduled maintenance windows, such as cold backups, where the application and/or database may not be available.

As well, blackouts also suspends data collection for the given targets. This means that certain metrics such as availability will not be affected.

To create a blackout from the GUI, login to Enterprise Manager, navigate to the target you would like to blackout and at the bottom of the page under Related Links you will see a Blackouts link. You will be brought to the following page:





On the screen above you can view any blackouts that may currently be in effect as well as create new ones. To create a new blackout click on the create button.



On this page you can create a name for the blackout, with the default being “Blackout-”. You can also select the targets you wish to set the blackout for. As you can see from the screenshot I am going to set a blackout for an infrastructure application server (infra10g).

You can also provide a reason for the blackout by click on the Reason drop down list. Quite a few are available, from Server Bounce to Security Patch. Jobs can be disabled by deselecting the Run jobs during the blackout checkbox. If your applying a security patch then you may not want a scheduled backup to run as it will either error or cause problems.



The next screen allows you to select which components within the target will be blacked out. I selected a full blackout but you can select certain members if the outage will only affect specific components.



This screen allows you to schedule the blackout. It can either start immediately or you can choose a date along with a duration. Blackouts can be repeating as well, so you only have to create one for that monthly maintenance window for example.



The last screen provides a summary and once you have finished reviewing click on the Finish button.

You can also set blackouts from the command line, which is useful if you have some maintenance scripts which are not executed from Enterprise Managers job system. I’ve only tested this on linux but it should be the same for windows.

To set a blackout for a list of targets:

emctl start blackout [[:]]…. [-d Duration]

To set a blackout for all targets on a host:

emctl start blackout [-nodeLevel] [-d ]

-nodeLevel tells the agent to stop monitoring all targets on the server.

-d Duration allows you to set a duration in the format of [days] hh:mm. ex. 1 02:05 means the blackout will last for 1 day, 2 hours and 5 minutes.

For example, to use this in a script in which all targets will be unavailable you would start a blackout at the beginning of the script and stop it at the end:

cd $AGENT_HOME/bin
./emctl start blackout alltargets-myserver –nodeLevel

cd $AGENT_HOME/bin
./emctl stop blackout alltargets-myserver

Troubleshooting

In case you hit issues with blackouts take a look at the following notes:

Subject: Agent Blackout Initiated By Emctl Command Not Ending Doc ID: 559577.1

Subject: EMDiagkit Download and Master Index Doc ID: 421053.1

Subject: How to Troubleshoot the EM 10gR1 Blackout Sub-system Doc ID: 284024.1

Subject: Troubleshooting Blackouts in EM 10g Grid Control using EMDiag Kit Doc ID: 300671.1

They provide alot of information and solutions to different scenarios. I hit an issue over the weekend in which the blackout didn’t end properly. When I tried to stop it from the command line:

[oracle@myserver ~]$ /u01/app/oracle/product/agent10g/bin/emctl stop blackout alltargets-myserver
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Blackout stop Error : Blackout name alltargets-myserver is invalid

When trying to end the blackout via Enterprise Manager:

Error stopping the blackout on "infra10g": ORA-20710: Agent-side blackouts cannot be edited or stopped ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 501 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_ENGINE", line 3262 ORA-06512: at "SYSMAN.MGMT_BLACKOUT", line 74 ORA-06512: at "SYSMAN.MGMT_BLACKOUT_UI", line 1167 ORA-06512: at line 1 .

To fix this problem I performed the following:
1. Shutdown the agent on the target server myserver
2. Removed the blackouts.xml file under $AGENT_HOME/sysman/emd
3. Used note 421053.1 to install the EMDiag kit
4. Logged in as sysman on the Enterprise Managers repository database and executed the following query:

select blackout_guid, blackout_name
from mgmt_blackouts;

BLACKOUT_GUID BLACKOUT_NAME
-------------------------------- ----------------------
30E2956CA329F0E59FBDF50951F2578E alltargets-myserver

5. Then executed:

exec mgmt_diag.KillBlackout(HEXTORAW(‘30E2956CA329F0E59FBDF50951F2578E’));

6. Restarted the agent on myserver and when I looked in Enterprise manager the blackout had cleared.

I have seen the command used above for some other scenarios but not this one specifically. Before executing any commands in your environment please test first.