Minimize Impact As Users Are Complaining For Performance
Sep 9, 2011
in my oracle enterprise manager under " user i/o " .i am having basically four category:
if we rank them out of ten it would be like :
read by other session 2/10
db file scattered read 1/10
direct path read .5/10
db file sequential read 6.5/10
and all these are comming for 2 tables involved for almost all time .some way to handle "read by other session" and " db file sequential read " .
i am rebuilding indexes of these involved table once in 10days and statistics for the these tables are collected every day using "analyze table "xxx" compute statistics."tell me the indepth approach i should take to minimize the impact as users are complaing for performance.
View 2 Replies
ADVERTISEMENT
Apr 30, 2013
I'm trying to find some information on the performance impact of a trigger on a heavily updated table when the condition to fire the update trigger is NOT met. In other words I guess what I'm really trying to find out is what the performance impact of the system checking the condition on the trigger to determine if it should fire or not is.
For example I have a batch job that inserts and updates a table heavily, but the batch job almost never updates the column in question on the trigger to the value that would cause it to fire, but it does update that column to other values often.
I know about the many downsides of using triggers in general, but I'm working with a third party application, so more optimal solutions aren't an option.
View 1 Replies
View Related
Nov 28, 2011
my sql query has three tables in from clause so it has two join conditions and one where condition.
account_no is number data type and v_account_no is varchar2() data type
The where clause is :
"where account_no=to_number(v_account_no)" with this condition in my sql query has the cost 392
we just modify the where clause as where v_account_no=to_char(account_no) with this condition in the sql query has the cost 11.
what is impact of this data type conversion and difference between these two "to_number() and to_char()" in performance wise to reduce the cost of query?
View 8 Replies
View Related
Nov 6, 2012
I have been used to the consciousness that we should use the minimum length for varchar2 field that can store the data we need manipulate. But recently I was told that it has little impact on performance if we assign a much longer size.
View 13 Replies
View Related
Mar 27, 2013
I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?
View 2 Replies
View Related
Dec 10, 2012
One of my clients need to remove three(of four) CPU to comply the licensing agreement with Oracle.
To avoid problems and also to list the possible problems that removing the CPU can bring, I wish to make a survey of the possible impacts, especially in performance, that removal can cause.
How can I get this information?
View 8 Replies
View Related
Jul 20, 2012
We have a change in IT Dept in our organisation and we have been asked us about the impact of the following changes
1. Change in listener Port
2. Change in value of send and Recieve buffer in listener.ora
3. Change in Archive Size from 40M to 20M
View 2 Replies
View Related
Mar 3, 2011
I am attempting to read from the maillog of our server, but I wish to make as few changes as possible for fear of blocking other systems access to the file.
I was initially going to call create directory maillogs as '/var/log/maillog' and then drop directory maillogs; when I was done but I found my user does not have "create any directory" permissions.
Rather than compromise security of the existing database configuration, I thought I would permanently add the maillogs to the list of available data directories. Are there any implications to the filesystem if I do this, or should I be able to add this without consideration of affects.
Understand that I will only be opening the file for (R) READ TEXT access only.
Primarily I am concerned that Oracle (in the background) will keep a file pointer open or something of that nature that would block other programs from writing to the file even after I close the file pointer. I want to make as little impact as possible to the file system.
View 4 Replies
View Related
Dec 21, 2010
Which config file is used to change the os version(RHEL 4.0 to RHEL 5.5) by OS admin what will be its impact on ORACLE Databases.
View 3 Replies
View Related
Aug 28, 2013
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
PL/SQL Release 11.1.0.6.0 - CORE 11.1.0.6.0
TNS for Linux: Version 11.1.0.6.0 - NLSRTL Version 11.1.0.6.0
When I checked the status of $emctl status dbconsole. I got this error OC4J Configuration issue.
I was told by the forums to drop and recreate the repos. But I noticed the moment you drop the repos you will loose the passwords of SYSMAN and DBSNMP. emca -deconfig dbcontrol db -repos dropemca -config dbcontrol db -repos createI tried default passwords does not worked. it looks like the only option I have is to reset the passwords. but my main concerns is this impact the DB in generally or its common. or is there any other way we can get the OEM back(I already dropped the repos).
View 2 Replies
View Related
Oct 21, 2012
Any impact is there if is do the following:
ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;
will there be any lock on the table during this operation? DML operations will work without any issue or not?
View 10 Replies
View Related
Feb 29, 2012
I am looking at a performance issue at the moment and trying to replicate on a test system. I am initially looking at the impact of upto-date statistics on the main schema's objects.
For this I wanted to:
first run the batch with whatever stats were present in the database Flashback the db to before the batch . Gather stats Re-run the batch with updated stats and compare results.
However, I inadvertently ran the stats job before running the load the first time! I have the SCN from when the environment was set up like production (ie before the stats were run) so am I correct in saying that if I flashback to this point then the stats will be "old" and I can just run the batch then? I know I can verify this when I Flashback the database by looking at LAST_ANALYZED on tables etc but it would be good to know this before hand as it's a 12 hour batch.
View 1 Replies
View Related
May 19, 2007
I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.
int sysrun(char *command)
{
return system(command);
}
This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/
Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.
SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0
PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
[code]...
View 1 Replies
View Related
Oct 28, 2009
I am using prebuilt MV to perform replication of about 300-400 master tables from one database to another database. I am wondering about the impacts on triggers in general replication.
IS there a general rule to enable/disable a trigger before a refresh.
View 1 Replies
View Related
Jun 3, 2011
I have 4 tables as follows.
1.Deploybase(DeployId)
2.LicenseHistory(DeployId,UserId)
3.Users(UserId,User_Detail_id)
4.UserDetails(User_Detail_id)
Tables are joined using below joins.
Deploybase.DeployId=LicenseHistory.DeployId
LicenseHistory.User_id=Users.User_id
Users.User_Detail_id=UserDetails.User_Detail_id
When I join LicenseHistory,Users,UserDetails, all records are unique.
But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want
I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.
Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)
Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails
View 1 Replies
View Related
Dec 23, 2011
HOW TO LOGOUT ALL USERS FROM ORACLE IN ONE COMMAND
View 2 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
Mar 2, 2013
I would like to extract the user rights from the ERP that I use with SQL/Oracle.
There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.
I would like to know all the actions that all users can do or not.
The result must be something like:
Action 1
User 1 Yes
User 2 No
Action 2
User 1 No
User 2 Yes
....
View 9 Replies
View Related
Jun 2, 2011
how to see password of users in 11g
View 11 Replies
View Related
Dec 8, 2010
I want to ask for a password from the user so they can access their account, however I want the password to have constraints on it. For example I want the user input to between 6 and 12 characters in length containing at least one number and one letter.
View 2 Replies
View Related
Feb 27, 2013
I've been issuing commands like these by using a session on my guest Oracle VM that is hosted by Windows 8.
sqlplus / as sysdba
create user PocketGuide3rdEd identified by top-secret-password ;
grant connect to PocketGuide3rdEd ;
grant create table to PocketGuide3rdEd ;
grant create view to PocketGuide3rdEd ;
grant create trigger to PocketGuide3rdEd ;
grant create sequence to PocketGuide3rdEd ;
grant unlimited tablespace to PocketGuide3rdEd ;
I've discovered that I cannot do "sqlplus / as sysdba" remotely. Is there a way I can create another account like sysdba that can issue the above commands from windows so I don't have to log into the oracle guest VM?
View 4 Replies
View Related
Oct 2, 2013
I am trying to find out if there is a definite way to find out (by querying database) which database users have been created by Oracle (either during installation or as part of patching or adding new feature) and which database users have been created by DBAs.
I have looked into the documentation but could not find anything relevant. Ideally, I would like to know if this can be done for any versions starting from 9iR2.
View 7 Replies
View Related
May 2, 2013
Are you aware of any "Best Practice Document" to grant select accesses to users on databases. These users are developers which select data out of database for the investigation and application bug fix.
From time to time user want more and more access to different tables so that they can do investigation properly.
View 2 Replies
View Related
Dec 26, 2012
The users are getting an error when connecting to the DB. I'm also not able to connect thru sqlplus . I get the message
$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 26 14:39:55 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
ERROR:
ORA-01075: you are currently logged on
ORA-04031: unable to allocate ORA-04031: unable to allocate 4064 bytes o bytes of shared memory ("","","","")
ORA-04031: unable to allocate ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select a.obj# OBJOID, a.clas...","sga heap(1,0)","kglsim hea
I need to restart the DB.
View 5 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Apr 23, 2010
I want to know what will be the performance of my DB before loading data and how many users can connect with my database.
View 7 Replies
View Related
Jun 18, 2012
we are refreshing data base for our application from the base load,Every time when ever there is refresh required we need to drop the users and recreate every thing from the base load. For this we need to kill the sessions and drop the users ,Putting the instance in the restricted mode and refreshing the db.Some time when ever During the killing and dropping process there are some errors like you can not drop the users which is currently connected .
set termout on
set echo on
spool Kill_sessions_drop_users.log
DECLARE
[code]...
how to get rid of this
View 6 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
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
