Performance Tuning :: Options For Querying On The Skewed Data

Feb 10, 2012

We have a table with huge data which is skewed on a 'status' column. The 'status' column has 6 distinct values with 1 particular value occupying 80-85% records.

In the batch process we query the data on the status and process the retrieved records. My senior is insisting on partitioning which I see not much feasible considering cost implications just for a part of functionality

See there are 6 status 'A','B','C','D','E','F'

with 'A' occupying 80% records
'B' to 'F' occupies 2% till 14% records in the table(approx)

1) Create a conditional index on status (using case) to have records with all statuses except 'A' Then create If-ELSE structure

IF input parameter is 'A'
select /*+ FULL Parallel(t) */ * from t where status='A';
ELSE
Select /*+ INDEX (t conditional_index) */ * from t where status in ('B','C');
END IF;

I want to create conditional index here for 2 reasons

1] since it will have values for status except 'A' this nullify the chance that this index will be picked up when status='A' will be queried
Thus making the performance worst (status ='A' is for 80% records) - The IF-ELSE is additional protection
2] Less impact on the DMLS as the index will not be on status='A' which contribute to large chunk of records

2)Populate a dummy table which would contain rowid and status. Since the business closes at 21:00 and batch process starts at 21:30
Between these times periods refresh the dummy table every day using merge (to catch business transactions during the day)

Now during the batch process retrieve records from the main table using the rowids in the dummy table depending on the input status value

3)Create index on status
Make sure hard coded status values are used in the database procedures
Gather stats with the histograms
And leave it to the Optimizer to choose the best possible path

View 3 Replies


ADVERTISEMENT

Performance Tuning :: Options While Using RBO?

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

Performance Tuning :: Options For Optimizing SQL Running Against Wide Tables

Nov 13, 2012

The scale of the tests that generate the following scenario is not huge right now, only 50 users simulated (or you can think of them as independently running threads if you like). But here is the crunch, the queries generated (from generic transaction layer) are all running against a table that has 600 columns! We can't really control this right now, but this is causing masses amounts of IO (5GB per request) making requests queue for disk availability (which are setup RAID 0/1); its even noticable for as few as 3 threads.

I have rendered the SQL on one occasion to execute in 13 seconds for a single user but this appears short lived as when stats were freshly gathered it went up to the normal 90-120 seconds. I've added the original query to the file, however the findings here along with our DBA (who I trust implicitly) suggest that no amount of editing the query will improve the response times, increasing the PGA/SGA (currently 4/6GB respectively) will only delay the queuing for a bit and compression can work either. In short it looks as though we've hit hardware restrictions already for this particular scenario.

As I can't really explain how my rendered query no longer takes 13 seconds, it's niggling me that we might be missing a trick.So I was hoping for some guidance on possible ways of optimising these type of queries against such wide tables, in other words possibilities that we haven't considered...

Attached is the query and plan.

View 9 Replies View Related

Performance Tuning :: Querying GTT In Parallel Mode

Apr 24, 2012

I am inserting data into a global temporary table and then using 'parallel' hint to query from this temporary table. I remember reading that the queries on the temp table may not run in parallel as the parallel sessions may not be able to see the data in the temporary table

However the execution plan as well as px_session, v$sql indicate that the query on the temporary table in fact run in parallel mode

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 7d68g52g0mskz, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(t,4) */ * from dbo_gtt t order by id,object_id

Plan hash value: 5815349
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------
| 1 | PX COORDINATOR | | 1 | | 99999 |00:00:01.46 | 3 | | | |
|
| 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 11M| 1311K| 424K (0)|
|
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 5 | PX SEND RANGE | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 6 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
|* 7 | TABLE ACCESS FULL| DBO_GTT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)

select px_servers_executions from v$sql where sql_text like 'select%dbo_gtt t%';

PX_SERVERS_EXECUTIONS
---------------------
0
4

select sid, qcsid, server#, degree from v$px_session where qcsid = 228;

SID QCSID SERVER# DEGREE
---------- ---------- ---------- ----------
247 228 1 4
196 228 2 4
224 228 3 4
234 228 4 4
226 228 1 4
252 228 2 4
212 228 3 4
229 228 4 4
228 228

9 rows selected.

unfortunately I do not have access to get trace (tkprof) report. What must have happened during the execution?

View 3 Replies View Related

Performance Tuning :: Alternate Query Instead Of Querying Table Twice?

Nov 11, 2012

The Item data for individual cycles is as below.

Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A

The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.

In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.

Below is the code which I have written which considers the rundate as Input parameter.

-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate

[code]....

In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.

Is there any way to query above only once and get the required results using Lag/Lead functions etc.

View 5 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

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

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

DB Used : Oracle 10g.

A table X : NUM, INST are column names

NUM ----- INST

1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1

-- Based upon requirement :

1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,

Eg:

For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298

3) Save these values into

A table Y : INST, NUM are column names.

INST NUM
23 1234,1298

1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.

2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..

View 4 Replies View Related

Performance Tuning :: Get AWR Report Data?

Aug 8, 2012

I am having only select_catalog_role in database. Can I take complete AWR report data from awr views without using DBMS_WORKLOAD_REPOSITORY package?

View 6 Replies View Related

Performance Tuning :: Data Caching

Feb 7, 2011

sometimes when I re-run a query a few times, the speed after the first run become much faster. this is a problem for me when I'm trying to optimize a query. is there some sort of cache? can it be disabled?

View 7 Replies View Related

Performance Tuning :: Inserting Data Using FOR ALL

Mar 6, 2011

CREATE OR REPLACE procedure fast_proc (p_rows out number)
is
TYPE object_id_tab IS TABLE OF all_objects.object_name%TYPE INDEX BY BINARY_INTEGER
lt_object_id object_id_tab;
CURSOR c IS

[Code]....

Warning: Procedure created with compilation errors.

Errors for PROCEDURE FAST_PROC:

LINE/COL ERROR
-------- ---------------------------------------------------------
13/7 PL/SQL: SQL Statement ignored
13/22 PL/SQL: ORA-03001: unimplemented feature

I am not able to do INSERT but I am able to do UPDATE/DELETE? What is this inbuilt functionality?

View 3 Replies View Related

Performance Tuning :: Big Table Data Maintenance

Jun 17, 2011

We have few tables in our production database which are havoc in size and will increase in size in future too so as part of the corrective measures , we have jotted down the below 3 methods to manage the size of those tables :-

1> Partitioning the table and take the export of identified partitions and after that, truncate those partition.
2> Creating history tables and remove not so current data from the original table to history table.

View 3 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 :: Oracle Data Migration

Jun 7, 2011

We have a data migration scripts written for oracle. Data is not huge but we are observing that the migration is faster in the development labs but is 5x slower in the production site.

The development Oracle setup is on Windows and Production setup on Solaris. I have attached the AWR generated for a period where migration was run for 3 hours and stopped due to slow performance.

Here is my initial analysis.

1) The first timed events is the DB CPU. Hence I feel the migration scripts can be modified to run in parallel so that they can finish faster. However here the question arises why it should run faster in development env if this is an issue.
2) I tried increasing the
a.large_pool_size set to 512M
b.sga_max_size set to 8G
c.sga_target set to 8G
from 0, 4G and 4G respectively.

I have attached the AWR and below are the etc/system contents for solaris settings.

* Begin MDD root info (do not edit)
rootdev:/pseudo/md@0:0,1,blk
* End MDD root info (do not edit)
set noexec_user_stack=1
set noexec_user_stack_log=1
* IBMdpo vpath_START (do not remove)
* default SCSI timeout is 60 seconds
* uncomment to change SCSI timeout * set sd:sd_io_time=0x1e
forceload: drv/vpathdd
* IBMdpo vpath_END (do not remove)

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10

P.S. The awr report is renamed to .txt from .html to be able to upload the file.

View 6 Replies View Related

Performance Tuning :: Table Rebuild Should Be Done After Removing Most Of Its Data?

Oct 23, 2012

We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.

However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?

I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?

View 4 Replies View Related

Performance Tuning :: Which Index Will Be Better For Fast Retrieval Of Data

Nov 12, 2010

I create a view on production server which takes almost 10 to 12 minutes when it shows data. this view contains 3 or 4 tables on which all primary and unique columns have indexes.which index will be better for fast retrieval of data .

View 5 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 :: 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

Performance Tuning :: Insert Data Fast On Table

Oct 15, 2013

I am inserting data using a procedure for 2012 and 2013 year which is using partitioned tables includes crore of data in a partition taking lot of time or taking months. Is there any other way by which I can insert data fast from our query.

View 14 Replies View Related

Performance Tuning :: How To Refresh Materialized View Data

Jun 12, 2013

I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.

here is the materialized view i created;

CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID

View 2 Replies View Related

Performance Tuning :: Data Type Conversion Impact?

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

Performance Tuning :: Used Same Dmp File To Restore Data And Settings

Oct 15, 2013

I am getting back into Oracle (from a long haul in MS only env.) and am now testing Oracle installs.I have been given a task of seeing the diff. between 12c and 10.2g...I set up 2 vms (excatly same configs) and used the same dmp file (on both env.) to restore data and settings for our jobs to run.We have some aggregated data, and cubes with DIM tables each being run on the vm machines. We run nightly jobs to rebuild our cubes.

I am supposed to see/analyze the value of 12c, and understand things might vary from company to company, but am perplexed at my result.12c is half the speed of 10.2g, both env. are the same out of the box with same dmp file and same hardware.

I am using the same dmp file, with the same jobs on each machine, with both vms having 10.2g or 12c installed out of the box as is.what default oracle settings might have changed from 10.2g to 12c that could make the exact same env. run twice as slow on the 12c?

Expectations were that out of the box with both machines running same jobs on same data (from dmp files) would have it that 10.2g would be slower than the 12c, except the 12c takes 2 times as long to run the jobs. I have reviewed every possibility as I know usually the problem is the person sitting in the chair and not the pc...but I confirmed all was identical from the one vm env. to the other, except the version of oracle out of the box.

What could be done to bring that default setting back to atleast equal time between the 2, that would give me a great starting point. Otherwise, I would have to toss this up to bloatware.

I read up a bit on the CBO, and know this might have changed in 12c.is there a way to bring it back to a backwards ealier config, so as to atleast match both env. execution plans?

View 19 Replies View Related

Performance Tuning :: Data Dictionary Cache - Dc_objects

Jul 4, 2011

In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test environment which is quite quick in spite of the fact that we port the data from Live every month.

There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.

I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.

1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.

The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.

View 27 Replies View Related

Performance Tuning :: Database Links - Display Consolidated Data?

Oct 14, 2013

A website requires to display consolidated data from databases located in different geographical regions (India, London and New York). The application server for the website is hosted only in one location India. What are the techniques that can be used for faster retrieval of data from all 3 databases?

Note: There is no need of real time data retrieval from different regions; however the user should able to view the updated data at predefined intervals.

View 9 Replies View Related

Performance Tuning :: How To Increase Data Retrieval / Insertion Speed

Oct 24, 2013

How To Increase Data Retrieval / Insertion Speed my data base has more than 0.5 million records Forms Some Time Respond Very Slow .

View 8 Replies View Related

Performance Tuning :: Best Disk Config For SME Scale Data Warehouse

May 15, 2013

We are working on a Data warehouse (ard 50G ) architecture with the following acquired environment:

Single server X3650 M4 Dual CPU ( 16 core in total ) with 48G ram
Oracle standard 10g x64
Windows 2008 x64
128 SSD x 8
IBM ServeRAID M5110e SAS/SATA Controller

Due to budget concern, we will be running the App server(Business OBjects 4.0 w/ Tomcat and DB server on the same machine. ) We have a user base of around 30 ppl on the app server.

We intend to have external redundancy using IBM raid card on raid 10 configuration. I wonder what kind of disk config yield better performance if we only have write update in the morning and 95% read for the rest ?

Raid 1 for OS (128SSD x 2 including DB logfile )
Raid 10 for DB server ( 128 SSD x 6 )

I heard ASM provides better disk management but just wonder it increase performance in anyway.

View 2 Replies View Related

Performance Tuning :: How To Track Delay In Displaying Data On Application GUI

Dec 21, 2011

What could be the reasons that some queries execute fast when executed on sqlplus on server, whereas the same queries run slower with same input values fed from application screen?

One issue I guess would be bind variable peeking while using application whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"

If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?

I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI

finally how to set arraysize in jdbc to improve performance by reducing roundtrips?

View 3 Replies View Related

Performance Tuning :: Query Takes More Than 30 Minutes To Return Data

Aug 10, 2012

The below query takes more than 30 minutes to return data.All the objects used are views. There is no direct reference to any table.The views with _mnth_ have data for 7 distinct months. The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID

I need the order by, as the query is part of informatica code, and the order by works in the further processing.

SELECT ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt
--removed the rest of column list to reduce size of code.
FROM edxf.ar_rsrv_mnth_v ac,
edxf.crdt_acct_mnth_v c,
edxf.crdt_acct_v ca,
(SELECT msrmnt_prd_id, msrmnt_prd_dt
FROM edxf.msrmnt_prd_v
WHERE msrmnt_prd_id =
[code]....

Also, the count of data in the views is as below.

ViewTotal countCount for 1 msrmnt_prd_id
---------------------------------------------------------
ar_rsrv_mnth_v1841892281945
crdt_acct_mnth_v664941457087369
crdt_acct_v12258728NA

View 7 Replies View Related

Performance Tuning :: Does High Volume Of Data Can Change Plan

Jun 4, 2010

The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.

My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.

View 6 Replies View Related

Performance Tuning :: Data Type Of Key_Desc And Key1 Is CHAR

Sep 7, 2011

I am using ORacle 10g R2.

SELECT key_value
FROM PARAMETERS
WHERE company_id = '01'
AND key_code = 'SHRINK'
AND key_desc <= 3888
AND key1 > 3888

Data type of Key_Desc and Key1 is CHAR.

The above query used to run in 9.2.0.5 and now in 10.2.0.4 i am getting invalid identified error..

View 16 Replies View Related







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