Performance Tuning :: Cursor Embedded In Select
Jul 21, 2010
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 ?
View 2 Replies
ADVERTISEMENT
Jun 26, 2012
I have the following tables: First one (PART) is the master and other 2 are children.I need to create query that would link both children to the parent. So I was thinking about using embedded refcursor (since my function returns a ref cursor).
on using this ? Is this ok or any other better way of doing this ?
TABLE PART
PART_ID, PART_NAME
TABLE ABC
ABC_ID,PART_ID
[code]...
View 6 Replies
View Related
Nov 5, 2013
we are using Release 11.2.0.3.0 of oracle. And have cursor_sharing default setup as 'exact'.
In case of 'sql queries' using bind variable, which suffers performnace issue(unstabilized plan) due to different bind values at runtime addition with skewed columns. In these cases 'adaptive cursor sharing', will monitor major variation in selectivity/cost of multiple available plan(path of execution) and automatically switch to optimum plan during run time query execution.
my question is , considering 'up to date stats for all our database objects' what is the requirement of sql plan baseline(spm)? At which situation ,ACS(adaptive cursor sharing) won't able to stabilize the query plan, so that we have to go for baseline or SPM?
View 2 Replies
View Related
Dec 18, 2012
I have a table "t", with a highly skewed ID column
Cursor_sharing=force
select count(*) from t;
--79003
select count(*) from t where id=1;
--79002
select count(*) from t where id=99;
--1
I have a index on id column and since highly skewed data is there in id column therefore histogram is there
SQL> select table_name,column_name,histogram,num_buckets from user_tab_cols where table_name='T';
TABLE_NAME COLUMN_NAME HISTOGRAM NUM_BUCKETS
------------------------------ ------------------------------ --------------- ------------
T ID FREQUENCY 2
When i use literals , explain plan comes up with optimal execution plan
select * from t where id=1;
Full table scan
Select * from t where id=99;
Index range scan
But when i use bind variables,sub optimal execution plan
For id=1, it should use Full table scan but it goes for Index range scan , WHY? why ACS is not getting kicked?
alter system flush shared_pool;
set autotrace traceonly
variable n number;
exec :n=1;
select * from t where id=:n;
[code]...
Why is_bind_sensitive not "Y"?
View 10 Replies
View Related
Mar 12, 2013
I am trying to run an Oracle report with a query that has an embeded sql. this sql is returning more than 1 row, and the report is failing.
I need to pick the latest record entered that this sql return.
I tried rownnum and it works but only i can get the rown num I specify, not the latest record. I try to order, but I am getting an error back.
select w.emp_no, (select t.timestamp
from tob.work_unit t
where t.work_date = to_date('20130312', 'YYYYMMDD')
and rownum = 1
order by t.timestamp desc)
,w. spare_type
from work.work_unit w
where w.work_date = to_date('20130312', 'YYYYMMDD')
I am getting missing right parenthesis at the order by keyword My report is much complex than this, but I am tring to see if I can get the row that I want.
View 10 Replies
View Related
Aug 28, 2013
Does parallel hint works in cursor queries? The cursor query is something like :
cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;
The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.
View 13 Replies
View Related
Feb 22, 2011
In an OLTP environment what cursor_sharing setting is preferred?Though typically we retain the original setting for most of the parameters except memory settings etc. I have queries in the following context
No. I am not facing any issue as of now (I am not supporting any Live environment) But I want to know the desgn considerations
First of all in OLTP environment (say one I am referring) we use pl/sql variables which are obviously bind variables Only in case where plan is expected to change we use hard coded values like 'CREDIT' or 'DEBIT' etc. for acc_type column
Again there can be 2 scenario
1) we use the same query for both acc_type values
2) we use 2 different queries
IF v_parameter = 'CR'
select * from accounts where acc_type='CREDIT'...
else
select * from accounts where acc_type='DEBIT'...
end if;
Again suppose the values are skewed and we gather stats with histograms hereIs't it the setting 'cursor_sharing=similar' which will be useful in above case?as with this setting optimizer will 'think' which plan to pick depending upon the values and bind variable peeking is taken care in option 2 above with IF ELSE clause?
BTW
I have carried several tests but not getting conclusive results For example I created following table with skewed data, created index and gather stats with histogram
SQL> select object_id,count(*) from skewed_data_tab group by object_id;
OBJECT_ID COUNT(*)
---------- ----------
5 30
6 2970
7 10797
8 150000
9 300000
SQL> create index i_skewed_tab_data on skewed_data_tab(object_id);
SQL> exec dbms_stats.gather_table_stats(user,'SKEWED_DATA_TAB',cascade=>true,
method_opt=>'for all columns size 254');
Then traced with following options
1) alter session set events '10046 trace name context forever, level 12';
SQL> begin
for i_outer in(select n from ids order by tstamp)
loop
for i_inner in (select /* for exact */ object_id,object_name,object_type
from skewed_data_tab where object_id=i_outer.n)
[code]...
2) set termout off
alter session set events '10046 trace name context forever, level 12';
@/u04/scripts/exact.sql 5
cat /u04/scripts/exact.sql
select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=&1;
But not getting consistent results
View 11 Replies
View Related
Jun 2, 2011
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
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 32018 | 25 (4)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 14 | 32018 | 25 (4)| 00:00:01 | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 14 | 32018 | 17 (0)| 00:00:01 | KEY | 13 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| T_ECOMM_MEM_RELATIONS | 14 | 32018 | 17 (0)| 00:00:01 | KEY | 13 |
|* 5 | INDEX RANGE SCAN | X_ECOMM_MEM_RELATIONS3 | 15 | | 3 (0)| 00:00:01 | KEY | 13 |
|* 6 | INDEX FULL SCAN | I_PR_BRAND | 1 | 3 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "HH"."T_PR_BRAND" "B" WHERE
LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND'))))
4 - filter("A"."STATUS"='S')
5 - access("A"."AUDIT_TIME">SYSDATE@!-1 AND "A"."CODE_CORR_ECAT"='PREA')
filter("A"."CODE_CORR_ECAT"='PREA')
6 - filter(LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND')))
=========================
I tried not exists and some Antijoin hints in the subquery which is used in filter NOT IN. I tried minus too.
View 15 Replies
View Related
Dec 23, 2010
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]........
View 7 Replies
View Related
Apr 8, 2011
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:
CREATE TABLE SKU
(
ABC VARCHAR2(1 CHAR),
SKU VARCHAR2(32 CHAR) NOT NULL,
Lastcyclecount DATE,
[code]....
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"):
COUNT (DISTINCT sk.sku) - COUNT (DISTINCT s.sku)
View 6 Replies
View Related
Dec 27, 2010
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
--------------------------------------------------------------------------------
[code]....
View 3 Replies
View Related
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
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);
Explained.
SQL> Select * From Table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | PARTITION LIST SINGLE| | 33115 | 18 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TB_HXL_LIST | 33115 | 18 (0)| 00:00:01 |
View 3 Replies
View Related
Sep 8, 2009
SQL> select * from v$version;
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
[code]...
View 10 Replies
View Related
Feb 11, 2011
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.
View 6 Replies
View Related
Sep 25, 2013
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
[code]....
Explain plan output:
Plan hash value: 2779236890
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name| Rows| Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 1 | 29 | 9633M (8)|999:59:59 |||
|* 1 | FILTER |||| ||||
| 2 | PARTITION RANGE ALL || 738M| 19G| 6321K (1)| 21:04:17 | 1 | 6821 |
[code]....
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (<not feasible>)
4 - filter("PRODUCTID"=:B1)
5 - filter(ROWNUM<100)
12 - access("MODELID"=:B1)
[code]....
Note: - SQL profile "SYS_SQLPROF_014153616b850002" used for this statement
View 2 Replies
View Related
Jun 26, 2010
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)
View 3 Replies
View Related
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
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
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
Jun 16, 2010
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?
View 2 Replies
View Related
Oct 20, 2010
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 ?
View 6 Replies
View Related
Jun 16, 2011
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?
Simple database with simple schema.
How many records begin to be too many?
View 2 Replies
View Related
Nov 15, 2010
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?
View 13 Replies
View Related
Apr 12, 2013
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.
View 13 Replies
View Related
Aug 9, 2010
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.
View 6 Replies
View Related
Dec 6, 2011
I have an issue with export(expdp).
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.
expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk
Do i need any look into any memory parameters for this?
View 1 Replies
View Related
Oct 17, 2011
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'
[code]...
View 9 Replies
View Related
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
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
