Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DEPTNO"="B"."DEPTNO")
Nested loop by defintion means,for every row returned by the outer query,the inner query is executed that many times.
In the above example,oracle does a full table scan and returned 14 rows.Now for dept table,it does a index unique scan and applies the predicate a.deptno=b.deptno and returns 1 row.
My question is why it is returning only 1 row? That measn for every 14 rows,this one row is fetched 14 times.
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
explain plan for select count(*) from orders, lineitem where o_orderkey= l_orderkey.
The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.
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
I'm joinging two tables event_types and tmp_acc tables.
event_types contains 2 Billion records tmp_acc contains 20,000 records.
Resulting rows are about 300,000 records in event_types table end_t and account_obj_id0 are joined indexed
no indexs in tmp_acc.
When I run below query with nexted loop it takes 6 hrs to complete. But when I run with hash join even after 4 days it was still running. what is wrong with hash join here. Why it takes so long. I'm joining only 20000 rows. So I think there should be a way to get result rows quickly.
show parameters hash_area_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ hash_area_size integer 2097152
explain plan for select --+ parallel(e,6) [code]....
I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.
CODECREATE OR REPLACE TYPE ADDR_T AS OBJECT ( ADDR1 VARCHAR2 (50), ADDR2 VARCHAR2 (50) ); CREATE OR REPLACE TYPE t_ADDr AS TABLE OF ADDR_T;
[code]....
I have added some records and created index on ID column. I want to get result of CODEselect id, p.addr1,p.addr2 from nested_table n,table(n.COL1) p where id=1
Explain plan for that is:
CODESELECT STATEMENT ALL_ROWSCost: 8 Bytes: 231 Cardinality: 3 4 HASH JOIN Cost: 8 Bytes: 231 Cardinality: 3 2 TABLE ACCESS BY INDEX ROWID TABLE SYS.NESTED_TABLE Cost: 2 Bytes: 13 Cardinality: 1 1 INDEX RANGE SCAN INDEX SYS.FDSFAS Cost: 1 Cardinality: 1 3 TABLE ACCESS FULL TABLE (NESTED) SYS.COL1_TAB Cost: 5 Bytes: 163,840 Cardinality: 2,560
How to avoid full table scan on nested table? Cardinality is sum of all records in nested column in all rows in main table, why?
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?
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,
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'.
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?
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 ?
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?
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?
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.
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.
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.
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'
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.
are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?
I have a table where each record has a numerical x-coordinate value as one of its fields. I want to loop through a group of the records that have another field in common in a given order. In a nested loop, I would like to subtract the coordinate of the outer loop from the coordinate of the inner loop for all records in the inner loop that appear later in the sequence. The result is a list of the distances between all coordinates.
Example: x-coordinate 3 4 6 7
It would look like this: 4-3, 6-3, 7-3, 6-4, 7-4, 7-6
I can do this using two nested cursors that select the same thing basically. But the table being selected from is pretty large and it takes forever to keep selecting from the huge table when the inner cursor could just copy the results of the other cursor and repeatedly iterate through them.
Is it possible to copy a cursor's results into another cursor or reset the cursor index back to the beginning so that it doesn't have to do the select statement every time?
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table where id=id on grade table)
Rule: A ‐ LastName ends with a character between A‐F B ‐ LastName ends with a character between G‐K C ‐ LastName ends with a character between L‐P D ‐ LastName ends with a character between Q‐T E ‐ LastName ends with a character between U‐Z
I'm attempting to write a plsql for finding missing archived logs for streams.
requirement is to run a select statement and print 1. 'NOT FOUND' if name column is null 2. '<name of the files>' if rows are returned 3. 'NOT FOUND' if no rows are selected. (here is were i'm having trouble)
code i developed so for: for cr in (select decode(name, NULL, 'NOT FOUND', name) from v$archived_log where deleted='YES' and status!='A') loop if (cr.name = 'NOT FOUND')
i am trying to use loops to retrieve the ename of emp table of scott schema , then under they are retrieved , all "sal" values appear under them , i do not want employee's salary under each employee , i need retrieve the 14 record of enames , then under them the 14 record of sal column appears , so i used this code :
declare cursor emp_cur is select ename from emp; emp_rec emp_cur%rowtype ; [code]....
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE ( medical_record_number VARCHAR2(600), requested_test_name VARCHAR2(39), result_date DATE
[code]..
Process :will be explaining with a patient id : 1) Consider the patient 1001274 from mbs_first_date table. 2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor). 3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP) 4) The first record we have is CHL with 08/05/2009 (May 8th 2009).. 5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient. 6) The next record for SBP is 11/05/2009 (May 11th 2009) . 7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009).. Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it. 9) Go to the next test for the same patient which is DBP.. 10) The DBP first date is July 08th 2008.. 11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record. 12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be 1001274 SBP 11/05/2009 1001274 CHL 08/05/2009 1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
I am working on an assignement where client is using Oracle 10g but stuck to using RBO Now the application team, from the GUI available to them build dynamic queries and some of them run very slow.
Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO).For some long running queries the Tuning advisor is not producing any recommendations.
Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!
I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.
SELECT DISTINCT hash_value, sql_text FROM gv$sql sq WHERE hash_value IN (SELECT DISTINCT prev_hash_value FROM gv$session se WHERE sid IN (SELECT sid FROM gv$lock l WHERE type = 'TX' AND ctime >= 2000 AND l.inst_id = se.inst_id AND l.sid = se.sid) AND sq.inst_id = se.inst_id); [code]....
