Performance Results :: Transaction Time Vs User Load?

Aug 18, 2010

after running some TPC-C performance benchmarks on oracle 11g/r2 the following results for average transaction time vs user load were found:

Userload 1 : 60ms
Userload 2 : 55ms
Userload 3 : 25ms
Userload 4 : 25ms

Surely these should be the other way wound, transaction time increasing as the number of users does?

what may have caused this? or are the user loads too small to be representative

View 2 Replies


ADVERTISEMENT

Oracle 11g - Record Query And Time To Fetch Results For A Specific User ID

Jul 22, 2013

I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?

View 7 Replies View Related

Performance Tuning :: Real Time Speed Improvements And Auto-trace Results

Feb 4, 2011

I've got a query running a select count (*) over a table. The default plan takes in the order of 15 minutes to return, a hinted plan to use a different index takes 3 minutes to return.

Unfortunately I cant get at the index stats and a few other areas which I suspect may be key here.When running autotrace against the two queries I see fairly different values as one would expect.

Query

select count (*) from fulfilmentitem bfi where created >= sysdate-30 AND bfi.status = 'FA' AND bfi.fulfilmentmethod = 'D'
Slow run
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 33119 (1)|
| 1 | SORT AGGREGATE | | 1 | 15 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| FULFILMENTITEM | 12525 | 183K| 33119 (1)|
|* 3 | INDEX RANGE SCAN | IDX_FULFIL_METHODSTATUS | 250K| | 1786 (1)|
----------------------------------------------------------------------------------------------
[code]....

IDX_FULFIL_METHODSTATUS is across FULFILMENTMETHOD & STATUS in that order.
IDX_BFI_CREATED is on CREATED and is approx 70% of the size of the other index

The row counts estimated in the explain plan are out, the count(*) comes in at 32.8k rows.As you will have seen, the fast run shows a pretty significant consistent get increase compared to the slow run and a decent though not dramatic physical read drop.

My uncertainty is around if these changes in consistent get/phys read values would typically be enough to suggest the real time improvements I'm observing or if other (albeit perhaps temporary) factors are involved. It is a prod OLTP environment so the data will be rapidly changing and that may be a factor.

I know it can never be an exact science without intimately knowing the hardware/current loads etc but I also know that there's enough experience on these boards to have a loose handle on if the time shifts between queries are likely (or not) to be reflective of the stat changes or if those differences alone shouldn't (or typically wouldn't account) for it.

I'm thinking about instructing the query to ignore its original plan but am hesitant to do so without being a little more confident that it's not just a timing thing or something other than the change of index approach which may be causing the improvement. the autotrace stat changes observed I couldn't put my hand on heart say "yup - that change is good, ignore the default index all the time for this job".

View 11 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related

Grant Permission To User On Package Dbms-transaction?

Mar 21, 2013

i have to grant permission to a user on a package dbms_transaction.After i granted permission i am getting error:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSACTION", line 88
ORA-06512: at line 1

View 7 Replies View Related

What Happens When UNDO Space Is Full / Transaction Takes More Time To ERROR

Jan 26, 2011

I have a long running transaction (more than 3 hours), and at the same time other operations are occurring on different tables, using the same UNDO space.

Sometimes we see ORA-30036, but this error occurs very late in the process. The transaction normally takes 3 hours, but when UNDO space is full, we do not get ORA-30036 upto 8 hours or 9 hours of process.

I am wondering what could be happening in the background, when UNDO space is full, which makes the transaction to extend upto 8 hours or 9 hours (pl. note, this transaction gets completed within 3 hours normally). This is in 11g, UNDO space is managed manually.

View 2 Replies View Related

Performance Tuning :: Copy Data From Transaction (DB1) To Reporting (DB2)

Nov 26, 2010

We are copying our transaction tables data into another database for our reporting applications (say every day midnight refresh will happen).

The Transaction Database has some 30tables. Existing system is following below points and it is taking 2hours to complete.

1) Truncate data from reporting database (or schema)

2) Direct path Insert into reporting database (or schema) as select * from transaction tables.

3) Rebuild index and Enable constraints.

Note: Each tables data will vary from 30lakhs to 50lakhs. Dump/import/export is not advised by the client.

I want to cut down the time i.e., below 2hours. Instead of above method. Can go for a field in each table specifying the time of each records update/insert operation and then pick the modified records only and copy into reporting db.

View 4 Replies View Related

Forms :: 10.1.2.0.2 Taking More Time To Load On Some Machines

Mar 31, 2010

Some of my forms taking to much time while loading on some machines but same forms working fine on other machines with same configuration.

Forms, version: 10.1.2.0.2, Clients: Win_XP_Pro, App Server: Window 2003 Server, IE7, Jinit 1.3.1.26

View 3 Replies View Related

SQL & PL/SQL :: How To Load Flat File Into Database At Regular Interval Time

Apr 5, 2011

how can we load a Flat file into a Database At Regular Interval Time.

View 2 Replies View Related

Performance Tuning :: OWB Mapping - Cube Load

Apr 27, 2012

I have a OWB mapping which takes input from a staging table and add those row to the Cube. The underlying table behind cube is a relational fact table joined with the dimensions using foreign keys. Explain plan behind the query has a rather high cost and the mapping runs for 30 minutes. If you see below, in step 17, the cost goes up to 1,396,573 which is also where nested loops start to appear. Query plan is also attached in the image format.

Plan
SELECT STATEMENT ALL_ROWSCost: 1,746,526,275 Bytes: 386,835,904 Cardinality: 464,947
46 NESTED LOOPS OUTER Cost: 1,746,526,275 Bytes: 386,835,904 Cardinality: 464,947
41 NESTED LOOPS OUTER Cost: 1,744,200,663 Bytes: 380,791,593 Cardinality: 464,947
37 NESTED LOOPS OUTER Cost: 1,743,270,415 Bytes: 374,747,282 Cardinality: 464,947
34 NESTED LOOPS OUTER Cost: 1,740,476,128 Bytes: 368,702,971 Cardinality: 464,947
29 NESTED LOOPS OUTER Cost: 1,739,545,862 Bytes: 362,658,660 Cardinality: 464,947
25 NESTED LOOPS OUTER Cost: 1,710,193,475 Bytes: 356,614,349 Cardinality: 464,947
[code].........

View 1 Replies View Related

Partitioning Table To Improve Load And Query Performance?

Nov 20, 2012

I have a big table in which we load about 37M recrods. We have informatica ETL which Loads the data in bulk Mode and creats index after completion. The data load takes about 1Hr and Index Creation takes about 1/2 hr. In total it takes about 90 to 95 Mnts.

Now I thought if Partition and Load paralley, It will improve perfromance. We did 4 partition and and each Partition about 9M records. The data load in Bulk mode is completing in 25 Mnts. Again When I am creating index over it, It is taking about 40 Mnts. and in Total Load time is 65 Mnts.

Is there way I can better performance to complete the load in 1/2 hr ?

View 2 Replies View Related

Performance Tuning :: Tools For Monitoring Load Of Database?

Dec 3, 2010

which tools are available for monitoring load of the database?

View 4 Replies View Related

Server Utilities :: How To Load Date And Time From Text File To Oracle Table Through Sql Loader

May 31, 2010

what i miss to load date and time from text file to oracle table through sqlloader

this is my data in this path (c:externalmy_data.txt)

7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30

my table in database emp2

create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);

the control file code in this path (c:externalctrl.ctl)

load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)

this is the error :

C:>sqlldr scott/tiger control=C:externalctrl.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
C:>

View 21 Replies View Related

Performance Tuning :: Script To Load Trace Files Errors To A Table?

Jul 13, 2013

I need to create a script which can fetch the error related details from Trace files regularly (may be twice a day ) to a custom table in DB.

View 3 Replies View Related

How To Drop More Than One User At Time

Nov 3, 2012

in one short i want to drop more than one user?

View 6 Replies View Related

SQL & PL/SQL :: Drop All The Tables In A User At A Time

Sep 15, 2011

is there any syntax to drop all the tables at a time in a user

View 12 Replies View Related

CPU Time Per User Call / High Or Low?

Aug 10, 2012

I've plan to use "CPU Time Per User Call" metrics.

The thresholds are:

Warning: 8000
Critical: 10000

But this alarm raise every minute.

I think it's too low.But which is the correct value to identify performance problems?

View 4 Replies View Related

Know User Account Locked Time In HH:MM:SS Format?

May 30, 2011

How we know a user account locked time in HH:MM:SS format ?

View 1 Replies View Related

SQLPLUS Select Statement To Get Last Failed Time From Each User

Aug 13, 2010

I'm new to the sqlplus,

User ID Failed_timestamp
----------------------------------
userA 20100801 11:22:33
userB 20100802 11:22:33
userA 20100802 12:22:44
userA 20100803 13:34:55
userC 20100803 20:21:23
userB 20100804 16:34:56

I wanna to get the last failed time from each user. what is the select statement?

my expected result will be:

userA 20100803 13:34:55
userB 20100804 16:34:56
userC 20100803 20:21:23

View 3 Replies View Related

Forms :: Captcha At Time Of Password Recovery To User

Jan 14, 2012

i would like provide a captcha at the time of password recovey to the user. is it possible to place a captcha in forms 6i. if possible how to provide it. i want the piece of code.

View 5 Replies View Related

Performance Tuning :: Sql Taking More CPU Time?

Nov 23, 2010

I have one query in my production which is taking more CPU time. when that statement executing the CUP is taking more than 90%

I am attaching the sql query and indexes on the table.

View 4 Replies View Related

Performance Tuning :: Update XML Eating Up A Lot Of Time

Sep 26, 2012

update xml eating up a lot of time is there any way to tune

SELECT UPDATEXML(:B3 , '/FCUBS_RES_ENV/FCUBS_BODY/FLD/FN[@TYPE="' || :B2 ||
'"]', :B1 )
FROM
DUAL

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 499 0.44 0.90 0 3 0 0
Fetch 499 1.49 2.87 0 0 0 499
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 999 1.93 3.77 0 3 0 499

real code
SELECT updatexml(l_xml,
'/FCUBS_RES_ENV/FCUBS_BODY/FLD/FN[@TYPE="' ||
upper(replace(cspkes_misc.fn_getparam(p_parent_list,
l_parent_list_clob,
'Y',
l_cnt,
'>'),
'-',
'_')) || '"]/text()',
l_fn_str)
INTO l_xml
FROM dual;

View 4 Replies View Related

Performance Tuning :: Avg Time Taken By Execution Plan

Apr 12, 2013

How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.

View 7 Replies View Related

Performance Tuning :: Time Calculation In AWR Report

Dec 12, 2011

Till statspack we had

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?

View 2 Replies View Related

Performance Tuning :: Loading And Reading At Same Time

Feb 13, 2013

For example, we have a table ACCOUNT (snowflake dimension containing other dimension keys) and I have many fact tables based on this dimension. Normally data warehouse load happens like first dimensions needs to be loaded and then facts. Our frequency of loads is 30 mins.

To increase the rate in which the data will be available in the facts (as its a financial application), am considering to have two batches one with dimension and another one with fact (came to this conclusion as there is no dependency like first dimensions to be loaded then only fact) just the update might get missed sometimes. But if I do that, when dimension gets loaded, it will be read in the facts in another session. Will this affect the performance ?

LOADING (insert/update) and selecting data from table at the same time. Will it affect the performance in any way.

View 1 Replies View Related

Performance Tuning :: Database Time In AWR Report

Jul 9, 2012

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?

DB Time = sum of database CPU time + waits

Is I/O considered as a part of CPU time?

Does this equation changes with SAN, OS caching?

View 3 Replies View Related

Performance Tuning :: SQL Ordered By Elapsed Time

Feb 2, 2013

Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB TimeSQL IdSQL ModuleSQL Text

3,263 32 1 3263.49 2.79 3ta0ms19fvgds httpd.exe SELECT CASE WHEN COUNT >= 1...Elapsed
6,360 164 2 3180.17 5.44 51jx99dm0swv7 cpm_srvscript@ahcaxasmil1b (TNS V1-V3) SELECT /*+ CCL<PFT_GET_RP...

On AWR, I see two script that are out of ordinary, and I want to make sure that I interpret them correctly.

1) "Elap per Exec (s)" shows 3263.49 with 1 "Executions".
2) "Elap per Exec (s)" shows 3180.17 each execution with 2 "Executions".

Does this mean that this script ran for ~ 54 minutes (3263.49 / 60 seconds) for 1 "Execution" and ~ 53 minutes (3180.17 / 60 seconds) per each execution? I need to understand "Elapsed Time (s),CPU Time (s),Executions ,Elap per Exec (s), % Total DB Time" represent.

View 9 Replies View Related

Performance Tuning :: Lot Of Time Being Taken To Delete Data From A Table

Apr 27, 2012

I have a table which contains 8,21,177 amount of data totally.Now I am trying to delete around 4,84,000 of data from this table by using just one filter i.e. my query is something like below

DELETE /*+ parallel(resource,4) */ FROM resource where created_by = 'MIGN'

This is going to delete 4,84,000 rows of data . But my current issue is this is taking lots of time to delete the data . To be precise , its almost taking 25 hours to delete this data..The created_by column is indexed .

Execution Plan
----------------------------------------------------------
Plan hash value: 2389236532

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 499 | 20459 | 39 (0)| 00:00:
01 |

| 1 | DELETE | RESOURCE | | | |
[code]....

View 26 Replies View Related

Performance Tuning :: Lost Index From Second Execution Time?

Jul 11, 2013

The problem was describe:

- First time to execute: Using all indexes on 2 tables

- Second time to execute: Using only indexes on first table, full table scan on the other

- Third time to execute: Do FTS on both of tables.

Now, I show the objects and relate information here:

The Tables:

system@dbwap> select count(*) from my_wap.news_relation;

COUNT(*)
----------
272708

system@dbwap> select count(*) from my_wap.news_content;

COUNT(*)
----------
95092

system@dbwap> desc my_wap.news_content;
Name Null? Type
----------------------------------------------------- -------- ----------------
ID NOT NULL NUMBER(11)
SUBJECT NOT NULL VARCHAR2(500)
TITLE VARCHAR2(4000)
STATE NUMBER(1)
IMGPATH VARCHAR2(500)
ALIGN VARCHAR2(10)

[Code]....

View 7 Replies View Related

Performance Tuning :: Max And Delete Statement Talking Lot Of Time?

Apr 15, 2011

Test1 table have around 385772300 rows. below delete and select statment talking lot of time.

Select stament taking more than 1 hrs.

SELECT TO_NUMBER(MAX(f.T3))
--INTO v_FISCAL_MONTH_ID
FROM Test1 f;

delete statment taking more than 2 hours

DELETE FROM TEST1 WHERE TRUNC(T10) < TRUNC(ADD_MONTHS(SYSDATE,-36));
CREATE TABLE Test1
(

[Code].....

View 4 Replies View Related







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