PL/SQL :: Count Sequential Events

Jul 5, 2012

I have a table with around half a million rows. I would like to determine how many sequential events occur per device per unique group of events. The requirement is that the events continue to occur within 1 second of each other, every second, with no gaps. For example, in the sample below there is a sequence of 2 events for device 2737, and another sequence of 5 events for device 2737. If there was a 1 second interval with no event (Signal_time) then that would be the end of one group of events, this is also shown below.

Using Oracle 11.2.0.2.0

DEVICE_ID     SIGNAL_TIME
4401     17-JUN-12 12.59.59 PM
2332     02-MAY-12 12.59.59 PM
1086     30-MAY-12 12.59.58 PM
2737     17-JUN-12 12.59.58 PM -- start of one group / sequence of events
2737     17-JUN-12 12.59.58 PM -- end of one group / sequence of events

[Code]...

-- start of one group / sequence of events

2737     16-JUN-12 11.59.59 PM
2737     16-JUN-12 11.59.59 PM
2737     17-JUN-12 12.00.00 AM
2737     17-JUN-12 12.00.01 AM

-- end of one group / sequence of events

2737     17-JUN-12 12.00.03 AM

-- this is excluded from the above group of 5 events, but this may be the start of another group

Required output

Device--Count(*)--Start_Signal_Time-----------End_Signal_Time------------Duration (seconds)
2737----5-------------16-JUN-12 11.59.58 PM---17-JUN-12 12.00.01 AM--4
2737----2-------------17-JUN-12 12.59.58 PM---17-JUN-12 12.59.58 PM--1

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Show Sequential Numbers For Each Group

Oct 4, 2010

MyTable has missing records:

id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6

As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.

I need to show all sequential records, incluing missing ones, like this:

Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6

I try this query:

/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/

but it shows record 7, 8 and 9 as members of group 2.

View 9 Replies View Related

Performance Tuning :: Analyzing Non-sequential Snapshots In AWR

Mar 5, 2013

How can check database performance in a non-sequential parts of a day using AWR tool. For example, suppose we divide a day to 2 parts: low-traffic time and high-traffic time with the following time interval:

low-traffic time : 7:00am - 10:00am and 7:00pm - 11:00pm
high-traffic time : 10:00am - 01:00pm and 4:00pm - 06:00pm

I want to examine performance using snapshots gathered by AWR. If I get 2 AWR reports for low-traffic time of day (one for 7:00am - 10:00am, another for 7:00pm - 11:00pm ) and compute average of values in 2 reports, could I called it database performance in low-traffic part of day or not?

View 9 Replies View Related

Database Scattered / Sequential Read And Async IO Waits

Nov 23, 2010

We have a table (call it PROBLEM table) that varies between 60 and 100 million records from one data archival process to the next.The table has data from all 50 states and is range partitioned by each states unique code and has a primary key based on the following:

STATE the data is from
BMRK YEAR the year the data was last benchmarked
AREA location the data is from
SERIES industry the data is located in
YEAR the data was reported
MONTH the data was reported
DATA TYPE the type of data that was reported
ESTIMATE TYPE the type of statistical estimate that was produced from the data
REPORT ID report id unique to each state
REPT SFC state the reporter resided in

We have developed a job that will process data from other tables and create some statistics resulting in approx 2.2 million inserts, .5 million updates and .5 million deletes on PROBLEM table during each job run. Once this table is loaded another process (call this PROBLEM_PROCESS)takes off that reads this updated information to produce some statistical data that is stored in another table. This data is produced by summing anywhere between 2 and 5000 records per query from the PROBLEM table.Here is the query (call it PROBLEM_QUERY)

select round(sum(cm_value*sample_weight),3),round(sum(pm_value*sample_weight),3)
from matched_sample
where state_fips_code = :1
and area_fips_code = :2
and series_type_code = 'B'
and year = :3
and month = :4
[code]...

When we run this job it is very fast (20 minutes) until it gets to the PROBLEM_QUERY, at this point we get DB file scattered read waits, DB file sequential read waits and async IO waits and the job has run for as long as 2 days. When I look at grid, the PROBLEM query is doing a full table scan and ignoring the index.

Our systems people advised us to re-organize and re-index that data in the PROBLEM table after all of the DML and before the PROBLEM_PROCESS takes off. If we do this, it works and cuts our time down alot. However, the re-organize and re-index process adds eight hours to this process. Note: When we re-roganized the data we create a copy table on another set of table spaces and insert the data into the copy sorting the data on the primary key columns. After this we re-index the new table and drop the old table renaming the new table. When we do this later, we move the data back to another set of table spaces. So, we move the data back and for between table spaces A and B we can say.

We have Oracle installed on a M5000 server with Solaris as the OS the binaries and data files are stored on a NetApp Storage array(model 3160) of 500 1TB SATA 7200 RPM drives. However, there are 128 other databases on the NetApp filer as well.

IMO the array and the slow disks are the problem. I believe this because we are catering to the slow disks by re-organizing and re-indexing the PROBLEM table during each run. I don't believe this should be neccessary. We normally re-organize and re-index our data each week in our production system after many more transactions than this.

Our systems people state it is our application. Oracle Support tells us the statistics are out of date but have not answered us on why the statistics are out of date and the index is abandoned after 1 run.

View 7 Replies View Related

Performance Tuning :: High CPU Utilization In RAC And Log File Sequential Read Event

Apr 13, 2011

In a 3-node RAC setup; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-

EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS
CPU
time 5,802 34.9

RFS
ping 15 5,118 33,671 30.8 Other

Log file sequential
read 234,831 5,036 21 30.3 System I/O

Sql*Net
more data from
client 24,1711,08745 6.5 Network

Db file sequential
read130,939 4533 2.7 User I/O

Findings:-
On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.

1)Are these symptoms of undersized log buffer?
2)I feel Network wait can be reduced by tweaking SDU & TDU values based on MDU.

View 2 Replies View Related

Export/Import/SQL Loader :: Sequential Data File Record Processing?

Oct 1, 2013

If I use the conventional path will SQL*Loader process a data file sequentially from top to bottom?  I have a file comprised of header and detail records with no value found in the detail records that can be used to relate to the header records.  The only option is to derive a header value via a sequence (nextval) and then populate the detail records with the same value pulled from the same sequence (currval).  But for this to work SQL*Loader must process the file in the exact same sequence that the data has been written to the data file.  I've read through the 11g Oracle® Database Utilities SQL*Loader sections looking for proof that this is what will happen but haven't found this information and I don't want to assume that SQL*Loader will always process the data file records sequentially. 

View 9 Replies View Related

Performance Tuning :: Query Hanging With Sequential Read Or Latch Free Waits?

Apr 7, 2011

Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records

UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')

[code]...

During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.

During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION

[code]...

However instead of self join when I creaed global temporary table as

create global temporary table t as select * from invoice_header where release='A5'

And used it in the update as

UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)

It updated the records in a second!!

Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?

know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case

View 14 Replies View Related

SQL & PL/SQL :: Trigger All DML At All Table Events?

May 4, 2011

i want to write, just ONE trigger that handles all DMS events at schema level and writes the field changes to a specific log table. i wanna to write like 'FOR EACH FIELD' as for each row (& also each table). it turns in the fields section and if the 'old' and 'new' values isnt same, it triggers this function & writes changes to log.

ex. is like:

FOR EACH ROW
FOR EACH FIELD
if (old.value != new.value)
insert into LOG(..., old.name,new.name) values (..., old.value, new.value)
NEXT // FIELD
NEXT // ROW

View 19 Replies View Related

Performance Tuning :: Top 5 Timed Events?

Aug 31, 2010

In my awr reports I have CPU time(in seconds) as one of top 5 timed events.Does that mean oracle is waiting for my cpu to execute the statements.But my cpu usage is 0-10% always

View 4 Replies View Related

SQL & PL/SQL :: Constraints And Delete Cascade Events

May 9, 2011

I have a table with 2 defined FK constraints which fires delete cascade events.

I want to deny deletions when the event is fired from one of the tables and permit them when the event is fired from the other table.

Is it possible?

Is any way to solve which table fires the event?

View 2 Replies View Related

Reliable Message - Top First Timed Events

May 20, 2013

One fo my Db having reliable message as top first timed events.

Event Waits Time(s) Avg wait (ms) % DB time Wait Class

reliable message 685,139 24,625 36 29.58 Other

View 1 Replies View Related

Create Trigger To Stop Events From Being Deleted

Jun 2, 2010

I am trying to create a trigger to stop events from being deleted if the date is before the current date. This is the SQL syntax for my table

CREATE TABLE event
(event_id numeric(5) not null,
concert_id numeric(5) not null,
event_date date,
);

What I have attempted so far is:

CREATE OR REPLACE TRIGGER event_deleting
BEFORE DELETE ON event
FOR EACH ROW
BEGIN
IF DELETING THEN
[code]......

This gives me a few warnings, the first is that a DECLARE is missing and the second is a INTO is missing.

View 1 Replies View Related

Application Express :: Registration Form For Events?

Nov 6, 2012

registration forms for events (like Technology days and so on) with Apex? I want to build up something like that where customers can register for several events.

What is needed:

registration form with a limited registration (e. g. 80 customers) e. g. create a trigger?

show how many seats are still available

cancel of the registration

I´m using Apex 4.1

View 0 Replies View Related

Turning Off Trace On Specific Deadlocks Events?

Jun 17, 2013

Can I disable tracing just on deadlocks events on 11gR2?One of our applications provoke several deadlocks. I've warned the developers team several times, but it's taking time to solve it. However, these deadlocks generate trace files of several sizes (from 300M to 3G ..)The Deadlock Graph on them are very useful but the PROCESS STATE section are too long, and until the developers team fix the problem they are just filling up my user dump dest.  Can I disable them or disable the PROCESS STATE section from them?

View 2 Replies View Related

Performance Tuning :: Digging Out Wait Events

Oct 15, 2013

I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.

select time,
round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client,
round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock ,
round(max(case when event = 'enq: KO - fast object checkpoint' then
[code]....

Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_ active_ session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.

Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ).

SELECT user_id,
PROGRAM,
machine,
session_id,
SESSION_SERIAL#,
sample_time-7/24,
session_state,
[code]....

View 14 Replies View Related

TimesTen In-Memory :: Unable To Receive XLA Events After A While?

Mar 12, 2013

We have been using XLA to capture events on TimesTen for a while now without any issues. We were on TimesTen 7.0.5 and then moved to 11.2.2.3.0 and now 11.2.2.4.0. The XLA processes used Java 6 64 bit and works well with TimesTen versions 11.2.2.4.0 - 64 bit and 11.2.2.3.0 - 64 bit, without any issues.

However, we recently upgraded to Java 7 64 bit both during build time of our XLA processes as well during runtime. The problem we see now is that XLA process upon startup processes events for a while and then after that doesnt receive any events. The process doesn't throw any errors/exceptions. If we restart the XLA process, all the unprocessed events are received and then it behaves normally for a while after which it fails to receive any further events.

Is there any issue with Java 7 64 bit and the TimesTen XLA API? I read the TimesTen 11.2.2.4.0 manual and it says that Java 7 and the ttjdbc7.jar have been certified to work well. Was just wondering if there were any other issues.

In addition, to debug the problem, we ran XLA on a single node and on a two safe setup. The same observations are noted. On Java 6, the process runs just fine capturing all events without any issues, but with Java 7, the issues persist.

In addition, we are only performing updates/inserts on to TimesTen, no delete actions. The OS on which TimesTen's and XLA runs is Red Hat Enterprise Linux Server release 5.7 (Tikanga).

View 2 Replies View Related

Trace Events (19027) Generated In Oracle

Aug 31, 2013

DB version: 11.2.0.3

I have enabled the below trace event before running query on my session. 

ALTER SESSION SET events = '19027 trace name context forever, level 0x1000';ALTER SESSION SET tracefile_identifier = store_trace;

Then I ran my query, and upon the finishing of the query, I ran the below to disable trace. 

ALTER SESSION SET events = '19027 trace name context off, level 0x1000'; 

Now I checked back on the trace folder in my Oracle DB directory structure, but cannot find any file created by using the above set tracefile_identifier. I had previously run the query with "sql_trace" ON with tracefile_identifier, and successfully got the tracefile generated by using the identifier. 

View 4 Replies View Related

Active Session History And Null Events

Sep 13, 2013

11.2.0.3 EVENTS: Null(blank) or called a 'null event' See link. Checked the docs. I don't see anythingI think a null (blank) is for an idle wait since per the V$ACTIVE_SESSION_HISTORY spec these are not record. Am I correct?What is a 'null event'. I see a paper from Oracle magician dating to 9i that says this is an 'oracle goof'.  Descriptions of Wait Events

View 5 Replies View Related

Performance Tuning :: Maximum Wait Events On PX Deq - Execute Reply?

Oct 19, 2010

I have a partitioned table with degree for parallelism defined as 10.

I am getting maximum wait events on PX Deq: Execute Reply.For 2 hours trace the wait event is almost 1.5 hour.

I have done some seraching and i found this.

Quote:
In principle:A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition. Unfortunately, this is NOT strictly true.

It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins. You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.

So i have following questions
1) What is the meaning of PX Deq: Execute Reply.
2) Is this not recommended to use DEGREE clause in Partitioning.
3) Defining DEGREE clause in partioning of table will automatically executes DML on table PARALLELY same a PARALLEL hint clause

View 6 Replies View Related

Performance Tuning :: AWR Report Network Foreground Wait Events?

Jun 29, 2011

I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.

Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU 224 80.70
Other 2,668 0 28 10 9.99
System I/O 4,753 0 9 2 3.23
Administrative 1 0 6 5543 2.00
Commit 357 0 4 11 1.46

[code]....

The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 224 80.70
unspecified wait event 2,666 28 10 9.99 Other
control file sequential read 4,753 9 2 3.23 System I/O
switch logfile command 1 6 5543 2.00 Administrative
log file sync 357 4 11 1.46 Commit

View 8 Replies View Related

Reports & Discoverer :: Matrix Report For Showing Calendar Events

Feb 8, 2012

I have to create the matrix report which shows calender. Looks Like this.

View 14 Replies View Related

Performance Tuning :: Top Wait Events - Specific Document To Analyze AWR Report

Jun 24, 2013

We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.

Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....

View 2 Replies View Related

Server Utilities :: Execution Series Of Application Through Oracle Trace Events

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

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

Nov 16, 2009

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?

View 13 Replies View Related

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

Nov 24, 2011

difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.

SQL> select * from t1;

A B C
---------- -------------------- --------------------
1 2 3
2
5

SQL> select rownum,a.* from t1 a;

ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

PL/SQL :: How To Count

Feb 28, 2013

I have one tbale like

TABLE A with columns bu_code and bu_cty

bu_code bu_cty
12 ES
12 ES
13 AP
13 AP

so now i want to know the count for bu_code

like my output shd be

bu_code(12)    ---count is 2
bu_code(13)------count is 13

View 4 Replies View Related

How To Get COUNT On Query

Mar 4, 2009

I have this query and I want to get the COUNT:

SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;

This returns one column which is the n_p; how do I get the count of n_p?

View 2 Replies View Related

Count For A Particular String?

Oct 9, 2008

say there is astring "mumbojumbo "i need the count of given string in it

ex:when o is given count shuld be 2 when m is given count shuld be 3

is there any pre defined function for counting a given string ...

View 2 Replies View Related







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