Performance Tuning :: Slow Join Between Dba_tab_cols And Dba_types

Nov 14, 2012

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.

View 1 Replies


ADVERTISEMENT

Performance Tuning :: Delete Is Very Slow

Apr 26, 2013

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.

View 13 Replies View Related

Performance Tuning :: (Fast Refresh) MV Too Slow?

Sep 23, 2010

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 ?

View 2 Replies View Related

Performance Tuning :: Retrieving From Data Is Slow

Jan 12, 2011

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.

View 6 Replies View Related

Performance Tuning :: Application Slow In EE But Fast In XE

May 22, 2012

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.

View 1 Replies View Related

Performance Tuning :: DML Slow When Table Have Many Rows

Sep 4, 2011

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?

View 6 Replies View Related

Performance Tuning :: Database Is Slow On Insert

Mar 23, 2012

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,

View 1 Replies View Related

Performance Tuning :: DB Import Too Slow After Server Reboot

Jul 4, 2011

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

View 11 Replies View Related

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

View 13 Replies View Related

Performance Tuning :: HIBERNATE Query Runs Slow But Ok In SQLPLUS

Sep 14, 2012

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]....

View 7 Replies View Related

Performance Tuning :: Join With 30 Tables

Jan 16, 2012

I have to do the optimization of a query that has the following characteristics:

- Takes 3 hours to process
- Performs the inner join with 30 tables
- Produces an output of 280 million records with 450 fields

First of all it is not feasible to make 30 updates (one for each table) to 280 million records.

The best solution that I had found so far was to create 3 temporary tables, where each of them to do the join with 1/3 of the 30 tables, and in the end I make the join between the main table and these three tables temporary.

I know that you will ask (or maybe not) to the query and samples, but it is impossible to create 30 examples.

how to optimize this type of querys that perform the join with multiple tables and produce a large output with (too) many columns.

View 15 Replies View Related

Performance Tuning :: When To Use Sub-query And When To Use Join

Dec 14, 2010

In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.

So when should I use sub-query and when should I go for join?

View 9 Replies View Related

Performance Tuning :: Join Condition In Index?

Mar 14, 2012

For a hash join statement, is it beneficial to have the join condition objects in the index as well as the objects in the where clause?

View 19 Replies View Related

Performance Tuning :: Force Optimizer To Consider All Join Permutations?

Oct 14, 2013

I'm looking to see if there is a way (fully expecting it to be an underscore, or two...) to force the optimizer to keep churning until all permutations are exhausted.I'm aware that it, to paraphrase, cuts out when it's spent more time parsing than it would just running it based on it's estimates.

I've got some irritating problems with xml rewrite, xml indexes and access paths/cardinalities etc and I'm really needing the entire thing considered as a one off for debugging this. I've already cranked up the maximum permutations to the max but it's not enough, it shorts out after 5041 permutations (I'd set that to 80000 max).

I know you'd not want to do this in the real world but I cant get the damned thing to run the plan I want in a 10053 so I can see the values it has there. I know I can hint it, but I'm trying to ascertain why it's not even considering it in a "normal" parse.

View 6 Replies View Related

Performance Tuning :: Explain Plan - %CPU Seems To Be Worse For JOIN Near Top?

Oct 24, 2011

however I was able to identify a poorly performing query that seemed to be maxing out our CPU. I have been trying to understand the Explain Plan. The plan below is from our test system which has considerably less information in the tables than our PROD system.

I can see there are a bunch of table scans at the end which may indicate missing indexes, but I am unclear on whether this is actually a problem as the %CPU seems to be worse for the JOIN near the top of the plan.

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1870M| 3018G| | 677M (1)|999:59:59 | | |
| 1 | SORT ORDER BY | | 1870M| 3018G| 3567G| 677M (1)|999:59:59 | | |

[code]...

View 4 Replies View Related

Performance Tuning :: Select Distinct From Cartesian Join

Sep 12, 2011

Having production system: 11.2.0.1 on Windows Server x64
Test system: 9.2.0.1 on Windows XP

Problem preface: to get all unique CASEID which should be checked up by biometric system.What i should check - all CASEs for different PERSONs having same PHONEs at least among one phone type (1..4).Real table contains little bit more than 10 million records.I made test scripts.

Below the DDL for test table creation:
------------------------------------------
-- Create CASEINFO test table
------------------------------------------
DROP TABLE CASEINFO;
CREATE TABLE CASEINFO

[code]...

Below i've put SQL/DLL to make test data.number of records inserted 2 millions.
PERSON_COUNT := #/8;
------------------------------------------
-- fill CASEINFO with sample data
------------------------------------------
DECLARE
I INTEGER;

[code]...

Below SQL select to check the data in created table.
------------------------------------------
-- Check test data counters
------------------------------------------
SELECT 'TOTAL',count(*) from CASEINFO
UNION ALL
SELECT 'LEGAL',count(*) from CASEINFO where

[code]...

The PROBLEM is that i am experiencing HUGE perfomance problems on both test and production systems with that query:

select distinct b.caseid
from CASEINFO a, CASEINFO b
where (a.person<>b.person) and (a.sex=b.sex) and
(
(a.phone1=b.phone1) or
(a.phone1=b.phone2) or
(a.phone1=b.phone3) or

[code]...

This query takes almost 90 minutes to execute.And i do not know how to avoid this.Full SQL file to make test attached.

View 13 Replies View Related

Performance Tuning :: Full Tablescan Even Though Join Is On Indexed Field

Oct 18, 2010

I am posting the below query:

SELECT PEA.INCURRED_BY_PERSON_ID AS PERSON_ID,
PEA.EXPENDITURE_ENDING_DATE AS WEEK_END_DATE,
CASE

[Code].....

The explain is below:

SELECT STATEMENT ALL_ROWSCost: 48,287 Bytes: 18,428,818 Cardinality: 297,239
3 HASH JOIN Cost: 48,287 Bytes: 18,428,818 Cardinality: 297,239
1 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURES_ALL Cost: 2,964 Bytes: 3,506,094 Cardinality: 194,783
2 TABLE ACCESS FULL TABLE PA.PA_EXPENDITURE_ITEMS_ALL Cost: 43,425 Bytes: 26,637,468 Cardinality: 605,397

View 9 Replies View Related

Performance Tuning :: NESTED LOOPS JOIN And Distributed Operations?

Oct 30, 2012

I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).

What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?

I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).

SELECT A.POLICY ,
F.MIN_MEMBER_ID,
MIN(A.EFF_DATE) EFF_DATE,
A.EXP_DATE ,
G.DESCRIPTION PROGRAM_NAME,

[code]...

View 5 Replies View Related

Performance Tuning :: Cost Calculation For Nested Loop Join

Mar 27, 2012

Following is the query on TPC-H schema.

explain plan for select
count(*)
from
orders,
lineitem
where
o_orderkey= l_orderkey.

The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: LINEITEM Alias: LINEITEM
#Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
Column (#1): L_ORDERKEY(NUMBER)
AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
[code]....

how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.

View 7 Replies View Related

Performance Tuning :: Where Filter Result Rows Save Before Join And Group By Operation

Jul 7, 2012

Where filter middle_rows save before join and grop by operation?

It is save rows in PGA Private SQL Area or save blocks in SGA databuffer?

View 11 Replies View Related

Performance Tuning :: How Oracle Optimizer Choose Joins (hash / Merge And Nested Loop Join)

Oct 18, 2012

I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.

View 2 Replies View Related

Slow Performance On New 11gr2 Linux DB?

Aug 27, 2013

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 .

View 3 Replies View Related

Performance Very Slow Inserting Data Into Table

Jun 25, 2013

I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query

INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;

Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.

View 4 Replies View Related

Performance Tuning :: Tools For Database Tuning And Instance Tuning

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

DB Performance Is Slow Using Oracle 11gR2 On Windows Server

May 25, 2013

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')

View 1 Replies View Related

Querying A Table With 129 Million Records - Performance Is Slow?

Oct 9, 2013

I have table with 129 million records.

If I just to select count(*) on the table its taking more than a minute in Sql Developer.

The table structure is as below, Primary key is a sequence and then 3 foriegn keys and one non-unique index on the date column.

<Table_Name>
column1 NOT NULL NUMBER ( Primary Key)
column2 NOT NULL NUMBER ( FK1)

[Code].....

View 1 Replies View Related

Forms :: Slow Performance Using OLE2 Reading Xls File

Oct 10, 2011

I just trying to import some informations from excel to Oracle using OLE2 over Oracle Forms 6i, but It´s very slow when I have import under then 10k lines. anything to optimize that ? Follow the code used...

application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: Unable To Query Dba_tab_cols / Insufficient Privileges?

Oct 29, 2010

I have installed oracle 10g express edition, and created 2 users

1. DBA
2. HR -- is the normal application developement user.

When i issue the following query from DBA user, it works.

select * from dba_tab_cols; -- works fine for DBA user
select * from dba_tab_cols; -- doesnot work for HR user - invalid table name

When i try to issue the previage from DBA user like,

grant select on DBA_TAB_COLS to hr; -- DBA user says like, insufficient privileges.

View 6 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

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

Performance Tuning :: How Length Of Column Width Effects Index Performance

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







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