SQL & PL/SQL :: Using Analytic Function To Determine Maximum Concurrent Calls?
Apr 27, 2010
I have a requirement to calculate the maximum number of concurrent calls from the following data:
Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
e.g.
SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min
[code]....
View 7 Replies
ADVERTISEMENT
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies
View Related
Jul 23, 2013
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,
View 10 Replies
View Related
Dec 15, 2010
The following query calls the function get_meter_desc 8 times.
SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...
How can I replace the function call by the join in the main query?
View 19 Replies
View Related
Oct 23, 2012
I have oracle table has records like this clnt pno beg_dt end_dt load_ts
502852 02 01-NOV-93 31-OCT-94 01-AUG-12
502852 01 01-OCT-93 31-AUG-94 01-SEP-12
502866 01 01-JUN-90 31-DEC-90 01-AUG-12
256974 01 01-JAN-90 31-MAY-90 01-SEP-12
280441 01 01-JUN-96 31-MAY-97 01-AUG-12
[Code]....
I am writing an oracle query using analytic funtion to list that has max(load_ts)
But my query returns bad results and retrieve all the records for some reason.
select a.*
from
(
select
CLNT,
[Code]....
View 5 Replies
View Related
Nov 4, 2011
Is it possible to make query with analytic function or somehow to represent output data for next example (sql: ???):
Table: Order
sql: select * from order
Sql output>
Item Barcode Qty
---- ------- ---
1 100100 2
2 100200 1
3 100300 3
Table: Order
sql: ???
Sql output>
Item Barcode Qty ElementarQty
---- ------- --- ---
1 100100 2 1
1 100100 2 1
2 100200 1 1
3 100300 3 1
3 100300 3 1
3 100300 3 1
View 2 Replies
View Related
Mar 8, 2012
Do we have analytic function equivalent of following?
select object_type,owner from dba_objects group by object_type,owner ;
I am trying to get a unique srno for a combination of a 2 fields - here object_type and owner
OWNEROBJECT_TYPESRNO
SYSVIEW1
SYSTABLE2
SYSPROCEDURE3
SYSTEMVIEW4
SYSTEMTABLE5
SYSTEMFUNCTION6
SYSTEMPROCEDURE7
SCOTTTABLE8
SCOTTVIEW9
.......................
also how can I get the SRNO?
I can' use sequence in the group by function and if I get equivalent analytic for above group by even then I can't write row_number as the order by gives detail record
I don't want to wrap this select inside other select
View 4 Replies
View Related
Sep 30, 2013
ihave query quite like this: with--
This query selects one
"representant" acct_id per group (about 300 rows total)acct_repres as( select distinct acct_id, origin_id, acct_parm_id from ( select a.* , source_id , dense_rank() over (partition by source_id origin_id order by acct_nbr nulls first, acct_id) as odr from account a join account_parm ap on (a.parm_id = ap.acct_parm_id) ) where odr = 1)select col1 , col2 , ( select accct_id from acct_repres ar where ar.acct_parm_id = t2.acct_parm_id) col3 , ( select count(1) from acct_repres) col4from some_table t1join other_table t2 on (....)
And here it comes.
The "acct_repres" subquery returns more than 300 rows when executed separately. But when used in CTE sometimes (depending on execution plan) it seems to have only one row - the value in the column col4 is "1",while value for col3 is NULL for most of the cases. It looks like the the dense_rank function and the condition "where odr =1" are evaluated at the very end.
When I use MATERIALIZE hint the result was the same. But when I put the result of account_repres into dedicated table and use that table instead of CTE the output is correct.
View 6 Replies
View Related
Feb 23, 2012
How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well
select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......
View 8 Replies
View Related
Nov 4, 2010
I have the following query with analytic function but wrong results on the last column COUNT.
1)I am getting the output order by b.sequence_no column . This is a must.
2)COUNT Column :
I don't want the total count based on thor column hence there is no point in grouping by that column. The actual requirement to achieve COUNT is:
2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1
(In other words, if it is different from the following row)
2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different.
(In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .
My present query:
select r.name REGION ,
p.name PT,
do.name DELOFF,
ro.name ROUTE,
[code]...
My incorrect output[PART OF DATA]:Quote:
REGIONPT DELOFF ROUTE THOR LOC SEQ COUNT
NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 1
NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 2 1
[code]...
My required output[PART OF DATA]-:Quote:
REGIONPT DELOFF ROUTE THOR LOC COUNT
NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1
NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 1
NAASNAAS MAYNOOTHMAYNOOTHR010 PRIMHILL CEL 1
[code]...
NOTE :Count as 1 is correctly coming.But where there is same rows and I want to take the total count on them, I am not getting.
View 9 Replies
View Related
Oct 24, 2013
Oracle 11g databaseidval1val2100a110b120c200a220b
WITH input AS
(SELECT 1 id
[Code].....
input; Output:idval1val2assigned_number100a0110b0120c2200a0220b1 The dense numbering sequence should be assigned to each row based on id and val1 column. For a given Id, the numbering only starts after val1 > 1 till then the assigned_number will be zero.
View 1 Replies
View Related
Jul 24, 2013
determine if a function is worth pinning in memory? I want to come up with a percentage, implying that if the function is already im memory 80%+ of the time then it is not worth it.
View 5 Replies
View Related
Aug 9, 2010
any inbuilt function available for finding out the maximum value of a column out of a plsql collection.
View 15 Replies
View Related
Dec 16, 2011
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...
View 9 Replies
View Related
Mar 7, 2013
I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
Did I miss something when I created my network?
View 11 Replies
View Related
Mar 16, 2011
Now, I am trying to come with an alternative. My first thought was to use materialized views with Fast Refresh - refresh only changed rows. This might not even work because there are many contraints when using Fast refresh. I am in the process of learning their data model so I do not know much.
Have you ever encountered a problem like this?
View 7 Replies
View Related
Nov 8, 2012
Please explain :
What is the difference between : User calls and executes in
AWR Load Profile :
User calls: 1,373.51 0.80
Executes: 8,558.06 4.98
As i understand , than User calls is : the number of DML (insert , update, Delete and Select ) cursor : parse,execute , fetch , and execute is ?
Why executes > User calls ?
Or may be one pl/sql package procedure may execute many SQL ? How to explain this ?
View 2 Replies
View Related
Mar 24, 2011
My Oracle DB 10g R2 running on AIX 5.3 OS. When I am shutting down the oracle I see that it is waiting for complete the active calls and I determined that these active calls/processes are from remote machines.
When I try to run this ps -ef as shown below, it shown me number of oracle process with LOCAL=NO
/home/oracle> ps -ef | grep 91334
oracle 85210 87320 0 12:47:56 pts/35 0:00 grep 91334
oracle 91334 1 0 12:45:11 - 0:00 oracleIDB (LOCAL=NO)
What are the possible scenarios for this LOCAL=NO? How do I find that this process is from which machine or IP address?
View 2 Replies
View Related
Jul 19, 2013
create table call(id number(15) primary key, calling_no varchar2(15), called_no varchar2(15), calldate date, calltime timestamp, duration number(10) ,calltype varchar2(10)); 1. CALL DETAILS OF 9891826547insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (1, 9891826547, 9891210785, to_date('01-Jun-13','dd-Mon-yy'), to_date('19:12:00','hh24:mi:ss'),10,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (2,9891826547, 9899985476, to_date('01-Jun-13','dd-Mon-yy'), to_date('22:10:12','hh24:mi:ss'),50,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (3, 9891826547, 9818415767, to_date('02-Jun-13','dd-
[code]...
View 3 Replies
View Related
Nov 3, 2011
how to provide the description and parameters of job which calls package on database
package name is some 'xxxxx'
we need to find the job which call the package 'xxxxxx'.
View 2 Replies
View Related
May 8, 2013
Can multiple ODP.NET calls potentially execute within a single Oracle session due to connection pooling?
How can I test/observe this behavior?
We have an IIS application that uses one connection string for all logged in users and call aspx pages.
View 1 Replies
View Related
Jul 17, 2012
I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.
Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)
Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......
Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.
To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')
View 7 Replies
View Related
Apr 18, 2013
You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.
lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.
The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.
What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.
but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?
View 0 Replies
View Related
Oct 24, 2013
I have a context package that uses a session_id.
I want to use multiple sessions as I will have multiple users creating contexts at the same time.
Once the context is created with a session_id I want to reference that session_id in a view.
Can this be done? Using Oracle 10g R2 Context:
-- create ctxCREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG ACCESSED GLOBALLY; -- ctx package CREATE OR REPLACE PACKAGE CTX_PKG IS PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2); PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2); PROCEDURE SET_CONTEXT(IN_NAME VARCHAR2, IN_VALUE VARCHAR2); END CTX_PKG; -- context packatge CREATE OR REPLACE PACKAGE BODY CTX_PKG IS GC$SESSION_ID VARCHAR2 (100); PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2) IS BEGIN GC$SESSION_ID := IN_SESSION_ID;
[Code]...
The first query returns valoc but the second query doesn't return the session_id.
View 5 Replies
View Related
Oct 8, 2010
having a concurrent request that is always up?
As part of an interface I've implemented, I have a 'listener' concurrent request that registers for a dbms_alert and uses wait_one to detect appropriate alerts. When an alert has been detected, a loader concurrent request is kicked-off, before the listener loops back to the wait_one call. I've set a nominal wait_one timeout period of ten minutes. It is not envisaged that many alerts will be raised - maybe several a day at the moment, but I am always reluctant to use polling unless I can avoid it.
Our EBS system has gradually ground to a halt after the release of this program, and the DBAs suspect this as a possible cause. CPU usage was very high overall, with various java-related usage figure also being high.
The overall idea is to have a 'poor-mans' equivalent of OAI, so I'd hope to add more listeners, with each being the equivalent of an OAI adapter.
Surely a big powerful thing like Oracle EBS is not going to keel over just because of a few processes doing little more than waiting for alerts?
View 7 Replies
View Related
Feb 24, 2011
I am trying to execute two scripts at the same time (concurrent) in Oracle SQL Developer. I know we can schedule a job using DBMS_job package and define the job. But is there any other way of doing it using Threads ?
View 3 Replies
View Related
Jul 25, 2006
analytic functions regarding the ORDER BY part =)
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc) rank
4 from emp)
[code]...
why is that i just added ename on the ORDER BY part of the DENSE_RANK and then
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran
[code]...
ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?
View 9 Replies
View Related
Oct 9, 2012
I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.
We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.
if there is a clever way of preventing the same SP to be run concurrently?
Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.
Are there better more efficient/effective ways of doing this?
View 7 Replies
View Related
Oct 26, 2010
My requirement is:
I want to run multiple Reports Concurrently, being called from my Form.
Suppose I have 10 Reports Say Report1, Report2, ....Report10.
I am using "RUN_PRODUCT" to call these Reports from my Form. But it's taking too long time to run all the Reports one after another. Can I run all these Reports Concurrently at the Same time.
View 4 Replies
View Related
Nov 5, 2012
We have an application with database Oracle 10g.
I want to add a new validation to restrict concurrent user and/or session from a client. (we have almost 60 client firms using the software to enter daily trasnactions). All users from all clients are connecting to the database using a common functional ID.
What I did was:
1) Add a column 'user_logged_in' in the master table for client and update it as Y when user from that client logged on to the system,
2) Insert the application logon details (we can figure out the client details from this) into a global temp table,
3) Create a logoff trigger to update the 'user_logged_in'flag in client master table by using values from global temp table when session logged off and
4) Restrict the users from same client if the flag is 'Y'
But the problem in this case is logoff trigger will not be executed in case if the session got killed or terminated abnormally.
View 3 Replies
View Related
