PL/SQL :: Resource Planning And Jobs Grouping In Oracle
Mar 20, 2013
I have a requirement where I need to group sessions which takes time as given below when ran individually .There are a total of 32 sesions.I want to divide them into 7 groups.I want to submit each one of the 7 groups in a job.So there will be seven jobs on the whole.Execution flow will be group 1 completes ,then group 2 starts like that.Actually I don't want to bombard the database at a time with 32 jobs ,hence the idea of grouping 32 jobs among 7 groups.grouping the sessions so that on the whole ,resources are equally allocated among sessions in a group and which in turn makes the process complete faster. I want to achieve parallelism but at the same time database should not be loaded too much.I just want to know is there any good practice from oracle side while doing this type of grouping. I am planning to group the most time taking one with least time taking jobs.
S.NO Session_Name DURATION(HH:MM:SEC)
1 Session_1 00:11:56.202368
1 Session_2 00:00:00.058542
1 Session_3 00:00:00.055205
1 Session_4 00:00:00.043454
1 Session_5 00:00:00.039994
2 Session_6 00:08:05.786031
2 Session_7 00:00:00.579035
2 Session_8 00:00:00.537957
[code]...
View 0 Replies
ADVERTISEMENT
Jul 21, 2010
A batch job was running for a long time which executes a stored procedure that was doing physical deletes on an oracle table. This job ran for about 3 hrs and is still executing. Meanwhile another job(which was making indexes unusable in another table) has cancelled saying "resource busy and acquire with NOWAIT specified", i think it was ORA-0054! So i killed the first job and reran the second thinking the first one was taking up resources which resulted in the cancel of the second job. Now the second job ran successfully. Both these tables are not related to each other whatsoever...but why was there a cancel?
View 6 Replies
View Related
Oct 31, 2011
how to do a capacity planning for a DB ?
View 1 Replies
View Related
May 31, 2012
Am getting an output like this in oracle9i,
S.No Column1 Column2 Column3 DateCol
1 A B C 10/2001
2 A B C 03/2001
3 B B C 02/2001
4 B B C 01/2001
5 A B C 03/2000
But My real scenario is , i need to populate the output in below structure
S.No Column1 Column2 Column3 DateCol
1 A B C 10/2001
A B C 03/2001
2 B B C 02/2001
B B C 01/2001
3 A B C 03/2000
I dont know how to form the query , to retrieve the structure..
View 2 Replies
View Related
Apr 23, 2012
I want to select the IDs which have no IN or PLANNING status in AREA 9
ID 1 has AREA 9 and STATUS IN so dont select
ID 2 SELECT
ID 3 has area 9 BUT STATUS is OUT so Select.
So if an ID has has IN or PLANNING status in AREA 9 i do not wish to see it!
CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)
INSERT INTO DAN_T1 (ID,AREA,UNIT,STATUS) VALUES (1,6,'AA','IN');
[code]......
ID AREA UNIT STATUS
16AAIN
17ABPLANNING
17ACOUT
19ADIN
25ACIN
25AAOUT
35BAPLANNING
36DAIN
35CAIN
39CGOUT
WANT:
ID
2
3
View 2 Replies
View Related
Nov 20, 2012
I have a procedure that needs to be scheduled as a job. This procedure takes an average of 20-25 minutes to complete. The constraint is that these jobs are submitted using dbms_job.submit with an interval of 5 minutes.
For Example:
Job1 Procedure_B 20-Nov-12 21:05:00
Job2 Procedure_B 20-Nov-12 21:10:00
Job3 Procedure_B 20-Nov-12 21:15:00
Job4 Procedure_B 20-Nov-12 21:20:00
So, will Job2 execute only after Job1 completes [even though Job1 takes 25 minutes to complete] or is there any other mechanism for setting incompatibility for a job with itself.
View 5 Replies
View Related
May 6, 2011
I work an migration jobs Oracle Forms 4.5 INp to 10G.Have any sentence that i havent idea:
DEFINE TRIGGER
NAME = xxxxx
TRIGGER_TYPE = V2
DEFINE STEP
TEXT = <<<
#EXEMACRO NOFAIL NULL;
>>>
REVERSE = ON
ENDDEFINE STEP
special with REVERSE = ON...I need replace this en PL/SQL Oracle Forms 10G
View 6 Replies
View Related
Nov 22, 2010
Recently we are planning to migrate oracle 10g to 11g. We are migrating PHASE WISE (Schema wise) as this is a 50TB of Production instance. We are using Oracle Golden gate for this purpose.
Is there is any way that we can migrate oracle jobs from 10g to 11g?
View 6 Replies
View Related
Dec 20, 2010
My Oracle jobs are not running. I can run them manually but they don't start automatically as per interval. The job_queue_processes=100 and CJQ process is not running. I started CJQ0 process by resetting it to 0 and then to 100. But as soon as I enabled a job this background process stopped.
The job also did not execute in its next execution time. I have created the database manually without using DBCA. Is the problem because of this? I have set crontab immediately to get rid of it.
View 4 Replies
View Related
Mar 19, 2013
installing Oracle for a new Data-Center(still in planning).It will be an OLTP shop working 24/7, with 250 concurrent users.
1. Can it, in theory run with good performance on a quad processor(something like Intel Xeon Series 56XX) , with 32GB of memory?
2. What is the Oracle Database Edition required for this configuration?
3. What additional things should one take into account to plan the most cost-effective configuration?
View 2 Replies
View Related
Sep 5, 2005
How to
1) to define a job in oracle9i for solaris
2) to schedule this job
Remember i have prior experience deploying jobs at windows platform but when i try it on (9i for ) solaris, the script does not work as it does not accept a submit job request.
View 4 Replies
View Related
Feb 8, 2012
what jobs will be created when we upgrade to oracle 11.1 version ?
View 4 Replies
View Related
May 24, 2013
select *from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
prepare data:
CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;
sql1:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,LAG(CODE ignore nulls) OVER(ORDER BY ID )) CODE
FROM a;
the sql is slow,most of the waits on session is resmgr:cpu quantum and almost all of the cpu resource is occupied
sql2:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,last_value(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) CODE
FROM a;
this sql is fast
How lag function is implemented if the offset is not deterministic?
View 10 Replies
View Related
Sep 21, 2011
I have a question:
With this plan active
PLAN GROUP_OR_SUBPLAN TYPE CPU_P1 CPU_P2 CPU_P3
x_PLAN x_APP_GROUP CONSUMER_GROUP 75 0 0
x_PLAN x_BO_HIGH_GROUP CONSUMER_GROUP 10 0 0
x_PLAN x_BO_LOW_GROUP CONSUMER_GROUP 5 0 0
x_PLAN OTHER_GROUPS CONSUMER_GROUP 10 0 0
if there is only an user of X_APP_GROUP that is working in a specified moment, he will use the 100% of CPU or il will be limited at 75% as specified in the plan?
View 1 Replies
View Related
Sep 22, 2010
I am trying to get to where I can add a RAD (Resource Access Descriptor) to allow connection to an oracle database. Right now I have "No Configuration Privilege" when logging onto Oracle Identity Management.
How to I get to the configuration tab? What privilege do I need?
View 1 Replies
View Related
Sep 1, 2010
i have one procedure that run at same time for two different partition.In the begin of the processes i do the truncate partition and after that the insert of data in the partition.Since i have two processes running at the same time, i will fill up data in two different partition.
But it occurred one error :
"ORA-00054: resource busy and acquire with NOWAIT specified"
The problem is caused by the truncate partition. I'm doing two simultaneous execution of the same process, but when one process is doing the truncate other is insert data.How can i do the truncate of this two partition, or insert in to, without get any error. I think the two simultaneous insert is not a problem.
So how can i lock the table in the first truncate and after that release the table for the next truncate?
View 5 Replies
View Related
Oct 31, 2008
I want to change the processes parameter in RAC system.I am using spfile.I have ASM environment and two instances.
I followed the below steps from first server but i m getting error.
1) connected as sys user
2) alter system set cluster_database=false scope=spfile sid='<firstinstanceid>';
3) exited from oracle
4) stoped second instance from linux.
$ srvctl stop instance -d <DBname> -i <secondinstanceid>
5) Again i connected as sys user.
6) create spfile from pfile;
7) shutting down the db and started.
[code]....
But second instance is not starting and it is giving error.
PRKP-1001 : Error starting instance <sencondinstanceid> on node st-habam-db2
CRS-0215: Could not start resource 'ora.<DBname>.<secondInstanceid>.inst'.
View 5 Replies
View Related
Apr 6, 2012
i am working in scott's schema and i want to create foreign key on new table that is "test" table ,structure is----
create table test(id number, name varchar2(20),dno number,pno number);
and now create a foreign key on id column of "test" table reffering the empno of "emp" table which has pk already. but it shows an error-------"ora-ORA-00054-resource busy and acquire with NOWAIT specified or timeout expired"
while i have create the "test" table just 1 min ago.
View 3 Replies
View Related
Jun 5, 2013
am trying to load java resource in oracle llgr2 using load java. It shows resource loaded but I am not able find it in database dictionary. I configured JVM also on database and gave all the neccessary grants. whenever I am trying to encrpt it give me
SQL> HOST loadjava -u scott/scott1234@mock -v -resolve /data1/Encrypt/javasp/KeyAccess.class
arguments: '-u' 'scott/***@cmsmock' '-v' '-resolve' '/data1/Encrypt/javasp/KeyAccess.class'
creating : class javasp/KeyAccess
loading : class javasp/KeyAccess
[code]...
below are the db version details
SQL> select banner from v$version
2 ;
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
View 2 Replies
View Related
May 31, 2012
I am database developer and suddenly i have been given a DBA work. I have to monitor database for resource intensive queries , whether i/o is more , process memory and many other stuffs.
Provide me some sql queries or scripts which shows that process are over loaded and memory needs to be allocated, queries are taking long time , i/o operation are more instead of memory.
View 1 Replies
View Related
Dec 8, 2010
I am using dbms_sqltune package to tune some resource intensive queries. In this I am looking to know how Sql profile works
I created task for one sql using dbms_sqltune, then in Report it was recommended to accept its sql profile (which will potentially benefits 65%)
then accepted that sql profile with
exec dbms_sqltune.accept_sql_profile(task_name => 'pc1_61d2dhmdwzc8d', replace => TRUE);
and re-executed same query but NO difference in time. Then, what that mentioned potentially benefit 65% is about.
1) Originally query is taking 10 sec to execute, with this 65% i thought that it will execute within 3-4 sec. Is it right?
2)Also, the query for which i have created tuning task has some hardcoded input values, what if i change input values next time, will that profile works with new input values?
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 65.17%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'pc1_61d2dhmdwzc8d',
replace => TRUE);
View 2 Replies
View Related
Aug 19, 2013
I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).
View 3 Replies
View Related
Mar 27, 2013
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392
[code]....
View 4 Replies
View Related
Oct 14, 2011
I have a table like this
Name Hours date
a810/11/2011
a 510/12/2011
a610/13/2011
a710/14/2011
a710/15/2011
a810/16/2011
a710/17/2011
a810/18/2011
a810/19/2011
a710/20/2011
a710/21/2011
If i want the sum of hours for 3 days range ,how should i do it.
E.g. say
name hrs startdate enddate
a 19 10/11/2011 10/13/2011
a 22 10/14/201110/16/2011
a 23 10/17/2011 10/19/2011
View 11 Replies
View Related
Feb 10, 2013
How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id.
In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.
If, there is manager_id 117, it should create grp_id 3.
To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num
I am looking for an update statement for this issue.
Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
CREATE TABLE HR.EMPLOYEES_2
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE),
EMAIL VARCHAR2(25 BYTE),
PHONE_NUMBER VARCHAR2(20 BYTE),
[Code]....
Expected result
----------------
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID GRP_NUM GRP_ID
114 11000 100 30 1 1
115 3100 100 30 2 1
116 2900 114 30 1 2
117 2800 114 30 2 2
118 2600 114 30 3 2
119 2500 114 30 4 2
View 8 Replies
View Related
Nov 18, 2010
I am getting "ORA-00060: deadlock detected while waiting for resource" while deleting a datafile.
when i checked in dba_data_files and V$datafile the ONLINE_STATUS/STATUS is in RECOVER mode.
I do not need this datafile so i tried to drop using the following command ..
ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF';
I checked the Trace file geenrated for the deadlock, the Session which is in question is the session where i ran the command (SQLPLUS).
View 8 Replies
View Related
Nov 4, 2013
i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log
Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal) Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System.
[code]....
View 11 Replies
View Related
May 13, 2011
why there is no "Row Source Operation" information when I trace a long and complicated SQL query (It takes at least 25 minutes ro run)?
However, I can get the "Row Source Operation" information for a simple SQL Query.
Here are the tkprof output for a simple query and complicated query:
tkprof Output for a simple query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 16 0 173
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 16 0 173
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139
Rows Row Source Operation
------- ---------------------------------------------------
173 TABLE ACCESS FULL MY_TABLE (cr=16 pr=0 pw=0 time=0 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 13 0.00 0.00
SQL*Net more data to client 24 0.00 0.00
SQL*Net message from client 13 5.33 5.47
Tkprof output for a complex query
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.38 0.40 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.38 0.40 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 3139
No Row Source Operation information here Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 73062 0.25 692.23
db file scattered read 16099 0.18 76.65
latch: shared pool 4 0.02 0.03
latch: cache buffers chains 3 0.04 0.04
latch free 16 0.00 0.00
View 1 Replies
View Related
May 19, 2013
I am trying to break down the balance_date to display the following groupings:
7:00-17:30 CDT
18:00-4:30 CDT
I currently have the query setup to display by day instead of these time ranges. I would like the output to read
19 May Day
19 May Night
20 May Day
20 May Night
I am fairly new to this, but how would I go about making this change?
SELECT
TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,
[Code] ........
View 1 Replies
View Related
Jun 6, 2013
SELECT
pas_code,
pas_profile,
count(sutp_id),
sum(sutp_price),
[code]...
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex
2664good stuff310069< because pbk_price is like 67 from that period
2664good stuff310071< because pbk_price is like 50 from other period
how to get all results in a single line like:
pas_codepas_profilesutp_idsutp_pricex
2664good stuff6200140
View 3 Replies
View Related
