A quick little post, but I think this is an important one as most people have upgraded/plan to upgrade to 12c
Oracle performance can be impacted by Oracle itself. People are often too quick to jump to the conclusion that bad performance is the result of a bug. That does not mean that there aren't situations where bugs do occur.
In 12c I have seen a couple of instances where bugs are using a noticable amount of system time. This consumes CPU cycles and potentially takes away time from other processes.
Looking at top statements and top wait events can lead to identifying potential bugs. With the 12c bugs being encountered they can be seen using AWR, ASH, or queries against system views to identify the top statements by Elapsed Time or other key metrics.
Once these key consumers of time are identified it is worth the effort to perform a quick search on My Oracle Support to see if there are known issues associated with this issue. There may be a patch or workaround identified that will resolve the bug or "performance feature".
A couple of examples seen recently are related to SQL Ids frjd8zfy2jfdq and 47r1y8yn34jmj. There are a couple of 12c patches which can improve this activity.
See
Frequent Execution of Recursive Sql Metrics Collector SQL_ID "frjd8zfy2jfdq" in 12.1.0.2 (Doc ID 2059121.1)
Higher CPU and Slow Performance after Migration to 12c with Top Sql as SELECT DEFAULT$ FROM COL$ WHERE ROWID=:1 (Doc ID 2013851.1)
Friday, December 16, 2016
Wednesday, October 12, 2016
Don't Forget System Level Statistics
If you do any work with performance tuning, you know the importance of Object Level statistics. Oracle uses this information to estimate how many rows will be returned by different steps in the plan. These estimates help the Optimizer form what it thinks is the best plan.
There are numerous subtopics that can have large impacts on SQL Statements. One statistics related item that is often overlooked is System Level Statistics.
When I talk about system level statistics I am referring to three different things:
1. Fixed object statistics
2. Data Dictionary statistics
3. System stats.
If these items are not kept up to date, then you can see some performance degradation. Oracle has several blog posts detailing the importance of these statistics. These statistics should be updated following upgrades to the database software. The system statistics should be updated following any hardware changes.
You can check the status of the fixed object statistics using the following query:
SELECT owner
, table_name
, last_analyzed
FROM dba_tab_statistics
WHERE table_name IN
(SELECT name
FROM v$fixed_table
WHERE type = 'TABLE'
)
ORDER BY last_analyzed NULLS LAST;
Fixed Objects are the X$ objects defined in the database. These objects are not owned by regular users, so gathering stats on user level objects will not impact these objects. There is a different command, EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, that will gather stats on these objects. The statistics should be run while the system is performing a normal workload. If there are significant updates to the workload then the statistics should be regathered. Also, the statistics should be regathered following any upgrades.
For more information about the importance of fixed objects can be seen here:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why
You can check the status of data dictionary statistics with the following query:
SELECT owner
, table_name
, last_analyzed
FROM dba_tab_statistics
WHERE owner='SYS'
ORDER BY last_analyzed NULLS LAST;
The command to update these statistics is DBMS_STATS.GATHER_DICTIONARY_STATS.
At a minimum this should be run after every database upgrade. To keep these statistics up to date, you may choose to run the gather command more frequently such as every quarter.
For additional information you can also look at the My Oracle Support Document, How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (Doc ID 457926.1)
Warning! There is a case where a bug caused the command to collect inaccurate information. Oracle bug 9842771 impacted the system statistics collected in release 11.2.0.2. So it is advised to review the values before and after collecting.
To check the system statistics use the following query
select *
from sys.aux_stats$;
These fixed object and system statistics provide a foundation that is used by the optimizer to impact many different statements. If you are collecting user level objects but not keeping the other statistics up to date then you are putting yourself at risk for poor performance.
There are numerous subtopics that can have large impacts on SQL Statements. One statistics related item that is often overlooked is System Level Statistics.
When I talk about system level statistics I am referring to three different things:
1. Fixed object statistics
2. Data Dictionary statistics
3. System stats.
If these items are not kept up to date, then you can see some performance degradation. Oracle has several blog posts detailing the importance of these statistics. These statistics should be updated following upgrades to the database software. The system statistics should be updated following any hardware changes.
Fixed Object Statistics
This is different from user level object statistics. For example if you are querying the EMP and DEPT tables then you usually will want to have accurate statistics on the tables which reflect the data distribution of values in the columns of those tables. I say usually because there may be exceptions to this rule.You can check the status of the fixed object statistics using the following query:
SELECT owner
, table_name
, last_analyzed
FROM dba_tab_statistics
WHERE table_name IN
(SELECT name
FROM v$fixed_table
WHERE type = 'TABLE'
)
ORDER BY last_analyzed NULLS LAST;
Fixed Objects are the X$ objects defined in the database. These objects are not owned by regular users, so gathering stats on user level objects will not impact these objects. There is a different command, EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, that will gather stats on these objects. The statistics should be run while the system is performing a normal workload. If there are significant updates to the workload then the statistics should be regathered. Also, the statistics should be regathered following any upgrades.
For more information about the importance of fixed objects can be seen here:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why
Data Dictionary Statistics
Similar to the fixed objects, data dictionary statistics are also used by the system to build plans for system queries. If these are not kept up to date you will see some performance issues related to queries against those objects.You can check the status of data dictionary statistics with the following query:
SELECT owner
, table_name
, last_analyzed
FROM dba_tab_statistics
WHERE owner='SYS'
ORDER BY last_analyzed NULLS LAST;
The command to update these statistics is DBMS_STATS.GATHER_DICTIONARY_STATS.
At a minimum this should be run after every database upgrade. To keep these statistics up to date, you may choose to run the gather command more frequently such as every quarter.
For additional information you can also look at the My Oracle Support Document, How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (Doc ID 457926.1)
System Statistics
The system statistics collect information about the hardware, in particular CPU performance and I/O performance. The Oracle optimizer will use this information to determine the cost of a statement. If this data is not accurate then it can result in suboptimal plans. This data is collected with the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS. As part of any hardware changes or database upgrades, these statistics should be recollected. Note that if you are running on Exadata, you should use the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’).Warning! There is a case where a bug caused the command to collect inaccurate information. Oracle bug 9842771 impacted the system statistics collected in release 11.2.0.2. So it is advised to review the values before and after collecting.
To check the system statistics use the following query
select *
from sys.aux_stats$;
These fixed object and system statistics provide a foundation that is used by the optimizer to impact many different statements. If you are collecting user level objects but not keeping the other statistics up to date then you are putting yourself at risk for poor performance.
Monday, June 13, 2016
Fun with SQL Translation Framework
An interesting feature in 12c that is not talked about a lot is SQL Translation Framework. Kerry Osborne has an excellent blog post on this feature that should also be reviewed.
The advertised purpose of this software component is to allow statements written for another database to be translated into Oracle standard SQL. This will aid in migrations of programs from other database systems. In fact there is some relation with Oracle SQL Developer and its application scanner scripts in order to facilitate this sort of activity.
The SQL Translation Profile is a database object that contains the non-Oracle statements along with their translations. The profile can also change Oracle standard statements to different statements. I'll focus on the second feature of the component as it provides some interesting results.
Before parsing, the Translation framework will replace the text. There are some translations that are invalid, we'll look at one of those below as well.
To use this component the framework needs to be enabled. The session then needs to be altered to allow for the framework to be used. The steps are outlined as follows. A framework will be created and then access will be granted to a database user, OP. After the setup is completed some translations will be created.
exec dbms_sql_translator.create_profile('pj_test');
grant all on sql translation profile pj_test to op;
alter session set sql_translation_profile=pj_test;
alter system flush shared_pool;
alter session set events = '10601 trace name context forever, level 32';
If there are any issues with the setup you will see messages like the following:
ORA-24252: SQL translation profile does not exist
24252. 00000 - "SQL translation profile does not exist"
*Cause: An attempt was made to access a SQL translation profile that
either did not exist or for which the current user did not have
privileges on.
*Action: Check the SQL translation profile name and verify that the
current user has appropriate privileges on the SQL translation
profile.
You can change the statement slightly or you can access a totally different query by assigning a translation.
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 1 from op.vc',
translated_text => 'select 42 from op.vc');
end;
/
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 2 from op.vc',
translated_text => 'select * from op.dept');
end;
/
Now we can see the output
SQL> select 1 from op.vc;
42
----------
42
1 rows selected
SQL> select 2 from op.vc;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected
If I look in the data dictionary to see what has been run (factoring out some other unrelated queries) I see the following translations in place, but not the original statements (e.g. select 1 from op.vc).
SQL> select sql_text from v$sql where sql_text like 'select%from op.%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from op.dept
select 42 from op.vc
You can imagine that this could be quite a security risk, if somebody were to translate a select statement into a delete/update/insert. Fortunately that type of translation is not allowed. For example, when I translate a select into a delete
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 3 from op.vc',
translated_text => 'delete from op.dept where deptno=10');
end;
/
Running the statement gives an error.
select 3 from op.vc;
ORA-00900: invalid SQL statement
00900. 00000 - "invalid SQL statement"
*Cause:
*Action:
The advertised purpose of this software component is to allow statements written for another database to be translated into Oracle standard SQL. This will aid in migrations of programs from other database systems. In fact there is some relation with Oracle SQL Developer and its application scanner scripts in order to facilitate this sort of activity.
The SQL Translation Profile is a database object that contains the non-Oracle statements along with their translations. The profile can also change Oracle standard statements to different statements. I'll focus on the second feature of the component as it provides some interesting results.
Before parsing, the Translation framework will replace the text. There are some translations that are invalid, we'll look at one of those below as well.
To use this component the framework needs to be enabled. The session then needs to be altered to allow for the framework to be used. The steps are outlined as follows. A framework will be created and then access will be granted to a database user, OP. After the setup is completed some translations will be created.
exec dbms_sql_translator.create_profile('pj_test');
grant all on sql translation profile pj_test to op;
alter session set sql_translation_profile=pj_test;
alter system flush shared_pool;
alter session set events = '10601 trace name context forever, level 32';
If there are any issues with the setup you will see messages like the following:
ORA-24252: SQL translation profile does not exist
24252. 00000 - "SQL translation profile does not exist"
*Cause: An attempt was made to access a SQL translation profile that
either did not exist or for which the current user did not have
privileges on.
*Action: Check the SQL translation profile name and verify that the
current user has appropriate privileges on the SQL translation
profile.
You can change the statement slightly or you can access a totally different query by assigning a translation.
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 1 from op.vc',
translated_text => 'select 42 from op.vc');
end;
/
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 2 from op.vc',
translated_text => 'select * from op.dept');
end;
/
Now we can see the output
SQL> select 1 from op.vc;
42
----------
42
1 rows selected
SQL> select 2 from op.vc;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected
If I look in the data dictionary to see what has been run (factoring out some other unrelated queries) I see the following translations in place, but not the original statements (e.g. select 1 from op.vc).
SQL> select sql_text from v$sql where sql_text like 'select%from op.%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from op.dept
select 42 from op.vc
You can imagine that this could be quite a security risk, if somebody were to translate a select statement into a delete/update/insert. Fortunately that type of translation is not allowed. For example, when I translate a select into a delete
begin
dbms_sql_translator.register_sql_translation(
profile_name => 'pj_test',
sql_text => 'select 3 from op.vc',
translated_text => 'delete from op.dept where deptno=10');
end;
/
Running the statement gives an error.
select 3 from op.vc;
ORA-00900: invalid SQL statement
00900. 00000 - "invalid SQL statement"
*Cause:
*Action:
Thursday, May 5, 2016
Tips for travel (update)
This is a followup to a Tips for Travel post I had created several years ago.
Over the course of time there have been technology changes that I wanted to address. Also I have accumulated additional advice that I think will be helpful updates to the prior tips.
Technology updates
I still use my iPhone heavily when I travel. The apps I use like CamScanner and Maps continue to be some of the most important items on my phone. One update is that Maps will provide audio prompts which is much safer than trying to check the phone while driving.
Some other apps that are useful include the apps provided by airline, hotel, and rental car companies. I often use these apps to make travel changes without having to find a computer or connection for my laptop. With the airline apps I will check in with my phone and save the boarding pass to my Wallet app. This reduces the paper I have to print and carry.
The other apps I use most often are Audible, SiriusXM, and Podcasts. I have a subscription with Audible which provides me with 2 audiobooks every month. A typical book is around 15 hours which gives you plenty of entertainment while you are traveling. We have Sirius radio in our car, so I downloaded the Sirius app which allows me to listen to music, talk radio, etc while I travel. Note that if you are not connected to wifi, then the Sirius app will use quite a bit of data. Finally there are several Podcasts I listen to on a regular basis. The latest episodes can be downloaded to your device when you are connected to wifi then listened to at your leisure in order to save data usage.
Lastly, I am a fan of Amazon Prime. With my Kindle device I can download movies and TV shows I've purchased, but also with a prime membership you can download movies or shows that are available for streaming. This allows you to watch something when you are not connected to wifi, such as on a plane or sitting in an airport. I have noticed that my Bose in-ear headphones allow me to hear the shows much better, especially when I'm on a plane. Also as a prime member I can use the Kindle lending library to read some books for free. Note the selection is pretty weak, but you can probably find something that would do in a pinch.
Travel updates
Enroll in TSA PreCheck. Most airports have different lines for PreCheck which will allow you to get to your gate much faster than going through regular security. If you travel internationally then you should look into the Global Entry program, but for domestic flights PreCheck is all you need.
Accept the fact that you will have flight delays. Between weather issues, mechanical problems, and cascading delays it's almost surprising that flights leave on time at all. When you want to get home or if you are in a hurry to get to a client's site, delays can be very frustrating but you have to remind yourself to accept the things you cannot change. Stressing about these delays only hurt yourself. Given that, you should always check your flight connection times to ensure that you give yourself enough time to reach your next gate.
I don't have memberships to the airline clubs, but with their credit cards you can get a one-time pass for a reasonable amount of money. If I know I have a multi hour delay I will sometimes take advantage of that feature to spend time in the clubs. The free house drinks, free wifi, snacks, and comfortable chairs makes those delays pass by much faster. With a good audiobook or a downloaded movie on my Kindle, the time goes by even faster.
Over the course of time there have been technology changes that I wanted to address. Also I have accumulated additional advice that I think will be helpful updates to the prior tips.
Technology updates
I still use my iPhone heavily when I travel. The apps I use like CamScanner and Maps continue to be some of the most important items on my phone. One update is that Maps will provide audio prompts which is much safer than trying to check the phone while driving.
Some other apps that are useful include the apps provided by airline, hotel, and rental car companies. I often use these apps to make travel changes without having to find a computer or connection for my laptop. With the airline apps I will check in with my phone and save the boarding pass to my Wallet app. This reduces the paper I have to print and carry.
The other apps I use most often are Audible, SiriusXM, and Podcasts. I have a subscription with Audible which provides me with 2 audiobooks every month. A typical book is around 15 hours which gives you plenty of entertainment while you are traveling. We have Sirius radio in our car, so I downloaded the Sirius app which allows me to listen to music, talk radio, etc while I travel. Note that if you are not connected to wifi, then the Sirius app will use quite a bit of data. Finally there are several Podcasts I listen to on a regular basis. The latest episodes can be downloaded to your device when you are connected to wifi then listened to at your leisure in order to save data usage.
Lastly, I am a fan of Amazon Prime. With my Kindle device I can download movies and TV shows I've purchased, but also with a prime membership you can download movies or shows that are available for streaming. This allows you to watch something when you are not connected to wifi, such as on a plane or sitting in an airport. I have noticed that my Bose in-ear headphones allow me to hear the shows much better, especially when I'm on a plane. Also as a prime member I can use the Kindle lending library to read some books for free. Note the selection is pretty weak, but you can probably find something that would do in a pinch.
Travel updates
Enroll in TSA PreCheck. Most airports have different lines for PreCheck which will allow you to get to your gate much faster than going through regular security. If you travel internationally then you should look into the Global Entry program, but for domestic flights PreCheck is all you need.
Accept the fact that you will have flight delays. Between weather issues, mechanical problems, and cascading delays it's almost surprising that flights leave on time at all. When you want to get home or if you are in a hurry to get to a client's site, delays can be very frustrating but you have to remind yourself to accept the things you cannot change. Stressing about these delays only hurt yourself. Given that, you should always check your flight connection times to ensure that you give yourself enough time to reach your next gate.
I don't have memberships to the airline clubs, but with their credit cards you can get a one-time pass for a reasonable amount of money. If I know I have a multi hour delay I will sometimes take advantage of that feature to spend time in the clubs. The free house drinks, free wifi, snacks, and comfortable chairs makes those delays pass by much faster. With a good audiobook or a downloaded movie on my Kindle, the time goes by even faster.
Saturday, April 2, 2016
APPROX_COUNT_DISTINCT overview
Oracle has a new function advertised with 12.1.0.2 called APPROX_COUNT_DISTINCT. This SQL function is will allow the user to query a rough estimate of a group of distinct values. This feature is supposed to run more efficiently than the current methods.
This post will test that out with a couple of examples. I will look at accuracy and performance to evaluate the functionality. The first test will be of a full scan, the second test will use the SAMPLE clause, and the final test will use the new APPROX_COUNT_DISTINCT function.
To simplify the test I will create a copy of the DBA_OBJECTS table called BIG_TABLE. The query used will group by OWNER while performing a count on distinct OBJECT_ID. To evaluate the accuracy I will reduce the output of 38 rows to 7 key rows.
As a note, when I evaluate the execution plan I will be using the gather_plan_statistics hint to get ALLSTATS from dbms_xplan.display_cursor. When I execute different queries I often use an additional string in the hint to help search v$sql for the SQL_TEXT I want to review. That explains why you will see hints like /*+ gather_plan_statistics a*/.
A. Full Scan
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)
from big_table
group by owner
order by owner;
B. Using sample size of 20%
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)
from big_table sample (20)
group by owner
order by owner;
C. Using new feature
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct (object_id)
from big_table
group by owner
order by owner;
The results will show that even though the sample size query ran faster than APPROX_COUNT_DISTINCT, the results of the latter were more accurate. The performance of the full scan had a run time of twice that of APPROX_COUNT_DISTINCT, so the performance was also much improved with the new function.
A:
OWNER COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000 3600
BI 8
HR 34
LBACSYS 237
PUBLIC 37125
SYS 42110
XFILES 141
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 07q279qdm8pgp, child number 0
-------------------------------------
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)
from big_table group by owner order by owner
Plan hash value: 2085152455
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38 |00:00:00.30 | 1577 | 1565 |
| 1 | SORT GROUP BY | | 1 | 90869 | 38 |00:00:00.30 | 1577 | 1565 |
| 2 | VIEW | VM_NWVW_1 | 1 | 90869 | 93680 |00:00:00.27 | 1577 | 1565 |
| 3 | HASH GROUP BY | | 1 | 90869 | 93680 |00:00:00.24 | 1577 | 1565 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 1 | 90869 | 93680 |00:00:00.18 | 1577 | 1565 |
------------------------------------------------------------------------------------------------------------
B:
OWNER COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000 741
HR 4
LBACSYS 49
PUBLIC 7455
SYS 8508
XFILES 30
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 729uapczb0dd2, child number 0
-------------------------------------
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)
from big_table sample (20) group by owner order by owner
Plan hash value: 1425374893
------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 37 |00:00:00.06 | 1577 | 1565 |
| 1 | SORT GROUP BY | | 1 | 3635 | 37 |00:00:00.06 | 1577 | 1565 |
| 2 | VIEW | VM_NWVW_1 | 1 | 3635 | 18904 |00:00:00.05 | 1577 | 1565 |
| 3 | HASH GROUP BY | | 1 | 3635 | 18904 |00:00:00.05 | 1577 | 1565 |
| 4 | TABLE ACCESS SAMPLE| BIG_TABLE | 1 | 3635 | 18904 |00:00:00.03 | 1577 | 1565 |
------------------------------------------------------------------------------------------------------------
C:
OWNER APPROX_COUNT_DISTINCT(OBJECT_ID)
-------------------- --------------------------------
APEX_050000 3630
BI 8
HR 34
LBACSYS 236
PUBLIC 37338
SYS 42118
XFILES 140
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3bmz5b1a9uzqh, child number 0
-------------------------------------
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct
(object_id) from big_table group by owner order by owner
Plan hash value: 3184991183
------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38 |00:00:00.14 | 1577 | 1565 |
| 1 | SORT GROUP BY APPROX| | 1 | 90869 | 38 |00:00:00.14 | 1577 | 1565 |
| 2 | TABLE ACCESS FULL | BIG_TABLE | 1 | 90869 | 93680 |00:00:00.09 | 1577 | 1565 |
--------------------------------------------------------------------------------------------------------
If you need to get a rough estimate of counts in a table and you are using a version >= 12.1.0.2 then you should consider this feature. If exact numbers are still needed then you will need to perform a full scan.
This post will test that out with a couple of examples. I will look at accuracy and performance to evaluate the functionality. The first test will be of a full scan, the second test will use the SAMPLE clause, and the final test will use the new APPROX_COUNT_DISTINCT function.
To simplify the test I will create a copy of the DBA_OBJECTS table called BIG_TABLE. The query used will group by OWNER while performing a count on distinct OBJECT_ID. To evaluate the accuracy I will reduce the output of 38 rows to 7 key rows.
As a note, when I evaluate the execution plan I will be using the gather_plan_statistics hint to get ALLSTATS from dbms_xplan.display_cursor. When I execute different queries I often use an additional string in the hint to help search v$sql for the SQL_TEXT I want to review. That explains why you will see hints like /*+ gather_plan_statistics a*/.
Queries
The queries evaluated were the following:A. Full Scan
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)
from big_table
group by owner
order by owner;
B. Using sample size of 20%
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)
from big_table sample (20)
group by owner
order by owner;
C. Using new feature
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct (object_id)
from big_table
group by owner
order by owner;
The results will show that even though the sample size query ran faster than APPROX_COUNT_DISTINCT, the results of the latter were more accurate. The performance of the full scan had a run time of twice that of APPROX_COUNT_DISTINCT, so the performance was also much improved with the new function.
Detail Results
The details are provided here for anybody interested. Following this I will have a Summary of this information.A:
OWNER COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000 3600
BI 8
HR 34
LBACSYS 237
PUBLIC 37125
SYS 42110
XFILES 141
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 07q279qdm8pgp, child number 0
-------------------------------------
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)
from big_table group by owner order by owner
Plan hash value: 2085152455
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38 |00:00:00.30 | 1577 | 1565 |
| 1 | SORT GROUP BY | | 1 | 90869 | 38 |00:00:00.30 | 1577 | 1565 |
| 2 | VIEW | VM_NWVW_1 | 1 | 90869 | 93680 |00:00:00.27 | 1577 | 1565 |
| 3 | HASH GROUP BY | | 1 | 90869 | 93680 |00:00:00.24 | 1577 | 1565 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 1 | 90869 | 93680 |00:00:00.18 | 1577 | 1565 |
------------------------------------------------------------------------------------------------------------
B:
OWNER COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000 741
HR 4
LBACSYS 49
PUBLIC 7455
SYS 8508
XFILES 30
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 729uapczb0dd2, child number 0
-------------------------------------
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)
from big_table sample (20) group by owner order by owner
Plan hash value: 1425374893
------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 37 |00:00:00.06 | 1577 | 1565 |
| 1 | SORT GROUP BY | | 1 | 3635 | 37 |00:00:00.06 | 1577 | 1565 |
| 2 | VIEW | VM_NWVW_1 | 1 | 3635 | 18904 |00:00:00.05 | 1577 | 1565 |
| 3 | HASH GROUP BY | | 1 | 3635 | 18904 |00:00:00.05 | 1577 | 1565 |
| 4 | TABLE ACCESS SAMPLE| BIG_TABLE | 1 | 3635 | 18904 |00:00:00.03 | 1577 | 1565 |
------------------------------------------------------------------------------------------------------------
C:
OWNER APPROX_COUNT_DISTINCT(OBJECT_ID)
-------------------- --------------------------------
APEX_050000 3630
BI 8
HR 34
LBACSYS 236
PUBLIC 37338
SYS 42118
XFILES 140
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3bmz5b1a9uzqh, child number 0
-------------------------------------
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct
(object_id) from big_table group by owner order by owner
Plan hash value: 3184991183
------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38 |00:00:00.14 | 1577 | 1565 |
| 1 | SORT GROUP BY APPROX| | 1 | 90869 | 38 |00:00:00.14 | 1577 | 1565 |
| 2 | TABLE ACCESS FULL | BIG_TABLE | 1 | 90869 | 93680 |00:00:00.09 | 1577 | 1565 |
--------------------------------------------------------------------------------------------------------
Summary
To summarize the information seenPerformance
| Method | Run time |
| Count Full | 0.30 |
| Count Sample | 0.06 |
| Count Approx | 0.14 |
Accuracy
| Owner | Count full | Count sample | Count sample*5 | Count Approx |
| APEX_050000 | 3600 | 741 | 3705 | 3630 |
| BI | 8 | 0 | 0 | 8 |
| HR | 34 | 4 | 20 | 34 |
| LBACSYS | 237 | 49 | 245 | 236 |
| PUBLIC | 37125 | 7455 | 37275 | 37338 |
| SYS | 42110 | 8508 | 42540 | 42118 |
| XFILES | 141 | 30 | 150 | 140 |
Conclusion
The new function runs faster than the full scan and it is more accurate than using the sample feature. With the 20% sample, the BI records were not even seen. If you multiple the 20% sample by 5 you can get an estimate of the full table values with this method, but the values are not as close as the APPROX_COUNT_DISTINCT.If you need to get a rough estimate of counts in a table and you are using a version >= 12.1.0.2 then you should consider this feature. If exact numbers are still needed then you will need to perform a full scan.
Sunday, March 13, 2016
Hotsos Symposium 2016 Wrap-up
Another Hotsos symposium is in the books. There were a so many great sessions that it is hard to quickly outline things. In addition to the presentations, there was a wonderful Training Day by Richard Foote.
The topic of indexes (Richard Foote's training day material) is more vast than we realize when doing day-to-day work in Oracle. Richard went through a great deal of information on indexing. During the course of his day long session he debunked many widely held myths about indexes. For example, is it best with a composite index to have the most selective values at the start of the index? Are you sure about that...?
This basic information is critical to supporting an Oracle system. A long standing belief of mine is that people should spend more time on the fundamentals of database design and performance rather than going down rabbit holes of features or techniques that are often not really needed. Many times I see people using these rare techniques to resolve issues that sometimes have much simpler fixes.
Throughout the conference there were a mix of presentations from veteran presenters and new participants. The typical big names including Tanel Poder, Kerry Osborne, Jeff Smith, et al provided the high quality presentations we are used to seeing from them. As usual for the Hotsos Symposium, the content was performance centric however it covered a wide variety of topics.
The training day wrapped up the Symposium but the start was from Kellyn Pot'Vin-Gorman and ThatJeffSmith on the importance of social media presence. Their presentation served to remind me of how infrequent I have been blogging and interacting in the social media space lately. As a result of their wonderful presentation, I will be working on doing more than a just few blog posts a year.
In addition to the increased social media activity I also plan on being more active with presentations and articles. I have done quite a bit in the past, but have taken a little bit of a break lately.
I guess that is the sign of a good conference... Being more energized when it is over.
Subscribe to:
Posts (Atom)