in Education by
I have two insert statements, almost exactly the same, which run in two different schemas on the same Oracle instance. What the insert statement looks like doesn't matter - I'm looking for a troubleshooting strategy here. Both schemas have 99% the same structure. A few columns have slightly different names, other than that they're the same. The insert statements are almost exactly the same. The explain plan on one gives a cost of 6, the explain plan on the other gives a cost of 7. The tables involved in both sets of insert statements have exactly the same indexes. Statistics have been gathered for both schemas. One insert statement inserts 12,000 records in 5 seconds. The other insert statement inserts 25,000 records in 4 minutes 19 seconds. The number of records being insert is correct. It's the vast disparity in execution times that confuses me. Given that nothing stands out in the explain plan, how would you go about determining what's causing this disparity in runtimes? (I am using Oracle 10.2.0.4 on a Windows box). Edit: The problem ended up being an inefficient query plan, involving a cartesian merge which didn't need to be done. Judicious use of index hints and a hash join hint solved the problem. It now takes 10 seconds. Sql Trace / TKProf gave me the direction, as I it showed me how many seconds each step in the plan took, and how many rows were being generated. Thus TKPROF showed me:- Rows Row Source Operation ------- --------------------------------------------------- 23690 NESTED LOOPS OUTER (cr=3310466 pr=17 pw=0 time=174881374 us) 23690 NESTED LOOPS (cr=3310464 pr=17 pw=0 time=174478629 us) 2160900 MERGE JOIN CARTESIAN (cr=102 pr=0 pw=0 time=6491451 us) 1470 TABLE ACCESS BY INDEX ROWID TBL1 (cr=57 pr=0 pw=0 time=23978 us) 8820 INDEX RANGE SCAN XIF5TBL1 (cr=16 pr=0 pw=0 time=8859 us)(object id 272041) 2160900 BUFFER SORT (cr=45 pr=0 pw=0 time=4334777 us) 1470 TABLE ACCESS BY INDEX ROWID TBL1 (cr=45 pr=0 pw=0 time=2956 us) 8820 INDEX RANGE SCAN XIF5TBL1 (cr=10 pr=0 pw=0 time=8830 us)(object id 272041) 23690 MAT_VIEW ACCESS BY INDEX ROWID TBL2 (cr=3310362 pr=17 pw=0 time=235116546 us) 96565 INDEX RANGE SCAN XPK_TBL2 (cr=3219374 pr=3 pw=0 time=217869652 us)(object id 272084) 0 TABLE ACCESS BY INDEX ROWID TBL3 (cr=2 pr=0 pw=0 time=293390 us) 0 INDEX RANGE SCAN XIF1TBL3 (cr=2 pr=0 pw=0 time=180345 us)(object id 271983) Notice the rows where the operations are MERGE JOIN CARTESIAN and BUFFER SORT. Things that keyed me into looking at this were the number of rows generated (over 2 million!), and the amount of time spent on each operation (compare to other operations). JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by
Use the SQL Trace facility and TKPROF.

Related questions

0 votes
    If I have a view in SQL which contains various computed columns, some of which could be very expensive, ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 19, 2022 in Education by JackTerrance
0 votes
    How do you load more than 1 Max Sal in each Department through Informatica or write sql query in oracle?...
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    DROP TYPE Position; CREATE OR REPLACE TYPE Position AS OBJECT (longitude NUMBER(11,7), lattitude NUMBER(11, ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Feb 18, 2022 in Education by JackTerrance
0 votes
    IN and NOT IN operations decreases the performance of an SQL query compared to AND/OR operations. it it True or False? a. True b. False...
asked Feb 19, 2023 in Technology by JackTerrance
0 votes
    You are given a dataset on cancer detection. You have built a classification model and achieved an accuracy of 96 percent ... with your model performance? What can you do about it?...
asked Apr 27, 2021 in Technology by JackTerrance
0 votes
    I have been having some issues with LINQ-To-SQL around memory usage. I'm using it in a Windows ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 21, 2022 in Education by JackTerrance
0 votes
    I have been having some issues with LINQ-To-SQL around memory usage. I'm using it in a Windows ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 21, 2022 in Education by JackTerrance
0 votes
    I have been having some issues with LINQ-To-SQL around memory usage. I'm using it in a Windows ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 20, 2022 in Education by JackTerrance
0 votes
    How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR ... Saleable, * FROM Product Select the correct answer from above options...
asked Jan 28, 2022 in Education by JackTerrance
0 votes
    Solve the Riddle :: Three men were on a boat that was stuck one mile from the shore. One was afraid of Sharks. ... they reached the shore without any problems. How did they do it?...
asked Feb 11, 2021 in Education by JackTerrance
0 votes
    How to rectify SQL performance for developed dashboards?...
asked Oct 30, 2020 in Technology by JackTerrance
0 votes
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 17, 2022 in Education by JackTerrance
0 votes
    Which is the subset of SQL commands used to manipulate Oracle Database Structures, including tables? (a) ... Transaction Model topic in section Transactions of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    _______________ is a procedural extension of Oracle - SQL that offers language constructs similar to those in ... in chapter Query Processing Techniques of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
...