Beiträge getaggt mit 11g New Features
Adaptive Cursor Sharing
I am back in Germany, teaching an 11g Performance Tuning course in Munich. There are several New Features in the 11g version related to Performance Tuning, one of them is Adaptive Cursor Sharing.
It adresses the problem that can occurr in earlier versions if you use bindvariables together with indexes on columns that have skewed values. Typically, you create histograms on skewed columns to notify the optimizer of the skew, so that it can use an index if rare values are asked and make a FTS if values with a low selectivity are used.
Bindvariables used to cross that approach – even Bind Peeking did not always resolve the issue, because the first content of the peeked bindvarible determined all future execution plans.
With 11g, this behaviour has changed. In other words: You can have multiple execution plans now with the same statement that uses bindvariables (with different contents). You may find a very nice explanation of this 11g New Feature from the guys who invented it here.
Pending Statistics
Back again in Munich after a short weekend at home, I am now teaching an 11g New Features course. As it happens frequently, one of the students already attended a previous course with me being the instructor. It is always nice to see friendly and somehow familiar looking faces in the auditorium 🙂
One of the minor but nevertheless interesting 11g New Features is the possiblity to defer the publishing of gathered optimizer statistics for productive usage. One reason for doing so might be that you want to make sure, you have gathered all the statistics of your (very big) segments, before publishing them as a whole. Another possible reason might be that you suspect the new statistics could cause bad performance and therefore, you first want to test them before using them productively. The following little demonstration, I have developed for the present course:
---------------------------
-- Demo Pending Statistics
---------------------------
connect system/oracle
drop user psuser cascade;
create user psuser identified by psuser
default tablespace users quota unlimited on users;
grant create session,create table to psuser;
create table psuser.customers
(cust_id number,name varchar2(10));
create table psuser.products
(prod_id number,product varchar2(10));
create table psuser.sales
(cust_id number ,prod_id number,
buy_date date, price number (10,2));
insert into psuser.customers values (1,'John Doe');
insert into psuser.products values (42,'Oracle EE');
insert into psuser.products values (43,'Oracle SE');
insert into psuser.sales values (1,42,sysdate,9999.98);
insert into psuser.sales values (1,43,sysdate,999.98);
insert into psuser.sales values (1,43,sysdate,998.98);
commit;
-----------------------------------------
-- Statistics for the psuser schema will
-- not be published immediately:
-----------------------------------------
exec dbms_stats.set_schema_prefs('psuser','publish','false')
exec dbms_stats.gather_schema_stats('psuser')
select table_name,num_rows from dba_tables
where owner='PSUSER';
select table_name,num_rows from dba_tab_pending_stats
where owner='PSUSER';
---------------------------------------------------
-- The gathered Statistics are not being used yet:
---------------------------------------------------
connect psuser/psuser
set autotrace on explain
select name,product,price from psuser.sales
natural join psuser.products
natural join psuser.customers;
---------------------------
-- We may first test them:
---------------------------
connect system/oracle
alter session set optimizer_use_pending_statistics=true;
set autotrace on explain
select name,product,price from psuser.sales
natural join psuser.products
natural join psuser.customers;
----------------------------------------------
-- After successful testing, we publish them:
----------------------------------------------
exec dbms_stats.publish_pending_stats('psuser',null)
Flashback Part 2 11g Addendum
As mentioned before, we can query the content of a table from the past (since 9i with flashback query) and even reinstate the past content of that table (since 10g with flashback table to timestamp) by making additional use of before images stored in the undo tablespace. Of course, this is only possible as long as these before images in the undo tablespace are not overwritten by new ones.
A new feature of the 11g version is to safe the information from the before images for selected tables, thereby enabling a flashback query or flashback table to timestamp for these selected tables even years after their modification. That is called Total Recall and works like this:
SQL> create flashback archive fla1 tablespace tbs1 retention 10 year;
SQL> alter table hr.departments flashback archive fla1;
You will now be able to do a flashback query for the departments table in 10 years until today and to any time in between!
Pickleball spielen auf YouTube
Pickleball spielen 