SQL & PL/SQL :: Performance With Union And Global Temp Table
May 24, 2010
I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
View 7 Replies
ADVERTISEMENT
Sep 2, 2010
what are minimum privilege required to create GTT (Global Temp Table)?
View 7 Replies
View Related
Jan 16, 2013
We had an issue with a PL/SQL package taking hours to run as a concurrent program. Database version is 10.2.0.4.0, running on Linux x86 64-bit. A tkprof'd trace file revealed the problem SQL statement to be a cursor. This one SQL statement would run for 3+ hours. I copied the SQL statement and ran it in TOAD and it completed in seconds, returning the exact same result set. To resolve the issue in the PL/SQL package I created a global temp table and ran the exact same SQL statement as an INSERT into the global temp table.
Again, instead of hours, the SQL statement completes in seconds. If I revert the change, it goes back to taking hours. I've attached the relevant sections of the tkprof showing the two SQL statements (identical other than the insert in front of one) and the resulting explain plans and performance data. I've always been under the impression that a cursor was a better option than a temp table and I've never run into a situation where the same SQL statement runs so much longer when executed as a cursor.
Attached File(s)
SQL_As_Cursor.jpg ( 274.02K )
Number of downloads: 7
Explain_for_SQL_As_Cursor.jpg ( 189.43K )
Number of downloads: 4
SQL_as_Insert.jpg ( 277.38K )
Number of downloads: 4
Explain_for_SQL_As_Insert.jpg ( 180.66K )
Number of downloads: 2
View 2 Replies
View Related
Jul 25, 2012
One of our customer have problem with following sql statement:
SELECT c.table_name, c.column_name
FROM user_tab_columns c, user_tables t
WHERE c.table_name = t.table_name
AND c.data_type IN ('CLOB', 'BLOB');
During execution it takes all the TEMP tablespace size(8GB).
I gather system stats (dbms_stats.gather_dictionary_stats(estimate_percent=>null)) but it doesn't resolve problem.Above sql statement works fine with RULE hint but I want to know what is the reason of problem with temporary tablespace.
View 10 Replies
View Related
Aug 5, 2010
this statement is taking 1hr , can we reduce the timing?
CREATE TABLE DGT_ITEMEFFORTDATA (ENTERPRISEID, OWNERTYPE, OWNERID, SUPEROWNERTYPE, SUPEROWNERID,
ITEMTYPE, ITEMID, STAGEID, USERID, DATEIDENTIFIED,
DATECLOSED, ACTIVITYCODEID, PHASEID, RELEASEID, MONTHID,
QUARTERID, INITIALEFFORT, BASELINEDEFFORT,
ACTUALEFFORT, ITEMSTATUS, ALLOCATIONSTATUS, STAGESTATUS,
OCCURANCETYPE, DSLPROJECTTYPE, METRICCALCRUNID,
[code].....
This is the explain plan of the above query
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11M| 4137M| 46149 (
| 1 | UNION-ALL | | | |
| 2 | TABLE ACCESS FULL| DGT_ITEMEFFORTDATA_DAILY | 3455K| 428M| 14575
[code].....
This is the index details
1DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMID4
2DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDITEMTYPE3
3DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERID2
4DGT_ITEMEFFORTDATA_DAILYHCLT_IDX_DGT_IFDOWNERTYPE1
There is no index on DGT_ITEMEFFORTDATA_TEMP table
[code].....
View 27 Replies
View Related
Jun 16, 2010
I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..
create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;
create index lmn on abc_tab(a,b,c)
View 10 Replies
View Related
Mar 19, 2012
getting how many local and global indexes on particular oracle table
View 2 Replies
View Related
Aug 20, 2010
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
View 3 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Aug 10, 2010
which one is better?
unloading 5 tables of same structure using a ETL tool then merging the data
using Union operator to unload 5 tables then do transformations in ETL tool
View 4 Replies
View Related
Oct 11, 2009
I m looking for any other way that i can code without using union all for my case.my data example is like this
table1
--------
col1 col2 Segment1 Segment2 Segment3
-------------------------------------------------------------
A11 B11 John Jhonny Johnathan
A12 B12 Melisa Amy Abagial
I need to create view of above record as below:
table2
------
col1 col2 col3 col4
--------------------------------------------------------------
A11 B11 Segment1 John
A11 B11 Segment2 Jhonny
A11 B11 Segment3 Johnathan
A12 B12 Segment1 Melisa
A12 B12 Segment2 Amy
A12 B12 Segment3 Abagial
now my code is using UNION ALL to get output as in table2
select col1,col1,'Segment1' col3,Segment1 col4 from table1
union all
select col1,col1,'Segment2' col3,Segment2 from table1
union all
select col1,col1,'Segment3' col3,Segment3 from table1
But the problem is the performance is realy bad.Is there any way i can do this without using union all? The time that take to execute this is not exceptable.
View 4 Replies
View Related
Sep 9, 2010
Oracle UNION ALL performance issue: when I try to run below SQL query separately SQL part1 and SQL part2 it takes some seconds only but if I run together with group by and without group by it take much time.
SELECT AVG(date_completed-login_date),to_char(to_date(login_date), 'YYYY') as wYear FROM
(
SELECT test.date_completed 'date_completed',sample.login_date 'login_date')
FROM sample test
where (some conditions) ) ---SQL part 1
UNION ALL
[code]...
View 33 Replies
View Related
Jan 27, 2012
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
SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID",
V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1,
V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3,
V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS,
V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE,
V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME,
V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN,
V_IDENTIFIER_LOOKUP.IDENTIFIER,
V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE,
[code]...
View 1 Replies
View Related
Aug 17, 2010
I have a partioned (by row_create_date) table, lets called it TABLE_X, which has about 300 million records. This table has 7 columns including the primary key and a non-unique, locally partitioned column called trace_id; 99% of queries access this table via this column.
Lately, querying TABLE_X via the trace_id has been very very bad. Queries run for over 1 hr in some cases. So we decided to change the index for trace_id to a global index. Now queries against TABLE_X return in seconds. So far so good.
However, when the query has to join TABLE_X to another table, the query sometimes runs for over 1 hours; back to the same old problem. Here is an illustration;
SELECT COUNT(1) FROM TABLE_X WHERE TRACE_ID = 'XXXXX';
-- returns in seconds
SELECT COUNT(1)
FROM TABLE_X,
TABLE_Y
WHERE TABLE_X.TRACE_ID = 'XXXXX'
AND TABLE_X.TRACE_D = TABLE_Y.TRACE_ID;
-- runs for over 1 hr, even when TABLE_Y.TRACE_ID is a primary key.
View 3 Replies
View Related
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
Jul 19, 2010
My stats jobs failed last night with "ORA-01652 :Unable to extend TEMP" error.
Is there any way to check this history data, what other session was using TEMP tablespace extensively ?
View 6 Replies
View Related
Dec 22, 2010
Allwasy temp tablespace shows 100% full, even though database bounce temp is not cleared again it shows 100% full.Is their to Tune this issue.
View 1 Replies
View Related
Nov 17, 2011
I am getting temp tablespace error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" for the following code.
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3 ) leading ( vd1 ,vd2 ,vd3 , tvd) */ vd1.vendor_record_seq_no,
tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1, vendor_data vd2, vendor_data vd3,
(SELECT rownumber, MAX (DECODE (control_column_seq_no, 91150, original_value, NULL)) AS value1,
[code]...
Right now used tables has the following number of records-
SELECT COUNT(*) FROM vendor_data --292890442
SELECT COUNT(*) FROM temp_vendor_data --0
SELECT COUNT(*) FROM temp_vendor_record --0
This query is part of an application, but consuming too much of temporary tablespace (68 GB allocated). I found it out by using query below:
select * from v$session a, v$sql b
where a.sql_id=b.sql_id
and status = 'ACTIVE'
I am not sure, why this problem is occuring.
View 5 Replies
View Related
Nov 23, 2011
I run a query, takes 20 minutes or so, I traced it and can see no more then 20-30 mb of temp space required in the plan.
I developed it for use in a materialized view, however when I create the mview with the sql, the temp space required grows until it maxxes out. I increased the existing 10gb to 50gb but still maxxed out. Took the SQL out, reran it, ran in 20 minutes barely scratching the temp, I ran a "create table as <select>" and same behaviour as the SQL, barely touched the temp as per the plan. So the temp space blolwing is unique to the mview create.
Im working with mviews years on several sites and have never seen this.
View 6 Replies
View Related
Nov 27, 2012
The below query is utilizing more than 17 Gb temp space. But still it is getting failed out due to insufficient temp space. is there any way to rewrite this query to reduce the temp utilization?
SELECT T12.FRGHT_AMT_CURCY_CD,T23.LAST_UPD,T11.PAR_OU_ID,T9.MAIN_PH_NUM,T23.DISCNT_PERCENT,T23.X_ERROR_NUM,T18.ADDR,T14.X_ECO_B_END_1141,
T14.X_ECO_A_END_1141,T9.X_ECO_VALIDATION_FLG,T23.X_ECO_ERR_DESCR,T14.ASSET_NUM,T20.NAME,T23.X_ECO_REASON2,T14.X_ECO_B_END_ID,
T14.ASSET_NUM,T14.X_ECO_B_END_IWPC,T23.X_AE_CON_PH_NUM,T23.SHIP_ADDR_ID,T19.NAME,T23.X_BE_CON_LST_NAME,T23.CREATED_BY,T23.X_ECO_LOCATION,T8.LOC,
T3.MODIFICATION_NUM,T10.INTEGRATION_ID,T23.INTEGRATION_ID,T23.X_MESSAGE,T9.PR_ADDR_ID,T12.ACCNT_ID,T23.X_BEARERNO,T23.X_SUB_STATUS_CD,
[code]....
View 3 Replies
View Related
Mar 7, 2011
I am trying to run on Oracle report via Oracle Application Concurrent job. Concurrent job is completing normal but I don't get anything on print out page. In log file of this request I see message 'MSG-01003: Errors =>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP'. I almost doubled the TEMP tablespace in size but still I am not able to get rid of this error message.
View 1 Replies
View Related
Apr 19, 2010
The below query throws an error as mention below
My PGA_AGREGATOR_TARGET = 2GB
below query is given below.
RowsPlan
1SELECT STATEMENT
1 HASH JOIN
1 MERGE JOIN CARTESIAN
1 TABLE ACCESS BY INDEX ROWID WAT_SOURCE_DATA
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE INDX_WAT_SRC_DATA_BIT
[code]....
Error Message : ORA-01652:unable to extend temp segment by 128 in tablespace TEMP
Query :
SELECT OR004.wat_id "WAT_ID",
SYSDATE "DATE_FIRST_IDENTIFIED",
SYSDATE "DATE_LAST_IDENTIFIED",
'OR-004' "RULE_REFNO",
'RISK' "RULE_TYPE",
OR004.workspace_id "WORKSPACE_ID",
OR004.workspace_name "WORKSPACE_NAME",
[code]....
View 6 Replies
View Related
Apr 6, 2009
this huge report that uses inline views. I keep getting the following error message when running the script through toad. I was thinking about using the USE_HASH hints. The sql optimizer we use is very buggy in Toad. I'm using oracle database version 10.2.0.3.
I can upload explain plan if needed.
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^'||
'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'||
'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'||
'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'||
'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'||
[Code]...
View 5 Replies
View Related
Jul 16, 2013
I have a TEMP tablespace with autoextend on next 10M and maxsize 5120M, now my tablespace is 99.98% full. Am getting ORA-1652: unable to extend temp segment by 128 in tablespace temp error, can i use the method to increase the maxsize value to 10240M.
View 2 Replies
View Related
Dec 21, 2010
Is there any alternative to Union or Union all of two different table. Example would be great.
View 5 Replies
View Related
Sep 18, 2012
I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.
This query works:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
UNION ALL
[Code]...
I feel like I should be able to execute the query like this: This query doesn't work:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
[Code]...
-- not some huge query
View 4 Replies
View Related
Jul 5, 2012
I have this sql (generated by discoverer plus), and work fine:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
We need change the table stock_por_sucursal for a view like this, with similar indexes each table
select * from stock_por_sucursal_old
union all
select * from stock_por_sucursal_new
and the result is not good:
SQL Statement from editor:
SELECT COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
GAR_DW.V_STOCK_POR_SUCURSAL o10475761,
[code]...
finally add a HINT /*+ gather_plan_statistics push_pred(TABLE) */ and the result was not very good in this case, but improved the resolution of the view, the rest got worse
SQL Statement from editor:
SELECT /*+ gather_plan_statistics push_pred(o10475761) */ COUNT(o10475761.SUC_ID)
FROM GAR_DW.ARTICULOS o10475528,
GAR_DW.EMPRESAS o10475602,
GAR_DW.L_DIA o10475639,
[code]...
View 2 Replies
View Related
Feb 20, 2010
What is the best option for GLOBAL TEMPORARY TABLE
1) option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS. and wheverever this is used for calculation commit at the end of porcedure.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
)ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin
[Code]....
2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
);
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
[Code]....
View 26 Replies
View Related
Sep 2, 2010
I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
View 5 Replies
View Related
Mar 12, 2013
I have created global temporary tables to be used in my stored procedure, in order to view reports which i created in JASPER. Since global temporary tables are session based, when multiple users are trying to generate the report, every user is getting inconsistent data.
To make it clear, what i meant is if a user A tries to view a report with some filter criteria and simultaneously user B is trying to generate the same report with another filter criteria, User A is getting User B's report data and User B is getting User A's report data. How can we avoid this problem?
View 1 Replies
View Related
