Performance Tuning :: How To Keep RANGE SCAN Even After Adding New Conditions
Aug 29, 2011
I am adding condition in color to my existing query but my query taking too long time after that. This condition calculates last 3 month end dates from given COB (20110516).
SUMMARY is my summarized table and COB is my reference table
Without adding condition in query it gives
INDEX (RANGE SCAN)--PK_SUMMARY(INDEX(UNIQUE))
After adding condition in query it gives
INDEX(FAST FULL SCAN)--PK_SUMMARY(INDEX(UNIQUE))
how can i keep RANGE SCAN even after adding my new conditions?
SELECT
distinct
COB.COB_ID_C,
SUMMARY.SUMM_COB_ID_C
FROM
I want to make full table scan of my query so that I can execute it using parallel option & the query will get completed in few mins. Even though I used this hint "/*+ parallel(t2,4) full(t2) */, the query is executing with 8 parallel processes but it is going for "Rowid Range Scan" and it is taking more time to complete. How to make this query to go for full table scan instead of "Rowid Range Scan" so that this query can be tuned.
SELECT /*+ parallel(t2,4) full(t2)*/ID, COUNT (1) FROM acnt_transact t2 GROUP BY ID;
In the link below [URL] Thomas kyte has said, use the CBO and select /*+ FIRST_ROWS */ primary_key from table where rownum = 1; it'll read the index and stop at the first row. very fast on a big empty table (as the index is small and empty).
very fast on a big full table as the index is just read to find the first leaf node and then "stop".
It gives faster result if the primary key is used. But what if we have a table with around 1000 million rows and for the predicates there is a index range scan on the table.
What if we have a table say big_table (10000000000 rows) and the sql is something like
select /*+ first_rows */ 1 /* id, attribute_id*/ from big_table where attribute_name ='Gross Premium' and value ='10000' and version_date is null and rownum=1; --it's taking around 3 min
We observed that in such case there will be a range index scan for the index on the predicates. For a particular id there may be different values for attribute 'Gross Premium' and may have multiple versions.
How I would tune such a query where the purpose is to check if at least 1 records exists in the table for the input?
Below query is degrading the performance of database. As we know that, without where clause, query do full table scan.Now, it is written to generate the sequence no.
SQL> explain plan for 2 SELECT NVL(MAX(P.NUM_SERIAL_NO), 0) + 1 FROM CNFGTR_IRDA_ENVELOPE_DTLS P 3 / Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3345343365 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------
I was confused by partitioed table, when i select a partition of table, how does oracle to scan blocks? it scan all blocks of table or scan a single partition blocks only?
SQL> Explain Plan For 2 Select Count(1) From Tb_Hxl_List Partition(p_L3);
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
I am trying to run an SQL query which refers/joins around 10 tables.In my case I would want to add an if-else or case condition in the WHERE clause of select query
For example
select a.column1, b.column1, c.column1, d.column1 from a, b, c, d WHERE a.column2 = b.column2 AND (if a.column3 = 10 then ( I want 2 conditions to be AND b.column2 = c.column2) added else after WHERE clause as AND...) AND b.column2 = d.column2 ) AND d.column2 = a.column2
CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN ......... ...... END
In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.
Irrespective of using AND or OR after the first condition I am getting the same results.
I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied
here we have an scenario where we want to find out all the sql statements that are executed in a particular time. The sql statements are executed via our application. I tried in awr report but it shows only the sql query which has taken long time to execute. and i even tried in V$session and V$sqlarea. how to view the executed sql statements in a particular session/current session
I created table Rang pertitionned, and List subpartitionned. My table is Interval partitionning. My subpartition is template based as is :
PARTITION BY RANGE ( DINFOIDENTITE ) INTERVAL ( (NUMTOYMINTERVAL(1,'MONTH')) ) SUBPARTITION BY LIST ( AVANT_DERNIER_MATCLE )
[Code]....
i would have the same repartition with 10 differents tablespaces, one for each subpartition.
I search on Oracle documentation this morning, but the only thing i've found is that this option is possible with the STORE IN clause, but only for HASH partitionning.
Is there a way to specify STORAGE clause with a template for my indexes ?
As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .
I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.
I am using Oracle version 9.2.0.3.
select d.propertyid,d.totaldwellingarea, e.size_, scr.size_ from eqid e, dimensions d, brt_eval.size_code_ranges scr where e.style not in ('1','A','G','L') and e.size_ = '0' and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and scr.highvalue;
SELECT MAX(fndattdoc.LAST_UPDATE_DATE ) as LAST_UPDATE_DATE, MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL)) as COMMENTS, MAX(fnddoc.description) as REASON FROM fnd_attachment_functions fndattfn, fnd_doc_category_usages fndcatusg, fnd_documents_vl fnddoc, fnd_attached_documents fndattdoc, fnd_documents_short_text st, fnd_document_categories_tl fl, WSH_NEW_DELIVERIES DLVRY [code]....
I have three tables, I have to merge those three tables, all three tables having same conditions and filter conditions(in each table one filter condition changed), I highlighted in the red difference the filter conditions in each table, finally my result should be 7 columns like
We are using oracle 11.2.0.3.0 with 3 node rac. Earlier 3 scan vip and 3scan listener running on each node.But we found recently node1 running using 2vip and 2scanlistener and in node2 1vip and 1scanlisteners were running.but no longer running scan vip or scan listener in node 3. If i decided to reloacate the scan vip/Scan_listener to node3 from ndoe 1 using below command,does it cause any impact on my transcation?
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?
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530; Table created. SQL> create index i_test on test(id); Index created. SQL> SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
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,
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'.
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?
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 ?
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?
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?
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.
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.
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.
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'
