SQL & PL/SQL :: Execution Of Blocks In Oracle
Sep 26, 2010
I have a block of code that looks something like this, I'll write it in pseudo code to avoid pasting 100's of lines of
IF <condition> THEN
FOR record in (select query here..)
LOOP
--add data
END LOOP
END IF;
IF <condition> THEN
--do soemthing
END IF;
IF <condiiton> THEN
--do soemthing
END IF;
Now, in the 1st IF statement there is a for loop which basically builds up a tab type array - this takes at least an hour to execute based on the select query which returns 1000's of records.
The strange thing is, that my log files shows that at the time the procedure was executed, the stuff in the 2nd IF statement was executed almost straight away.. is this even possible?
If the loop in the 1st IF statement takes over an hour to finish, how is it possible for the 2nd IF statement stuff to process straight away? My log files show me that the loop in the 1st IF statement was going on for a good hour.. yet the 2nd IF statement was executed straight away.
View 7 Replies
ADVERTISEMENT
Sep 9, 2012
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300
SQL> col TABLESPACE_NAME for a25
SQL> col FILE_NAME for a65
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB
2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB
------------------------- ---------- ----------------------------------------------------------------- --- ----------
SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M;
Alter database datafile 3 RESIZE 60000M
*
ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
View 3 Replies
View Related
May 4, 2013
find the below table. The entire data is within DOCSTART and DOCEND. The data is further enclosed within BACCSTART and BACCEND. This type of block is repeatable. I have to pick up any of ABCD which is also repeatable and TOTAL(occurring once per block) and ACCNAME (occurring once per block) for each block within BACCSTART and BACCEND and form an xml like
<BACCSTART>
<TOTAL>100</TOTAL>
<ABCD>abcd</ABCD>
<ACCNAME>name</ACCNAME>
</BACCSTART>
for each such block. Presently I am using a for loop, but the performance is not up to the mark. It will have around 200 such blocks for which I have to form the xmls within 15 seconds. Presently the for loop is taking around 53 secs.
ROWNUM NAME VALUE
1 DOCSTART null
2 BACCSTART null
3 ABCD abcd
4 ABCD abcd2
5 PQRS pqrs
6 PQRS pqrs2
7 TOTAL 100
8 ACCNAME name
9 BACCEND null
10 BACCSTART null
11 ABCD abcd
12 ABCD abcd2
13 PQRS pqrs3
14 PQRS pqrs4
15 TOTAL 150
16 ACCNAME name
17 BACCEND null
18 DOCEND null
View 2 Replies
View Related
Sep 15, 2011
I was confused by partitioed table, when i select a partition of table, how does oracle to scan blocks? it scan all blocks of table or scan a single partition blocks only?
SQL> Explain Plan For
2 Select Count(1) From Tb_Hxl_List Partition(p_L3);
Explained.
SQL> Select * From Table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | PARTITION LIST SINGLE| | 33115 | 18 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TB_HXL_LIST | 33115 | 18 (0)| 00:00:01 |
View 3 Replies
View Related
Jul 28, 2010
Oracle Version is 11g.
I need to call the same procedure with different parameter multiple time in parallel.
I have job_control Table
CREATE TABLE JOB_CONTROL
(
JOB_CONTROL_ID NUMBER NOT NULL,
JOB_SEQ_NO NUMBER NOT NULL,
MODULE_NAME VARCHAR2(32 BYTE) NOT NULL,
JOB_STATUS VARCHAR2(15 BYTE),
NO_OF_RECORDS NUMBER,
PROCESSED_RECORDS NUMBER
);
Insert into JOB_CONTROL
(JOB_CONTROL_ID, JOB_SEQ_NO, MODULE_NAME, JOB_STATUS, NO_OF_RECORDS)
Values
(20, 1, 'SALES', NULL, 5);
Insert into JOB_CONTROL
[code]........
Now the table look like this.
JOB_CONTROL_IDJOB_SEQ_NOMODULE_NAMEJOB_STATUSNO_OF_RECORDSPROCESSED_RECORDS
201SALES5
212SALES5
223SALES5
234SALES5
245SALES5
256SALES3
based on the data available in table i wanted to execute a procedure 6 times
create table job_table
(now date, seq_no number)
the procedure will have one parameter as job_seq_no
create of replace procedure job_call (i_job_seq_no number)
is
begin
for i in 1.. i_job_seq_no
loop
insert into job_table
values
[code]......
the problem is i want to do this activity in parallel , i.e. all six calls to the procedure would be parallel. and this will be one time activity.
View 10 Replies
View Related
Apr 3, 2012
We have a query which makes Oracle behave very strangely. It is a straight-forward join between four tables of about 30.000 rows each, with some simple comparisons and some NOT LIKE:s.
When we run this query, it either takes about 1 second or more than 1.000 seconds to run and return the approximately 5.000 rows of the result. If we run the same query over and over again, it fluctuates back and forth between two different execution plans, apparently at random, 3 times out of 4 selecting the 1.000 second version and 1 time out of 4 the 1 second version.
There are no other connections to the database, the schema is not modified, the data is identical, the query is identical, and the response is identical, but the execution time alternates between 1 second and 1.000 seconds.On the same database instance we have another schema which is identical, but with slightly less data, which is used for development. The 1.000 second run times did not happen in that schema, but only in the test system's database.
Therefore we would REALLY like to understand what happens and why, so that we can avoid triggering this in the future. We could try locking the 1 second execution plan, but then we're afraid of doing the same thing wrong again in the future.
Here are the two execution plans that Oracle switches between, more or less at random:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15663 pr=10536 pw=0 time=855673 us cost=82273 size=2707430769293 card=14028138701)
79272 79272 79272 TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=22154 us cost=277 size=10693 card=289)
[code]...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15664 pr=0 pw=0 time=778178696 us cost=30838477 size=741611997206725 card=3842549208325)
375411 375411 375411 TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3782 pr=0 pw=0 time=51533 us cost=1029 size=25152738 card=375414)
[code]...
The query:
select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member
[code]...
And excerpts from the schema:
CREATE TABLE "PDB"."GROUPS"
(
"UCID" VARCHAR2(256 BYTE),
"UNIX_GID" NUMBER(*,0),
[...]
[code]...
View 4 Replies
View Related
Feb 6, 2013
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies
View Related
Apr 4, 2013
I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
View 4 Replies
View Related
Feb 20, 2013
Would like to know the execution series of an application(From a particular screen) through Oracle trace events.
View 1 Replies
View Related
Dec 24, 2012
The below query is taking high CPU almost 98% and longer time to execute.
SELECT ancestor,
Max(D.alarmstate) ALARMSTATE,
Max(D.sialarmstate) SIALARMSTATE,
Max(D.uncralarmstate) UNCRALARMSTATE,
Max(M.commstate) COMMSTATE,
Max(M.nncommstate) NNCOMMSTATE,
Max(M.servicestate) SERVICESTATE,
Max(M.abnormal) ABNORMAL,
CASE
[code]....
View 15 Replies
View Related
Aug 19, 2011
Below is the output of Tom Kytes script show_space, which I have run on one of my indexes.
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 102,936
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 28,615,887
Total Blocks............................ 28,748,800
Total Bytes............................. 235,510,169,600
Total MBytes............................ 224,600
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 233
Last Used Ext BlockId................... 1,574,409
Last Used Block......................... 12,800
PL/SQL procedure successfully completed.
If I look at the unformatted blocks its zero, which tells me that data is being placed into every block (pretty well compressed). But what I don't understand is why there are 102,936 blocks that are only 25-50% full? I would have expected to see some blocks that in the75%-100% full range as this index was recently dropped and rebuilt 2 months ago.
This index is on a partitioned tabled, where the 90th day and higher partitions are dropped daily.
Here is the layout of the index
CREATE INDEX T1.FEAT_IDX ON T1.FEAT
(R_SEQ, SYSTEM_NAME, FEATURE, FLAG)
NOLOGGING
TABLESPACE TB1
[Code] .........
what I need to do to get the value of FS4 (# of block 75%-100% used higher)
View 3 Replies
View Related
Jun 14, 2011
can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?
View 2 Replies
View Related
Jun 14, 2011
If in a PlSQL procedures there are many DML statements which are part of the same transaction. and there are many calls to other PLSQL blocks also.
what is the best practice to use commit either after each DML statement or to use it in the End of Block?
View 8 Replies
View Related
Apr 19, 2012
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
View 5 Replies
View Related
Jun 20, 2011
DB: Oracle 10g R2
OS: SUSE Linux SP 1 x864
We are maintaining a DR of our Database Server(oracle 10g R2 atop SUSE SP1 Linux) using Platespin(
[URL]......
Platespin is set to replicate(block based), incremental data(delta) every 1.5 hour from Production to DR site over a 30 Mbps dedicated fiber link.
Our maximum changes of data per day(during business hours) wont exceed 300 MB. During business hours Platespin replicates at least 1 GB at every replication cycle, while during off hours it replicates 300 to 500 MB per replication cycle. We are facing this strange issue with this box only(SLES 10 SP1 + Oracle 10g R2), we have protected MS Exchange 2007 Server based workloads without this strange issue, i.e in case of Exchange only delta replicates from Production server to DR site on Platespin.
Platespin support says us that Oracle re-indexes its database for better performance, so it is possible that re-indexing causes the blocks level changes on the storage, and since Platespin works on Block level, thats why it replicates so much(even though data is not changed that much)
here is actual words of Platespin support
<snip>
I think whenever Oracle database Indexing happens, it changes almost most of the Blocks of database and Platespin replicate all those Blocks.
As you know, Platespin checks the Date/Time attribute of every blocks before replication and if Date/Time attribute changes from last replication, it considers as changed block and replicate those blocks on Platespin Appliance. So, my suggestion is just look into the Oracle server behaviour before/after Data indexing process and do needful or do some workaround to overcome this issue.
</snip>
is there anything we can do at oracle level ?
View 1 Replies
View Related
Jul 12, 2010
I have a written a script which doesnt take any parameter. this is an anonymous block. I run my script as below: SQL> @filename.sql
I would like to convert this into a procedure which should be called in an anonymous block. run a procedure from an anonymous block.
I would also like to know how to run the same from the SQL prompt.
View 5 Replies
View Related
Jul 6, 2011
what executable statements in PL/SQL mean? My objectives are:
1)Writing Executable Statements
2)Create PL/SQL executable blocks using DML and transaction control
I have to explain what exactly each is and what should come under it. what kind of statements and what should be covered under executable statements?
View 3 Replies
View Related
Jan 4, 2011
what empty blocks are, and how to remove them.What I'd like to do is not have empty blocks in the first place on loading a table. I load a lot of "static" tables and would like to not have any wasted space at the end, with minimal shinanigans.
I've set pctfree 0
I"ve set initial to close to the end table size
I've set next to 1M
I've set pctincrease 0
blocksize is 8k
Yet I still need to at least do an alter table deallocate unused
View 8 Replies
View Related
Jul 31, 2012
I have two blocks, both are multi record block. 1st block is control block and second is database block. Both the blocks have same fields(Example: Location,Location_name,Location_Type). In the first block(Control Block) I have check box. My goal is when I check the checkbox and click on add button all the records which are selected in first block should go to second block.
View 3 Replies
View Related
Oct 22, 2010
We are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.
My main question is
1) Should i need to increase freelist value (Right now my value is 1)
2) Or i have to move on segment space management = auto
SQL> select * from v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.
View 1 Replies
View Related
Oct 2, 2010
i am using form6i & oracle 8i.
i have master -detail form. both are database blocks.
i have inserted values for the master block bt not for the detail record my problem is.. "user should not be allowed" to move to next record of the master block before saving the current record
View 10 Replies
View Related
Feb 19, 2011
We are having two data blocks as Earnings and Deductions. We need to export this to an excel in single sheet parallel [ imagine your payslip format ].
if we use normal text_io we are not able to get the result we want. so we have tried using a package called export2excel. we achieved what we want. The form is working perfectly in client server concept. When we move the same form to our Unix application server, it is not working.
View 1 Replies
View Related
Oct 16, 2012
I have this error. To put it simply I have two blocks.
Block1 contains two drop down list with PL/SQL statements for queries.
Block2 contains tabular form created from block wizard (I tried already in manual)
that will catch the result in Block1 queries.
Now I have a button with a trigger when-button-pressed that contains
BEGIN
INSERT INTO dummy1
VALUES ('hello',1,2,3);
COMMIT;
END;
My goal is to add into dummy1 values from :block2.item_name1, :block2.item_name2, :block2.item_name3 but to put it simply I tried these values but I received the same error.
When I run it and first things first click the button, the values will be added into dummy1 table but when I execute the block1 - dropdown list queries and try to press the button. I received the error.
View 10 Replies
View Related
Jul 24, 2012
I have installed Oracle server and SQL Server on separate machines which cause me a time delay of 21 seconds for each execution. Why executions delay? I have set RPC out (true).
Note: My main concern is either if the query is correct/incorrect it executes for 21 seconds._
Another case when I have both servers on the same machine it executes in milliseconds. I have tried Following methods in SQL SERVER.
*1, Using OPENQUERY:*
SELECT * From OPENQUERY(Linked Server Name,’Select * from OracleTableName ‘)
*2, Using Exec:*
DECLARE @sql NVARCHAR(MAX);
SET @sql =(’Select * from OracleTableName ‘);
EXEC (@sql) AT Linked Server Name ;
How to reduce the time delay caused for the execution?
View 0 Replies
View Related
Dec 15, 2010
I HAVE 2 BLOCKS E.G. MASTER-DETAIL AND i have created it manually then how to insert values thrugh these blocks using a button called "save" using "commit_form;" suppose form fields are
master-block:
emp_id
emp name
age
detail block:
address
city
where emp_id is primary key as well foreign key in detail table
how these values will be inserted in both tables
View 1 Replies
View Related
Dec 24, 2010
I have created two non base table text items(from date, to date) and if values are not entered from front end i.e. null then hadrcoded in pre_query trigger to default values.But even i enter values dynamically from front end and when ever i press fetch button ,it is taking default values i.e. it is not accepting values what i have entered.
View 6 Replies
View Related
Jul 18, 2011
I have 4 blocks in my form which is basically used for travel booking for the employees in a company within India
1) Header block :- contains info abt the person who is booking the tickets for number of employees. Here i have given booking no a primary key.
2) Employee Detail :- Here the basic info of an employee is entered. Here i have taken booking number as a foreign key and then given emp_cd & booking number as a composite primary key.
3) Travel Detail :- Here the travel detail of individual employee will be entered wherein a unique trv_no will b generated 4 every single travel. Again i have taken foreign key as bkng_no frm 1st blck and emp_cd frm 2nd blck and tkn a composite primary key which comprises of bk_no,emp_cd and trv_no. this is used to maintain the uniqueness for single travel.
4) Vehicle Hotel Details :- This block is placed on different canvas for same form.It is meant for Other details in which details regarding hotel,vehicle etc booking aftr reaching the destination is entered. In this block thrs no primary key, but i hv taken the composite primary key of 3rd block as a foreign key since thr will be multiple entries for this one entire travel.
At every level there will be multiple entries for each corresponding entered record. I am able to enter one single record properly i.e. for 1 emp i am able to enter multiple travel details and his other requirements but asi try to enter more then one employee info, his travel details, other requirements i face an error stating foreign key constraint violated.Parent key not found for 3 rd level block.
How can i get the desired o/p wherein all the multiple records for every single subsequent record are stored correctly taking all the constraints in to consideration.
View 8 Replies
View Related
Apr 9, 2013
I am testing nested blocks, but my logic is apparently not correct.
I am running this script:
DECLARE
v_one number(1) := 1;
v_two number(1) := 2;
[Code]....
I receive the output from only one of the nested blocks:
bad
PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
View 8 Replies
View Related
Jul 26, 2011
we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426
1 row selected.
further when i checked the session details using TEMP segment, i got below output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK#
explain what is the exact meaning of this column ?
to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;
View 3 Replies
View Related
Jan 10, 2011
If the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?
View 19 Replies
View Related