Performance Tuning :: Select Partition - Oracle To Scan Blocks
Sep 15, 2011
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 a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
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;
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
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 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.
So our situation is pretty simple. We have 3 tables.
A, B and C
the model is A->>B->>C
Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> Bhave many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.
It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these) We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution has been to remove the leading wildcard.
We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that. We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
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 .
We have a person running a query and following is the explain plan
explain plan for
select distinct(extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')) as ThisBrand from hh.t_ecomm_mem_relations a where extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') not in (select b.code_brand from hh.t_pr_brand b) and a.code_corr_ecat = 'PREA' and a.status = 'S' and a.audit_time > sysdate - 1 ;
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 1904775187
I have a view, below, which does few left outer joins to the same V_MARKET view to get data i need. When I run SQL by itself, ut runs pretty fast, 2-5 seconds. But when I do "select * from V_DEPT_DATA where busines_date = '01-APR-10'", it takes more than 10 minutes to run. I added all needed indexes and still have problems with it .
CREATE OR REPLACE VIEW V_DEPT_DATA AS SELECT v1.business_date , v1.division , v1.department , v1.account , en.trader , [code]........
I'm working on a query that will show how many differents SKUs we have on-hand, how many of those SKUs have been cycle-counted, and how many we have yet to cycle-count.I've prepared a sample table and data:
What I also want to do is select another column that will group by sku.abc and count the total number of A, B, and C SKUs where the lot.qty is > 0:
SELECT sk.abc AS "STRATA", COUNT (DISTINCT sk.sku) AS "Total" FROM sku sk, (SELECT sku FROM lot WHERE qty > 0) item WHERE item.sku = sk.sku(+) GROUP BY sk.abc
Finally, I need the last column to display the DIFFERENCE between the two totals from the queries above (the difference between the "counted" and the "total"):
I've been examining som old queries in an existing db due to more and more problems regarding performance. The sql is used as backend for a java/jboss web application with the possibility for users to enter data. With more and more data, there starting to come complaints about the performance.
I stumbled upon a select query with an embedded cursor similar to this :
select id, name ..., cursor(select id, sequence.... from table2), cursor(select id, name.... from table3) from table1 join table4 on (table1.id = table4.id) where .....
The javacode is a prepared statement with the actual sql as a string and the content of the cursors saved in conjunction with each row.
when i use sqldeveloper to show the explain plan without the cursors, the cost is 2428 when i use sqldeveloper to show the explain plan with just 1 of the cursors, the cost is ~165000
Is there a better way to do this instead of cursors ?
high number of executions of specific types of queries which is using only rownum clause. For exam.
select ani, rowid from tbl_smschat_upuor where rownum<=:"SYS_B_0";
DB is having high number of executions of these type of queries and these when I m checking the execution plan for the same type of queries it is accessing the full table scan.
======================execution plan for above query 1000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 91289622 --------------------------------------------------------------------------------
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.
I am rebuilding some UNUSABLE local index partitions on Oracle 8.1.7.4.0 (64bit) database . The platform is a HPUX machine.
The DDL of the partition table/indexes: ========================= CREATE TABLE TESTME ( INST_NO CHAR(3) NOT NULL, ACCT_NO CHAR(16) NOT NULL, REC_NO CHAR(9) NOT NULL, TRAN_TYPE CHAR(2) DEFAULT ' ', STAT CHAR(2) DEFAULT ' ', [code]...
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Solaris: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
I have a problem with views and nested selects which I cannot explain. Here is a trimed down version of the research I have done. notice the following:
1) all code is executed from the same user CDRNORMALCODE. this user has all views and procedural code 2) all data is owned by a different user CDRDATA. This user has no views and no code.
My problem is this:
If I reference the table directly with a delete statement that uses a nested select (i.e. IN clause with select), the index I expect and want is used.But if I execute the same delete but reference even the most simple of views (select * from <table>) instead of the table itself, then a full table scan is done of the table.
Here is an execute against the table directly (owned by cdrdata). Notice the reference to the table in the table schema on line 3. Also please notice INDEX RANGE SCAN BSNSS_CLSS_CASE_RULE_FK1 at the bottom of the plan.
SQL> show user USER is "CDRNORMALCODE" SQL> SQL> explain plan for 2 delete
[code]...
OK, here is an update. The views I am useing normally have instead of triggers on them. If I remove the instead of trigger the problem looks like it goes away, when I put the trigger back the problem comes back.But why would an instead-of-trigger change the query plan for a view?
SQL> DELETE FROM PLAN_TABLE;
5 rows deleted.
SQL> explain plan for 2 delete 3 from BSNSS_CLSS_MNR_CASE_RULE_SV
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION FROM AIMUSER.SEGMENT_JOBS WHERE SEGMENT_JOB_ID NOT IN (SELECT SEGMENT_JOB_ID FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ; [code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
select serialnumber from product where productid in (select /*+ full parallel(producttask 16) */productid from producttask where startedtimestamp > to_date('2013-07-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and startedtimestamp < to_date('2013-07-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and producttasktypeid in
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300 SQL> col TABLESPACE_NAME for a25 SQL> col FILE_NAME for a65 SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB 2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB ------------------------- ---------- ----------------------------------------------------------------- --- ---------- SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M; Alter database datafile 3 RESIZE 60000M * ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
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?
