Performance Tuning :: Capacity Planning For 250 User OLTP Shop
Mar 19, 2013
installing Oracle for a new Data-Center(still in planning).It will be an OLTP shop working 24/7, with 250 concurrent users.
1. Can it, in theory run with good performance on a quad processor(something like Intel Xeon Series 56XX) , with 32GB of memory?
2. What is the Oracle Database Edition required for this configuration?
3. What additional things should one take into account to plan the most cost-effective configuration?
View 2 Replies
ADVERTISEMENT
Oct 31, 2011
how to do a capacity planning for a DB ?
View 1 Replies
View Related
Aug 6, 2012
I'm planning to decrease the time taken to execute data by managing the redo log file but I'm kinda stuck in some aspect : > Why is my OPTIMAL_LOGFILE_SIZE is showing NULL ? > I'm trying to resize the LOGFILE capacity from 100M to 200M and I'm also adding 1 more LOG GROUP with 200M capacity too but turned out that didn't decrease my execution time.
View 12 Replies
View Related
Feb 22, 2011
In an OLTP environment what cursor_sharing setting is preferred?Though typically we retain the original setting for most of the parameters except memory settings etc. I have queries in the following context
No. I am not facing any issue as of now (I am not supporting any Live environment) But I want to know the desgn considerations
First of all in OLTP environment (say one I am referring) we use pl/sql variables which are obviously bind variables Only in case where plan is expected to change we use hard coded values like 'CREDIT' or 'DEBIT' etc. for acc_type column
Again there can be 2 scenario
1) we use the same query for both acc_type values
2) we use 2 different queries
IF v_parameter = 'CR'
select * from accounts where acc_type='CREDIT'...
else
select * from accounts where acc_type='DEBIT'...
end if;
Again suppose the values are skewed and we gather stats with histograms hereIs't it the setting 'cursor_sharing=similar' which will be useful in above case?as with this setting optimizer will 'think' which plan to pick depending upon the values and bind variable peeking is taken care in option 2 above with IF ELSE clause?
BTW
I have carried several tests but not getting conclusive results For example I created following table with skewed data, created index and gather stats with histogram
SQL> select object_id,count(*) from skewed_data_tab group by object_id;
OBJECT_ID COUNT(*)
---------- ----------
5 30
6 2970
7 10797
8 150000
9 300000
SQL> create index i_skewed_tab_data on skewed_data_tab(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'SKEWED_DATA_TAB',cascade=>true,
method_opt=>'for all columns size 254');
Then traced with following options
1) alter session set events '10046 trace name context forever, level 12';
SQL> begin
for i_outer in(select n from ids order by tstamp)
loop
for i_inner in (select /* for exact */ object_id,object_name,object_type
from skewed_data_tab where object_id=i_outer.n)
[code]...
2) set termout off
alter session set events '10046 trace name context forever, level 12';
@/u04/scripts/exact.sql 5
cat /u04/scripts/exact.sql
select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=&1;
But not getting consistent results
View 11 Replies
View Related
Aug 9, 2012
How can i find the last user analyzed particular table?
I can able to find last analyzed date, but can't able find the which user has done it.
View 2 Replies
View Related
Jun 11, 2010
How can i differentiate between system issued sql's and user issued sql's in the tkprof report ?
View 5 Replies
View Related
Jul 12, 2010
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
View 1 Replies
View Related
Mar 21, 2009
i am a beginner in database design.i want to know what will be the database design of any food shop like pizza hut..i have created some tables...i am confuse with design of order table..
employee_details
-ID primary key
-name
-address
-date_of _joining
-salary
product_details
- category_name ,foriegn key
- product_name,primary key
- category
- unit price
-discount
categories
-category_name , primary key
View 3 Replies
View Related
Oct 31, 2011
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
View 39 Replies
View Related
Sep 30, 2010
How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
View 24 Replies
View Related
Jun 16, 2010
I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
View 2 Replies
View Related
Oct 20, 2010
There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?
View 6 Replies
View Related
Jun 16, 2011
How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?
300 Millions in only one table with 500K transactions / day is too much?
Simple database with simple schema.
How many records begin to be too many?
View 2 Replies
View Related
Nov 15, 2010
Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.
The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.
any possible reason that we'd have to re-install a procedure to correct a performance problem?
View 13 Replies
View Related
Apr 12, 2013
I need to check the package performance and need to improve the package performance.
1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.
View 13 Replies
View Related
Aug 9, 2010
Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.
Q1. If it is true what statistics do we adjust for influencing the execution plan and how?
For example, I have the following simple query:
select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.
If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.
Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?
I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.
View 6 Replies
View Related
Dec 6, 2011
I have an issue with export(expdp).
When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.
expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk
Do i need any look into any memory parameters for this?
View 1 Replies
View Related
Apr 23, 2012
I want to select the IDs which have no IN or PLANNING status in AREA 9
ID 1 has AREA 9 and STATUS IN so dont select
ID 2 SELECT
ID 3 has area 9 BUT STATUS is OUT so Select.
So if an ID has has IN or PLANNING status in AREA 9 i do not wish to see it!
CREATE TABLE DAN_T1
(
ID varchar(8),
AREA varchar(8),
UNIT varchar(8),
STATUS varchar(8)
)
INSERT INTO DAN_T1 (ID,AREA,UNIT,STATUS) VALUES (1,6,'AA','IN');
[code]......
ID AREA UNIT STATUS
16AAIN
17ABPLANNING
17ACOUT
19ADIN
25ACIN
25AAOUT
35BAPLANNING
36DAIN
35CAIN
39CGOUT
WANT:
ID
2
3
View 2 Replies
View Related
Oct 17, 2011
The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.
If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.
For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.
DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'
[code]...
View 9 Replies
View Related
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
Mar 20, 2013
I have a requirement where I need to group sessions which takes time as given below when ran individually .There are a total of 32 sesions.I want to divide them into 7 groups.I want to submit each one of the 7 groups in a job.So there will be seven jobs on the whole.Execution flow will be group 1 completes ,then group 2 starts like that.Actually I don't want to bombard the database at a time with 32 jobs ,hence the idea of grouping 32 jobs among 7 groups.grouping the sessions so that on the whole ,resources are equally allocated among sessions in a group and which in turn makes the process complete faster. I want to achieve parallelism but at the same time database should not be loaded too much.I just want to know is there any good practice from oracle side while doing this type of grouping. I am planning to group the most time taking one with least time taking jobs.
S.NO Session_Name DURATION(HH:MM:SEC)
1 Session_1 00:11:56.202368
1 Session_2 00:00:00.058542
1 Session_3 00:00:00.055205
1 Session_4 00:00:00.043454
1 Session_5 00:00:00.039994
2 Session_6 00:08:05.786031
2 Session_7 00:00:00.579035
2 Session_8 00:00:00.537957
[code]...
View 0 Replies
View Related
May 14, 2013
I have a table TEMP_TEST_1 to be loaded with multiple rows which contains the available items for a store with their sequence numbers. I also have another table TEMP_INV_1 which holds a column for inventory count of the items, and this column is to be updated after loading the first table TEMP_TEST_1.
The table TEMP_TEST_1 is updated through all possible channels in OLTP. So while updating the inventory, which is the best way. My update should either add the total number of inserts per item into the TEMP_TEST_1 table's INVENTORY_CNT column i.e. existing value + count of new rows OR a full update by taking the complete row count (Grouped by item id) and update the INVENTORY_CNT column
--------------------------------------------------------------------------------
-- Initial setup SQL's, TEMP_TEST_1 is updates on OLTP through multiple channels
--------------------------------------------------------------------------------
CREATE TABLE TEMP_TEST_1 ( ITEMID NUMBER ( 4 ),
INVENTORY_CNT NUMBER ( 2 ) );
CREATE TABLE TEMP_INV_1 ( ITEMID NUMBER ( 4 ),
ITEMSEQ NUMBER ( 5 ) );
INSERT INTO
[code].......
--------------------------------------------------------------------------------
-- Inserts for new items in the store
--------------------------------------------------------------------------------
INSERT INTO
TEMP_INV_1
VALUES
( 1,
11 );
INSERT INTO
TEMP_INV_1
[code].......
View 4 Replies
View Related
Apr 29, 2010
I have to create/configure the environment for the database having high volume of the transactions nearly 10000 transactions per day and at peak time nearly 1500 transactions per second.
RAC can be one of the solution but that comes up with high cost. I googled for this type of environment configuration, what could be possible settings need to be configured for this environment configuration in both RAC and Non-RAC.
View 6 Replies
View Related
Sep 2, 2012
I am trying to enable OLTP compression on tables and at tablespace level for the tables
Steps I am following are:
1. Move indexes to its own tablespace
2. enable OLTP compression at table level:
alter table table_name move compress for OLTP
3. Rebuild indexes
4. Issue I have is what to do with tables with LOB columns
ALTER TABLE lob_table MOVE LOB (LOB_COL) STORE AS (TABLESPACE index_tbsp); -- Is this correct?
5. alter tablespace data_tablespace default compress for OLTP;
I have a question, is the sequence of steps correct. For tables with LOB columns do we needto move lobindex to index tablespace. Beacuse lobsegment and lobindex are created in data tablespace?
View 2 Replies
View Related
Mar 17, 2012
are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?
View 8 Replies
View Related
Mar 27, 2013
I am working on an assignement where client is using Oracle 10g but stuck to using RBO Now the application team, from the GUI available to them build dynamic queries and some of them run very slow.
Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO).For some long running queries the Tuning advisor is not producing any recommendations.
Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!
View 11 Replies
View Related
Jul 4, 2012
I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.
SELECT DISTINCT hash_value,
sql_text
FROM gv$sql sq
WHERE hash_value IN (SELECT DISTINCT prev_hash_value
FROM gv$session se
WHERE sid IN (SELECT sid
FROM gv$lock l
WHERE type = 'TX'
AND ctime >= 2000
AND l.inst_id = se.inst_id
AND l.sid = se.sid)
AND sq.inst_id = se.inst_id);
[code]....
View 7 Replies
View Related
Nov 2, 2012
I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.
how a same query can have different SQL_ID's but same HASH_VALUE?
Note: Statistics are not modified on the base tables of this query.
View 10 Replies
View Related
Aug 10, 2011
I am running Oracle 10.2.0.1.0 on MS Windows 2003 server 64-bit with 16G RAM.
Here is the findings for my Oracle database.
SQL> select * * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1293560 No
Redo Buffers 7094272 No
Buffer Cache Size 830472192 Yes
[code]...
I find that the SGA component "Buffer Cache" is decreasing from the start "1.8G" and down to now 0.8G. On the other hand, the component "Shared Pool" is increasing from the start 0.3G to now 1.2G. I noticed that there are 100 operations of shrinking of "Buffer cache" and growth of "Shared Pool" in Oracle every day.Is it a indicator that I should raise up the SGA_MAX_SIZE?
I tried to increase the SGA_MAX_SIZE to 4G. But I cannot start the Oracle afterward.Is it a limitation of MS Windows(OS) or Oracle?I set the SGA_MAX_SIZE to 3G. This time, I can startup Oracle.What is the optimum/maximum I can set to SGA_MAX_SIZE?Is there any adverse effect/concern when setting the SGA_MAX_SIZE more than 2G?
View 6 Replies
View Related
Jul 11, 2012
Here i have three tier application. I want to know it host name from sid or sqlid . I want to know which query run on which host. Because i have one user from application to database. So i want to know which query consume more time on which host ?
View 19 Replies
View Related
