Posts Tagged exadata

Accelerated space reclamation on Exadata Cells

During my present Exadata course in Munich I learned that there is an (invisible) default threshold present on the Exadata Cells that triggers “Accelerated space reclamation” when 80 % Filesystem Utilization is reached. This is what I observed on a Cell where I consumed 82 % of space:

CellCLI> list alerthistory detail
name:                   4_1
alertMessage:           "File system "/" is 82% full, which is above the 80% threshold. 
Accelerated space reclamation has started.  
This alert will be cleared when file system "/" becomes less than 75% full."
         alertSequenceID:        4
         alertShortName:         Software
         alertType:              Stateful
         beginTime:              2012-09-12T13:17:53-04:00
         examinedBy:
         metricObjectName:       /
         notificationState:      0
         sequenceBeginTime:      2012-09-12T13:17:53-04:00
         severity:               critical
         alertAction:            "MS includes a file deletion policy that is triggered 
when file system utilitization is high. Deletion of files is triggered when file utilization 
reaches 80%. In particular, the deletion policy is as follows: For the /opt/oracle and 
/var/log/oracle file systems, files in the ADR base directory, metric history directory, and 
LOG_HOME directory will be deleted using a policy based on the file modification time stamp.
 
Files older than the number of days set by the metricHistoryDays attribute value will be 
deleted first, then successive deletions will occur for earlier files, down to files with 
modification time stamps older than or equal to 10 minutes, or until file system utilization 
is less than 75%. The renamed alert.log files and ms-odl generation files that are over 5 MB, 
and older than the successively-shorter age intervals are also deleted. 

Crash files that are over 5 MB and older than one day will be deleted. For the / file system, 
files in the home directories (cellmonitor and celladmin), /tmp and /var/spool directories that 
are over 5 MB and older than one day will be deleted. Try to delete more recent files, or files 
not being automatically purged, to free up space if needed."

 

I have not seen that documented, so I thought it might be of interest for the Exadata community. The threshold does not show, though:

CellCLI> list threshold
--Nothing here --

The mentioned metricHistoryDays attribute is documented and defaults to 7 days, which can be modified like this:

CellCLI> list cell attributes metricHistoryDays
         7

CellCLI> alter cell metricHistoryDays=3
Cell qr01cel01 successfully altered

As always: Don’t believe it, test it! 🙂

9 Comments

No Smart Scan with AUTO_SAMPLE_SIZE

During the Exadata Database Machine Admin Workshop that I deliver this week in Munich, I got the question whether the gathering of Optimizer Statistics could be done via Smart Scan on Exadata. I didn’t know that and that particular topic seems not to be covered much yet in the docs or other Blogs that I researched briefly. Therefore I came up with this little test case here:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=1000000000;

Session altered.

SQL> create table sales as
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=1e7;

Table created.

We go on with the recommended method to gather statistics and compare that with 30 % and 100 % sample size:

SQL> connect adam/adam
Connected.
SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan                0

SQL> exec dbms_stats.delete_table_stats('ADAM','SALES')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>30)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan                0

SQL> exec dbms_stats.delete_table_stats('ADAM','SALES')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>100)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan       477.690498

Conclusion: It is possible to gather Optimizer Statistics via Smart Scan, but only with a 100 % sample size. Now the question is, whether AUTO_SAMPLE_SIZE will gather statistics faster although running without Smart Scan. The quality of the resulting stats will be very similar in both cases. If you are using ESTIMATE_PERCENT < 100 presently, consider to change to AUTO_SAMPLE_SIZE or 100 % on Exadata, as this may complete faster there with Smart Scan than your non-AUTO_SAMPLE_SIZE value does. As always: Don’t believe it, test it 🙂

 

,

14 Comments

The making of Training On Demand

This week, my Oracle University course Exadata Database Machine Administration Workshop in Düsseldorf got recorded for our new delivery format Training On Demand (TOD). I write this to give you an impression how that is done and what TOD means. In essence, we take a live class as it is delivered in an Oracle University education center to the attendees and record it. Plans are to do a lot of these recordings also in local language, which was German in this case.

In order to enable TOD, we had to plug in a special video card into the instructors machine that can send the monitor signal directly to the camera. My Delivery Manager Theo and I did that on the Friday before the course – long time ago that I opened a PC for maintenance. This turned out to be a mistake, because the (Linux-based) course module did not build correctly. When the video crew arrived from the US on Sunday, they could not do the setup for the teacher PC therefore. We had to plug the card out and trigger again the course module build, which takes extremely long for this particular course. We needed to continue the setup including the plugin on Monday (was incidentally a holiday in Germany). On Tuesday we were prepared to record when the course started.

The attendees have been notified before that this course will have a video crew inside recording and they needed to sign a paper that they agree with this. The video crew takes care that none of the attendees shows up on video and their questions are not recorded either. Only the instructor gets a microphone. I needed to repeat the questions during my answers so that the TOD attendees later on know what I am talking about. Also I took care to do better readable sketches on the whiteboard. Apart from that, it was to me not so different from an ordinary course. I do pretty much self-reflection and self-control anyways, so it was not really an effort for me to take care about my words, knowing they will be recorded…

The attendees at least were quite pleased with the course and gave us a 100% delivery score for it. Below you see a picture that I took during the course. It shows (left to right) Toine (Senior Marketing Programs Manager – Self Study & Certification), Nicole (Marketing Director OU EMEA), David and Dan (Video Crew).

You can tell from the smiling faces that this shooting was done in a very pleasant atmosphere – would do it again anytime with these guys 🙂

Addendum: See here the now available ToD course that was made from the recording.

,

2 Comments

%d bloggers like this: