10 x 10=100. Larryisnm's from oow11

I wanted to put down my impressions on the big announcements at openworld this year. First is Exalytics.. Analyitics at the speed of throught. This is an intriguing product, and I can definately see the uses for applications where real time analytics is key. I think for most of us, this appliance is going to be out of our range. I know I don't know of any business cases. No prices was mentioned either. Second was EM grid 12c. Now this was pretty impressive. I was surprised on all the enhancements that was put into it. It really seems to do a nice job of centralized for Cloud support. I was especially impressed with the virtualization pieces. The provisioning, and support of virtual environments is a great component. It is also a very big carrot for those companies turning to virtualization, and aren't sure whether to choose VMware or Oracle VM. Big Data Appliance. - This one I am waiting to see more specs on. it looks interesting, but what is the licensing model ? I can't believe that there is no software licensing (other than OS pieces). All these are interesting announcments, but I think the 12c features was the most interesting to me. IF only the documentation was available I would install it right now.

partitioning Local vs Global

In my last post I talked about creating a function based index on a GTT after my query plans went to hell after partitioning.  Someone asked me to elaborate why my query plans went south.

Well to start with, I deal with very large tables.. Not terribly large (250g 2 billion rows).  We are in the processess of partitioning this data, so we can purge it.. The performance on the data is very good, but we keep eating up disk space.

Seems simple enough right ? partition by date ranges, with some hash partitions thrown in on the column used the most for lookups.  Nice and neat. At this point we have 116 partitions.  Smaller is better right.

Since the whole reason for doing this is being able to purge, we created local indexes on almost all the columns except for the primary key.  Being able to maintain the partitions is critical.

Doing all this I assumed we would be OK with local indexes. The application does index lookups, and the ones that don't use the primary key (or the hash partitioned key) are close to unique.

How long can a lookup take with an index and number of distict values = num_rows.  Easy..

Then the dbreplay came, and the queries were slower.. much slower.. plan was similar but buffer gets was off.

% Total Gets Gets per Exec #Executions Exec Time (ms) per Exec CPU Time (ms) per Exec I/O Time (ms) per Exec Physical Reads per Exec #Rows Processed per Exec #Plans   
SQL Id 1st 1st Total 2nd 2nd Total Diff 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st 2nd 1st/2nd/Both SQL Text 
d76xhcfh5dsrs 0.71 1.42 25.46 50.96 24.75 12,401.18 875,159.54 2,389 2,518 347 2,819 135 2,130 203 351 23.43 82.44 9.34 9.48 1/ 1/ 2 SELECT vpcyd_wrkr_id, vpcyd_cl... 
.

It's hard to see above, but the 2 critical values are 12,401 buffer gets vs 875,159 buffer gets.. and 347 ms/exec vs 2,819 ms/exex

Buffer gets was making a huge difference with my partitioned tables.. Now to dig into the trace file.

Here is the part that really stood out..147,157 cr (buffer block reads), to get 54 rows of data.. wow..

       545        545        545                     PARTITION RANGE ALL PARTITION: 1 29 (cr=147157 pr=19 pw=0 time=1142973 us cost=232 size=0 card=1)
       545        545        545                      PARTITION HASH ALL PARTITION: 1 4 (cr=147157 pr=19 pw=0 time=1126073 us cost=232 size=0 card=1)
       545        545        545                       INDEX RANGE SCAN PIDX_CUST_ID PARTITION: 1 116 (cr=147157 pr=19 pw=0 time=1032020 us cost=232 size=0 card=

I isolated this lookup, and found that it was a "unique" key (it had no duplicate values).. Why would 545 rows of data take all that time? (this was where the time was going).

I created a small query, and did a index lookup for one row and compared partitioned vs non-partitioned.

                SQL_ID       PLAN_HASH_VALUE BUFFER_GETS EXECUTIONS CPU_TIME ELAPSED_TIME AVG_HARD_PARSE_TIME APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME 
Partitioned     gz67xt981w53p  3,540,849,128       7,323          6  472,928      642,455  625,819       0 168,862 
Non-Partitioned gz67xt981w53p    791,655,517          32          6    3,999        4,473    2,847       0 0 


 

Comparing the index partitioned vs non-partitioned, (with 116 subpartitions), you can see the difference. 3,999 ms vs 472,928 ms .  What caused me the biggest issue is that I didn't realize it was doing a nested loop, 54 times.. this made the difference 36,000 ms vs 4,256,352 ms.  116x longer with a local partion vs global. 

Lesson learned was that with partitioning you need to balance performance with maintainability.. Local indexes can be very expensive.  Especially with nested loops.

Why are the developers using functions ?

Hi all,

  I have been working all week on trying to figure out why a query went to hell when we partitioned the tables.  I dug into it, and found one good fix.. But  I can't implement it.

The detail on what happened in my last post.. Keep in mind I found that issue, but working through this one, and moving the bottleneck.

Here is the problem.. They are joining to a GTT (global temporary table), but they are using a function on the column in the table. ARGH.. They are making it impossible for the optimizer to find the best plan.

Here is an example of what's happening...

First here the GTT

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  tmp_strt_dt  date,
  tmp_end_dt   date
) ON COMMIT preserve ROWS;

Here is table and lets load 128 rows of data into it.

create table test_table
(  strt_dt   date,
   end_dt    date,
   col1      varchar(1));

insert into test_table values(sysdate-1000,sysdate+1000,'Y');
insert into test_table select * from test_table;
insert into test_table select * from test_table;
insert into test_table select * from test_table;
insert into test_table select * from test_table;
insert into test_table select * from test_table;
insert into test_table select * from test_table;
insert into test_table select * from test_table;
commit;

Now lets insert into the temporary table, and analyze both tables.

insert into my_temp_table values(sysdate,sysdate);

exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'MY_TEMP_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1');
exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'TEST_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

Now for my query ..

select * from my_temp_table ,test_table 
where "END_DT">=TRUNC("TMP_STRT_DT") AND                                                                              
      "STRT_DT"<=TRUNC("TMP_END_DT");

and the explain plan.. Notice the cardinality of 1, though there are 128 rows that match

Plan hash value: 1231029307

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    34 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |               |     1 |    34 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    16 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |     1 |    18 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("END_DT">=TRUNC(INTERNAL_FUNCTION("TMP_STRT_DT")) AND
              "STRT_DT"<=TRUNC(INTERNAL_FUNCTION("TMP_END_DT")))

So what to do ??? I removed the trunc function, and the cardinality was right...

select * from my_temp_table ,test_table 
where "END_DT">="TMP_STRT_DT" AND                                                                              
      "STRT_DT"<="TMP_END_DT";

Plan hash value: 1231029307

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |   128 |  4352 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |               |   128 |  4352 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    16 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |   128 |  2304 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("END_DT">="TMP_STRT_DT" AND "STRT_DT"<="TMP_END_DT")

Unfortunately, I can't change the code.. How do I get the optimizer to get the right cardinality ?? Function based indexes to the rescue. Here is what I did.. First create the indexes on the 2 columns.

create index my_temp_table_fbi1 on my_temp_table(TRUNC("TMP_STRT_DT"));
create index my_temp_table_fbi2 on my_temp_table(TRUNC("TMP_END_DT"));

Next insert into the table, and gather stats.. Notice that I am using "hidden" column clause.

insert into my_temp_table values(sysdate,sysdate);

exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'MY_TEMP_TABLE',estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 1');

Now to run my query and look at the cardinality.

elect * from my_temp_table ,test_table 
where "END_DT">=TRUNC("TMP_STRT_DT") AND                                                                              
      "STRT_DT"<=TRUNC("TMP_END_DT");

Plan hash value: 1231029307

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |   128 |  6400 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |               |   128 |  6400 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MY_TEMP_TABLE |     1 |    32 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_TABLE    |   128 |  2304 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("END_DT">=TRUNC(INTERNAL_FUNCTION("TMP_STRT_DT")) AND
              "STRT_DT"<=TRUNC(INTERNAL_FUNCTION("TMP_END_DT")))

Notice that the index is not used for the query plan, but by having the index, and gathering statistics, the optimizer is able to figure out the correct cardinality even though a function is used for the column. Problem solved without changing the query.

As always, you can find my script here