Performance Tuning :: Does Parallel Hint Works In Cursor Queries

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


ADVERTISEMENT

Performance Tuning :: Select Query Taking Time Even After Using Parallel Hint?

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

Performance Tuning :: Does Parallel Hint In Query Can Create Bottleneck / Slowdown / Crash Database

Oct 20, 2011

does parallel hint in query can create bottleneck/Slowdown/crash database..??

View 4 Replies View Related

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?

In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?

View 5 Replies View Related

Performance Tuning :: Hint Is Not Working Properly

Jul 14, 2010

I have a query with FULL hint that is behaving in a strange manner. The query fetches around 700000 of data. Sometimes it fetches the data with the hint and sometimes it does not fetch any data with the hint and then I have to remove the hint and have to fetch the data. Below is the query,

select /*+ FULL(COMP_TM) FULL(TRANS_TM) FULL(INVC_TM) */
CUST_BE_ID ,
DISTR_BE_ID ,
FG_BE_ID ,
KIT_BE_ID ,
BG_ID_NO_BE_ID ,
[code]....

The statistics gathering activity of FACT_DLY_ALGND_SLS table takes around 5 hours to complete. It is a range partitioned table with subpartitions.

View 14 Replies View Related

Performance Tuning :: Can Use Oracle Hint To Use Index In A Query Which Uses UNION

Mar 31, 2011

I have a SQL query where I am making UNION of two select statements. The table that I am joining in each select statement have indexes defined for those tables.

Now the UNION of the two select statements again in enclosed in an inline view , from which I fetching my final field values.

The select statements inside the inline view returns huge number of row (like 50 million rows).

The whole query fails with time out.

Is there a way to pass Oracle Hints so that Oracle uses indexes?

View 1 Replies View Related

Performance Tuning :: Syntax To Pass Hint To Emulate Good Attached Plan?

Aug 16, 2012

I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.I really don't want to do that as it may cause a load on my DB.

In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?

select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'

View 2 Replies View Related

SQL & PL/SQL :: Query Regarding Parallel Hint Degree?

Jan 3, 2012

I have been told that i should use multiple's of 4 as degree in the parallel hint to get maximum performance, so i am wondering is it true? that i should always use multiples of 4 or i can use any number inside the parallel hint.

View 4 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 :: Possible To Build Index Partition In Parallel

Jul 8, 2010

Is it possible to build index partition in parallel?I tried following command

alter index <index_name> rebuild partition <partition name> online parallel 5;

It executed without complaining, but want to know if index partitions can be build in parallel?

View 3 Replies View Related

SQL & PL/SQL :: ORA-30926 Occurs Only With PARALLEL Hint On MERGE Statement

Jun 26, 2013

I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.

Well this query throws below error.

----Error---

ORA-12801: error signaled in parallel query server P011

ORA-30926: unable to get a stable set of rows in the source tables

However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)

-----Query--------

MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)

[Code]....

View 1 Replies View Related

Performance Tuning :: Parallel Operations Not Executing With Expected Degree

Apr 2, 2012

I am executing a sql statement which is doing FTS in parallel mode The server has 8 cpus and threads_per_cpu is 2

The v$sql shows PX_SERVERS_EXECUTIONS as 8

select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2'
8, select /*+ full(a) parallel(a)....

however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid) Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2 and values for degree and required degree are 8 and 16 respectively

However, all the time, only 8 sessions which belong to server set = 1, were active though its state was waiting with event "PX Deq Credit: send blkd"

The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'

what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?

Note that paralel_max_servers setting is 16

Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops) however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.

View 2 Replies View Related

Performance Tuning :: How To Control Concurrent Inserts Of Record By Parallel Sessions

Aug 7, 2012

There is a detail table

There is a summary table

During batch process record is entered in detail table as well as summary table.

The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record
Whereas in the detail table it inserts the record directly

detail table :
group_no doc_no amount
101 doc1 100
101 doc2 200
102 doc3 300
102 doc4 400

summary table :
group_no amount
101 300
102 700

Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.

Can it be solved without using temp table, select for update?

View 4 Replies View Related

Performance Tuning :: Sub Queries With Function Call?

May 24, 2011

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same:

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
FROM TABLE1 TAB_ALIAS1
WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
AND TRUNC(TAB_ALIAS1.ELAP_TIME) =
(
SELECT MAX(ELAP_TIME)

[code]....

View 6 Replies View Related

Performance Tuning :: Queries Of Materialized Views

Jan 28, 2011

We are using the below query to create a Materialized View but it has been running since 3 hours. It is an Oracle 9i database running in HP-UX.The quey is as follows,

(SELECT
/*+ use_nl(A) parallel (A,4)*/
A.ICD_CODE AS ICD_CODE,
A.ICD_DESC AS ICD_DESC,
A.PROC_GROUP as PROC_GROUP,
B.COMPL_ICD_CODE AS COMPL_ICD_CODE,
B.COMPL_GRP_TXT AS COMPL_GRP_TXT,
C.PROC_TYPE AS PROC_TYPE ,
[code]....

View 21 Replies View Related

Performance Tuning :: How To Check SQL Queries Run By Application At Backend

Oct 28, 2010

Name some database tool from which I can check the SQL Queries which my application is running.

NOTE: I do not want to check the queries which I am executing at the SQL command prompt but queries that are being run by my application at the backend.

View 4 Replies View Related

Performance Tuning :: Materialized View To Improve Search Queries?

Jan 31, 2012

In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables

Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.

And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.

since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query

Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view

Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;

Will it improve performance of the search functionality

View 2 Replies View Related

Performance Tuning :: How To Find Out Queries That Are Executed In Particular Range Of Time

Sep 9, 2011

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

View 3 Replies View Related

Performance Tuning :: Queries On PGA Settings And Related Memory Management?

Feb 11, 2011

I have few queries on PGA memory management.Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same

As I understand many workareas can be allocated to a single sql statement and number and sizes of theses workareas is controlled internally by Oracle when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set) Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used

[1] Can we say paging happens and can be checked at this time?

[2] Is there a difference in handling memory while populating pl/sql tables?

As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.

With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all while there wasn't any heavy OS process involved during the period. I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.

[3] Isn't it expected to match?

[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?

select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;

During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)

SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4

[code]...

The above query is showing above values even when the pl/sql block execution is completed 30 minutes back

[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?Of course I am not checking at OS level as mentioned in question [3] above the values won't match!

Still the output of free command for reference(After the pl/sql block executed)

SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444

--(After the pl/sql block executed)

SQL> select * from v$pgastat;
NAMEVALUEUNIT
aggregate PGA target parameter 524288000bytes
aggregate PGA auto target 456256512bytes
global memory bound 26214400bytes
total PGA inuse 17328128bytes

[code]...

[6] What could be the significance of negative values of 'session pga memory/max'?

Last We have an OLTP system and in the night we run batch processes in 2-4 sessions

Suppose I have 10 GB RAM and with PGA setting of 3.5 GB Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning

[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?

Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size

[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?

View 8 Replies View Related

Performance Tuning :: Hierarchical Queries - Run Jobs Within Stipulated Time Frame

Nov 17, 2011

We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.

The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. I have attached in the text file.

View 4 Replies View Related

Performance Tuning :: Adaptive Cursor Sharing VS SPM?

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

Performance Tuning :: Adaptive Cursor Sharing?

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

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

Process Queries Parallel?

Feb 2, 2005

On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:

GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;

Is there a way to processes the queries parallel ?

View 1 Replies View Related

SQL & PL/SQL :: Multiple Parallel Queries Against Table

Jun 23, 2011

I would like to know the following.

If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.

In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.

View 2 Replies View Related

Performance Tuning :: Cursor Sharing Setting In OLTP Environment?

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

Server Administration :: Are Parallel Queries Supported In Oracle XE

Nov 18, 2010

I'm not sure is parallel query supported in xe ?

View 2 Replies View Related

SQL & PL/SQL :: ORA-12842 / Cursor Invalidated During Parallel Execution

Sep 17, 2010

I have received ORA-12842: Cursor invalidated during parallel execution error in my Pl/sql script. In pl/SQL 4MVs are used those are refreshed/refreshing while Pl/sql script start executing

Script is also dropping and creating table and index before actual processing starts.

Is that error coming because MVs are refreshing and they are not ready for use or some thing happened between actual processing and dropping/creating the table?

View 4 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

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







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