SQL & PL/SQL :: To Make Report Of Processing Time For Tasks
Nov 26, 2011
I want to make report of processing time for tasks. Every task has several stages and connected with them modification dates. I need calculate total processing time for every task.
In my calculations i have to consider only "working" hours. There is time window table (For example working hours: from 8 a.m to 4 p.m, but can be different thats why i use table with id - different id = different time window). Scripts i used to simulate problem:
create table t (
id number,
s_date date,
e_date date
);
declare
[code]........
table t constrains "working" hours:
12011-04-10 08:00:002011-04-10 16:00:00
12011-04-11 08:00:002011-04-11 16:00:00
...
but could be also
table w contains tasks history processing by team (8 hours for tenet, rest time (out of hours defined in table t) of working trough incident should be ignored/omitted in calculations):
At the beginning I tried this query, but I don't know how consider/dose time window from table t
select id, change_date,
round( to_number(change_date-lag(change_date) over (partition by id order by change_date))*24*60*60) sec
from w;
12011-04-10 10:00:00
12011-04-11 02:08:1658096 - sholud be 21600 (sum time only for 2011-04-10 16:00:00)
12011-04-11 16:23:1051294 - should be 28800 (sum time only from 2011-04-11 08:00:00 to 2011-04-11 16:00:00)
22011-04-12 08:12:30
22011-04-13 00:40:1559265 - should be 28050 (sum time only from 2011-04-12 08:12:30 to 2011-04-12 16:00:00)
32011-04-14 09:37:54
if there is any package provided with DB that would allow me to import XML file and then use XPath to read the data? I need to insert it in proper tables then. I could see packages to create XML files, but I could not see any one to load it from the file. I could see it could be done in Java using XMLDOM, but is there any way of doing this using pure PL/SQL?
(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)
i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)
Formula ->
A = Round down [A_TIME - A_STD] B = Round up [A_TIME) - 10 minute + A_STD]
where
A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM" A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"
Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)
I have a payment table that has the ids of cds that has being sold in the past month. From there I want to make a demand wise report. I am aware of the report wizard in the oracle forms but I dont want to use it. Here are the tables in a bit more details.
How to make report high 3.33 inch per page. so if there are more than one page in the report it will start printing in the correct position on the next page,
I have one view which displays the information of all the receipts with date now i want to make ageing report based on this date. the columns will be 0-30 , 30-60,60-180 and > 180 , i want to distribute the receipts qty based on number of days.
I have created a master-detail form. The detail columns are created as report columns instead of getting created as Items. I need to make a column non-editable.
I have a scenario where the requirement is to create a "SEQ NO". Based on the 'Tasks' & it's creation date. Where there are multiple 'tasks' for a single SR.
The following example will make it clear SR TASK Created on SEQ NO 11009 2345 14/10/1988 12:15:17 1 11009 2346 14/10/1988 12:15:57 2 11234 - - -
If there is a SR which has no task then the SEQ NO should be 0. And the seq no should be in the order of the created on field & if 2 tasks are created at the same time i.e suppose 2345 & 2346 have the same time stamp then the SEQ no should be 1,2 respectively n not both as 1.
i have batch file name exptab,to export all table in database
for example @echo off exp scott/tigger tables=emp file=C:impemp.dmp LOG =C:implog1 batch file working fine when i click on it,
I need to use the Scheduled Tasks in windows 2003 so when the computer startup batch file will run automatically i follow all the step but its not working i try the same story with windows xp still problem exist .
I'm having a problem with performing backups from EM DB Control on two of my Oracle 10g 10.2.0.4 systems. When I configure and submit any backup job from EM (either online or offline), the following happens:
- When the job is due to run a Pre backup task has status of 'Scheduled'
- Prebackup task completes and status changes to 'Succeeded'
- Backup task appears with status of 'Scheduled' and value of started time within the next minute
- Status of backup task never changes from 'Scheduled', and the started time keeps updating to the current time
- This goes on indefinitely until I abort the job
At the moment I have scheduled tasks configured to run an RMAN job every night, but I need to get this working through Enterprise Manager Database control.
Something else which has happended on one of these systems is that after applying some Microsoft Critical updates the database control service would not start. The only way I could find to resolve this was to recreate the Enterprise Manager repository. Could there be a link here at all?
I've also posted this here: [URL] but with no response so far.
i have installed oracle 11gr2 on centos 4.7,,but after that when i invoke command for startup in sqlplus for sys user it shows an insufficient privileges error. and what are the post installation tasks? is the database installed also?what is the name of database and where is it?
i want to use Graphic builder for make a chart in report builder. how can i use them for create a chart report? i need to see some samples for create my project. put a sample of .OGD and .rdf
how to make a form with report and an insert form in the same page, these two forms are related to the same table. Our customer wants a user can add new row to the table in a form and see all of rows created by this user in a report, this report should provide edit link as well. the problem is: whenever I inserted a new row or edit a row or delete a row, and submitted, and return to this page, all of hidden items lost their values, so report is blank, and some display only items also lost their values.
We are using Oracle 10g on Linux. Followings are some contents from the AWR report:
Elapsed: 60.26 (mins) DB Time: 437.27 (mins)Here why the DBTime is greater than Elapsed Time. Is it due to the fact that this is the total time spent in user calls by multiple sessions i.e. ( some sessions were on CPU, some were in IDLE state waiting for I/O)
The below statistics show that CPU was used only for 5,363 seconds. We have 8 CPU grid, so total CPU time is 60.26* 8= 482.08 Min. I want to know where does the remaining time was spent. Also there is 20.4% of Total Call Time. How does it is calculated.
CPU time 5,363 20.4. The below statistics for reference:
We have scheduled some Jobs such as monthly, semi-annually reports using dbms_scheduler. we use 10gR2 software and windows 2003 server. we have a report which supposed to run at 4:00 AM and as soon as the report finishes it sends the report as email to the authorized users. when the scheduler ran the report inside the report it shows as ran at 3:08 AM where they get systimestamp. but when i query the
select last_stat_time, next_run__date from dba_scheduler_jobs where job_name ='TEST';
last_start_time next_run_date 03-DEC-12 04.00.00..223000 AM -04:00 07-JAN-13 04.00.00..200000 AM -04:00
when I query systimestamp from dual on that database from sqlplus: i get the following: systimestamp 04-DEC-12 07.40.16..152000 AM -05:00
I see the difference of -04.00 and -05.00 from both of the queries. i know the systimestamp from dual is correct. how to I fix the scheduler Job to run at the correct time with daylight savings to take effect?
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time 2) DB time - as mentioned at the top of AWR report 3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack) 4) Total of time for all Statistics in "Time Model Statistics" 5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?
I understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?
I have an AWR report based on two snapshots one an one hour time window, under the "SQL ordered by Elapsed Time" section, there is a query having Elapsed Time = 7012 seconds, which is almost two hours, how is this possible? I have been told that this i aggregate value of all sessions.
We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36% Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
I have a page which downloads statspack reports from different servers and stores them in APEX. As well as this it also display a summary of data (top 5 wait events & load profile info) when hovering an icon: [URL]... The chart will refresh itself and display the data based on the value of P10_SNAPVAL (storing the PK value). The report does not do as I expect it to do (it isn't refreshing the table). I have the following:
PL/SQL Dynamic Content region making the report Dynamic Action- Event: Change- Item: P10_SNAPVAL- Action: Refresh- Affected Element: Region - Load Profile (the PL/SQL Dynamic Content)- Event scope:
I've tried both bind & live The value of P10_SNAPVAL is changed whenever the user hovers a penguin icon (this works because the chart works).
$('img[name=summary]').mouseover(function() { var get = new htmldb_Get(null,html_GetElement('pFlowId').value, nu
SQL Statement Processing Steps: 1. Create a cursor. 2. Parse the statement.
parsing (syntax check, semantic check, privilege check, allocate private sql area, shared pool check (library cache check shared sql area. Here is hard or soft parse)SQL queries submitted to the system first run through the parser, which checks syntax and analyzes semantics. The result of this phase is called a parsed representation of the statement.
This parsed representation is then sent to the optimizer, which handles three main functionality:Transformation, estimation, and execution plan generation.
3. Describe query results. 4. Define query output. 5. Bind variables. 6. Parallelize the statement. 7. Execute the statement. 8. Fetch rows of a query. 9. Close the cursor.
- What stages from the above are included in the 'Query Optimizer Stage' - Is the parse tree created after doing both syntax check and systematic check as I found notes where they mention it was done before symantic check - What stages are skipped when doing soft parse vs hard parse -when mentioned parsed representation, is that the same as parse tree.
I am just trying to understand more the flow of the query processing.
in this example empid is dummy empid (which will be issued for contractors), once the contractors are considered to be taken into payroll that u_empid will be updated by HR manually, we need to replace empid with u_empid. then data will become like this.
now my requirement is there are approximately 60 child tables are there for EMP table, and EMPID is the foreign key for all of them with "on delete cascade" defined on those. Now first i need to create records in all the child tables with actual employee id's (which is u_empid). if the actual id is already exists then just need to update rest of the columns in that table except the EMPID.
Once the records are created with actual empid, then i will issue a delete command to remove dummy employee id's in emp table which will take care of deletion in child tables as well.
I was thinking of two options, one is go with Merge other is write a cursor and handle the update in exception block using when duplicate value on index feature.
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
I have created a form based on a table and I want to override the processing which inserts/updates the data in the database. I have figured out how to create a new process but I am having trouble referencing the form field data.
If I have a filed call P40_USERNAME how do I reference this in the insert/update statement in the custom process.
$ sqlplus / as sysdba SQL*Plus: Release 11.1.0.6.0 - Production on Wed Nov 17 09:45:50 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to an idle instance. idle> startup ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/DOISOAT/spfileDOISOAT.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/DOISOAT/spfileDOISOAT.ora ORA-15077: could not locate ASM instance serving a required diskgroup idle> exit SP2-0042: unknown command "exit" - rest of line ignored. idle> exit Disconnected
First time, I tried to recreate spfile from pfile, however, it - the errors continued to occur. And I searched in metalink, so, I've got ID 387217.1. I've been beging followed:
1- Check the REQUIRED_RESOURCES was listed by crs_stat -p command, wasn't it? Then, the REQUIRED_RESOURCES did not contain anything
