Critical Application Batch Performance

Sep 21, 2012

We have a critical application batch that runs daily and had a 7.5 hr window after which the application needs to come online. During the peak batch times the truncates are running very slow leading to slow down of the batch jobs quite considerably due to which the batch is going beyond the window.

Technical details:
Database version: 11.2.0.2
OS: HP-UX 11.31

The wait events that show up when the truncates are running are local write wait

enq: RO fast object reuse
enq: CR block range reuse ckpt
db file parallel write

The ASH reports show that the top sessions are those executing DMLs(insert, merge, update) and DDLs(Create/alter index & truncate). In addition to this it also shows that the blocking sessions are background wait events: CKPT and DBWR. Changes to DB configuration done with respect to addressing these issues are:

1) We have increased the DBWR processes to 2
2) Reduced the buffer cache size to 20G(from the original 30G)
3) Flushing the buffer cache before the batch begins in order to reduce the load on DBWR during the batch peak time
4) Set the parameter filessytem_io to SETALL(from none)
5) Tuned the EVA(SAN storage) to improve its performance - by distributing the loads evenly between the controllers, reducing the IO transfer block size, etc
6) Suggested using the reuse storage clause to improve truncate performance.

All of these have worked bring a semblance of control but the fact remains that the batch is generating more jobs(hence increasing data volume) over time due to it being the peak season. This causes an inevitable increase in the number of sessions all running DMLs and DDLs which are IO intensive operations.

Suggestions pending from our end:

1) Increase DBWR beyond 2. For this we need a H/W upgrade since we have maxed out the maximum number of DBWR that can be configured
2) Implementing asynchronous IO for DBWR which on HP-UX requires moving to raw disks. Hence have suggested using ASM.
3) Tuning the application to either reduce the IO generated or redistribute the jobs such that those with maximum loads don't run together

instead of truncating tables, can we rename the tables and delete them later . will this improve performance ?

View 1 Replies


ADVERTISEMENT

Performance In Batch Run

Sep 15, 2012

We are having a major issues with the batch run. we are using oracle 11g db. We run the scripts to populate the tables and then call scripts to run the extractions. The issue here each time we run the sql it takes so much inconsistent time.We have created index and run the db stats then run the extractions.The sql sometimes takes 10 minutes or sometimes takes hours to run? This is major show stopper of the project.

View 3 Replies View Related

Performance Tuning :: How To Set Commit_write Parameter To (BATCH) In Another Session

May 16, 2013

How to set commit_write parameter to 'BATCH' in another session.

View 3 Replies View Related

Latest Critical Patch Update?

Oct 14, 2010

identify the latest Critical Patch Update for 10g RAC. My DB server is 10g RAC 2 node cluster running on IBM Aix Pseries servers. The version is 10.2.0.3.

View 1 Replies View Related

Download Latest Critical Patches?

May 3, 2011

I want download latest critical patches of 11.1.0.7.0, 11.1.0.6.0. how can i download

View 1 Replies View Related

Oracle Critical Patch Update

Jul 18, 2012

I need to apply this Critical Patch in my databases but I have a doubt about if the component that I find out in the documentation is the component where the fix has to be applied or doesn't matter and I have to apply this fix on all my installations.

CVE#           Component          

CVE-2012-1740 - Oracle Application Express Listener
CVE-2011-3192 - Apache
CVE-2012-1737 - Enterprise Manager for Oracle Database
CVE-2012-1745 - The vulnerability affects Microsoft Windows platforms only.
CVE-2012-1746 - The vulnerability affects Microsoft Windows platforms only.
CVE-2012-1747 - The vulnerability affects Microsoft Windows platforms only.
CVE-2012-3134 - The vulnerability affects Microsoft Windows platforms only.
CVE-2011-4885 - PHP

View 3 Replies View Related

Security :: Oracle Critical Patch Updates?

Jun 7, 2013

Is this the correct forum to ask questions about Oracle CPU's?

I ran the CPU-JUL-2012 on a workstation for version 11.1.0.7, got Return Code 0 and "OPatch Succeeded".

Yet a Retina scan ran after shows no change....?

I've tried reading the availability document, but I have no experience at Oracle patching.

View 4 Replies View Related

Security :: Applying Critical Patch Update?

May 23, 2012

Just to confirm with you if we can apply the October 2011 Critical Patch Update (CPU) to address vulnerabilities covered from CPU 2007 up to CPU 2011?

The PC Server (staging) where patching will be applied is running under Windows Vista have not been patched since it's database creation. This is maintained by our Contractors.

View 2 Replies View Related

Server Utilities :: Pre Requisite For Oracle Critical Patch Updates

Jan 20, 2011

I need some detail information regarding the CPM Patches released by oracle quarterly.

Is it compulsory to set the CPM patch when it is being realized. How to set or apply this patch. (either by runInstaller or OPatch utility i am not sure about this)

What is the difference between the interim & CPM patches.

View 3 Replies View Related

RAC & Failsafe :: Can't START Instance After Apply Critical Patch And Reboot

Apr 21, 2011

This is 2 node RAC database. Usually every month my system admin was applying OS critical patch then reboot the server.

While doing this activity i have stop all CRS and DB services. As usual i have did same in both the nodes and applied the patch and reboot the server.

After tried to start the CRS service and database services was used the following command. Except node1 instances all services are started.

E:oraclecrsproduct11.1.0crsBIN>crs_Stat -t
Name Type Target State Host
------------------------------------------------------------
ora....main.db application ONLINE ONLINE inf-...b-03
ora....n1.inst application ONLINE OFFLINE
ora....n2.inst application ONLINE ONLINE inf-...b-04
ora....SM1.asm application ONLINE ONLINE inf-...b-03

[code].....

Then i have tried to applied to old ocr backup also, then also i am getting same error.

View 1 Replies View Related

Performance Tuning :: SQL Not Using Index From Application?

Jun 6, 2013

We have a DELETE statement when coming from application is not using index but when run from Toad or SQLplus as same user uses index. Explain plan also shows using index.I did a query on v$sql below is the output of the query( I have attached the same as a txt file). All the stats are up to date and confirmed from the developer the variable B1 is using the same datatype as column MAXMKY.

SQL_TEXTSQL_ID DISK_READSOPTIMIZER_HASH_VALUE
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah19,099,1891,846,735,884
DELETE LOTA WHERE MAXMKY=:B1 2g2prrp3z56ah0 1,846,735,884
OPTIMIZER_COST HASH_VALUEPLAN_HASH_VALUE MODULEPARSING_SCHEMA_NAME

[code].....

View 9 Replies View Related

Performance Tuning :: Application Slow In EE But Fast In XE

May 22, 2012

My ERP Application is responding fast while running reports or saving entries, if Oracle 10g Express Edition (XE) is installed. But in Oracle 10g Enterprise Edition or Standard Editions the same application is running very slow.

View 1 Replies View Related

Windows :: PL/SQL With Batch File

Aug 17, 2012

I am executing multiple PL/SQL files(.sql) with single batch file. The batch file sql.bat has got 3 sub sql sub-tasks to complete once its run. The sql.bat is show below

@Echo off

CD C:Report
echo Loadin tables from text file Report.txt
sqlplus security/password <c:Reportloader_security.sql
echo Creating Security table
sqlplus security/password <c:Reportcreating_security_final.sql
echo Inserting text file Security table
sqlplus security/password <c:Reportinsert_security_final.sql

PAUSE

The sql.bat runs perfectly if I double click on the sql.bat file separately. But if I call the sql.bat from a different batch file 'Final.bat' it throws the below error.

Error
-----------
Executing SQL commands and loading file into SQL tables
Loadin tables from text file Report.txt
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
[code].....

The Final.bat file calls other bat files too. It is as show below.

CD C:ReportSecurity
echo Merging all Files
CALL merge.bat

CD C:ReportSecurity
echo Deleting old files
CALL del.bat

CD C:ReportSecurity
echo Executing SQL commands and loading file into SQL tables
CALL sql.bat

View 11 Replies View Related

SQL & PL/SQL :: Batch ID From Child Status

Mar 27, 2013

Im looking for a query which returns the batch for which all the child should either be in 'A_STATUS','B_STATUS' or 'C_STATUS'. In this query im expecting a query which returns batch 2,3 and 4.

create table batch (batchid number);
insert into batch values(1);
insert into batch values(2);
insert into batch values(3);
insert into batch values(4);

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: How To Run A Batch File In Oracle

Aug 18, 2009

I have a .bat file in my client system,which will open a web page after executing(after double clicking on it).I want to execute the same batch file from my pl/sql block.So,after executing my pl/sql block that .bat file should execute,and it should open the same web page.

View 12 Replies View Related

Performance Tuning :: Application (software / Utility) For Report?

Jul 28, 2010

Now, I'm tired to capture one-by-part image, and realize.

I've not seen any application or utility to make this work easier to me.

View 4 Replies View Related

Performance Tuning :: How To Check SQL Queries Run By Application At Backend

Oct 28, 2010

Name some database tool from which I can check the SQL Queries which my application is running.

NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.

View 4 Replies View Related

Updating Millions Of Rows In A Batch

Nov 9, 2011

I have a table which has plenty of rows. In production, I would estimate it to be from 30 millions to 300 millions. I need to update on column (flag) in all the rows (created before certain date).Now saying just:

UPDATE MyTable SET flag = 3 WHERE created < to_date('2010-10-08 23:59:59', 'YY-MM-DD HH24:MI:SS');
COMMIT;

Does not seem like a good idea - the commit-buffer would become too big.I will write a PL/SQL script for this. The question is, whether I should:

a) Update each row separately, and commit after every 10000 rows. ( WHERE RowId = [rowId] )
b) Update 10000 rows with set of dates ( WHERE rowId > [some_row_id] AND RowId < [some_row_id_2]

In the latter example the some_row_ids would naturally be fetched. The rowIds come from sequence. So which one would be more effective?I am not too familiar with PL/SQL or Oracle for that matter.

View 2 Replies View Related

Tuning SQLs In A Batch Process

Mar 18, 2013

I'm having an issue with stale optimizer statistics for some SQLs that are run in a batch process. The problem is that the process runs many times during the day - sometimes 20 to 30 times. And each time, the tables are updated, i.e. rows are inserted or deleted, etc.

So eventually the optimizer statistics for those tables become stale and the performance of the SQLs start to slow down (a lot). How best to gather the optimizer stats on the tables so they don't become stale when the batch process runs each time? The problem is that I also can't add/modify the code in the batch process because it is delivered by the vendor as is.

View 1 Replies View Related

Generate Last Seven Days Batch Run Times

Jan 8, 2013

I would the query to do the following:

1. Make the jobname distinct, because it keeps giving me multiple entries for each jobname
2. Add the the start_time of SOD_start_data9_UAT1 to end_time fodba_MUAT1 to get the combined duration
3. CONCAT jobnames SOD_start_data9_UAT1 and end_time fodba_MUAT1
4. Generate the last seven days batch run times
5. Generate a report into .csv format and email out
6. I have access to sqlplus and plsql developer

select distinct JOBNAME, schedtab
, to_char(to_timestamp(trim(timestmp)
, 'YYYYMMDDHH24MISS') - numtodsinterval(elaptime / 100
, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') as start_time
[code]...

View 3 Replies View Related

SQLPlus Script To Run In Batch File

Nov 1, 2010

I am trying to create a batch file which will be executed with windows scheduled task. This batch file will have sqlplus script running Oracle query. I can run this query from the command prompt, no problem,

This is the sqlplus scipt to run query:
C:\Documents and Settings\testuser>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 19:54:25 2010
Copyright © 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: TEST1
Enter password:
Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
TEST1@DB> spool testuserfile
here goes my query
TEST1@DB> spool off
TEST1@DB> exit
Disconnected from Oracle Database 10g Release 10.2.0.4.0 - Production
C:\Documents and Settings\testuser>

How do I put this in a batch file. I was able to figure how to logon, but I get stuck at the spool command. My batch file starts like this:

cd C:\Documents and Settings\testuser
sqlplus TEST1/Password

View 1 Replies View Related

Forms :: Batch Creation At 10 Records

Dec 13, 2011

I'm working on a Self assessment project regarding our tax returns. Currently, this is how it works - a return lodged generates a return number, but is batched later. In the change proposed, they want the same process whereby a return is generated still, but at a count of 10 returns generated on the same screen, a batch is to be created and these 10 returns will have to be added to that batch. We are on Oracle 10G and work with Forms, Reports 10G and TOAD/SQL Plus as tools so I was thinking of changing it on Post-Query but suggestions are to add on to System Parameter table.

View 3 Replies View Related

SQL & PL/SQL :: Error Handing With Batch SQL Scripts

Jul 16, 2012

I want run servral scripts in batch, and I use autorun.bat to call main.sql, which including servral scripts. If there has any pl/sql error in script, then the script will stop to run, but not exit SQL*Plus. If the pl/sql must exit, can it output the error messages in a file?

Please don't use "whenever sqlerror exit|continue...", because it will exit pl*sql tool or continue to run the other sql, it's not easy to know where the error happened

autoRun.sql
---------------------------------------------------------------------------
sqlplus "sys/manager@ORADB as sysdba" @main.sql

main.sql
---------------------------------------------------------------------------
--WHENEVER SQLERROR Exit SQL.SQLCODE
select sysdate from dual;
@@test1.sql
@@test2.sql
[code]....

View 3 Replies View Related

Forms :: Run Batch File Through Form?

Jan 12, 2012

I create a form with button....whenever i click button my batch file should be run..

My batch file code :

@ECHO OFF
cd
ipconfig /all
pause

When-button-pressed trigger :

======================================
client_host('D: est_ipa.bat');

When i double click .bat file, it will get proper info.But when i call this .bat file thru form, it will show blank screen..why this .bat file not running ?

View 2 Replies View Related

PL/SQL :: Validating Connection From Batch File?

Sep 3, 2012

I am trying to create a .bat file where I need to take inputs for below variables

DNAME
UNAME
PSWD
So I have done like this
SET /p DNAME="Enter the Database Name "
SET /p UName="Enter the Username "
SET /p Pswd="Enter the Password "

Now I need to validate whether I am able to establish the connection

sqlpus -s "%UName%/%Pswd%@%DNAME%"

if the above connection is valid one then perform certain action else need to echo user that Access is denied.

(ex: I am trying to say if cond is valid

stmt-1
else
echo message to user. )

View 3 Replies View Related

Performance Tuning :: How To Track Delay In Displaying Data On Application GUI

Dec 21, 2011

What could be the reasons that some queries execute fast when executed on sqlplus on server, whereas the same queries run slower with same input values fed from application screen?

One issue I guess would be bind variable peeking while using application whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"

If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?

I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI

finally how to set arraysize in jdbc to improve performance by reducing roundtrips?

View 3 Replies View Related

Application Express :: Performance Depending On Condition Expression Type

May 24, 2013

I was just wondering what people's thoughts and experiences are regarding performance of conditional expressions.

Tony blogs about it here

[URL}.........

And gives the example

1) Value of item/column in Expression 1 = Expression 2Expression 1: P1_JOB

Expression 2: SALESMAN

2) PL/SQL Expression

Expression 1: :P1_JOB = ‘SALESMAN’>

Recently I've been trying to eke every shortcut I can with an application and noticed this can be a contributing factor when done dozens of times within a page render.

View 1 Replies View Related

Tuning Searching Into Clob In Batch Operation?

Jul 3, 2012

My application runs a batch procedure weekly once for searching 'A_Text' from a column in Table1 in a clob column in Table2 and inserts accordingly into another Table3.

code snippet is like this -

---
CURSOR cr_sn
IS
SELECT serial_number

[Code]....

TABLE1 will have at least 1.1 Million rows but not significantly more than this.

This procedure takes 24+ hours to complete. I tried -

1. putting parallel hint ( INSERT /*+ PARALLEL*/INTO Table3)

2. partitioning TABLE2 based on last_update_date and putting a where clause in the above query last_update_date ( last_update_date between date1 and date2)

View 3 Replies View Related

Server Utilities :: Backup Through Batch File

May 18, 2011

exp system/abc@c file=c:dmp_%date%.dmp log=c:log.txt owner=xyz

I run this script through batch file its working.Problem is dump file showing with this format C:dmp_wed.dmp.I want to date format like that C:dmp_18052011.dmp.

for date formatting.How I can add date format in a batch file.

View 5 Replies View Related

Precompilers, OCI & OCCI :: Batch Error Mode?

Mar 4, 2010

what is OCI_BATCH_ERRORS and BATCH ERROR Mode ?and how the user can set it ?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved