Performance Tuning :: Oracle (HP-UX) Connection Very Slow From Windows Client?
Jun 18, 2012
I have an Oracle database (9.2.0.7) installed on a HP-UX server.When trying to access this database from another HP-UX or Linux server, connection is fine. But when trying to connect from a Windows based client, connection is very slow (almost 1 minute to return the result of a 'select count(*)' like query, which is immediate from the Linux client).
Here are some facts I can add :
- Clients and servers are on the same network segment (it is not a network matter)
- No matter which client version I use, there no difference
- I tried to know what happens on the Oracle server when performing my sample query using tusc command : the result is that the server is performing exactly the same actions when sending my query from a Linux client or a Windows client
- The only relevant difference seems to be the client OS
I am using 11gR2 on windows server. This is the query that runs many times a day and effect badly the performance of database. I don't have much idea about this query.
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count FROM sys.dba_audit_session WHERE returncode != 0 AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >= TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'), 'YYYY-MM-DD HH24:MI:SS')
I am running one simple delete statement in one table with rownum<10000 but it is taking nearly 10 to 15 mins.Table doesn't have any child table rows and triggers.
We have a MV which fetches data from around 27 tables containing 26 joins out of which 25 are outer joins. Some tables in the query are being referred multiple times through different alias names and hence the actual no of physical tables used is 18. This MV takes about 50 mins to refresh through complete refresh mechanism. We decided to make it fast refresh and thus made these configurations:
- Created MV logs based on rowid for each of the base tables. - Recreated MV using FAST refresh,with primary key option enabled - Pulled rowid for all these tables in the select column statement.
Even after making all the recommendations suggested by Oracle for fast refresh MV's we are still getting refresh time of around 65 mins(refresh time increased!!!).We already have indexes built on all the join columns of the base tables. What else do we need to do to make this a "fast" refresh MV ?
I'm extracting/retrieving the data from the oracle database using Java application it's bit slow. However, when I retrieve from the SQL server it's faster than oracle.
My ERP Application is responding fast while running reports or saving entries, if Oracle 10g Express Edition (XE) is installed. But in Oracle 10g Enterprise Edition or Standard Editions the same application is running very slow.
If a table(have a primary key) is empty(after truncate),the sql of dml(insert,update) is very quickly,but if the table have many rows about 10,000,000 rows, the dml is very slowly,why?
we are using oracle 9i on AIX Server. When Customer were accessing the database, accidentally power was shut down. we restarted the Server,and Oracle database. all resumed successfully.
However while doing "Payments by the customer" it takes a lot of time to insert even a single payment record on database.The database is Live and our customer are very much frustrated,
The product I work on requires a query to tell us what tables are dependent on certain types.
SELECT dba_tab_cols.owner, dba_tab_cols.table_name, dba_tab_cols.data_type_owner, dba_tab_cols.data_type FROM dba_tab_cols JOIN dba_types ON dba_types.owner = dba_tab_cols.data_type_owner AND dba_types.type_name = dba_tab_cols.data_type WHERE (dba_types.owner IN ('SCHEMA1', 'SCHEMA2'......))
I find this query to be pretty slow. I think it is because data_type_owner in dba_tab_cols is not indexed. Adding an index is not an option because users expect our product to read-only.
Few days ago, My database server no access to StorageBox then I reboot it then after works fine. But, know DB import process is too slow. Before 100GB DB import process completed within 10 hours when server normal running. Now 2 day working, but not complete
How to investigate this issue? Maybe I miss increase some parameters on the Server or Oracle?
Here is my server brief info:
RAM is 16GB, SWAP size is 16GB, CPU 12 cores
SQL> show sga;
Total System Global Area 4294967296 bytes Fixed Size 1984144 bytes Variable Size 369105264 bytes Database Buffers 3909091328 bytes Redo Buffers 14786560 bytes
I have a query which takes 5 minutes when run through the java app which uses hibernate. I've cut and pasted the SQL directly from hiberate trace file and run it in sqlplus/sqldeveloper and it runs instantly (0.01 seconds)(uses the index all ok and explain plan looks good - see below.) I don't know how to get the explain plan when it's running through the app or why it should be any different anyway as the query is identical.
My query is as follows:
SELECT /*+ INDEX (SPD SPD_SEQ_CODE) */ SPD.* FROM SEQ_ADDR_DATA SPD, SEQ_ADDR_LEVELS SPL WHERE SPD.SPVR_ID = '10' AND SPL.SPLE_ID = SPD.SPLE_ID AND SPL.SPLE_LEVEL <= '2' AND SPDA_ID NOT IN [code]....
I Develop Application with C# . I want to connect from Client to Server.
my connection string is ""Data Source=192.168.100.50:1521/XE;User Id=system;Password=system;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;""
When exectue this Connection on Server ، all things OK but when execute on Client this error accure :
"Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. The type initializer for 'OracelExperss' threw an exception"
TOAD 8.6.1 has an option of showing windows by connection. It is useful when there are multiple connections and, choosing one of these, all windows that belong to other connections are minimized.
Its icon looks like this:
Does TOAD 9.1 have the same option? I'm unable to locate it in options, toolbar customizations or elsewhere.
TOAD 9.5 Release Notes document says that "'Connection list' dropdown button has been restored and can be added to the Toad toolbar.". If I understand it correctly, it says that previous version didn't have that dropdown button, but 9.5 does.
In another words ... 9.1 might not have it at all.
We have two database instances on the same server. One was left at 9.2.0.7 and one was upgraded to 10.2.0.3. Connecting externally (sqlplus '/as sysdba') to the 9.2.0.7 database is lightning fast. Connecting externally to the 10.2.0.3 database is very slow, comparatively speakiing. This is on an IBM AIX-5L (64-bit) machine. We are using "tnsnames".
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim oradb As String = "Data Source=CallTaxi; UserName = SCOTT; Password=TIGER ;" Dim conn As New OracleConnection(oradb) conn.Open() Dim cmd As New OracleCommand
[code]...
Error msg: The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception.
I have been using oracle based database security but company now wants to handle with windows authentication.I have windows os 2008 R2 and oracle 11.2.0.3.
I also have set up the SQLNET.AUTHENTICATION_SERVICES= (NTS).I created user with create user "domainusername" identified Externally! but now how can i connection from application to database!
We have a .NET application on windows server 2008 32bit using the stored procedures on an Oracle 10g environment. We are trying to deploy the .NET application onto a 64bit x86 Windows server 2008.
We face an error trying to install the x64 version of the Oracle 10g client on windows 2008. The error we get is Problem signature:
Problem Event Name: APPCRASH Application Name: javaw.exe
Is the x64 version of the Oracle 10g client certified for Windows 2008? Has anyone successfully installed the 64bit version of the oracle client on win2008?
We have a workaround in place with 32 bit version of the client running with the 32 bit version of the .NET application on x64 win2008. However to maximize the infrastructure we need to use the 64 bit version or we would have to turn to MSSQL Server .
I'm trying to demonstrate the working of the OCI client result cache. I've set some parameters,orcl> sho parameter result_cache
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 1000000 result_cache_max_result integer 5 result_cache_max_size big integer 1984K result_cache_mode string FORCE result_cache_remote_expiration integer 0
If I understand the docs correctly, that should be all that is needed.I've complied and run the cdemoqc and cdemoqc2 OCI demos, but I never get anything in the V$CLIENT_RESULT_CACHE_STATS or CLIENT_RESULT_CACHE_STATS$ views. This is probably because the sessions exit after running the queries.
So I've also tried repeating arbitrary queries through cdemo2, which gives a persistent session, but there is still nothing in those views and furthermore the v$result_cache_objects.scan_count for my queries keeps increasing. So I don't think think the client side cache is working.
I am using dg4msql to connect to SQL server from Oracle(10.2.0.4, with AIX 5.3).
#from my laptop sqlplus (10.2.0.2)
SQL>select count(*) from table1@mssql; #works SQL>select * from table1@mssql; #ERROR
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Oracle][ODBC SQL Server Driver]String data, right truncation {01004}[Oracle][ODBC SQL Server Driver]String data, right truncation {01004}
#from my laptop, TOAD Both queries works fine.
i have compared
"select * from nls_session_parameters;" values , TOAD and sqlpus gives same result.
I have installed Oracle 11g R1 and have problem in connecting Oracle SQL Developer.
I have given user name: ora; pswd: ora.
In command prompt it works fine if I gave as below. uid: ora as sysdba pswd: ora
But when I am trying to connect to Oracle SQL Developer it says TNS listener does not know SID.
So I reinstalled this since i have doubt on myself whether I made mistake first time. Also I have doubt whether the un installation completely clears all the files from Registry. - What should I do to clear all the files from registry when un-installing.
1] What is SID. Is it session ID. If so how to find it. 2] Is there any way to confirm the values such HOST NAME: localhost, PORT: 1521, SID: (By default it has 'xe') which I gave for data base connection are correct.
I tried with SID value:135 (I got this value by executing [Select distinct sid from V$mystat;])
3] Is there any editor for practicing sql & pl sql queries. where I can download it.
Suppose my oracle database server IP is 128.1.1.100 in the office & the internet static IP is 115.118.33.100 is on that server. I want to retrieve the data by using oracle forms & report from my home's computer having different internet IP 115.118.33.25 on my home's computer.
what would be the settings on my home's computer for the same. i mean tnsname.ora file or any other setting.
I have a VB application which uses oo4o to connect to Oracle database (10g) using Oracle client v 8.1.7. All was working well till we migrated to windows 7. On windows 7 (64 bit), we have oracle client 11g (64 bit). but the Vb application cannot make connection with the database although i can query the database using SQL Plus.
I have been researching this issue and trying to install any Oracle 10 or 11 client on my new Windows 7 (64-bit) computer for two days. I read on the following link:
[URL].......
that such a client exists. where I can get it and whether any special steps are required to perform the install? I have tried the following:
Install 10gR2 and 11gR1(for x64) after downloading from Oracle. In each case, I ran the setup file from the zip file and tried again by unzipping and running the setup.exe as the administrator. In most cases, I got the error from the compatibility check that the Oracle install would not work with 6.1 (NT6.1, I think).
I tried one suggestion of modifying the refhost.xml file to list 6.1 as an acceptable OS, but then my install failed when oui.exe quit running.
we are busy updating one databasee from a windows platform 2003 oracle 10G to a linux and oracle 11r2
We exported/imported the data and it looks ok Explain plans look the same . but our heavy batches are twice slower than on the windows box ,the two top events are disk related, sequential and scattered reads there are 90% of the time of the batch job , i read some white paper and found that using ASM can be bad in some cases the same with the linux for this particular kind of scattered reads , i was just wondering if just changing the SGA to 10GB instead of 4GB to get more cache and speedup the things .
