X4-2 Exadata Announcement

These the differences with the new X4-2 just announced, along with a table comparing the differences.
1) Double the size of flashcache
2) Switch from 3tb drives to 4tb drives (HC)
3) More cpu cores
4) Increase in Infiniband throughput by using an Active-Active configuration
5) Automatic Flash compression on X3 and X4 systems (using the ACO option)

	x2	X3	X4
Database
Processesor	2 x Six-Core Intel Xeon® X5675 Processors (3.06 GHz)	2 x Eight-Core Intel Xeon®E5-2690 Processors (2.9 GHz)	2 X Twelve-Core Intel® Xeon® E5-2697 V2 Processors (2.7 GHz)
Memory	96G	128g/256g	256g
Disk controller	Disk Controller HBA with 512MB Batter Backed Write Cache	Disk Controller HBA with 512MB Batter Backed Write Cache	Disk Controller HBA with 512MB Batter Backed Write Cache
Internal disks	4 x 300 GB 10,000 RPM SAS Disks	4 x 300 GB 10,000 RPM Disks	4 x 600 GB 10,000 RPM Disks
Infiniband	2 x QDR (40Gb/s) Ports	2 x QDR (40Gb/s) Ports	2 x QDR (40Gb/s) Ports
Ethernet	2 x 10 Gb Ethernet Ports based on the Intel 82599 10GbE Controller	4 x 1/10 Gb Ethernet Ports (copper)	4 x 1/10 Gb Ethernet Ports (copper)
Ethernet	4 x 1 Gb Ethernet Ports	2 x 10 Gb Ethernet Ports (optical)	2 x 10 Gb Ethernet Ports (optical)
Full Rack	96 CPU cores and 768 gb memory for database processing (12 CPU cores and 96 GB memory per Database Server)	128 CPU cores and 1TB or  2 TB memory for database processing (16 CPU cores and 256 GB memory per Database Server)	192 CPU cores and 2TB memory for database processing (24 CPU cores and up to 512 GB memory per Database Server)
Storage Cells
CPU	2 x Six-Core Intel® Xeon® L5640 (2.26 GHz) Processors	2 x Six-Core Intel® Xeon® E5-2630L (2.0 GHz processors)	2 x Six-Core Intel® Xeon® E5-2630 v2 (2.6 GHz processors)
Memory	24 GB	64 GB	96 GB
HC (High Capacity)
Disk Bandwidth¹	Up to 18 GB/second of uncompressed disk bandwidth	Up to 18 GB/second of uncompressed disk bandwidth	Up to 20 GB/second of uncompressed disk bandwidth
Flash Bandwidth¹	Up to 68 GB/second of uncompressed Flash data bandwidth	Up to 93 GB/second of uncompressed Flash data bandwidth	Up to 100 GB/second of uncompressed Flash data bandwidth
Disk IOPS ²	Up to 28,000 Database Disk IOPS	Up to 28,000 Database Disk IOPS	Up to 32,000 Database Disk IOPS
Flash read IOPS ²	Up to 1,500,000 Database Flash IOPS	Up to 1,500,000 Database Flash IOPS	Up to 2,660,000 Database Flash IOPS
Flash write IOPS³	N/A	Up to 1,000,000 Database Flash IOPS	Up to 1,680,000 Database Flash IOPS
Flash Data Capacity (raw)⁴	5.3 TB Exadata Smart Flash Cache	22.4 TB	44.8 TB
Disk Data capacity (raw)⁵	504 TB of raw disk data capacity	504 TB	672 TB
Disk Data capacity (Usable)⁶	Up to 224 TB of uncompressed usable capacity	224 TB	300 TB
HP (High Performance)
Disk Bandwidth¹	Up to 25 GB/second of uncompressed disk bandwidth	Up to 25 GB/second of uncompressed disk bandwidth	Up to 24 GB/second of uncompressed disk bandwidth
Flash Bandwidth¹	Up to 75 GB/second of uncompressed Flash data bandwidth	Up to 100 GB/second of uncompressed Flash data bandwidth	Up to 100 GB/second of uncompressed Flash data bandwidth
Disk IOPS ²	Up to 50,000 Database Disk IOPS	Up to 50,000 Database Disk IOPS	Up to 50,000 Database Disk IOPS
Flash read IOPS ²	Up to 1,500,000 Database Flash IOPS	Up to 1,500,000 Database Flash IOPS	Up to 2,660,000 Database Flash IOPS
Flash write IOPS³	N/A	Up to 1,000,000 Database Flash IOPS	Up to 1,680,000 Database Flash IOPS
Flash Data Capacity (raw)⁴	5.3 TB Exadata Smart Flash Cache	22.4 TB	44.8 TB
Disk Data capacity (raw)⁵	100 TB of raw disk data capacity	100 TB	200 TB
Disk Data capacity (Usable)⁶	Up to 45 TB of uncompressed usable capacity	45 TB	90 TB
	¹Bandwidth is peak physical scan bandwidth achieved running SQL, assuming no database compression. Effective user data bandwidth is higher when database compression is used.
	²Based on 8K IO requests running SQL. Note that the IO size greatly affects Flash IOPS. Others quote IOPS based on 2K or smaller IOs and are not relevant for databases.
	³Based on 8K IO requests running SQL. Flash write I/Os measured at the storage servers after ASM mirroring. Database writes will usually issue multiple storage IOs to maintain redundancy.
	⁴Raw capacity is measured in standard disk drive terminology with 1 GB = 1 billion bytes. Capacity is measured using normal powers of 2 space terminology with 1 TB = 1024 * 1024 * 1024 * 1024 bytes. Actual formatted capacity is less.
	⁵Raw capacity is measured in standard disk drive terminology with 1 GB = 1 billion bytes. Capacity is measured using normal powers of 2 space terminology with 1 TB = 1024 * 1024 * 1024 * 1024 bytes. Actual formatted capacity is less.
	⁶Actual space available for a database after mirroring (ASM normal redundancy) while also providing adequate space (one disk on Quarter and Half Racks and two disks on a Full Rack) to reestablish the mirroring protection after a disk failure in the normal redundancy case.

--> -->

Oracle 12c PL/SQL improvements.

Last week I was giving a presentation for the UNYOUG (Upstate NY Oracle users Group) and I talked about the new features in 12c (along with In-Memory database).

I thought I would share some thoughts after the meeting.

I went through Tom Kyte's top 12 new features, and surprisingly the top feature that excited people was the PL/SQL improvements. 

The PL/SQL improvements had to do with the ability to write a PL/SQL as part of the query.

Lets say currently with 11g you have a function that includes the days between 2 dates.

  CREATE OR REPLACE FUNCTION Days_Between 
             (first_dt DATE, second_dt DATE)
                RETURN NUMBER IS 
       dt_one NUMBER; 
       dt_two NUMBER; 
BEGIN 
      dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD')); 
      dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));  
           RETURN (dt_two - dt_one); 
 END Days_Between;   

select Days_between(start_date,end_date) from mytable;



The problem is that in order to test this function you need to create the function.  There are multiple issues that developers face with having to do this.

1. Developers often don't have the authority to create/change functions, especially if they need to be owned by a different schema
2. Replacing the current function affects other users and this may not be desirable while debugging changes.
3. Testing against production data is often not possible because of authorization, and collision issues.

The answer in 12c is the ability to include a function in the "WITH" clause.. The above would become


WITH  FUNCTION Days_Between
             (first_dt DATE, second_dt DATE)
                RETURN NUMBER IS
       dt_one NUMBER;
       dt_two NUMBER;
BEGIN
      dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));
      dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));
           RETURN (dt_two - dt_one);
 END Days_Between;  
select Days_between(start_date,end_date) from mytable;

So. what about Procedures you ask ? You can also include procedures in the mix.  The main purpose of doing this is to include any procedures that are invoked from the function.  This way you can include all the "dependencies" in the with clause. 

Finally, I read an article talking about how much this improves performance too.

http://www.oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php#plsql-support

but to the developers I talked to the big advantage was with the ability to test..

As far as performance gains, I don't know how much I would put reusable code (like functions) directly into a sql statement. It would be a bear to support any changes to a "common function" defined multiple places.
 

 

Performance and Indexes with oracle

It's been a while since I've written a post, but twice this week, the same issue came up..

The story goes like this.. "I have a query that is using indexes not FTS, but it is much slower then expected".  It seems most folks have it drummed into their heads that Indexes are fastest.

This is the statement I've made twice....

"The only thing worse than a FTS is an index lookup of the whole table."  I figured I would show you what I mean.

First I create 2 tables..

TEST_TABLE with 76,989 rows of data.  There is a primary key.
DRIVER  with the same rows of test_table.

This is the 2 test I did.

1) I created DRIVER with 1 row, and analyzed it , then I deleted the row, and inserted all the rows from TEST_TABLE.
2) I ran the following query which should return every row.

select  /*+ GATHER_PLAN_STATISTICS */  count(distinct a.capture_time)
from test_table a,
     driver b
where a.pval=b.pval
and b.instance_number < 20;

So what happens ?  since the statistics on DRIVER say there is only row you can see the plan and actual vs estimated below.

----------------------------------------------------------------------------------------------        
| Id  | Operation                       | Name       | Starts | E-Rows | A-Rows |   A-Time   |    
----------------------------------------------------------------------------------------------   

PLAN_TABLE_OUTPUT                                                                                                                           
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |      1 |        |      1 |00:00:00.86 |
|   1 |  SORT AGGREGATE                 |            |      1 |      1 |      1 |00:00:00.86 |
|   2 |   VIEW                          | VW_DAG_0   |      1 |      1 |  77961 |00:00:01.28 |
|   3 |    HASH GROUP BY                |            |      1 |      1 |  77961 |00:00:01.01 |
|   4 |     NESTED LOOPS                |            |      1 |        |  77989 |00:00:01.00 |
|   5 |      NESTED LOOPS               |            |      1 |      1 |  77989 |00:00:00.58 |
|*  6 |       TABLE ACCESS FULL         | DRIVER     |      1 |      1 |  77989 |00:00:00.07 |
|*  7 |       INDEX UNIQUE SCAN         | PVAL_KEY   |  77989 |      1 |  77989 |00:00:00.21 |
|   8 |      TABLE ACCESS BY INDEX ROWID| TEST_TABLE |  77989 |      1 |  77989 |00:00:00.22 |
----------------------------------------------------------------------------------------------
                                                                                                

Since the Optimizer is only expecting 1 row to come back from the table DRIVER, it does an index lookup on TEST_TABLE, for every row. Notice the actual number of rows is the full table.

Now lets look at the cost of this index lookup.

select  /*+ GATHER_PLAN_STATISTICS */  count(distinct a.capture_time)
from test_table a,
     driver b
where a.pval=b.pval
and b.instance_number < 20

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.84       0.85          0      83932          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.84       0.85          0      83932          0           1

OK.  so the cost of the index lookup row-by-row is .85 seconds elapsed time.

Now after analyzing the DRIVER table, you can see the plan changed to a FTS.

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT                                                                                                                           
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.16 |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.16 |
|   2 |   VIEW                | VW_DAG_0   |      1 |  76723 |  77961 |00:00:00.40 |
|   3 |    HASH GROUP BY      |            |      1 |  76723 |  77961 |00:00:00.23 |
|*  4 |     HASH JOIN         |            |      1 |  76723 |  77989 |00:00:01.23 | 
|*  5 |      TABLE ACCESS FULL| DRIVER     |      1 |  76723 |  77989 |00:00:00.22 |
|   6 |      TABLE ACCESS FULL| TEST_TABLE |      1 |  79110 |  77989 |00:00:00.22 |
------------------------------------------------------------------------------------
                                                                                              

Notice the actual rows, and estimates match.  You can also see it is a FTS.

Now for the run time stats with the FTS.

select  /*+ GATHER_PLAN_STATISTICS */  count(distinct a.capture_time)
from test_table a,
     driver b
where a.pval=b.pval
and b.instance_number < 20

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.15       0.15          0       1390          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.15       0.15          0       1390          0           1

     

WOW.. look at that the FTS took .15 seconds compared to .85 seconds.

The bottom line is.. The next time you talk to developers, and they think their query should be fast because they are using an index, look deeper.  The biggest clue is to look at the Estimate vs Actual for the plan.  The index might not be what you want. FTS's can be good.

Big Data and Privacy

I am writing an editorial (which is unusual for me). This was caused by a conversation I had with my Dad about how the world is changing with big data, and how much retiree's (like him) should know.  So here goes.

Dear Dad,

  I know you are involved in college education for Older folks that have a passion to learn.  An interesting topic would be "Big Data".  For someone outside the IT field, I would tell you that Big Data describes the plethora of new data that is generated in today's society.
Where does it come from ??

• Logs from webservers 
• Cell phones (including location data).
• Search data
• Medical data
• Machine generated data (like from your computer in your car)
• Sales data

All this can be tied together in new ways, that most people didn't think were possible years ago.  My favorite example is... You are walking in the mall past past a store, and you get a text on your phone.  The store's computer system has texted you a 30% offer for a new sweater, good for 2 hours.  The store knows ...

• Your location from phone
• That you have a ski trip planned for the next week, from your search history, and purchase history
• That you are looking for a new sweater from your facebook, and twitter posts.

Amazing huh ?

All these things can open up miraculous possibilities. The day may come when your doctor calls you to schedule a preventive test.  Medical history gathered from a large group of people has shown that others with a similar medical history as yours have had a issue that can be tested for and prevented. Wow. really amazing things.

They can predict something that may go wrong on your car given all the information the computer has gathered.

Target (the department store), has even predicted when a woman is pregnant based on purchase history.. 

All these things are amazing possiblities, but they are also scary.

I find this topic very exciting, but I'm sure for a lot of people these ideas are very scary. Where do you cross the line and enter privacy issues.

This is going to be a interesting battle. Who ones all this data ? what data is public, and what data is private ?

But the most interesting thing is what can be done with the data....

Me, I'm an optimist so I can all the good that can come out of all this new data.

At the very least, the students would at least have a better idea what is going on in the world (behind the scenes), and they would understand why Target things "grandma" is pregnant.

ODI Monitoring scripts

I have included some usefule ODI monitoring scripts (if you want the abridged version of this blog post).

I haven't been blogging in a while (it's been crazy), but I wanted to share some information on ODI.

I have been working on trying to monitor ODI (Oracle Data Integrator).  ODI is a somewhat recent Oracle purchase, and it has a client GUI that is used by the developers.

Me (like many of you), are DBA's, and we want to go into the database to see what is happening.  We eithor don't have access to the GUI, or we don't want access. 

ODI is a great tool used for transforming data.  It can be used to build sql statments that are executed directly in the database.  This makes it a bit different from a tool like Datastage that runs sql remotely.

Here is the first sql I was able to come up with.  It will tell you information about the load plans that have been run. You need to qualify the tables with the owner of the ODI repository..

SELECT SLI.I_LP_INST AS "Load Plan Instance #"
     , SLR.NB_RUN AS "Load Plan Run #"
     , SLI.LOAD_PLAN_NAME AS "Load Plan Name"
     , SLR.CONTEXT_CODE AS "Source System"
     , SLR.STATUS AS "Load Plan Status"
     , SLR.RETURN_CODE AS "error code"
     , CASE WHEN SLR.END_DATE IS NULL 
            THEN TRUNC(ROUND((NVL(SLR.END_DATE , SYSDATE) - SLR.START_DATE)*86400) / 3600) || ':' || 
                 LPAD(TRUNC(MOD(ROUND((NVL(SLR.END_DATE , SYSDATE) - SLR.START_DATE)*86400), 3600) / 60), 2, 0) || ':' || 
                 LPAD(MOD(ROUND((NVL(SLR.END_DATE , SYSDATE) - SLR.START_DATE)*86400), 60), 2, 0)
            ELSE TRUNC(SLR.DURATION / 3600) || ':' || LPAD(TRUNC(MOD(SLR.DURATION, 3600) / 60), 2, 0) || ':' || LPAD(MOD(SLR.DURATION, 60), 2, 0) 
       END AS "Load Time"
     , SLR.START_DATE
     , SLR.END_DATE
     , substr(to_char(SLR.START_DATE,'mm/dd/yy:hh24'),1,11) start_date_hour
  FROM SNP_LP_INST SLI
  JOIN SNP_LPI_RUN SLR ON SLI.I_LP_INST = SLR.I_LP_INST
where 'JRNL_LOAD'=sli.load_plan_name

I was able to include this in an apex report, and it display some of the history.

The next SQL igives you detail of the scenarios, for the load plan. The input is the Load plan

SELECT SLI.Load_plan_name as "Load Plan Name",
      SUBSTR(SLR.CONTEXT_CODE, 9, 5) AS "Source System",
      SLS.LP_STEP_NAME AS "Target Table",
      SLS.scen_name as "scenario name",
     TRUNC(SUM(SSTL.TASK_DUR) / 3600) || ':' ||
             LPAD(TRUNC(MOD(SUM(SSTL.TASK_DUR), 3600) / 60), 2, 0) || 
            ':' || LPAD(MOD(SUM(SSTL.TASK_DUR), 60), 2, 0) AS "Load Time"
     , SST.SESS_NO AS "Session Number"
     , SLSL.start_date as "Start Time"
     , SLSL.End_date as "End Time"
     , sum(sstl.nb_ins) as "Rows Inserted"
     , sum(sstl.nb_upd) as "Rows Updated"
     , sum(sstl.nb_del) as "Rows Deleted"
     , sum(sstl.nb_err) as "Rows Errors"
     , case 
        when (sum(sstl.nb_ins) + sum(sstl.nb_upd)) > 0 then trunc(sum(sstl.task_dur)/(sum(sstl.nb_ins) + sum(sstl.nb_upd)) ,4)
        else 0
       end as "Throughput"
  FROM SNP_LP_INST SLI
  JOIN SNP_LPI_STEP SLS 
                  ON SLI.I_LP_INST = SLS.I_LP_INST
  JOIN SNP_LPI_STEP_LOG SLSL
        ON SLS.I_LP_STEP = SLSL.I_LP_STEP
      AND SLS.I_LP_INST = SLSL.I_LP_INST
  JOIN SNP_SESS_TASK SST
            ON SST.SESS_NO = SLSL.SESS_NO
  JOIN SNP_SESS_TASK_LOG SSTL
            ON SSTL.SCEN_TASK_NO = SST.SCEN_TASK_NO 
           AND SST.SESS_NO = SSTL.SESS_NO
  JOIN SNP_LPI_RUN  SLR 
            on SLI.I_LP_INST = SLR.I_LP_INST
 WHERE (1=1)
  AND SLSL.I_LP_INST = :P6_SCENARIO
   AND SLS.LP_STEP_TYPE = 'RS'
--   AND SLSL.STATUS IN ('M','D')
 GROUP BY SUBSTR(SLR.CONTEXT_CODE, 9, 5),
           SLSL.start_date,SLSL.end_date,SLI.load_plan_name,
          SLS.scen_name,SLS.LP_STEP_NAME, SST.SESS_NO

Finally, this is the last query. This query takes the task number as an input, and will display the detail for all the tasks contained in a scenario.

SELECT SST.TASK_NAME2 AS "Session Name"
     , SST.TASK_NAME3 AS "Task Name"
     , CASE WHEN SSTL.TASK_END IS NULL 
            THEN TRUNC(ROUND((NVL(SSTL.TASK_END , SYSDATE) - SSTL.TASK_BEG)*86400) / 3600) || ':' || 
                 LPAD(TRUNC(MOD(ROUND((NVL(SSTL.TASK_END , SYSDATE) - SSTL.TASK_BEG)*86400), 3600) / 60), 2, 0) || ':' || 
                 LPAD(MOD(ROUND((NVL(SSTL.TASK_END , SYSDATE) - SSTL.TASK_BEG)*86400), 60), 2, 0)
            ELSE TRUNC(TASK_DUR / 3600) || ':' || LPAD(TRUNC(MOD(TASK_DUR, 3600) / 60), 2, 0) || ':' || LPAD(MOD(TASK_DUR, 60), 2, 0) 
       END AS "Load Time"
     , substr(sst.def_context_code,9,5) "Context"
     , SSTL.TASK_BEG AS "Start Time"
     , SSTL.TASK_END AS "End Time"
     , SSTL.NB_DEL AS "Rows Deleted"
     , SSTL.NB_UPD AS "Rows Updated"
     , SSTL.NB_INS AS "Rows Inserted"
     , SSTL.NB_ERR AS "# Of Errors"
     , SST.SESS_NO 
     , sst.scen_task_no
/* UNCOMMENT TO GET SQL EXECUTED FOR THIS STEP */
  FROM SNP_SESS_TASK SST,
      SNP_SESS_TASK_LOGv SSTL 
 WHERE (1=1)
   AND SST.SESS_NO =:P7_TASK   
   AND SSTL.TASK_STATUS IN ('D','M','R')
   AND SSTL.SCEN_TASK_NO = SST.SCEN_TASK_NO 
   AND SST.SESS_NO = SSTL.SESS_NO
 

Anayzing query Cost example

Well,
  Here is the issue I've been dealing with.  The query cost doesn't stay consistent, and I was wondering if a profile would help keep it stable, and if it keeps things better or worse.

  I'm sure you have all run into this.  For some reason your cardinality can't be well estimated, the optimizer changes plans on you, and you want to know if a profile will help or hurt.

  The good news I had the perfect test case.  The query was part of a data load, and there was a driving table with a varying number of rows in it.  This is how I went about analyzing.

First I created a table to store the results.

create table bgrenn.mytable
 (ROW_COUNT                                          NUMBER,
 PLAN_HASH_VALUE                                    VARCHAR2(15)
 COST                                               number);

Next I took a copy of my driving table ( a full size table). I used this to create a smaller copy of the table.

declare


row_count number:= 0;
v_statement_id varchar2(10);
v_plan_hash_value varchar2(15);
v_cost number;
v_statement varchar2(4000);

begin

for row_count in 0..100000  loop


execute immediate 'drop table BGRENN.TMP_DRIVER purge';
execute immediate 'create table   BGRENN.TMP_DRIVERT as select * from  BGRENN.TMP_DRIVERB where rownum<' ||  to_char(row_count,'9999999');

dbms_stats.gather_table_stats('BGRENN','TMP_DRIVERT');

v_statement_id := to_char(row_count,'99999');

v_statement := 'explain plan SET STATEMENT_ID = ''' || v_statement_id || '''  for ' ||
'select   * ' ||
' from BGRENN.TMP_DRIVERT TMP_DRIVERA  ' ||
' INNER JOIN BGRENN.TABA TABA ON TMP_DRIVERA.ID=TABA.ID  ' ||
' INNER JOIN BGRENN.TABB ON TABA.GCC_ID=TABA.ID AND TABA.LOCN <> 8031431  ' ||
' INNER JOIN BGRENN.TABC TABC ON TABA.ID=TABC.ID AND TABC.id = 1168583  ' ||
' INNER JOIN BGRENN.TABD TABD ON TABA.ID=TABD.ID  ' ||
' INNER JOIN BGRENN.TABE TABE ON TABD.ID=TABE.ID  ' ||
' INNER JOIN BGRENN.TABF TABF ON TABD.ID=TABF.ID  ' ||
' INNER JOIN BGRENN.TABG TABG ON TABF.ID=TABG.ID  ' ||
' INNER JOIN BGRENN.TABH TABH ON TABG.ID=TABH.ID AND TABH.SEQ_NBR < 500  ' ||
' INNER JOIN BGRENN.TABI ON TABC.ID=ID  ' ||
' INNER JOIN BGRENN.TABJ TABJ ON TABC.ID=TABJ.ID  ' ||
' INNER JOIN BGRENN.TABK TABK ON TABJ.ID=TABK.ID and TABK.id in ( 1221589, 1219009, 1191882, 1221590, 1171956)  ' ||
' LEFT OUTER JOIN ERD.TABL TABL ON TABH.ID=TABL.ID  ' ||
' LEFT OUTER JOIN ERD.TABM TABM ON TABE.ID=TABM.ID  ' ||
' where (1=1)';

dbms_output.put_line(v_statement);

execute immediate v_statement;



SELECT substr(plan_table_output,18,12) into v_plan_hash_value  FROM TABLE(dbms_xplan.display(statement_id => v_statement_id)) where rownum <2;
select cost into v_cost from plan_table where id=0 and rownum<2 and statement_id=v_statement_id;

insert into bgrenn.mytable values(row_count,v_plan_hash_value,v_cost);

delete from plan_table where statement_id=v_statement_id;
commit;
end loop;

end;
/                                            number);

This produced a set of rows in the table with the cost.

I then copied the table, installed a profile and reran.

After joining the 2 tables on row count I created an "r" program and analyzed the results.
Here is the program.

# open Libarary psych for functions
library(psych)

#  open file
query_data <- read.table("c:/r/data/query_output.txt", header=T)

#what are the variables
describe (query_data)


pdf("c:/r/data/querydata.pdf")
plot(query_data$ROW_COUNT,query_data$orig_cost,type='l',col="red")
lines(query_data$ROW_COUNT,query_data$new_cost,type='l',col="green")
dev.off()

And here is the output ..  The red is the orginal plan, and the green is the plan with the profile. I can see that the cost of the profile plan remains more consistent. and is probably a better choice.

"enq: CR - block range reuse ckpt" and the recycle bin

I decided to write this blog entry because we ran into an issue over the weekend.  The process that normally runs fine, was running slow.. "like molasses" was the comment from one of the folks.  After digging into an AWR report I found one of the top waits for the sql was


enq: CR - block range reuse ckpt

OK.. what is that, and why ?   I didn't find a whole lot except that it indiciated contention on blocks from multiple processes trying to update blocks.. hmmm..

I looked further in the AWR and saw the top Segment for Logical reads was "RECYCLEBIN$"

and one of the top queries was.

"delete from RecycleBin$ where bo=:1"


Well since the process was finished I did my own test.. I created a tablespace and created an object in it.. dropped the object, added the to table, then dropped the table.. over and over again, until the number of objects in  the tablespace remained constant.  I then created the table again, and started to let it grow (so it would have to free up the recycle bin to get space)... And what I saw in the top 5 wait events was again.....

enq: CR - block range reuse ckpt

I wanted to document this for others that may hit this.. If a search for this wait event brought you to my blog, Please check your recycle bin and make sure that it isn't cleaning itself out to make room causing this wait event ...