Beiträge getaggt mit Performance Tuning
Retrieve Sessions with divergent Optimizer Settings using V$SES_OPTIMIZER_ENV
Just a little snippet from my present Performance Tuning course in Munich that you may find useful:
There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like
SQL> set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id=b.id
join v$session c on a.sid=c.sid
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP')
order by a.sid,a.name;
SID USERNAME NAME VALUE
---------- ---------- ---------------------------------------- ----------------------------------------
22 SCOTT workarea_size_policy manual
267 ADAM optimizer_mode first_rows
Probably of interest especially if one of these users complains about performance and we want to know what is different with their sessions 🙂
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 🙂
DIFF_TABLE_STATS_IN_HISTORY Example
In 10g, we introduced the automatic retention of old Optimizer Statistics. We might want to compare the Optimizer Statistics, presently in use with the old stats. This posting gives an example that shows how to do that. The Online Documentation for the function has no example for that – probably it is considered too trivial 🙂
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> connect scott/tiger Connected. SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd:hh24:mi:ss'; Session altered. SQL> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss'; Session altered. SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT'; NUM_ROWS LAST_ANAL ---------- ---------
There are no Optimizer Statistics yet for the DEPT table of SCOTT. We change that:
SQL> exec dbms_stats.gather_table_stats('SCOTT','DEPT')
PL/SQL procedure successfully completed.
SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT';
NUM_ROWS LAST_ANALYZED
---------- -------------------
4 2012-04-23:16:14:38
This is my demo setup. Now we have some DML on the table and gather statistics afterwards:
SQL> insert into dept values (50,'TEST','TEST');
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SCOTT','DEPT')
PL/SQL procedure successfully completed.
SQL> select num_rows,last_analyzed from user_tables where table_name='DEPT';
NUM_ROWS LAST_ANALYZED
---------- -------------------
5 2012-04-23:16:17:42
The above stats are actually in use, while the old stats got recorded. They show up in dba_tab_stats_history with the time they got stored there:
SQL> select table_name,stats_update_time from user_tab_stats_history;
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
DEPT 2012-04-23:16:14:38
DEPT 2012-04-23:16:17:42
I got two rows above; the first represents the NULL stats before the first gather, the second row are the stats before the second gather. We can now compare the present stats with the old stats in this way:
SQL> select * from table(dbms_stats.diff_table_stats_in_history(
ownname => user,
tabname => upper('&tabname'),
time1 => systimestamp,
time2 => to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'),
pctthreshold => 0));
Enter value for tabname: dept
old 3: tabname => upper('&tabname'),
new 3: tabname => upper('dept'),
Enter value for time2: 2012-04-23:16:17:42
old 5: time2 => to_timestamp('&time2','yyyy-mm-dd:hh24:mi:ss'),
new 5: time2 => to_timestamp('2012-04-23:16:17:42','yyyy-mm-dd:hh24:mi:ss'),
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : DEPT
OWNER : SCOTT
SOURCE A : Statistics as of 2012-04-23:16:25:16
SOURCE B : Statistics as of 2012-04-23:16:17:42
PCTTHRESHOLD : 0
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
DEPT T A 5 8 18 5
B 4 4 20 4
I had to shorten and adjust the above report to make it look nice on WordPress – there is also a section about Column Statistics in it that I left out here.
Conclusion: We can not only restore old Optimizer Statistics easy, we can also compare the old stats with the present stats beforehand.
Pickleball spielen auf YouTube
Pickleball spielen 