In this select I would like to get db time percentage for every sql_id as I can see in AWR report.How can I do this?
select
sql.sql_id ,
sql.executions_delta ,
round(sql.elapsed_time_delta/1000000) elapsed_sec,
round(sql.elapsed_time_delta/1000000)/sql.executions_delta elapsed_exec,
[code]...
couple of questions about below parameters, do they measure single processor or avg usage across all processorss and cpus for example will i recieve alert if CPU system time goes above 95% for single processors? or not
CPU Idle Time (%)
CPU System Time (%)
CPU User Time (%)
I am fairly new to Oracle SQL developer and I am having an issue that may very simple but I just cannot work it out logically for some reason.I am trying to calculate the percentage of sales each employee has made form the total of trades. I have been trying to use the COUNT function but it obviously splits the counts for each employee. I then tried a inline view but couldn't get it to work and also a sub-query and had an issue with "not a single group function" and then "not a GROUP BY expression".
how I can do this? I will also need to add the syntax to another inline view
I have a one more query
col1 col2
3-18083017013-Standard
3-18083225012-Significant
3-18082775913-Standard
3-18082426912-Significant
3-18082097112-Significant
3-18081539512-Significant
3-18081946712-Significant
3-18080523612-Significant
3-18076873112-Significant
3-18076872712-Significant
3-18080522412-Significant
3-18080064612-Significant
3-18070899912-Significant
3-18081155713-Standard
3-18071160013-Standard
3-18077564213-Standard
3-18079220312-Significant
3-18073201313-Standard
note:
3-Standard 240 min
2-Significant 120 min
1) i need to calculate the percentage value.
general form is :
total number of col1_values(count) in 120 min/count(col1) *100
conditions to calculate percentage:
1)need to ca
I want to calculate the running percentage. sample data is as below.
CREATE TABLE prod
(
code VARCHAR2(8),
name VARCHAR2(30),
stdate DATE,
itons NUMBER(7,3),
rtons NUMBER(7,3)
[code]....
Required Output is
CODE NAME STDATE ITONS RTONS %age
-------- ------------------------------ --------- ---------- ---------- -------
0-01-001 SEEDS 17-JUL-12 193.155 0
1-01-001 OIL 17-JUL-12 0 81.906 42.404
1-02-001 MEAL 17-JUL-12 0 104.304 54.000
1-03-001 DURT OIL 17-JUL-12 0 1.242 0.643
2-01-001 WASTE 17-JUL-12 0 5.703 2.953
[code]....
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
I want to find the Pass rate for each term by adding up the grades.
PASS = HD/D/PASS FAIL = FAIL and Z means no mark has been recieved yet so i do not want to include it in the calculation.
CREATE TABLE DAN_GR
(ID VARCHAR2(8),
TERM VARCHAR2(8),
GRADE VARCHAR2(8),
SUBJECT VARCHAR2 (8),
STAT VARCHAR2 (8))
[Code]....
So term 1201 has 1 Pass 1 Fail and a Z (which is not counted) so pass rate is 50%
term 1202 has has 1 Pass only so the pass rate is 100%
term 1203 has 1 Fail so pass rate is 0%
term 1204 has 1 Pass and 2 Fails and i dont count the 3rd fail because the stat is 'APSENT' i only want to count the once where stat is 'PRESENT'
WANT:
COUNT TERM PASS RATE
3 1201 50%
1 1202 100%
1 1203 0%
3 1204 33%
I am using:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
In my table three column are there, structure_code, attribute_code and percentage. one project have many attribute_codes, each attribute code have percentage value. The total of percentage value for a project is 100.
data like follows
structure_code Attribute_code percentage
160025 2531 30
160025 2536 20
160025 2537 50
160025 2538
162061 1468 0
Now i need to select which attribute_Code have maximum percentage for each project(structure_code).
See the attached pdf the output of my report. I want to calculate the GP at the Total column.
My GP formula is
trunc((profit) / decode(ACTUAL,0,1,ACTUAL)*100,2).
I tried using Placeholder Column & Formula Column but it is not working. I am using grouping in INV_DATE and summing all the columns.
Is it possible to grant a user a percentage amount of a specified tablespace ? for instance granting a user called userx 5% of USERS tablespace. How can I do that ?
View 3 Replies View Relatedlook at these tables (Customer and Rates), having some sample data. I've created a function for returning the interest percentage for a particular customer based upon his/her investment start date and end date. I'm getting the desired result but I'm wondering if I need calculate the composite interest here...then how can I do that? Suppose a customer has invested some amount for 45 days, then I need to calculate the interest for initial 30 days as 5% and additional 15 days as 6% and I need to give the final amount. In this case how can I pull this task off? how can we do this interest calculation by an Oracle program?
Customer Table
CUSTOMER_ID AMOUNT START_DATE END_DATE
1000 40000 15-JAN-13 15-FEB-13
1001 34000 15-DEC-12 15-FEB-13
1002 35678 15-NOV-12 15-FEB-13
Rates Table
RATE_PCT LOW_TENURE HIGH_TENURE
5 0 30
6 0 60
7 0 90CREATE OR REPLACE FUNCTION
F_INTEREST_PCT (V_CUSTOMER_ID IN CUSTOMER.CUSTOMER_ID%TYPE)
RETURN NUMBER AS V_RATE_PCT NUMBER;
[code]...
estimate the percentage of memory should i can allocate from the total physical memory (it is 6G) as attached in the image file as a print screen.
View 4 Replies View RelatedSL_NoStatus
191502Implemented
191690Implemented
190998Implemented
191346Implemented
190541Implemented
[Code]..
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case:
lets consider,
A->count(Sl_NO)
B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups.
CREATE TABLE target_data
(
T_LINK VARCHAR2(50)
, t_mon varchar2(6)
, t_grp varchar2(30)
, t_views NUMBER
[code]...
When I run this, I get output as
t_grp count_80 goal
grp3 1 0.1
But I want the output as
t_grp count_80 goal
grp3 1 0.1
grp2 1 0.1
grp1 ... ...
grp0 ... ...
I have two values:
'AA:10%:1'
'AC:P35:20%'
I need to extract the numerical value preceding the '%' sign. for first row 10 should be extracted and for the second 20.
I've gotten it to display the percentage of markup correctly(as in, multiplied by 100 with no decimal places), but I cannot for the life of me get the percentage sign to display on the right of the values(i.e. 40, but not 40%).
Here's my code so far:
SELECT title, to_char(round((retail-cost)/retail*100), '99')
AS "MARKUP %" FROM BOOKS;
I've tried concatenating with both the + sign and the ampersand (&) sign, to no avail.
value of "Shared Pool Free %" in v$sysmetric is larger than 100%
SQL> select value, group_id from v$sysmetric where metric_name = 'Shared Pool Free %';
VALUE GROUP_ID
186.45107 2
186.4685382 3
SQL> show parameter shared_pool
NAME TYPE VALUE
-------------- ------------ --
shared_pool_reserved_size big integer 832149913
shared_pool_size big integer 0
SQL> show sga
Total System Global Area 1.0155E+11 bytes
Fixed Size 2163880 bytes
Variable Size 8.1068E+10 bytes
Database Buffers 2.0401E+10 bytes
Redo Buffers 79310848 bytes
Oracle Express 4.0 I have set up a Tabular Form. It has 5 columns. I wanted to set 4 of the columns to a width of 15%. 1 column to 40%. I set the appropriate values to style=width: "15%"; and style=width: "40%"; in Column Edit's Element Attributes. But all columns are displayed equally. If I try the format style="width: 40%" I get what appears to be 40% of the cell width for the text field (not the table width). Can %'s be used on Ellement Attributes?
View 0 Replies View RelatedHow can we check completion status for running sql query. i.e. how much % completed
SQL> begin
2 delete from gsmcrmdw.wc_loy_txn_f_aa
3 where integration_id in
4 ( select integration_id
5 from support_olap.recover_wc_loy_txn_f_953to955
6 );
7 commit;
8 end;
9 /
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.
What does this exec and Fetch time is?
How to improve fetch time, (esp with sub-query) ?
I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code
""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......
I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.
This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.
The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.
. I have this query:
select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1
[code]...
As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies View RelatedI have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.
View 4 Replies View RelatedWe 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'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.
After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.
--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;
I have a field " Tran_date with data type Date . This field contains date as well as time both . while I run this query :
select to_char(tran_date,'DD-MON-YYYY HH24:MI:SS') from ABC
WHERE lOC='1' ;
It is showing result :
21-JAN-2012 00:00:00
Why it is showing 00:00:00 although there is time like 08:25:12 Where is the problem ? Why I can'y see time . I need to change anything ,anywhere ?
I have an issue while converting Date. I need to compare the Time Captured while comparing Dates,
find the following scenario,
SELECT TO_CHAR(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') TIME_STAMP ,
ffdh.last_update_datetime,
to_date(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') DATES ,
ffdh.fiscal_doc_id INVOICE_ID
[Code]....
Note That ffdh.last_update_datetime is DATE, and the Parameter for the value '17-JUN-10-01:30:17' is VARCHAR2
I suspect when I am doing to_date above both are converting only to Date and not having Time.
I checked the NLS_DATE_FORMAT as following,
SQL> show parameter nls_date_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-RR
Do I need to Change the NLS_DATE_FORMAT ?