Home » RDBMS Server » Performance Tuning » Can you please provide optimized query (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0, OS - I don't know)
| Can you please provide optimized query [message #526891] |
Thu, 13 October 2011 09:58  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Frnds,
I am not able to proivde you table structures (DDL/DML), and also its not needed.
I have 4 tables like displayed in below code with number of records in those tables. Right now I am executing below query. The query is taking too much time.
select count(*) from table_a; --9368953
select count(*) from table_b; --122901660
select count(*) from table_c; --3837
select count(*) from table_d; --records varies
SELECT r.vendor_record_seq_no,
r.vendor_subject_seq_no,
NVL(d.resolved_value, d.original_value) val,
d.control_column_seq_no
FROM table_a r, table_b d, table_c spq
WHERE d.study_seq_no = ip_study_seq_no
AND r.study_seq_no = ip_study_seq_no
AND r.control_dataset_seq_no = ip_dataset_seq_no
AND d.vendor_record_seq_no = r.vendor_record_seq_no
AND r.vendor_record_seq_no = spq.vendor_record_seq_no
AND NVL(r.vendor_subject_seq_no, -1) =
NVL(spq.vendor_subject_seq_no, -1)
AND NVL(r.vendor_subject_seq_no, -1) IN
(SELECT vendor_subject_seq_no FROM table_d)
ORDER BY r.vendor_record_seq_no, d.control_column_seq_no;
So I tried to optimize the query like below. Is that the correct and efficient way to get the functionality of above written query? If not, please provide the most efficient way to fetch data while not loosing the functionality of the query.
SELECT r.vendor_record_seq_no,
r.vendor_subject_seq_no,
NVL(d.resolved_value, d.original_value) val,
d.control_column_seq_no
FROM (select * from table_a where r.study_seq_no = ip_study_seq_no
AND r.control_dataset_seq_no = ip_dataset_seq_no) r,
(select * from table_b where d.study_seq_no = ip_study_seq_no) d, table_c spq
WHERE d.vendor_record_seq_no = r.vendor_record_seq_no
AND r.vendor_record_seq_no = spq.vendor_record_seq_no
AND NVL(r.vendor_subject_seq_no, -1) =
NVL(spq.vendor_subject_seq_no, -1)
AND NVL(r.vendor_subject_seq_no, -1) IN
(SELECT vendor_subject_seq_no FROM table_d)
ORDER BY r.vendor_record_seq_no, d.control_column_seq_no;
Thanks a lot.
Manu
[Updated on: Thu, 13 October 2011 10:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #526923 is a reply to message #526913] |
Thu, 13 October 2011 13:11  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
manubatham20 wrote on Thu, 13 October 2011 18:37Is there any affect of values of ip_study_seq_no and ip_dataset_seq_no on the explain plan?
Since we don't know what they are, because you haven't told us, how would we know?
|
|
|
|
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #526946 is a reply to message #526932] |
Thu, 13 October 2011 15:28 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
That is what I got from DBA Team:
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1047554899
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2066 | 1 (100)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 2066 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 2066 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-------------------------------------------------------------
1 - SEL$88122447
2 - SEL$88122447 / X$KGLCURSOR_CHILD@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("KGLOBT03")='?' AND "INST_ID"=USERENV('INSTANCE'))
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------
1 - (#keys=1) INTERNAL_FUNCTION("KGLOBT07")[22], "KGLOBT03"[VARCHAR2,13],
"KGLFNOBJ"[4000], "KGLOBT30"[NUMBER,22], "KGLOBTS4"[VARCHAR2,30]
2 - "INST_ID"[NUMBER,22], "KGLFNOBJ"[LOB,4000], "KGLOBT03"[VARCHAR2,13],
"KGLOBT07"[NUMBER,22], "KGLOBT30"[NUMBER,22], "KGLOBTS4"[VARCHAR2,30]
Regards,
Manu
|
|
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #527005 is a reply to message #526984] |
Fri, 14 October 2011 07:09 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Okay. I will be executing explain plan for that query. But I am asking one this since 3 posts, that using bind variable in that query make any difference rather using the actual values.
SELECT r.vendor_record_seq_no,
r.vendor_subject_seq_no,
NVL(d.resolved_value, d.original_value) val,
d.control_column_seq_no
FROM table_a r, table_b d, table_c spq
WHERE d.study_seq_no = :ip_study_seq_no
AND r.study_seq_no = :ip_study_seq_no
AND r.control_dataset_seq_no = :ip_dataset_seq_no
AND d.vendor_record_seq_no = r.vendor_record_seq_no
AND r.vendor_record_seq_no = spq.vendor_record_seq_no
AND NVL(r.vendor_subject_seq_no, -1) =
NVL(spq.vendor_subject_seq_no, -1)
AND NVL(r.vendor_subject_seq_no, -1) IN
(SELECT vendor_subject_seq_no FROM table_d)
ORDER BY r.vendor_record_seq_no, d.control_column_seq_no;
Here ip_study_seq_no and ip_dataset_seq_no are bind variables. Does providing the actual values will affect the output of explain plan? Columns r.control_dataset_seq_no, d.vendor_record_seq_no, r.vendor_record_seq_no are of number datatype.
What columns of explain_table you needed in the output.
I am also looking for the data about those tables and associated objects.
Thanks again!!!
|
|
|
|
| Re: Can you please provide optimized query [message #527006 is a reply to message #527005] |
Fri, 14 October 2011 07:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Get the explain plan like this:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
As for the binds - just leave them without values for now.
|
|
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #527039 is a reply to message #527007] |
Fri, 14 October 2011 12:52 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I executed
explain plan SET STATEMENT_ID = 'slowqry' for
SELECT r.vendor_record_seq_no,
r.vendor_subject_seq_no,
NVL(d.resolved_value, d.original_value) val,
d.control_column_seq_no
FROM vendor_record r, vendor_data d, STAGING_PROCESS_QUEUE spq
WHERE d.study_seq_no = :ip_study_seq_no
AND r.study_seq_no = :ip_study_seq_no
AND r.control_dataset_seq_no = :ip_dataset_seq_no
AND d.vendor_record_seq_no = r.vendor_record_seq_no
AND r.vendor_record_seq_no = spq.vendor_record_seq_no
AND NVL(r.vendor_subject_seq_no, -1) =
NVL(spq.vendor_subject_seq_no, -1)
AND NVL(r.vendor_subject_seq_no, -1) IN
(SELECT vendor_subject_seq_no FROM TEMP_SDTM_SUBJECT)
ORDER BY r.vendor_record_seq_no, d.control_column_seq_no;
Plan hash value: 3510476515
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 7733 (1)| 00:01:33 | | |
| 1 | SORT ORDER BY | | 1 | 73 | 7733 (1)| 00:01:33 | | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID | VENDOR_DATA | 1 | 28 | 4 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 73 | 7732 (1)| 00:01:33 | | |
| 4 | NESTED LOOPS | | 1 | 45 | 7728 (1)| 00:01:33 | | |
| 5 | MERGE JOIN CARTESIAN | | 1 | 24 | 7727 (1)| 00:01:33 | | |
| 6 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS FULL | TEMP_SDTM_SUBJECT | 1 | 13 | 2 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 72 | 792 | 7725 (1)| 00:01:33 | | |
| 9 | TABLE ACCESS FULL | STAGING_PROCESS_QUEUE | 72 | 792 | 7724 (1)| 00:01:33 | | |
| 10 | PARTITION HASH SINGLE | | 1 | 21 | 1 (0)| 00:00:01 | KEY | KEY |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| VENDOR_RECORD | 1 | 21 | 1 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX RANGE SCAN | VENDOR_RECORD_UK01 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 13 | PARTITION HASH SINGLE | | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 14 | INDEX RANGE SCAN | VENDOR_DATA_FK02 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - filter("R"."CONTROL_DATASET_SEQ_NO"=TO_NUMBER(:IP_DATASET_SEQ_NO) AND
NVL("R"."VENDOR_SUBJECT_SEQ_NO",(-1))=NVL("SPQ"."VENDOR_SUBJECT_SEQ_NO",(-1)) AND
"VENDOR_SUBJECT_SEQ_NO"=NVL("R"."VENDOR_SUBJECT_SEQ_NO",(-1)))
12 - access("R"."STUDY_SEQ_NO"=TO_NUMBER(:IP_STUDY_SEQ_NO) AND
"R"."VENDOR_RECORD_SEQ_NO"="SPQ"."VENDOR_RECORD_SEQ_NO")
14 - access("D"."STUDY_SEQ_NO"=TO_NUMBER(:IP_STUDY_SEQ_NO) AND "D"."VENDOR_RECORD_SEQ_NO"="R"."VENDOR_RECORD_SEQ_NO")
Regards,
Manu
|
|
|
|
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #527200 is a reply to message #527049] |
Sun, 16 October 2011 15:26 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Can AWR Report help me anyway to get the details? What information I can extract from AWR report. Is there any sensitive information remains there in AWR report? Can I paste it here?
@Blackswan: What should be rowcount in explain plan, should it be equal to the number of rows in those tables?
manubatham20 wrote on Sat, 15 October 2011 00:50Ok, one more thing I want to know, is there may be any reason, why same select query is running properly at a time, and another time it gets hanged (for the same inputs).
One more thing I want to add. This query has been internally used in a cursor. Can it may be any problem related to cursor? (Just a guess)
[Updated on: Sun, 16 October 2011 15:37] Report message to a moderator |
|
|
|
| Re: Can you please provide optimized query [message #527202 is a reply to message #527200] |
Sun, 16 October 2011 17:14 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If ip_dataset_seq_no is a variable being passed in pl/sql, then it is already bound and there is no need to change it to :ip_dataset_seq_no. In general, using bind variables is supposed to save time because the query does not need to be hard-parsed, however there can be problems if it either has to search through a huge SGA to find the query or if there are problems with bind variable peeking, as previously mentioned by others.
The first query that you posted looked like it was written logically. The only thing I might change would be changing the nvl's used in the join conditions. For example, instead of:
NVL(r.vendor_subject_seq_no, -1) =
NVL(spq.vendor_subject_seq_no, -1)
I might use:
(r.vendor_subject_seq_no = spq.vendor_subject_seq_no or
(r.vendor_subject_seq_no is null and spq.vendor_subject_seq_no is null))
Nvl is a function, so unless you have a function-based index on the column that nvl is used on, it might prevent usage of an index.
When in doubt, if you think another query might be faster, then test and compare times. You should initially be comparing the queries by themselves, separate from any cursor or program that it is used in. Once you have the query tuned, then you can see what else might be slowing it down. If you are looping through a cursor, that may very well be the cause of the slowness. If you post what you are actually doing, there may be a more efficient way, without looping.
You need to make sure that you have indexes on the columns that are used in join and filter conditions. You also need to make sure that you have current statistics on your tables and indexes. You can check the dates that statistics were last gathered using:
select last_analyzed from your_table_name;
select last_analyzed from your_index_name;
You should be able to get an explain plan yourself using:
set autotrace on explain
run your query
set autotrace off
Although other methods produce a more accurate plan, the above is a good start. Autotrace sort of gives you what it plans to do, which is not always exactly what it actually does.
You may not have statistics on temporary tables and may need to provide a cardinality hint or some such thing, to give the optimizer a clue what to expect from that table.
The optimizer takes all the information available and tries to choose the best plan. Usually, if it has correct information, it chooses the best plan. If your query is returning a lot of rows, then a full table scan may be the best plan. If your query returns a lot of rows, a first_rows hint may allow you to see the first rows in the result set faster, instead of waiting for all of them at end.
That is about all the advice I can offer without further information.
[Updated on: Sun, 16 October 2011 17:22] Report message to a moderator |
|
|
|
|
|
| Re: Can you please provide optimized query [message #527244 is a reply to message #527202] |
Mon, 17 October 2011 04:10 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Barbara Boehmer wrote on Sun, 16 October 2011 23:14If ip_dataset_seq_no is a variable being passed in pl/sql, then it is already bound and there is no need to change it to :ip_dataset_seq_no.
There is if you want to generate an explain plan for it in sqlplus using explain plan for.
Barbara Boehmer wrote on Sun, 16 October 2011 23:14
I might use:
(r.vendor_subject_seq_no = spq.vendor_subject_seq_no or
(r.vendor_subject_seq_no is null and spq.vendor_subject_seq_no is null))
Nvl is a function, so unless you have a function-based index on the column that nvl is used on, it might prevent usage of an index.
Is null can also prevent index usage since nulls aren't indexed in single column indexes.
Barbara Boehmer wrote on Sun, 16 October 2011 23:14
select last_analyzed from your_table_name;
select last_analyzed from your_index_name;
Should be:
select last_analyzed from user_tables where table_name = '<your_table_name>';
select last_analyzed from user_indexes where index_name = '<your_index_name>';
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #529033 is a reply to message #526891] |
Fri, 28 October 2011 06:44 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Sorry for delayed reply. I fell ill for these many days. Statistics has been gathered for all tables/indexes on every Saturday (Last is 22nd oct).
Indexes on VENDOR_DATA table (No function bases indexes all ascending)
VENDOR_DATA_IDX04 (STUDY_SEQ_NO, CONTROL_COLUMN_SEQ_NO, VENDOR_RECORD_SEQ_NO, ORIGINAL_VALUE)
VENDOR_DATA_PK (VENDOR_RECORD_SEQ_NO)
VENDOR_DATA_FK01 (STUDY_SEQ_NO)
VENDOR_DATA_FK02 (STUDY_SEQ_NO, VENDOR_RECORD_SEQ_NO)
VENDOR_DATA_FK03 (CONTROL_COLUMN_SEQ_NO)
VENDOR_DATA_UK01 (STUDY_SEQ_NO, VENDOR_DATA_SEQ_NO)
Indexes on VENDOR_RECORD table (All ascending)
VENDOR_RECORD_FK01 (STUDY_SEQ_NO)
VENDOR_RECORD_FK02 (CONTROL_DATASET_SEQ_NO)
VENDOR_RECORD_FK03 (VENDOR_SUBJECT_SEQ_NO, STUDY_SEQ_NO)
VENDOR_RECORD_IDX02 (VENDOR_RECORD_SEQ_NO, PROCESSING_FLAG)
VENDOR_RECORD_IX01 (CONTROL_DATASET_SEQ_NO, STUDY_SEQ_NO, SYS_NC00011$) --function based index used here
VENDOR_RECORD_PK (VENDOR_RECORD_SEQ_NO)
VENDOR_RECORD_UK01 (STUDY_SEQ_NO, VENDOR_RECORD_SEQ_NO)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4217567072
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 12440 (1)| 00:02:30 | | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID | VENDOR_DATA | 1 | 31 | 3 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 68 | 12440 (1)| 00:02:30 | | |
|* 3 | HASH JOIN | | 1 | 37 | 12437 (1)| 00:02:30 | | |
| 4 | NESTED LOOPS | | 2 | 50 | 6 (17)| 00:00:01 | | |
| 5 | SORT UNIQUE | | 1 | 3 | 2 (0)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | TEMP_SDTM_SUBJECT | 1 | 3 | 2 (0)| 00:00:01 | | |
| 7 | PARTITION HASH SINGLE | | 2 | 44 | 3 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| VENDOR_RECORD | 2 | 44 | 3 (0)| 00:00:01 | KEY | KEY |
|* 9 | INDEX RANGE SCAN | VENDOR_RECORD_IX01 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
| 10 | TABLE ACCESS FULL | STAGING_PROCESS_QUEUE | 3912 | 46944 | 12431 (1)| 00:02:30 | | |
| 11 | PARTITION HASH SINGLE | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX RANGE SCAN | VENDOR_DATA_FK02 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - access("R"."VENDOR_RECORD_SEQ_NO"="SPQ"."VENDOR_RECORD_SEQ_NO" AND
NVL("VENDOR_SUBJECT_SEQ_NO",(-1))=NVL("SPQ"."VENDOR_SUBJECT_SEQ_NO",(-1)))
9 - access("R"."STUDY_SEQ_NO"=TO_NUMBER(:B) AND "R"."CONTROL_DATASET_SEQ_NO"=TO_NUMBER(:C) AND
"VENDOR_SUBJECT_SEQ_NO"=NVL("VENDOR_SUBJECT_SEQ_NO",(-1)))
12 - access("D"."STUDY_SEQ_NO"=TO_NUMBER(:A) AND "D"."VENDOR_RECORD_SEQ_NO"="R"."VENDOR_RECORD_SEQ_NO")
There is no index on TEMP_SDTM_SUBJECT and STAGING_PROCESS_QUEUE. As records come and goes from these tables (insert/delete). And these tables don't hold any record if the process is not going on (running). What else I need to look into. Its on Production server, so I can't modify any code, while development server with almost same number of records in tables, its all running fine. Kindly suggest.
[Edit]: I am not getting how to shrink the width of the message.
Thanks.
Manu
[Updated on: Fri, 28 October 2011 06:53] Report message to a moderator |
|
|
|
|
|
| Re: Can you please provide optimized query [message #535189 is a reply to message #535179] |
Mon, 12 December 2011 14:14 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Data in these 2 tables populates on the fly and then goes off. Is there benefit of gather stats or making index on them as data is changing very rapidly. What I can do is to use dynamic sampling hint.
But data in these tables is not that much. Say in staging_process_queue its 50,000(0) records and in temp_sdtm_subject its not much.
Vendor_data and vendor_record have ample of data, I think I have stated above.
The above is the explain plan that I have been mentioned. Now I got the execution plan for that query, that looks different from that explain plan.
SQL_ID c43u1ah72cwy8, child number 0
-------------------------------------
SELECT R.VENDOR_RECORD_SEQ_NO, R.VENDOR_SUBJECT_SEQ_NO, NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO FROM VENDOR_RECORD R, VENDOR_DATA D, STAGING_PROCESS_QUEUE SPQ WHERE D.STUDY_SEQ_NO = :B2 AND
R.STUDY_SEQ_NO = :B2 AND R.CONTROL_DATASET_SEQ_NO = :B1 AND D.VENDOR_RECORD_SEQ_NO = R.VENDOR_RECORD_SEQ_NO AND
R.VENDOR_RECORD_SEQ_NO = SPQ.VENDOR_RECORD_SEQ_NO AND NVL(R.VENDOR_SUBJECT_SEQ_NO, -1) = NVL(SPQ.VENDOR_SUBJECT_SEQ_NO,
-1) AND NVL(R.VENDOR_SUBJECT_SEQ_NO, -1) IN (SELECT VENDOR_SUBJECT_SEQ_NO FROM TEMP_SDTM_SUBJECT) ORDER BY
R.VENDOR_RECORD_SEQ_NO, D.CONTROL_COLUMN_SEQ_NO
Plan hash value: 251684864
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1168 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 99 | 1168 (1)| 00:00:15 | | |
| 2 | NESTED LOOPS | | 1 | 99 | 1167 (1)| 00:00:15 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 82 | 1167 (1)| 00:00:15 | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 24 | 1164 (1)| 00:00:14 | | |
| 5 | TABLE ACCESS FULL | STAGING_PROCESS_QUEUE | 1 | 11 | 1162 (1)| 00:00:14 | | |
| 6 | BUFFER SORT | | 1 | 13 | 9223P(100)|999:59:59 | | |
| 7 | SORT UNIQUE | | 1 | 13 | 2 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | TEMP_SDTM_SUBJECT | 1 | 13 | 2 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 1 | 58 | 1165 (1)| 00:00:14 | | |
| 10 | PARTITION HASH SINGLE | | 1 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| VENDOR_DATA | 1 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX RANGE SCAN | VENDOR_DATA_FK01 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
| 13 | PARTITION HASH SINGLE | | 1 | 17 | 0 (0)| | KEY | KEY |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | VENDOR_RECORD | 1 | 17 | 0 (0)| | KEY | KEY |
|* 15 | INDEX RANGE SCAN | VENDOR_RECORD_UK01 | 1 | | 0 (0)| | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("D"."STUDY_SEQ_NO"=:B2)
14 - filter(("R"."CONTROL_DATASET_SEQ_NO"=:B1 AND NVL("R"."VENDOR_SUBJECT_SEQ_NO",(-1))=NVL("SPQ"."VENDOR_SUBJECT_SEQ_
NO",(-1)) AND "VENDOR_SUBJECT_SEQ_NO"=NVL("R"."VENDOR_SUBJECT_SEQ_NO",(-1))))
15 - access("R"."STUDY_SEQ_NO"=:B2 AND "R"."VENDOR_RECORD_SEQ_NO"="SPQ"."VENDOR_RECORD_SEQ_NO")
filter("D"."VENDOR_RECORD_SEQ_NO"="R"."VENDOR_RECORD_SEQ_NO")
This query takes too much time to complete. Sometimes fails with TempSpace error. Don't know how to solve. 
Is there any relation of /*+result_cache*/ hint with Temp Tablespace issue.
Thanks!!
[Updated on: Mon, 12 December 2011 14:17] Report message to a moderator |
|
|
|
| Re: Can you please provide optimized query [message #535193 is a reply to message #535189] |
Mon, 12 December 2011 15:39 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you're using temp tables I'd look to see if the process can be changed so that you don't need them.
Temp tables are rarely the most efficient approach in oracle.
A function based index on VENDOR_RECORD - NVL(VENDOR_SUBJECT_SEQ_NO, -1) would probably help.
And if TEMP_SDTM_SUBJECT is only populated for this process, and you know roughly how many rows are going to be in it,
using the cardinality hint for that table might be an idea.
Looks like the main problem is it's doing merge join cartesian because it thinks there's mo data there.
|
|
|
|
|
|
| Re: Can you please provide optimized query [message #535223 is a reply to message #535194] |
Mon, 12 December 2011 23:48 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
Blakswan: I am sorry, I didn't get you. How can I force explain and execution plan to show the the number of rows of our choice?
I think it generates the plan accordingly CBO.
cookiemonster: We used TEMP table (GTT), as processing on them is faster (because of non-logging) than normal heap tables. This query is just a small part of a big application.
Do you still want me to use heap table instead of GTT. Also I am not able to intercept in session to see how much data goes into that GTT.
I don't know why its using MERGE JOIN CARTESIAN, while appropriate join conditions are there. Thinking about using cardinality and dynamic sampling hints.
For creating index on NVL(VENDOR_SUBJECT_SEQ_NO, -1) in VENDOR_RECORD, I would say, in explain plan I am seeing whenever these tables has been accessed,
always accessed through an index. Do you still think creating function based index will be helpful. Please suggest.
Also I want to ask that why its showing too much time in Buffer Sort.
Thanks!!
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:47:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
