Skip navigation.

DBA Blogs

Why Locking Oracle Accounts is a Bad Idea

Pythian Group - Thu, 2015-12-10 08:59

 

Time and time again I run into database accounts, which are marked “LOCKED” or “EXPIRED & LOCKED”. The main problem here lies with how Oracle handles a failed login attempt when the account is locked. In this blog I will discuss why locking Oracle accounts is a bad idea.

Let’s consider the following scenario:

create user scott identified by tiger account lock;

User created.

select username, account_status from dba_users where username='SCOTT';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          LOCKED

 

So what happens if I put on my black hat, and try to get into this database? I may probe for some common users, and just happen to come across this:

connect scott/abc
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

 

What Oracle does there is give me a very valuable piece of information: it tells me that this user exists in the database. Why is that important?

Let’s see what we can find out – without even being able to connect, based solely on the account status of some common accounts:

 

USERNAME		       ACCOUNT_STATUS
------------------------------ --------------------------------
ANONYMOUS		       EXPIRED & LOCKED
APEX_030200		       LOCKED
APEX_PUBLIC_USER	       LOCKED
CTXSYS			       EXPIRED & LOCKED
DIP			       EXPIRED & LOCKED
EXFSYS			       EXPIRED & LOCKED
FLOWS_FILES		       LOCKED
OLAPSYS 		       EXPIRED & LOCKED
ORACLE_OCM		       EXPIRED & LOCKED
OUTLN			       EXPIRED & LOCKED
SQLTXADMIN		       EXPIRED & LOCKED
WMSYS			       EXPIRED & LOCKED
XDB			       EXPIRED & LOCKED
XS$NULL 		       EXPIRED & LOCKED

 

Simply by trying to connect to some of these, and Oracle telling me that the account is locked, I now know that the database has all of the following installed:

 

– APEX
– OLAP
– Oracle Text
– XML Database

 

That’s a lot of information I was just given for free. Depending on the components I’d find, I could also deduce that the Oracle JVM is installed in the database. And this frequently hits the news with newly discovered vulnerabilities.

In essence this means that by locking your accounts, you leave the door open way wider than you’re thinking. It’s a totally counter-productive way of doing things.

So what’s better?

The best approach is a very simple one. Putting my white hat back on, I just assign the user an impossible password hash, like so:

alter user scott account unlock identified by values 'impossible';

 

It’s not possible for this user to ever log in while this hash is in place. And if we try, all we get is:

SQL> connect scott/abc
ERROR:
ORA-01017: invalid username/password; logon denied</code>

 

Warning: You are no longer connected to ORACLE.

The second thing you’d want to do is ensure that those users’ passwords never expire. Or you’d end up with the same EXPIRED & LOCKED status again.

Happy unlocking, and stay secure! :)

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Trace Files -- 9 : Advantages

Hemant K Chitale - Wed, 2015-12-09 08:47
I received a question about why Trace Files are useful.

I jotted down a few quick points.  I may expand on  them or add to them later

  1. You don’t have to monitor a user screen , batch job in real time – e.g. if the user or batch is running at 2am, you’d have to login at 2am to monitor it.
  2. If you are doing real time monitoring, once the particular event of interest has gone off your screen, you don’t have time to think about it because something else is happening on the screen you are using to monitor the user / batch
  3. ASH doesn’t capture every wait event --- you’ll have many single block reads and multi block reads and even locks that may have occurred between two 1second AWR snapshots.  So it is an approximation but not an aggregation.  (Historical AWR is even worse as it maintains only 1 in 10 samples)
  4. You can repeatedly review the tracefile and spend more time on it which you can’t do in real-time monitoring
  5. I’d use a trace file for a batch job.
Categories: DBA Blogs

The End of an Era – Preparing for a SQL Server 2005 Upgrade

Pythian Group - Wed, 2015-12-09 07:02

 

With the impending End of Support for SQL Server 2005 coming up in April 2016, most companies know that they need to find an upgrade path in order to benefit from the new features.

There are many upgrade paths and targets you can take to get out of SQL Server 2005 and into 2014 or even straight into the cloud with Azure SQL Database or Azure SQL DataWarehouse.

Here at Pythian we have created a handy 45 minute webinar to cover upgrade scenarios, upgrade tools and we are even share our own process on how we approach SQL Server upgrade projects. We’re also including demos of upgrading to 2014 and to Azure SQL Database.

As much as we’ve enjoyed working with SQL Server 2005, it’s time to say good bye and jump 10 years ahead.

 

And just for fun, let’s go down memory lane and analyze what an amazing milestone SQL Server 2005 was for Microsoft data professionals.

SSIS was introduced. And still going strong after 10 years with countless improvements.

DMVs and DMFs were introduced. How did we ever do engine troubleshooting before!? Such a huge difference from SQL 2000 and was the foundation of the insane amount of instrumentation that is built into the product today.

CLR was introduced. .NET into the engine, a whole new world of programming capabilities opened.

Row multi-versioning was introduced in the engine. Opening up new levels of concurrency and closing the gap on a major piece of functionality that Oracle had from day 1.

Table partitioning was introduced. Another major feature needed to take SQL Server into the VLDB and warehousing space.

Database mirroring introduced. Shared-nothing, easy high availability without the need of shared storage. Obviously the foundation of the current AlwaysOn Availability Groups.

DDL triggers, XML support, ranking functions, recursive CTEs, separation of user and schema. Not much to add here.

And that’s not a complete list. Truly, the 2005 release finally placed SQL Server as a real enterprise-grade relational database system. No wonder it took 5 years, as still to this day, SQL 2005 is the foundation that has taken us all the way to 2016 and the massive world of cloud relational databases on Azure. It also kick started many careers (mine included) as more and more companies began to see the potential of the product and adopted Microsoft’s data platform.

If you’re considering upgrading from SQL Server 2005, let us know. We’ll connect you with one of our SQL Server experts to review your requirements and discuss how we can work with your team to ensure the success of your upgrade.

Categories: DBA Blogs

ORA-39070: Unable to open the log file

Oracle in Action - Tue, 2015-12-08 03:56

RSS content

I received this error message when I was trying to perform a data pump export of SH schema in parallel in a RAC database. I proceeded as follows:

Current scenario:
Name of the cluster: cluster01
Number of nodes : 3 (host01, host02, host03)
RAC Database version: 11.2.0.3
Name of RAC database : orcl
Number of instances : 3

  • Created a directory object  pointing to shared storage which is accessible by all the three instances of the database
SQL>drop directory dp_shared_dir;
SQL>create directory DP_SHARED_DIR as '+DATA/orcl/';
SQL>grant read, write on directory dp_shared_dir to public;
  • Issued the command to export SH schema in parallel across all active Oracle RAC instances with parallelism = 6 which resulted in error ORA-39070
[oracle@host01 root]$ expdp system/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y dumpfile='expsh%U.dmp' reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Tue Dec 8 14:45:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
 With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 Data Mining and Real Application Testing options
 ORA-39002: invalid operation
 ORA-39070: Unable to open the log file.
 ORA-29283: invalid file operation
 ORA-06512: at "SYS.UTL_FILE", line 536
 ORA-29283: invalid file operation

Cause:
The error message indicates that Log file cannot be opened. Since directory parameter points to a shared location on an ASM disk group and log file is not supported on it, I received the above error.

Solution:
I modified my command and explicitly specified log file to be created on  local file system pointed to by the directory object DATA_PUMP_DIR. Subsequently, export was performed successfully.

[oracle@host01 root]$ expdp system/oracle@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile='expsh%U.dmp' reuse_dumpfiles=y

Export: Release 11.2.0.3.0 - Production on Tue Dec 8 15:14:11 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_10": system/********@orcl schemas=sh directory=dp_shared_dir parallel=6 cluster=y logfile=data_pump_dir:expsh.log dumpfile=expsh%U.dmp reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
.....
.....
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_10" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_10 is:
+DATA/orcl/expsh01.dmp
+DATA/orcl/expsh02.dmp
+DATA/orcl/expsh03.dmp
+DATA/orcl/expsh04.dmp
+DATA/orcl/expsh05.dmp
+DATA/orcl/expsh06.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_10" successfully completed at 15:20:49

I hope it helps!!!

—————————————————————————————————————-

Related links:   Home 11gR2 RAC Index

Tags:  

Del.icio.us
Digg

Comments:  3 comments on this item
You might be interested in this:  
Copyright © ORACLE IN ACTION [ORA-39070: Unable to open the log file], All Right Reserved. 2016.

The post ORA-39070: Unable to open the log file appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Trace Files -- 8d : Full Table Scans

Hemant K Chitale - Sun, 2015-12-06 05:05
In the previous two posts, I have demonstrated direct path read and db file scattered read being used for Full Table Scans.  Since 11g, Oracle uses an internal formula to determine the manner of reading the table segment for a Full Table Scan.

Here I use an SQL trace to compare the two behaviours.  

In session 1, I run these queries :

[oracle@ora11204 ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 6 17:59:12 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set time on
17:59:18 SQL> set timing on
17:59:20 SQL> alter session set tracefile_identifier='SESSION_1';

Session altered.

Elapsed: 00:00:00.00
17:59:31 SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:59:41 SQL>
18:00:25 SQL> select count(*) from all_objects_many_list where owner = 'HEMANT';

COUNT(*)
----------
256

Elapsed: 00:00:45.67
18:01:28 SQL> select count(*) from all_objects_short_list where owner = 'HEMANT';

COUNT(*)
----------
3

Elapsed: 00:00:00.40
18:01:46 SQL> select /* second_run */ count(*) from all_objects_many_list where owner = 'HEMANT';

COUNT(*)
----------
256

Elapsed: 00:00:42.35
18:02:48 SQL> select /* second_run */ count(*) from all_objects_short_list where owner = 'HEMANT';

COUNT(*)
----------
3

Elapsed: 00:00:00.01
18:03:05 SQL>
18:10:57 SQL>
18:11:06 SQL> select /* third_run */ count(*) from all_objects_many_list where owner = 'HEMANT';

COUNT(*)
----------
256

Elapsed: 00:01:41.50
18:13:03 SQL> select /* third_run */ count(*) from all_objects_short_list where owner = 'HEMANT';

COUNT(*)
----------
3

Elapsed: 00:00:00.07
18:13:20 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora11204 ~]$

(I had another session running other queries between 18:03 and 18:11)

The Full Table Scan on "ALL_OBJECTS_MANY_LIST"  would have been executed as 'direct path read's.  The Full Table Scan on "ALL_OBJECTS_SHORT_LIST" (being a much smaller table) would have been executed as 'db file scattered read's.

Let's compare the queries in terms of the number of blocks read and how they were read.  First, the three executions on the larger table.

SQL ID: bpgst4ajh1wb2 Plan Hash: 2662061148

select count(*)
from
all_objects_many_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.05 44.33 97793 97798 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.06 44.34 97793 97798 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=97798 pr=97793 pw=0 time=44340391 us)
256 256 256 TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97798 pr=97793 pw=0 time=30091048 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 3 0.67 0.76
direct path read 1542 1.81 43.21
SQL*Net message from client 2 16.96 16.96
********************************************************************************


SQL ID: bqhqn8rvr5139 Plan Hash: 2662061148

select /* second_run */ count(*)
from
all_objects_many_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.96 42.35 97792 97797 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.96 42.35 97792 97797 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=97797 pr=97792 pw=0 time=42353099 us)
256 256 256 TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97797 pr=97792 pw=0 time=15066276 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 1542 1.66 41.95
SQL*Net message from client 2 17.06 17.06
********************************************************************************


SQL ID: 2pwy1jv1uu23x Plan Hash: 2662061148

select /* third_run */ count(*)
from
all_objects_many_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.05 101.44 97792 97797 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.05 101.44 97792 97797 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=97797 pr=97792 pw=0 time=101440638 us)
256 256 256 TABLE ACCESS FULL ALL_OBJECTS_MANY_LIST (cr=97797 pr=97792 pw=0 time=109050948 us cost=26809 size=5256 card=657)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 1542 1.06 101.01
SQL*Net message from client 2 17.00 17.00
********************************************************************************

All three executions of the Full Table Scan (inspite of one query on another table being executed between the two) were done with 'direct path read' reading all the blocks from disk.   Note the 'disk ' column and "Times Waited" column.  For all three executions, CPU time is only about a second in each execution and the disk reads took more than 40seconds in the first two executions and more than 100seconds in the third execution..

Next, the three executions on the smaller table.

SQL ID: 9k7ff0kmd0nbw Plan Hash: 3180576180

select count(*)
from
all_objects_short_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.10 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.09 140 580 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.20 142 582 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=580 pr=140 pw=0 time=95405 us)
3 3 3 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=140 pw=0 time=95379 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 21 0.03 0.08
db file sequential read 3 0.00 0.00
SQL*Net message from client 2 19.89 19.89
********************************************************************************


SQL ID: dgu2sxavbv996 Plan Hash: 3180576180

select /* second_run */ count(*)
from
all_objects_short_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 580 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 581 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=580 pr=0 pw=0 time=1173 us)
3 3 3 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=0 pw=0 time=1146 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 496.19 496.19
********************************************************************************


SQL ID: 2xuyj80zj1xnt Plan Hash: 3180576180

select /* third_run */ count(*)
from
all_objects_short_list where owner = 'HEMANT'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 580 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 582 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=580 pr=0 pw=0 time=1019 us)
3 3 3 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=0 pw=0 time=991 us cost=158 size=102 card=6)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 3.57 3.57
********************************************************************************

For these queries against the smaller (SHORT_LIST) table, it is only the first execution that shows Disk I/O  from the 'db file scattered read' waits and the "disk" column (140 blocks reported in the "disk" column and shown as physical reads indicated by the "pr=140" in the statistics).  The subsequent two executions were against the blocks in the Buffer Cache (as shown by 0 "disk" and "pr" statistics).  This is also evident in the third execution which occurred almost eleven minutes after the second execution.  Evidently, the buffers for the table were still evident in the Buffer Cache.  The session did not have to go back to disk to read the blocks.  This would be the behaviour for any other session also, as the Buffer Cache would be shared by multiple sessions.

Thus, we can see that for a table (the MANY_LIST table) that is large enough for Oracle to decide on direct path reads, there is no buffering of the blocks in the buffer cache.  Every Full Table Scan execution, whether by the same session or another session, has to re-read the table from disk (or filesystem buffers maintained by the OS) afresh.  This must be a consideration when you have a database application that frequently needs to do Full Table Scans.  Do you want every session to have to read from disk afresh ?  You would not take advantage of the buffer cache in the SGA.  You could choose to create a larger SGA and Buffer Cache.  There is another method -- the undocumented "_serial_direct_read" parameter  but that makes sense to use only if the table is smaller than the Buffer Cache.

Note : The 496.19 "SQL*Net message from client" wait event after the second query against the SHORT_LIST table is the elapsed time till the next query. The clock time showed at the SQLPlus prompt may not have been the time a query started because I might have waited some time before actually submitting the query (by hitting the ENTER query after the ";")

.
.
.


Categories: DBA Blogs

Locks, Blocks and Deadlocks – What’s the Difference?

Pythian Group - Fri, 2015-12-04 07:42

 

We are often being paged by development teams talking about locks, blocks or deadlocks and some people make the wrong use of the terms.

There is a big difference between the three and it will explained at a high level in this post:

 

Lock
Lock is acquired when any process accesses a piece of data where there is a chance that another concurrent process will need this piece of data as well at the same time. By locking the piece of data we ensure that we are able to action on that data the way we expect.

For example, if we read the data, we usually like to ensure that we read the latest data. If we update the data, we need to ensure no other process is updating it at the same time, etc.
Locking is the mechanism that SQL Server uses in order to protect data integrity during transactions.

 

Block
Block (or blocking lock) occurs when two processes need access to same piece of data concurrently so one process locks the data and the other one needs to wait for the other one to complete and release the lock. As soon as the first process is complete, the blocked process resumes operation. The blocking chain is like a queue: once the blocking process is complete, the next processes can continue. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem and the blocking may simply be causing performance issues.
A block can be described like this:

Block

A blocking situation may NOT be resolved by itself (i.e. if the blocking process did not complete the transaction properly) or may take a long time to complete. In these extreme situations, the blocking process may need to be killed and/or redesigned.

 

Deadlock
Deadlock occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked and waiting for the first process to release the lock.

In a simplified way, the deadlock would look like this:

Deadlock

In a deadlock situation, the processes are already blocking each other so there needs to be an external intervention to resolve the deadlock. For that reason, SQL Server has a deadlock detection and resolution mechanism where one process needs to be chosen as the “deadlock victim” and killed so that the other process can continue working. The victim process receives a very specific error message indicating that it was chosen as a deadlock victim and therefore the process can be restarted via code based on that error message.
This article explains how SQL Server detects and resolves deadlocks: Deadlocks are considered a critical situation in the database world because processes are just being automatically killed. Deadlocks can and should be prevented.Deadlocks are resolved by SQL Server and do not need manual intervention.

 

Lock-Avoiding Design Strategies

Some of the strategies are described here:

“There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

  • Use clustered indexes on high-usage tables.
  • Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
  • Break long transactions up into many shorter transactions. With SQL Server, you can use “bound connections” to control the execution sequence of the shorter transactions.
  • Make sure that UPDATE and DELETE statements use an existing index.
  • If you use nested transactions, be sure there are no commit or rollback conflicts.

My additions:

  • Access objects always in the same order (i.e.: update Table1, Table2 and Table3 rather than sometimes Table2 first).
  • Don’t schedule long data updating processes to run concurrently, if possible.
  • Keep transactions as short as possible.

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Log Buffer #452: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-12-04 07:28

This Log Buffer Edition covers top Oracle, SQL Server and MySQL blog posts of the week.

Oracle:

  • In Oracle EBS 12.0 and 12.1 the Workflow notification system was not enabled to send e-mail notifications to users or roles who happened to have multiple e-mail addresses associated to them.
  • Just how can a SQL Developer user quickly build out a SQL script for a database user that will include ALL of their privileges, roles, and system grants?
  • Oracle BI 12c has been released for some time now. There are a few changes in the way it is installed compared to the previous 11g releases. This post is about installing and configuring OBIEE 12c with detailed step-by-step instructions (Linux x86-64 in this case).
  • In today’s digital economy, customers want effortless engagements and answers to their questions regardless of how they connect with a brand.
  • Upgrade to Oracle Database 12c and Avoid Query Regression.

SQL Server:

  • Continuous integration (CI) is the process of ensuring that all code and related resources in a development project are integrated regularly and tested by an automated build system.
  • SSIS Issues after Master DB Corruption – “Please Recreate Master Key” When Running Package.
  • Check FileSize and LogUsage for all DBs.
  • Other Users Cannot Execute SSIS Packages after migration.
  • How to Get Started Using SQL Server in Azure.

MySQL:

  • Amazon Aurora in sys bench benchmarks.
  • “Data” and “Performance” is where MySQL Cluster’s heart is. In-memory performance and always-up drives our agenda. The Percona Live Data Performance Conference is coming up with two submitted sessions about Cluster.
  • Fixing errant transactions with mysqlslavetrx prior to a GTID failover.
  • MariaDB CONNECT storage engine handles access to JSON files through standard SQL. It comes with a set of UDFs (user defined functions) to manipulate the JSON format. This JSON content can be stored in a normal text column.
  • Become a ClusterControl DBA: Managing your Database Configurations.

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

How to Troubleshoot an ORA-28030 Error

Pythian Group - Fri, 2015-12-04 07:22

ORA-28030: Server encountered problems accessing LDAP directory service.
Cause: Unable to access LDAP directory service.
Action: Please contact your system administrator.

 

There are many reasons for causing this error when you are trying to login to the database with your oracle internet directory (OID) authentication. The error sample is shown as below:

SQL> conn howie@dbtest
Enter password:
ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service


Warning: You are no longer connected to ORACLE.

 

Here how I usually troubleshoot this kind of issue. Two examples.

First of all, you need to enable the trace to dump the actual errors in the database:

SQL> alter system set events '28033 trace name context forever, level 9';

 

Sencond, regenerate the error:

SQL> conn howie@dbtest
Enter password:
ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service

 

Third, disable the trace:

SQL> alter system set events '28033 trace name context off';

After checking the trace files, I found errors. This is related to the OID server lnx-ldap DNS configuration. Check /etc/hosts or DNS to make sure the OID server lnx-ldap or the port 3131 is reachable.

KZLD_ERR: failed to open connection to lnx-ldap:3131
KZLD_ERR: 28030
KZLD_ERR: failed from kzldob_open_bind.

Or you may see the error like this, this is because the wallet files were corrupted, you need to recreate the wallet, and make sure the wallet path is defined properly:

kzld_discover received ldaptype: OID
KZLD_ERR: failed to get cred from wallet
KZLD_ERR: Failed to bind to LDAP server. Err=28032
KZLD_ERR: 28032
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.

There are many possibilities to throw out ORA-28030, in this blog I am just simply giving you the hints for identifying the root cause.

Hope it helps!

 

Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Step-by-Step Upgrades to Cloudera Manager and CDH

Pythian Group - Fri, 2015-12-04 07:03

 

Introduction

Lately, several of our security conscious clients have expressed a desire to install and/or upgrade their Hadoop distribution on cluster nodes that do not have access to the internet. In such cases the installation needs to be performed using local repositories. Since I could not find a step-by-step procedure to accomplish this I thought I would publish it myself.

The following step-by-step procedure has been implemented using the following configuration and specifications:

Cloudera Manager Node : m3.large EC2 Instance running Centos 6.5 (CentOS-6.5-GA-03.3-f4325b48-37b0-405a-9847-236c64622e3e-ami-6be4dc02.2 (ami-8997afe0))
Name Node: m3.large EC2 Instance running Centos 6.5 (CentOS-6.5-GA-03.3-f4325b48-37b0-405a-9847-236c64622e3e-ami-6be4dc02.2 (ami-8997afe0))
Data Nodes (3): m3.large EC2 Instance running Centos 6.5 (CentOS-6.5-GA-03.3-f4325b48-37b0-405a-9847-236c64622e3e-ami-6be4dc02.2 (ami-8997afe0))

Existing Version of Cloudera Manager: 5.4.3
Existing Version of CDH: 5.4.2

Upgrade to Version of Cloudera Manager: 5.5.0
Upgrade to Version of CDH: 5.5.0

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.6 (Santiago)

# cat /proc/version
Linux version 2.6.32-504.16.2.el6.x86_64 (mockbuild@x86-028.build.eng.bos.redhat.com) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-9) (GCC) ) #1 SMP Tue Mar 10 17:01:00 EDT 2015

 

Upgrade Steps

We will be completing the upgrade of the cluster in two steps. In the first step only Cloudera Manager will be upgraded to version 5.5. Once the cluster has been verified to be functional with Cloudera Manager 5.5 then we will upgrade CDH to version 5.5.

 

1. Upgrade Cloudera Manager

 

1. Let’s start by creating the local repository for Cloudera Manager. Download latest version of Cloudera Manager from link below on Local Repository Host:

# wget -r –no-parent –reject “index.html*” “http://archive.cloudera.com/cm5/redhat/6/x86_64/cm/5.5/”# wget “http://archive.cloudera.com/cm5/redhat/6/x86_64/cm/RPM-GPG-KEY-cloudera”2. Copy downloaded files to pub/repos/cloudera-manager directory on Local Repository Host. After that start a local web server with pub/repos root directory. You may use any webserver including Python SimpleHTTPServer or Apache. Following are steps to use the SimpleHTTPServer:# cd pub/repos# nohup python -m SimpleHTTPServer 8000 &Expected output for http://Local Repository Host:8000/pub/repos/cloudera-manager [/vc_column_text] 82485_1

Expected output for http://Local Repository Host:8000/pub/repos/cloudera-manager/RPMS/x86_64

82485_2

3. Make sure the local repository for Cloudera Manager on ap-hdpen1t.oneadr.net:$ cat /etc/yum.repos.d/cloudera-manager.repo
[cloudera-manager] name=Cloudera Manager package mirror
baseurl=http://Local Repository Host:8000/pub/repos/cloudera-manager
gpgkey=http://Local Repository Host:8000/pub/repos/cloudera-manager/RPM-GPG-KEY-cloudera
gpgcheck=14. Log on to Cloudera Manager. Stop Cloudera Management Service:

82485_3

5. Make sure all services are stopped. Sample screens after stopping below:

82485_4

6. Stop the Hadoop Cluster:

7. SSH to Cloudera Manager Server. Stop Cloudera Manager Service:# sudo service cloudera-scm-server status
cloudera-scm-server (pid 6963) is running…
# sudo service cloudera-scm-server stop
Stopping cloudera-scm-server: [ OK ] # sudo service cloudera-scm-server status
cloudera-scm-server is stopped8. Before proceeding with the upgrade make sure you backup the Cloudera Manager Databases used by CDH services like Hive Metastore, Oozie, Sentry etc.9. When you are ready to upgrade issue command to upgrade Cloudera Manager:

# yum upgrade cloudera-manager-server cloudera-manager-daemons

Make sure the Upgrade Version for Cloudera Manager is as below:

82485_8

10. To verify if the upgrade is successful issue the following command:# rpm -qa ‘cloudera-manager-*’
cloudera-manager-daemons-5.5.0-1.cm550.p0.61.el6.x86_64
cloudera-manager-agent-5.5.0-1.cm550.p0.61.el6.x86_64
cloudera-manager-server-5.5.0-1.cm550.p0.61.el6.x86_6411. Start the Cloudera Manager:# service cloudera-scm-server start
Starting cloudera-scm-server: [ OK ]12. Monitor the Cloudera Manager Server Log for errors. The Cloudera Manager Server console is ready for use once you see the “Started Jetty Server” message in the log:# tail -f /var/log/cloudera-scm-server/cloudera-scm-server.log13. Log on to Cloudera Manager. You should now see the following screen. Note the running version:

82485_9

14. Choose Option as below to upgrade Cloudera Manager Agents. Press Continue:

82485_10

15. Choose Custom Repository:

In first box add: http://Local Repository Host:8000/pub/repos/cloudera-manager
In second box add: http://Local Repository Host:8000/pub/repos/cloudera-manager/RPM-GPG-KEY-cloudera

82485_11

Press Continue.

 

16. Check JDK/Java options as below and press Continue:

82485_12

17. Provide SSH credentials and Press Continue:

82485_13

18. Cloudera Manager will now upgrade the Agents:

82485_14

19. Verify Completion. Press Continue:

82485_15

20. Inspect Hosts for Correctness. Press Continue:

82485_16

21. You should now see a Confirmation Screen as below:

82485_17

22. Upgrade Cloudera Management Service. Press Continue:

82485_18

23. Confirm Restart of Cloudera Management Service:

82485_19

24. Verify Cloudera Management Service restarted. Press Finish:

82485_20

25. On the Cloudera Manager Home Screen. Choose Deploy Client Configuration:

82485_21

26. Verify Client Configurations Deployed:

82485_22

27. Start the Cluster:

82485_23

28. Verify Services on the Cluster are Active:

82485_24

29. Verify Cloudera Manager Version:

82485_25

30. Verify Agents Upgraded. Issue the following commands on all nodes:

# rpm -qa ‘cloudera-manager-*’
cloudera-manager-daemons-5.5.0-1.cm550.p0.61.el6.x86_64
cloudera-manager-agent-5.5.0-1.cm550.p0.61.el6.x86_64

31. Congratulations. Upgrade of Cloudera Manager was successful:

 

2. Upgrade Cloudera DistributionNow that the Cloudera Manager has been upgraded lets upgrade CDH to version 5.5.1. Download latest version of CDH from link below on Local Repository Host:wget http://archive.cloudera.com/cdh5/parcels/5.5/CDH-5.5.0-1.cdh5.5.0.p0.8-el6.parcel
wget http://archive.cloudera.com/cdh5/parcels/5.5/CDH-5.5.0-1.cdh5.5.0.p0.8-el6.parcel.sha1
wget http://archive.cloudera.com/cdh5/parcels/5.5//manifest.json2. Create/Refresh the local repository for Cloudera Manager by copying the downloaded files to pub/repos/cloudera-cdh5/ directory on Local Repository Host.
Expected output for http://Local Repository Host:8000/pub/repos/cloudera-cdh5/

82485_26

3. Back up HDFS metadata using the following command:$ whoami
hdfs
$ hdfs dfsadmin -fetchImage ~
15/11/27 19:23:58 INFO namenode.TransferFsImage: Opening connection to http://ip-10-169-250-118.ec2.internal:50070/imagetransfer?getimage=1&txid=latest
15/11/27 19:23:58 INFO namenode.TransferFsImage: Image Transfer timeout configured to 60000 milliseconds
15/11/27 19:23:58 INFO namenode.TransferFsImage: Transfer took 0.09s at 2715.91 KB/s
$ ls -l
total 244
-rw-rw-r–. 1 hdfs hdfs 244838 Nov 27 19:23 fsimage_00000000000000154184. Backup databases used for the various CDH services. The following screen shows the databases details used for various services like Oozie, HUE, Sentry etc:

82485_27

5. Log on to Cloudera Manager.6. Verify the parcel download setting is pointing to the local repository for CDH. Press the Parcels icon on the Cloudera Manager Home Page. Press Edit settings:

82485_28

7. Choose the following Option to start upgrade of CDH:

82485_29

8. Choose version 5.5:

82485_30

9. Make sure you have backed up all databases:

82485_31

10. The following screen indicates that we are all set to proceed. Press Continue:

82485_32

11. CDH Version 5.5 parcels will now be downloaded, distributed to all nodes and unpacked. Press Continue:

82485_33

12. Hosts will be inspected for correctness. Press Continue:

82485_34

13. Verify that no party is using the HH-TEST Cluster. Choose Full Cluster Restart. Press Continue:

82485_35

14. The HH-TEST cluster will now be stopped. Upgraded and restarted. Press Continue:

82485_36

15. Confirmation screen show now show the upgraded version of CDH. Press Continue:

82485_37

16. Review additional post-upgrade instructions. Press Finish.17. Verify CDH version on Cloudera Manager Home Page:

82485_38

18. Verify CDH version on back-end. SSH to any node in the cluster:$ hadoop version
Hadoop 2.6.0-cdh5.5.0
Subversion http://github.com/cloudera/hadoop -r fd21232cef7b8c1f536965897ce20f50b83ee7b2
Compiled by jenkins on 2015-11-09T20:37Z
Compiled with protoc 2.5.0
From source with checksum 98e07176d1787150a6a9c087627562c
This command was run using /opt/cloudera/parcels/CDH-5.5.0-1.cdh5.5.0.p0.8/jars/hadoop-common-2.6.0-cdh5.5.0.jar
$ hadoop fs -ls /
Found 3 items
drwxrwxr-x – solr solr 0 2015-11-26 20:58 /solr
drwxrwxrwt – hdfs supergroup 0 2015-11-27 02:29 /tmp
drwxr-xr-x – hdfs supergroup 0 2015-11-27 02:29 /user19. This completes the upgrade of CDH:

[/vc_column][/vc_row]

 

Discover more about our expertise in Hadoop.

Categories: DBA Blogs

DATA COLLECTOR AND POLICY-BASED MANAGEMENT: PART 3

Pythian Group - Fri, 2015-12-04 06:32

 

In my previous post I talked about how create and use a policy against your SQL Server Instance (don’t forget to check our part 1 in this series too). Now we will talk about the Data Collector and how configure it on your environment.

What is the Data Collector?

The Data Collector (DC) is one of the main components in the set of tools for data collection provided by SQL Server. With the DC we can define a centralized point for storage of all collected metrics through the instances of the SQL Server database in your infrastructure, and these metrics can be from multiple sources and not just related to performance metrics.

To increase the efficiency of the metrics collected, you must adjust the DC according to each existing infrastructure environment (development, approval, production). The DC stores all the collected information in a Management Data Warehouse (MDW) and allows you to set different retention periods for each metric that will be collected.

As the DC has a programming interface (API), we can customize collections for any other type of desired metric. However, in this article, we will focus only on the three collections of the DC system: Disk Usage, Query Activity and Server Activity. Figure 28 shows how the DC fits in the strategy for collecting and managing data in a SQL Server database.

 

Image28
Figure 28. Data collection strategy.

 

The Data Collector Architecture

Before starting the implementation of the DC, it is necessary to understand which components are part of this feature. They are:

  • Target: An instance of the SQL Server database that supports the process of collecting metrics by using the DC.
  • Target Type: Defines the type of target which will collect metrics. For example, an instance of SQL Server database has different metrics than the metrics collected from a SQL Server database itself.
  • Data provider: A data source that will provide metrics for the collector type.
  • Collector Type: A delimiter for the packages in the SQL Server Integration Service (SSIS), which provides the mechanism for the collection and storage of the metrics in the MDW.
  • Collection Item: Is a collection item in which are defined which the metrics will be collected, how often this gathering will be held and what is the retention time of the metric stored.
  • Collector Set: A set of Collection Items.
  • Collection Mode: The way that the metrics will be collected and stored in the MDW. The metrics can be collected on an ongoing basis (Cached Mode) or through a scheduling sporadic (Non-Cached Mode).
  • Management Data Warehouse (MDW): The relational database used to store all the collected metrics.

 Note: In SQL Server 2014 we have the following collector types: Generic T-SQL Query, Generic SQL Trace, Performance Counters and Query Activity.

The Figure 29 shows the dependencies and relationships between the components of the DC.

 

Image29
Figure 29. Relationship between the components of the DC.

The data provider is an external component in DC architecture and which, by definition, has an implicit relationship with the target. A data provider is specific to a particular target and provides metrics through views, performance counters and components of Windows Management Instrumentation (WMI) are consumed by the DC.

We can visualize from Figure 29 that a collector type is associated to a particular target, and that this relationship also defines how the metrics will be collected and what the storage schema of these metrics, for the collector type also provides the location of MDW, which can be on the server that is running the collection or on a centralized server.

A collection item has a default and collection frequency that can only be created within a collector set. The collector set, in turn, is created on the instance of SQL Server that will be monitored through the DC and consists of one or more collection items. The collection of the set of metrics defined in the collector set is accomplished through Jobs executed by the SQL Server Agent service, and the metrics collected are stored in the MDW periodically through predefined schedules.

The Figure 30 shows a collector set called system Disk Usage, in which we visualize that the configuration was performed with the collection mode set to Non-Cached, using two collection items of type Generic T-SQL Query Collector Type, and that the metrics are collected every 60 seconds, with retention of these metrics in the MDW for 730 days.

 

Image30
Figure 30. Definition of the collector system set Disk Usage.

It is important to note that the DC is fully integrated with the SQL Server Agent service and using Integration Services, using both intensively. After the DC configuration, the process of collecting and recording of metrics is accomplished by a set of SQL Server Agent Jobs created and started automatically.

 

Management Data Warehouse (MDW)

So we can use the metric collection through the DC, though you must first perform the configuration of the MDW that will be the relational database responsible for storing all the metrics collected by the collector sets.

For this we can use an existing relational database and configure it as a MDW. However, it is recommended that you set a new database, because during the configuration process of the MDW several schemas and tables relating to DC will be created. The schemas are generated automatically after the configuration of DC are the core and the snapshot. A third schema, custom_snapshots, name will be created when a collector set as customized is set by the administrator of the Bank.

The main schema of the MDW is the core, because it has the tables, stored procedures, and views that are available to all collector types that will also be used to organize and identify the metrics collected. To ensure the integrity and security of MDW, all database objects belonging to the core schema can only be changed by members of the Profiles database db_owner and mdw_admin.

The Table 2 lists all the existing tables in the core schema and their respective descriptions.

Table2

Table 2. Core schema tables.

 

The schema snapshot, in turn, owns the objects required for the storage of collected metrics through the system collection sets. The tables in this schema can only be changed by members belonging to the database profile mdw_admin.

The Table 3 illustrates which tables are used by collection sets of Server Activity system and Query Statistics, created after the setting of DC.

Table3
Table 3. Tables used by collection sets.

 

Already the custom_snapshot schema has the tables and views that were created when a custom collection set has been configured. Any custom collection set that you need a new table for to store collected metrics can create tables in this schema. The tables can be added by any member of the mdw_writer database.

 

Configuring the Data Collector

To exemplify the metric using the DC collection, we have the instance VITADB\SQLCMS, responsible for hosting the MDW database, and instances VITADB\SQLINSTANCE1 and VITADB\SQLINSTANCE2, which will have your metrics collected through the collector sets. That said, the first step to setting the DC is the creation of the MDW in VITADB\SQLCMSinstance, as the following steps:

1)   Through the Object Explorer, select the folder Management.

2)    Right-click on Data Collection -> Task -> Configure Management data warehouse.

3)     In the dialog box that appears (see Figure 31), select the VITADB\SQLCMS instance and create the MDW database via the button New.

4)     Select which logins have access to the MDW database ( Figure 32), and then click Finish.

 

Image31
Figure 31. Creation of the MDW.

 

Image32
Figure 32. Definition of permissions to the MDW.

 

Note: The members of the mdw_admin database have permission of SELECT, INSERT, UPDATE, and DELETE, in addition to being able to change any .MDW schema and perform the Jobs of maintaining DC. The members of the mdw_writer database have permission to upload the collected metrics to the MDW. Already members of the database profile mdw_reader have only SELECT permission on MDW.

After the creation and configuration of the MDW in VITADB\SQLCMS instance, you must start the process of collecting metrics on instances VITADB\SQLINSTANCE1 and VITADB\SQLINSTANCE2 by setting the collector sets of system in each of the instances and directing the metrics collected for the MDW database.

For the configuration of the collector sets of a system, we have the following steps:

1)  Through the Object Explorer, select the folder Management.

2)  Right-click on Data Collection -> Tasks -> Configure Data Collection.

3)  In the dialog box that appears (Figure 33), connect on VITADB\SQLCMS instance, select the MDW database, the collector set that you want, and then click Finish. For this example we will use the collector set of system System Data Collection Sets, which will be automatically created after Setup.

 

Image33
Figure 33. Collector set definition.

Complete the configuration of the collection, we’re creating three collector sets of system: Disk Usage, Query Statistics and Server Activity. The collector set Disk Usage collects metrics about the growth of data files (.mdf and .NDF) and log files (.ldf) user databases and existing systems in instance monitored by DC. With this information it is possible to know what the daily growth trend is, in MB, of the files examined.

Table 4 shows the properties of the collector set of system Disk Usage.

Table4

Table 4. Collector set properties of system Disk Usage.

In turn, the collector set of Server Activity system collects server activity metrics, statistics, performance, blocking chains, General information of memory, CPU, and network.

The Table 5 shows the properties of the collector set of Server Activity system.

Table5

Table 5. Collector set properties of Server Activity system.

Finally, the collector set of Query Statistics collection system metrics for queries executed against the database monitored by the ad, as statistics, execution plans, most costly queries in relation to your use of disk, CPU, memory and queries that took more time to be finalized.

Table 6 shows the properties of the collector set of Query Statistics system.

Table6

Table 6. Properties of the collector set of Query Statistics System.

 

You will see the metrics collected:

The metrics collected by DC can be accessed directly by T-SQL queries. However, after setting the collector sets of system, some standardized reports become available for viewing. To access them it is necessary to right-click on Data Collection -> Reports -> Management Data Warehouse. After the configuration of the collector sets, three reports are available. We will look at each of them in the following subtopics.

 

Server Activity History

All available information in this report is related to the use of resources of the database server, such as CPU or memory allocated in total, of which the biggest wait types that exist in SQL Server, the value of IOPs, among others. All this information is extremely useful for troubleshooting and tuning.

The Figure 34 shows the top of the Server Activity Historyreport, extracted from the VITADB\SQLINSTANCE2 instance.

Image34

Figure 34. The top of the report Server Activity History.

 

At the top of the report we visualize which SQL Server instance metrics are displayed and at what date and time it was requested. Below this information you can select what time period, in which the metrics were collected, must be loaded in the report. In each of the graphics presented, there is information about the operating system (green lines) and on the SQL Server (blue lines).

 

Figure 35 shows Server Activity History, extracted from the VITADB\SQLINSTANCE2 instance at the bottom of the report.

 

Image35
Figure 35. The bottom of the Server Activity History report.

 

These reports are also extremely useful in the process of performance analysis and troubleshooting, because they display the biggest wait types and what main types of activities that occur in the instance. From any of these reports we can visualize more details by selecting one of the lines or data bars and performing a drill-down on the desired information.

Note: Immediately after the setting of DC there will be information to be loaded in the reports, and the more metrics collected and stored in the MDW, the greater the detail achieved through the reports.

 

Disk Usage Summary

This report lists the size of the databases that are monitored by the ad and what the average growth is over a period of time. The metrics displayed by the report are separated by data files and the log files of monitored databases. As shown in Figure 36, each of the data files and log files has the initial size information, the current size and the average growth per day in MB.

 

Image36
Figure 36. Disk Usage Summary Report.

 

Query Statistics History

The most common reason for performance issues found in SQL Server is writing T-SQL commands inefficiently. Therefore, the collection of performance metrics of these consultations is an essential part for the tuning process. By default, you can view the 10 queries that consume more CPU, but you can change this filter and view the queries that carried out more IO operations, how long they were running, held more physical reads or carried out more logical writings.

 

Figure 37 shows the report Query Statistics History, extracted from the VITADB\SQLINSTANCE2 instance.

 

Image37
Figure 37. Query Statistics History Report.

 

Recommendations for Configuration of DC

To ensure that there is minimal impact during the environmental monitoring process of a SQL Server database by DC, adopt the following recommendations:

  • Use a centralized server to the MDW, it allows that there is only one location for execution and visualization of reports.
  • All database SQL Servers that will be monitored by the DC should be part of the same domain.
  • When creating a custom collector set using the collector type Generic SQL Trace, define a set of filters so that only the really necessary metrics are collected, because in this way the MDW doesn’t store unnecessary information.
  • Before you create a custom collector set using performance counters, you can be sure the collector set of Server Activity system is no longer collecting this metric.
  • If any collections of metrics across multiple T-SQL queries are carried out with the same frequency, combine them in a single collector set. Doing this we will reduce the amount of memory used by the DC executable (DCCEXEC.exe) while gathering metrics. Similarly, combine multiple collection items of type Performance Counters in a single collection item whenever possible.
  • Combine multiple collection items in a single collector set whenever possible. The only reason to create collector sets apart is if there are different retention periods or a different collection schedule.
  • A collector set using the collection mode set to Cached should always keep a running collection process. If the metrics are collected often, this is more efficient than starting and stopping the collection process where new metrics should be collected. In contrast, the collection mode set to NonCached doesn’t have a running collection process most of the time, that is, a new collection process will be started according to the predefined schedule and so will be stopped again, avoiding the excessive use of server hardware resources. So, if the metric collection occurs rarely, the collection mode set to NonCached is more efficient than leaving the collection process on hold most of the time. As a general rule, if the metric needs to be collected every five minutes or more often than that, consider configuring a collector set using the collection mode: Cached. If the collection of metrics can be performed with a frequency greater than five minutes, it is recommended to configure a collector set using the collection mode: Non-Cached.
  • The higher the frequency, the greater the overhead on the database server. In this way, choose to always configure the lowest frequency possible that meets the need of collecting.

 

Conclusion

As described earlier, beginning with SQL Server 2008 we have two tools that facilitate the management of a SQL Server database consisting of multiple instances. They are: the Policy-Based Management and the Data Collector.

Using the PBM you can create policies that assess certain conditions in existing objects on the instance of a SQL Server database. These policies can be designed manually or imported through XML files available after the installation of the database engine. Policies can be evaluated manually (OnDemand), following a predefined schedule (OnSchedule) or at the time a particular property of a SQL Server object is changed (OnChange) and can also be evaluated on multiple SQL Server instances at once, through the Central Management Server functionality.

With the DC we have a feature that collects metrics for all SQL Server instances and stores them in a centralized database, called the Management Data Warehouse. Through the configuration of DC and the Management Data Warehouse, three collector sets of the system are created. They collect metrics concerning the utilization of server hardware resources (CPU, memory, disk, and network), growth of data and log files of monitored databases in addition to the more costly T-SQL queries executed on the database server, which is accomplished through the collection of Jobs defined on a SQL Server Agent.

It is worth noting that the DC also provides a wide range of reports, so that the metrics collected by the collector sets can be evaluated during troubleshooting and tuning processes. Finally, note that the DC is a complete monitoring tool, that needs to be configured in the best possible way to avoid a high overload to database servers.

Until next time!

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

AWS Redshift Cluster Sizing

Pythian Group - Thu, 2015-12-03 15:50

 

Sizing a database is one of the primary DBA functions no matter what the technology. Though Redshift is highly managed for us, we must still address this task.

The first thing to note is that in sizing a cluster, we start with an estimated need of storage capacity, since the amount of storage available per node of the cluster is a fixed amount. While you get the disk space you pay for, AWS guidelines and user experience shows that performance can suffer when space becomes tight (>80%). So when sizing the cluster for a specific capacity an extra 20% will need to be tacked onto your calculations.

AWS currently offers two types of instances for Redshift clusters, dense compute (dc1.*) or dense storage (ds2.*) servers. The dc1 series offers about 6x the CPU and 6x the memory per terabyte of storage. These are good for use cases where there is a regular load of heavy analytics querying involving multiple joins. The ds2 series is more cost effective when the tables are highly denormalized and the analytics can be offloaded into a BI tool such as Microstrategy. It is possible to migrate to a new node type through snapshots, but the process will involve some downtime.

To address a few points of the Redshift architecture, note that only the compute nodes hold storage so the leader node is not considered (nor do you have to pay for it). Each storage disk is replicated to two others for redundancy, but these additional disks are not part of the calculations or specifications used for sizing (though they affect the disk monitoring calculations that we see later). Here we examine example clusters on a budget of around $10k using yearly contracted instances (saving 30+% over on-demand instances):

(7) dc1.large with 160Gb SSD @ $1380/year = 1120Gb @ $9660/year.
(2) ds2.xlarge with 2Tb HHD @ $4295/year = 4000Gb @ $8590/year.

The dc1 instances in this case are around 4x as expensive per terabyte (though still quite the bargain as compared to hosting the cluster in-house) and give a 30-80% performance gain (depending on the benchmarks, (example)). And while you can always add nodes to accommodate data growth, you can only add nodes of the same instance type which could potentially become quite expensive if you’re using instances of the small disk capacity dc1’s.

Once your cluster is up, it is vital to monitor disk and CPU utilization so you know when to add new nodes. It is highly advisable to watch the graphs under the Performance tab in the Redshift Console as you add new load to the cluster.

There are built in Cloudwatch alarms for disk usage, and these should be configured to alert above 70%. I like to know well in advance when it is getting there, so I regularly use Period= 5 minutes, Statistic = average, over 1 consecutive period, but since loads and vacuums can create usage surge spikes, you might want to configure the alert over more or longer periods. While Cloudwatch is great for this monitoring, it is convenient to also be able to compute capacity. There are several ways to query disk usage that render subtly different results, unfortunately none of which will yield the stats given by Cloudwatch. Here’s an example for a 6-node 12Tb cluster that currently shows disk space as 32% used on each node in the Console yet displays as 23%:

select
host
,sum(capacity)/3 as total
,sum(used)/3 as used
,sum(capacity)/3 – sum(used)/3 as free
,(((sum(used)/3)/(sum(capacity)/3.00)) * 100.00) as pct_used
from STV_PARTITIONS
group by host

hosttotalusedfreepct_used01904780450450145433023.6511904780449895145488523.6221904780449776145500423.6131904780450673145410723.6641904780451483145329723.751904780447840145694023.51

The point here is to be wary of querying disk space and rely on Cloudwatch and the Console.

Compression encoding on your tables can save substantial space (50-75% on average depending on the encoding) and can improve performance by 100+%. Encoding can also increase CPU usage, so we want to monitor it as we implement encoding. A cluster can be brought to a standstill by just one node hitting 100% CPU utilization, so we also need to setup Cloudwatch alarms to make sure we average < 70% or don’t hit spikes > 90% for more than 10 minutes. Once we bump up against those metrics, it’s time to add another node.

 

Discover more about our expertise in the Cloud.

Categories: DBA Blogs

Auditing DBMS_STATS usage

Hemant K Chitale - Thu, 2015-12-03 09:05
(I will be returning to the Tracing series .... but a quick diversion because I had received a request for assistance on auditing DBMS_STATS usage)

First, I setup auditing

SQL> alter system set audit_trail='DB_EXTENDED' scope=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> audit execute on sys.dbms_stats;

Audit succeeded.

SQL>

Next, I run a DBMS_STATS call and check the audit trail for it.

SQL> connect hemant/hemant
Connected.
SQL> create table obj_all_list as select * from all_objects;

Table created.

SQL> execute dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.lock_table_stats('','OBJ_ALL_LIST');

PL/SQL procedure successfully completed.

SQL>

Now, I check the Audit Trail.

SQL> connect / as sysdba
Connected.
SQL> set pages600
SQL> select to_char(timestamp,'DD-MON HH24:MI:SS'), username, userhost, sql_text
2 from dba_audit_object
3 where obj_name = 'DBMS_STATS'
4 and timestamp > trunc(sysdate)
5 order by timestamp;

TO_CHAR(TIMESTAMP,'DD-MO USERNAME
------------------------ ------------------------------
USERHOST
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
03-DEC 22:58:35 HEMANT
ora11204
BEGIN dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUM
NS SIZE 1'); END;


03-DEC 22:58:50 HEMANT
ora11204
BEGIN dbms_stats.lock_table_stats('','OBJ_ALL_LIST'); END;



SQL>

Note : Execution of DBMS_STATS by SYS would not be audited in the database audit trail table.  As you can see below :

SQL> execute dbms_stats.unlock_table_stats('HEMANT','OBJ_ALL_LIST');

PL/SQL procedure successfully completed.

SQL> select to_char(timestamp,'DD-MON HH24:MI:SS'), username, userhost, sql_text
2 from dba_audit_object
3 where obj_name = 'DBMS_STATS'
4 and timestamp > trunc(sysdate)
5 order by timestamp;

TO_CHAR(TIMESTAMP,'DD-MO USERNAME
------------------------ ------------------------------
USERHOST
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
03-DEC 22:58:35 HEMANT
ora11204
BEGIN dbms_stats.gather_table_stats('','OBJ_ALL_LIST',method_opt=>'FOR ALL COLUM
NS SIZE 1'); END;


03-DEC 22:58:50 HEMANT
ora11204
BEGIN dbms_stats.lock_table_stats('','OBJ_ALL_LIST'); END;



SQL>

I would need AUDIT_SYS_OPERATIONS and AUDIT_FILE_DEST to capture audit of actions by SYS.


SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB_EXTENDED
SQL>

.
.
.
Categories: DBA Blogs

Syncing Inconsistent MySQL Slaves

Pythian Group - Wed, 2015-12-02 11:52

 

Checksum is a standard practice among DBAs to verify the data consistency across replicated nodes. In this post we’re going to review the syncing options for an inconsistent MySQL slave of Galera cluster node.

Here we’re assuming a setup of regular replication to a MySQL instance from one of the Galera cluster nodes.
PXC-slave-checksum
In the usual MySQL replication setup, standard practice involves the usage of the pt-table-checksum tool to identify the discrepancies and usage of pt-table-sync to bring them in sync. The checksum tool, pt-table-checksum, can run across Galera cluster node to verify the data consistency and confirm if the MySQL slave is consistent with a chosen primary node.

What happens if this Galera cluster’s regular MySQL slave sees data inconsistency on it? Will pt-table-sync work there? The answer to this depends…
pt-table-sync when used with –sync-to-master causes it to take locks on master but Galera doesn’t like those lock attempts.

You may ask, why locks on a master?

Coz’ pt-table-sync will treat the master as the source and the slave as the destination. It will lock the table (–lock=1) on master, apply changes on master which will eventually be replicated to slave and thus causing the sync.

 

Respective snippet of code from pt-table-sync script V.2.2.15:

	lock_server(src => $src, dst => $dst, %args);
	...
	$exit_status |= sync_a_table(
               src   => $src,
               dst   => $dst,
               where => 1,  # prevents --where from being used
               diff  => $diff,
               %args,
            );
	 ...
         unlock_server(src => $src, dst => $dst, %args);

Again… coming back to our point, pt-table-sync wouldn’t work well on Galera with –sync-to-master. Let’s do an attempt:

PTDEBUG=1 ./pt-table-sync --verbose --execute --replicate=pyth.checksum --sync-to-master --tables slave_repl.tmp h=localhost,u=root,p=$pw > slave_repl.tmp.sql

If you run the above command (on slave) with debug you will note following error:

	# TableSyncer:6114 4650 Committing DBI::db=HASH(0x177cf18)
	Deadlock found when trying to get lock; try restarting transaction at line 6115 while doing slave_repl.tmp on localhost

(without PTDEBUG you won’t see much on slave except it will report nothing changed!)

Great, so why the error? Let’s again check the code snippet (pt-table-sync V.2.2.15):

	sub lock_table {
	   my ( $self, $dbh, $where, $db_tbl, $mode ) = @_;
	   my $query = "LOCK TABLES $db_tbl $mode";
	   PTDEBUG && _d($query);
	...

As you see, it’s calling up for LOCK TABLES and Galera, which as we know, doesn’t support explicite locking because of the conflict with multi-master replication. That’s the reason for the error above.

Okay, let continue… Upon executing pt-table-sync on slave, the “master”‘s (Galera node’s) error-log will show the following error:

	2015-08-27 14:45:07 6988 [Warning] WSREP: SQL statement was ineffective, THD: 17, buf: 1399
	QUERY: commit
	 => Skipping replication

 

We already have a bug report in place and if it affects you, go ahead and mark it so.

So how would you fix this?

Easy Answer: Do a complete rebuild of slave from a fresh data backup of the cluster node.
Desync the cluster node, take the backup and restore it on a slave machine and setup replication.

But let’s think about an alternate method other than a complete restore…

“fixing using pt-slave-restart?”

But pt-table-sync is “not Galera Ready” as they say!!

Even then, pt-table-sync can help us understand the differences and that’s the long answer :)

 

It can still work and prepare SQL for you using –print –verbose options.

./pt-table-sync --verbose --print --replicate=pyth.checksum --sync-to-master --tables slave_repl.tmp h=localhost,u=root,p=$pw > slave_repl.tmp.sql

So, all you need to do is run the SQL against a slave to fix the discrepancies. You may choose to desync the node and run the pt-table-sync to generate differential sql. You’ll still need to confirm if slave got synced by re-running the pt-table-checksum and the discrepancies are resolved.

Our steps to resync a PX Cluster’s slave using pt-table-sync are as follows:

(Note: It’s advisable to stop writes on cluster to fix the discrepancies on slave. “Why?” “Explained later.”)

– Desync master node:

set global wsrep_desync=ON;

– Generate differential SQL:

./pt-table-sync --verbose --print --replicate=pyth.checksum --sync-to-master --tables slave_repl.tmp h=localhost,u=root,p=$pw > slave_repl.tmp.sql

– Review the sql generated and execute them on slave.

– Once slave is synced, you can:

set global wsrep_desync=OFF;

– Finally rerun the pt-table-checksum to verify the discrepancies.

That concludes our solution.

 

“Wait, but why desync?”
hmm… Well wsrep_desync is a dynamic variable which controls whether the node can participate in Flow Control.

 

“hold on!! Flow control??”
Galera has synchronous replication where in node provides the feedback to rest in the group – fellas-I’m-late-hold-on OR okay-let’s-continue-the-job. So this communication feedback is flow control. (You should read galera-documentation & Jay’s post).

When we will set wsrep_desync=ON on master, it will continue to replicate in and out the writesets as usual; but flow control will no longer take care of the desynced node. So, other nodes of the group won’t bother about the deynced node lagging behind. Thus by desyncing we’re making sure that our operations on one node are not affecting the whole cluster. This should answer why writes need to be stopped before starting to sync.

Hope this helps.

 

Discover more about our expertise in MySQL.

Categories: DBA Blogs

Data Collector and Policy-Based Management: Part 2

Pythian Group - Wed, 2015-12-02 11:20

 

In my previous post we talked about the architecture of Policy-Based Management. In this post we will create and run a policy against some SQL Server Instances.

Creating a Policy

In SQL Server 2014 you are allowed to create a policy manually via T-SQL, export existing policies in an instance of a SQL Server database or import policies made available by Microsoft. When designing a policy we can use advanced conditions that allow almost unlimited amounts of validation on database objects existing in an instance.

To facilitate the understanding of all the components used in a policy and how the same interact with each other, we will create a policy from scratch. To do this, you must first define a condition and then the policy used in this condition. With the policy set, it will be possible to categorize it and define which targets should be evaluated.

In order to illustrate this in a practical way we will use PBM. From now on, we will set a policy that will evaluate if all databases in a given SQL Server instance are disabled with the AutoShrink property.

Creating a Condition

The first step in the creation of the policy is the setting for the condition, which can be accomplished through SQL Server Management Studio (SSMS) or by using T-SQL commands.

To create a condition with SSMS, we have the following steps:

1)  Using the Object Explorer, open the folder Management and then the Policy Management folder (see Figure 8):

Image8
Figure 8. Folder Policy Management.

 

2)  Within that folder, right-click the folder Conditions and select the New Condition.

3)  This will open the window for the creation of the new condition, as it exposes the Figure 9.

 

Image9

Figure 9. Condition creation window.

 

According to this figure, while creating the condition these were the following options:

  • Name: The condition was created with the name AutoShrink False.
  • Facet: The facet used was the Database.
  • Expression: The property was valued @AutoShrink and its value must be equal to false.

Additionally, you can include a detailed description about the condition through the Description, as shown in Figure 10.

 

Image10
Figure 10. Condition description window.

The second option for creating the condition is through the system stored procedure SP_SYSPOLICY_ADD_CONDITION. The Listing 1 shows that the creation of condition AutoShrink is False, with the same options set in SSMS.

Listing 1. Creating the condition with T-SQL.

&amp;lt;/pre&amp;gt;
Declare @condition_id int

EXEC msdb. dbo. sp_syspolicy_add_condition @name=N ' AutoShrink False ', @description=N ' ', @facet=N ' Database ', @expression=N ' &amp;lt;Operator&amp;gt;

&amp;lt;TypeClass&amp;gt;Bool&amp;lt;/TypeClass&amp;gt;

&amp;lt;OpType&amp;gt;EQ&amp;lt;/OpType&amp;gt;

&amp;lt;Count&amp;gt;2&amp;lt;/Count&amp;gt;

&amp;lt;Attribute&amp;gt;

&amp;lt;TypeClass&amp;gt;Bool&amp;lt;/TypeClass&amp;gt;

&amp;lt;Name&amp;gt;AutoShrink&amp;lt;/Name&amp;gt;

&amp;lt;/Attribute&amp;gt;

&amp;lt;Function&amp;gt;

&amp;lt;TypeClass&amp;gt;Bool&amp;lt;/TypeClass&amp;gt;

&amp;lt;FunctionType&amp;gt;False&amp;lt;/FunctionType&amp;gt;

&amp;lt;ReturnType&amp;gt;Bool&amp;lt;/ReturnType&amp;gt;

&amp;lt;Count&amp;gt;0&amp;lt;/Count&amp;gt;

&amp;lt;/Function&amp;gt;

&amp;lt;/Operator&amp;gt; ' , @is_name_condition=0, @obj_name=N ' ', @condition_id=@condition_id OUTPUT

Select @condition_id

GO

 

Creating a Policy

Now that we have a condition, we can define a policy that uses it. As a condition, the policy can be configured either through the SQL Server Management Studio, such as through T-SQL.

First we create a policy using the SSMS:

1)  Using the Object Explorer, open the folder Management and then the Policy Management folder (see Figure 8).

2)  Within that folder, right-click on the Policies folder and select New Policy.

3) This will open the window for the creation of the new policy, as shown in the Figure 11.

Image11

Figure 11. Window for policy creation.

 

According to the Figure 11, during the creation of the policy the following options were set:

  • Name: The policy was created with the name Checks AutoShrink.
  • Check Condition: The condition evaluated by policies is the AutoShrink False.
  • Against Targets: The policy should be evaluated in any existing database on the instance of SQL Server.
  • Evaluation Mode: The evaluation mode selected was the OnDemand, which determines that this policy must be performed manually.
  • Server Restriction: To this policy there will be no conditions that restrict the database servers.

Note: As a target it won’t always be a database. The targets change based on the context and evaluation of the condition. For example, if we create a policy to standardize the name of new tables using the facet Tables, Against Targets option will display All Tables.

The evaluation modes available in the list depend on the facets available in the condition. All facets support the OnChange and OnSchedule, but the OnChange: Prevent depends on the possibility of using the DDL triggers to perform the transaction rollback procedure. Already the evaluation mode OnChange: Log Only is based on the capacity of changes in the facet being captured for an event.

To facilitate the maintenance and management of configured policies, we can add more details through the Description, like Figure 12.

Image12

Figure 12. Policy description window.

 

In addition, you can also perform the creation of policies using the system stored procedures SP_SYSPOLICY_ADD_OBJECT_SET, SP_SYSPOLICY_ADD_TARGET_SET, SP_SYSPOLICY_ADD_TARGET_SET_LEVEL and SP_SYSPOLICY_ADD_POLICY.

The Listing 2 exemplifies the creation of policy Checks AutoShrink, with the same options set in SSMS.

Listing 2. Creation of the policy with T-SQL.


Declare @object_set_id int

EXEC msdb. dbo. sp_syspolicy_add_object_set @object_set_name=N ' AutoShrink_ObjectSet_1 ' Checks, @facet=N ' Database ', @object_set_id=@object_set_id OUTPUT

GO

Declare @target_set_id int

EXEC msdb. dbo. sp_syspolicy_add_target_set @object_set_name=N ' AutoShrink_ObjectSet_1 ' Checks, @type_skeleton=N ' Server/Database ', @type=N ' DATABASE ', @enabled=True, @target_set_id=@target_set_id OUTPUT

GO

EXEC msdb. dbo. sp_syspolicy_add_target_set_level @target_set_id=@target_set_id, @type_skeleton=N ' Server/Database ', @level_name=N ' Database ', @condition_name=N ' ', @target_set_level_id=0

GO

Declare @policy_id int

EXEC msdb. dbo. sp_syspolicy_add_policy @name=N ' Checks AutoShrink ', @condition_name=N ' AutoShrink False ', @execution_mode=0, @policy_id=@policy_id OUTPUT, @root_condition_name=N ' ', @object_set=N ' AutoShrink_ObjectSet_1 ' Checks

GO

Note: All policies created are stored in the system database msdb. After we create our policies, we have to make sure the system database msdb is part of the backup strategy used in the environment, because in this way it will be possible to rebuild all policies if there is some migration or disaster with the current environment.

 

Importing Policies

In order to increase the efficiency in the administration of a database environment, we can import policies made available by Microsoft during the SQL Server installation process. These policies are stored in XML format files that are located in the Tools directory where SQL Server was installed.

The import of predefined policies has some advantages, because along with the policies all necessary conditions for the correct operation are created. In addition to the predefined policies we can import an XML file that was generated based on customized policies already created by the database administrator using the PBM.

To run the import of a policy we must perform the following steps:

1)  Using the Object Explorer, open the Management folder (see Figure 8).

2)  Within that folder, right-click the Policies folder and select the option Import Policy.

3)  This will open the window to import the XML file with policy settings, as shown in the Figure 13.

Image13

Figure 13. Importing a policy.

 

To avoid duplication during the import, select Replace duplicates with Items Imported to overwrite any policy and condition that has the same name as the policy that is being imported. To overwrite an existing policy, the information will not be lost if the validation was already carried out within the policy.

We can also preserve the default configurations of the policy being imported, as well change them after importation policy, as Figure 14 presents.

Image14

Figure 14 . Definition of the status of the policy.

 

After importing the XML file containing the definitions of the policy, we can see it in the Policies folder. As shown in Figure 15, the new policy was created with the name Data and Log File Location.

Image15

Figure 15. Policy created through the import process.


It is interesting to note that unlike the previous created policy, the policy created through the import process has a constraint that limits the server validation of existing conditions only in instances of SQL Server using Enterprise or Standard editions.

Figure 16 shows the General information that was automatically inserted when imported through the policy XML file. The category, description and hyperlinks were populated, facilitating the process of documentation that states the reason why this policy has been implemented.

Image16

Figure 16. Description of the policy created through the import process.

 

Exporting Policies

Similarly, we can import policies using XML format files, and you can export the policies already created for files of this format. From these files can import these same policies in other SQL Server servers.

To perform the export policy procedure in XML format, there are two options:

  1. Exporting an existing policy.
  2. Exporting the current state of a facet.

To export an existing policy to an XML file, simply execute the following steps in SQL Server Management Studio:

1)  Using the Object Explorer, open the Management folder (Figure 8).

2)  Within that folder, open the folder Policy Management -> Policies to list existing policies (see Figure 17).

3)  Right-click the policy that you want to export and select Export Policy option (see Figure 18).

4)  According to the Figure 19, select the desired location to write the XML format file and the name of the same.

Image17

Figure 17. List of existing policies.

 

 

Image18
Figure 18. Export the policy.

 

 

Image19
Figure 19. Selecting policy storage location.

 

You can also export a policy by sourcing the current setting from the properties of a facet. For example, after setting up a customization for facet properties called Surface Area Configuration, you can export these settings to a file in XML format as follows:

1)  Through the Object Explorer, right-click the SQL Server instance and select Facets (see Figure 20).

2)  Select the facet you want, and then click Export Current State as Policy (Figure 21).

3)  According to Figure 22, fill in the name of the policy, the name of the condition and the export target of policy.

 

Image20
Figure 20. Listing the facets.

 

Image21
Figure 21. Exporting the current state of the facet as a policy.

 

Image22
Figure 22. Setting the policy storage location.

 

Policy Evaluation

Policy evaluation is the process in which we execute the policy in a target determined and reviewed by the same results. The PBM allows a policy to be evaluated in a single instance or a group of instances using the CMS. As the purpose of this article is the management of multiple instances, we will use the CMS to evaluate the policy Checks AutoShrink, created earlier, in two instances of the SQL Server database.

The Table 1 shows instances of SQL Server that will be used to assess the policy defined by the PBM.

Name of the InstanceVersionEditionBuildVITADB\SQLCMSSQL Server 2014Enterprise12.0.2000VITADB\SQLINSTANCE1SQL Server 2014Enterprise12.0.2000VITADB\SQLINSTANCE2SQL Server 2014Enterprise12.0.2000

Table 1. Instances used by PBM

 

To use the VITADB\SQLCMS instance as our management, we must perform the following steps:

1) Right-click the Central Management Servers option and select the option Register Central Management Server (see Figure 23).

2) In the dialog box New Server Registration, fill in the connection information, in accordance with the Figure 24.

3) Right-click the VITADB\SQLCMS instance and select New Server Registration (Figure 25).

4) Repeat the procedure described in step 3 and record the instances VITADB\SQLINSTANCE1 and VITADB\SQLINSTANCE2.

5) Right-click the VITADB\SQLCMS instance and select Evaluate Policies.

6) In the dialog box, select the instance that has the list of policies,  of which will be evaluated and, as shown in Figure 26, click the Evaluate button to start the validation of the selected rule.

 

Image23
Figure 23. Starting the creation of a central management instance.

 

 

Image24

Figure 24. Connection properties.

 

 

Image25
Figure 25. Registering a new server in the CMS.

 

 

Image26
Figure 26. By selecting the policy will be evaluated.

 

After the evaluation of the policy Checks AutoShrink can analyze, as in the Figure 27, there is a database named DBTeste1 on the VITADB\SQLINSTANCE1 instance and a database named DBTeste2 on the VITADB\SQLINSTANCE2 instance that are out of politics. That is to say, where the AutoShrink property is enabled, contrary to the previously set condition that determines how to correct the situation with a disabled property.

 

Image27
Figure 27. Result of the policy Checks AutoShrink.

As you can see, PBM has managed to create and evaluate policies in one or more database instances. In this way we have a simpler and more efficient management environment composed of multiple instances.

In the last part of this series, we will take a look at the Data Collector tool and how to use it in relation to a centralized monitoring of instances of SQL Server.

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Full Database Caching in #Oracle 12c

The Oracle Instructor - Tue, 2015-12-01 09:43

If your Oracle Database fits into the buffer cache, we will put all segments into it now upon access – no more direct reads:

SQL> connect / as sysdba
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1424

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1872

The requirements are met here, I don’t need to force anything:

SQL> select force_full_db_caching from v$database;

FORCE_FUL
---------
NO

SQL> select bytes/1024/1024 as mb from dba_segments 
     where owner='ADAM' and segment_name='SALES';

	MB
----------
       600

SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
parallel_degree_policy		     string
MANUAL

The table got accessed once and is now loaded into the buffer cache. PARALLEL_DEGREE_POLICY is on the default MANUAL, remember that.

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					   42
physical reads						    0
physical reads direct					    0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads					75620
physical reads						    0
physical reads direct					    0

Only logical reads, because the table is in the buffer cache. Also for parallel queries:

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC					   LAST_QUERY SESSION_TOTAL	CON_ID
-------------------------------------------------- ---------- ------------- ----------
Queries Parallelized					    1		  1	     0
DML Parallelized					    0		  0	     0
DDL Parallelized					    0		  0	     0
DFO Trees						    1		  1	     0
Server Threads						    4		  0	     0
Allocation Height					    4		  0	     0
Allocation Width					    1		  0	     0
Local Msgs Sent 					  156		156	     0
Distr Msgs Sent 					    0		  0	     0
Local Msgs Recv'd					  156		156	     0
Distr Msgs Recv'd					    0		  0	     0
DOP							    4		  0	     0
Slave Sets						    1		  0	     0

13 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME							VALUE
-------------------------------------------------- ----------
session logical reads				       152410
physical reads						    0
physical reads direct					    0

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select name,value from v$sysstat where name='physical reads direct';

NAME							VALUE
-------------------------------------------------- ----------
physical reads direct					   21

Looks like I did In-Memory Parallel Query although PARALLEL_DEGREE_POLICY is on MANUAL, doesn’t it? Just for comparison, I did the same with an 11g version:

SQL> select banner 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> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1090

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1200

SQL> select bytes/1024/1024 as mb from dba_segments where owner='ADAM' and segment_name='SALES';

	MB
----------
       528


SQL> select count(*) from adam.sales;

  COUNT(*)
----------
  10000000

SQL> show parameter parallel_degree_policy

NAME				     TYPE			       VALUE
------------------------------------ --------------------------------- ---------
parallel_degree_policy		     string			       MANUAL

SQL> connect adam/adam
Connected.
SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							       26
physical reads									0
physical reads direct								0

SQL> select count(*) from sales;

  COUNT(*)
----------
  10000000

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							    67465
physical reads								    67433
physical reads direct							    67433

SQL> select /*+ parallel (sales,4) */ max(amount_sold) from sales;

MAX(AMOUNT_SOLD)
----------------
	    5000

SQL> select * from v$pq_sesstat;

STATISTIC				   LAST_QUERY SESSION_TOTAL
------------------------------------------ ---------- -------------
Queries Parallelized				    1		  1
DML Parallelized				    0		  0
DDL Parallelized				    0		  0
DFO Trees					    1		  1
Server Threads					    4		  0
Allocation Height				    4		  0
Allocation Width				    1		  0
Local Msgs Sent 				  116		116
Distr Msgs Sent 				    0		  0
Local Msgs Recv'd				  116		116
Distr Msgs Recv'd				    0		  0

11 rows selected.

SQL> select name,value from v$mystat natural join v$statname 
     where name in ('physical reads','physical reads direct','session logical reads');

NAME									    VALUE
---------------------------------------------------------------------- ----------
session logical reads							   135115
physical reads								   134866
physical reads direct							   134866

As you can see, I got direct reads in 11g for both the serial and the parallel query.
Do not confuse this feature with the In-Memory Option, it is much less sophisticated than that. But it doesn’t come with an extra charge at least :-)


Tagged: 12c New Features, full database caching, Performance Tuning
Categories: DBA Blogs

SQL 2016 – Improve Performance with the Query Store feature: Part 1

Pythian Group - Mon, 2015-11-30 15:19

 

After playing with this great new feature for some time and speaking about it during a SQL Saturday in Montreal, I can say that I am very excited about Query Store in SQL 2016!
Performance tuning is our bread and butter here at Pythian and I think that almost every DBA will agree that, unless you have some 3rd party tool (and even if you have such a tool), it’s not always straight forward to tune T-SQL code. It’s usually a tedious process and it’s a science by itself that requires a good background and understanding of how things work “behind the scenes”, as well as how to help the optimizer “make” the right decisions.

Here are some great links to start with:

If you don’t have a dev/testing environment, you can even start with Azure DB which has this feature already. Just note that this feature is currently at a CTP version, meaning that it is not yet supported. Azure DB is cheap and affordable. Another option would be using a dev/test Azure Virtual Machine with SQL Server 2016 on it.

 

Good luck and feel free leave us questions or comments!

 

Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Trace Files -- 8c : Still More Performance Evaluation from Trace File

Hemant K Chitale - Sun, 2015-11-29 09:46
In previous blog posts here and here, I have demonstrated Full Table Scans of a table called ALL_OBJECTS_MANY_LIST being executed via Direct Path Reads (shown as 'direct path read') in the Wait Events.

This sort of read does NOT use the Buffer Cache and the read from disk directly goes to the user's server process PGA.  Thus, the blocks read are not shareable with other users.  Multiple users reading the same table(s) via Direct Path Read are not able to share the Buffer Cache, resulting in Disk Reads being repeated for every Full Table Scan request.  (Note : The OS may be using a File System Buffer Cache or the Storage may be using a Storage Cache to service the Disk Reads, but Oracle will always see every re-read of the table as a Disk Read).

Pre-11g, this behaviour could only be possible for Parallel Query.
However, 11g introduced the feature of "serial_direct_read" which allows non-PQ FullTableScans  to bypass the Buffer Cache.  This behaviour is predicated by the size of the table (segment) vis-a-vis the Buffer Cache, in relation to what is called "small_table_threshold".  A table exceeding a certain multiple of the "small_table_threshold" (which, by default, is a certain ratio of the Buffer Cache) is read via Direct Path Reads.

The Pre-11g and the 11g behaviour for smaller tables is to use Disk Reads that feed into the Buffer Cache, making the buffers shareable for repeated reads by either the same session or other sessions.  This obviates the need to make Disk Read calls for subsequent requests (as long as the blocks are still in the Buffer Cache).  The Wait Event we'd see for such Disk Reads is the (well known) "db file scattered read".

Here's a demo with a smaller table ALL_OBJECTS_SHORT_LIST.

SQL> exec dbms_session.session_trace_enable(waits=>TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects_short_list;

COUNT(*)
----------
28117

SQL> exec dbms_session.session_trace_disable();

PL/SQL procedure successfully completed.

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3029.trc

SQL> exit
[oracle@ora11204 ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3029.trc \
> SHORT_LIST.PRF aggregate=NO sys=NO

TKPROF: Release 11.2.0.4.0 - Development on Sun Nov 29 23:13:43 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


[oracle@ora11204 ~]$

SQL ID: 69hzxtrb3dv1b Plan Hash: 1680768796

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("ALL_OBJECTS_SHORT_LIST")
FULL("ALL_OBJECTS_SHORT_LIST") NO_PARALLEL_INDEX("ALL_OBJECTS_SHORT_LIST")
*/ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM
"HEMANT"."ALL_OBJECTS_SHORT_LIST" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5")
SEED (:"SYS_B_6") "ALL_OBJECTS_SHORT_LIST") SAMPLESUB


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.11 437 72 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.11 437 72 0 1

Misses in library cache during parse: 1
isses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=72 pr=437 pw=0 time=110985 us)
3289 3289 3289 TABLE ACCESS SAMPLE ALL_OBJECTS_SHORT_LIST (cr=72 pr=437 pw=0 time=173325 us cost=19 size=61752 card=5146)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7 0.04 0.05
db file scattered read 9 0.02 0.05
--------------------------------------------------------------------------------



SQL ID: 5ayuqj4djbjxh Plan Hash: 3180576180

select count(*)
from
all_objects_short_list


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 2 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.09 140 580 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.11 142 582 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=580 pr=140 pw=0 time=90644 us)
28117 28117 28117 TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=140 pw=0 time=110678 us cost=158 size=0 card=30071)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file scattered read 21 0.02 0.07
db file sequential read 3 0.00 0.00
SQL*Net message from client 2 9.76 9.76
********************************************************************************

The first query is the Dynamic Sampling query (because no statistics exist on the target table).  As in example 8a, this is done with a mix of single block ("db file sequential read") and multiblock ("db file scattered read") waits.  Here is a sampling of the waits for the Dynamic Sampling query :
WAIT #139712598070504: nam='db file sequential read' ela= 16 file#=1 block#=61344 blocks=1 obj#=35064 tim=1448809955554822
WAIT #139712598070504: nam='db file sequential read' ela= 951 file#=1 block#=61350 blocks=1 obj#=35064 tim=1448809955555840
WAIT #139712598070504: nam='db file scattered read' ela= 956 file#=1 block#=61362 blocks=6 obj#=35064 tim=1448809955556893
WAIT #139712598070504: nam='db file sequential read' ela= 720 file#=1 block#=61370 blocks=1 obj#=35064 tim=1448809955557710
WAIT #139712598070504: nam='db file sequential read' ela= 837 file#=1 block#=61378 blocks=1 obj#=35064 tim=1448809955558589
WAIT #139712598070504: nam='db file scattered read' ela= 1020 file#=1 block#=61385 blocks=7 obj#=35064 tim=1448809955559711
WAIT #139712598070504: nam='db file sequential read' ela= 804 file#=1 block#=61392 blocks=1 obj#=35064 tim=1448809955560643
WAIT #139712598070504: nam='db file scattered read' ela= 1270 file#=1 block#=61400 blocks=8 obj#=35064 tim=1448809955562000


This is the execution of the actual FullTableScan (the count(*) query submitted by me) :
EXEC #139712598962024:c=0,e=36,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3180576180,tim=1448809955666350
WAIT #139712598962024: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=35064 tim=1448809955666420
WAIT #139712598962024: nam='db file scattered read' ela= 28044 file#=1 block#=61345 blocks=5 obj#=35064 tim=1448809955695578
WAIT #139712598962024: nam='db file sequential read' ela= 1691 file#=1 block#=61351 blocks=1 obj#=35064 tim=1448809955697475
WAIT #139712598962024: nam='db file scattered read' ela= 11 file#=1 block#=61352 blocks=8 obj#=35064 tim=1448809955697539
WAIT #139712598962024: nam='db file scattered read' ela= 64 file#=1 block#=61360 blocks=2 obj#=35064 tim=1448809955697790
WAIT #139712598962024: nam='db file scattered read' ela= 468 file#=1 block#=61368 blocks=2 obj#=35064 tim=1448809955698673
WAIT #139712598962024: nam='db file scattered read' ela= 1493 file#=1 block#=61371 blocks=5 obj#=35064 tim=1448809955700255
WAIT #139712598962024: nam='db file scattered read' ela= 64 file#=1 block#=61376 blocks=2 obj#=35064 tim=1448809955700510
WAIT #139712598962024: nam='db file scattered read' ela= 1856 file#=1 block#=61379 blocks=5 obj#=35064 tim=1448809955702457
WAIT #139712598962024: nam='db file sequential read' ela= 11 file#=1 block#=61384 blocks=1 obj#=35064 tim=1448809955702568
WAIT #139712598962024: nam='db file scattered read' ela= 1184 file#=1 block#=61393 blocks=7 obj#=35064 tim=1448809955703916
WAIT #139712598962024: nam='db file scattered read' ela= 5970 file#=1 block#=61408 blocks=8 obj#=35064 tim=1448809955710042
WAIT #139712598962024: nam='db file scattered read' ela= 263 file#=1 block#=61424 blocks=7 obj#=35064 tim=1448809955710475
WAIT #139712598962024: nam='db file scattered read' ela= 2461 file#=1 block#=82176 blocks=8 obj#=35064 tim=1448809955713060
WAIT #139712598962024: nam='db file scattered read' ela= 2585 file#=1 block#=82184 blocks=8 obj#=35064 tim=1448809955715814
WAIT #139712598962024: nam='db file scattered read' ela= 70 file#=1 block#=82192 blocks=8 obj#=35064 tim=1448809955716030
WAIT #139712598962024: nam='db file scattered read' ela= 687 file#=1 block#=82200 blocks=3 obj#=35064 tim=1448809955716832
WAIT #139712598962024: nam='db file scattered read' ela= 28205 file#=1 block#=82204 blocks=4 obj#=35064 tim=1448809955745140
WAIT #139712598962024: nam='db file scattered read' ela= 13 file#=1 block#=82208 blocks=8 obj#=35064 tim=1448809955745285
WAIT #139712598962024: nam='db file scattered read' ela= 1070 file#=1 block#=82216 blocks=8 obj#=35064 tim=1448809955746453
WAIT #139712598962024: nam='db file sequential read' ela= 5960 file#=1 block#=82304 blocks=1 obj#=35064 tim=1448809955752560
WAIT #139712598962024: nam='db file scattered read' ela= 15 file#=1 block#=82538 blocks=5 obj#=35064 tim=1448809955754269
WAIT #139712598962024: nam='db file scattered read' ela= 15 file#=1 block#=82560 blocks=8 obj#=35064 tim=1448809955754481
WAIT #139712598962024: nam='db file scattered read' ela= 22 file#=1 block#=82674 blocks=14 obj#=35064 tim=1448809955755267
WAIT #139712598962024: nam='db file scattered read' ela= 18 file#=1 block#=82688 blocks=12 obj#=35064 tim=1448809955755460
FETCH #139712598962024:c=9997,e=90650,p=140,cr=580,cu=0,mis=0,r=1,dep=0,og=1,plh=3180576180,tim=1448809955757097
STAT #139712598962024 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=580 pr=140 pw=0 time=90644 us)'
STAT #139712598962024 id=2 cnt=28117 pid=1 pos=1 obj=35064 op='TABLE ACCESS FULL ALL_OBJECTS_SHORT_LIST (cr=580 pr=140 pw=0 time=110678 us cost=158 size=0 card=30071)'


The Segment Header is in Block#61344 which has already been read into the Buffer Cache by the Dynamic Sampling query. So, we don't see a Wait Event for it in the count(*) query.  The Disk Reads start from Block#61345. From 61345 onwards, 5 blocks are read from disk (61345 to 61349). Block#61350 has already been read into the Buffer Cache by the Dynamic Sampling query.  Block#61351 is the last block of the Extents (8 blocks beginning at #61344) so it is a singe block read ("db file sequential read").  Even when Extents are contigous, Oracle will not make a read call that spans Extents.  The next Extent starts at Block#61352 and Oracle does a proper 8 block read.
At Block#61360, Oracle does a 2 block read because Block#61362 has already been read by the Dynamic Sampling query and is in the Buffer Cache.
All the Extents for this table are very small

Thus, you can see that multiblock reads using the "db file scattered read" wait event are reads that also rely on knowing which blocks are already present in the Buffer Cache.  Oracle does not do a "db file scattered read" read for a block that is already present in the Buffer Cache.

(What about Blocks 61363 to 61367 ?    Unfortunately, not every read call does get reported into the trace file, sometimes there may be "holes".  Not every Read Call is reported as being read to the last block in the Extent.
If you look at the Block# IDs being reported above and compare them with the Extents allocated, you will see that Oracle may not have reported every block as a Read Wait Event.
SQL> l
1 select extent_id, file_id, block_id, blocks
2 from dba_extents
3 where
4 owner = 'HEMANT'
5 and segment_name = 'ALL_OBJECTS_SHORT_LIST'
6* order by 1, 2, 3
SQL> /

EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 1 61344 8
1 1 61352 8
2 1 61360 8
3 1 61368 8
4 1 61376 8
5 1 61384 8
6 1 61392 8
7 1 61400 8
8 1 61408 8
9 1 61424 8
10 1 82176 8
11 1 82184 8
12 1 82192 8
13 1 82200 8
14 1 82208 8
15 1 82216 8
16 1 82304 128
17 1 82432 128
18 1 82560 128
19 1 82688 128

20 rows selected.

SQL>

Also, once again note that file#1 indicates that these are in the SYSTEM Tablespace. .
.
.

Categories: DBA Blogs