Transcription

Monitoring Exadata with PreciseOracle Exadata comprises of Oracle database servers, Oracle Exadata Storage Servers, and an InfiniBandfabric for storage networking in a single Database Machine. Oracle markets Exadata for both OLTP andBI workloads. A logical representation is shown below.Having both workloads on the same Exadata can mean you get much better utilization of the totalcapacity of the Exadata since each workload type favours a different part of the infrastructure thatmakes up Exadata. The BI workload can be Offloaded and use the CPU and Memory on the Storage Cellsinstead of the DB Compute Nodes. The full scans will return directly to the PGA with Offloading and thismeans the Buffer Cache is not affected by the BI activity. This means that the DB Compute Nodes willhave much more capability to handle OLTP functionality more efficiently.ConceptsOracle uses a technique called “Offloading” or Smart Scan to push the processing down to the storagecells instead of being carried out on the database compute nodes. The basic concept of offloading is tomake the storage system smart enough to filter out as much data as possible before returning it to thedatabase. It reduces the amount of data returned, and returns that data over a much faster channel Infiniband. By doing this, Exadata drastically reduces the impact of physical I/O on the database. Thisremoves one of the major performance bottlenecks and speeds the system up significantly.Smart Scan or Offloading runs when the query really requires a full scan to be executed using direct pathreads. This means that it does not run for everything that the database executes. The optimizer mustchoose to do either a Full Table Scan or a Fast Full Index Scan in order for Smart Scan to be possible.That scan must be a direct path read which will allow parallel slaves to process the data on each storagecell. The database will check with ASM to see which storage cells contain that object and will fork offparallel requests to each storage cell asking for the range of blocks to be returned, and they will includethe key facts in the query. This allows each storage cell to process the data request in the most efficientway possible and return only the data necessary to satisfy the query. It can filter out those columns fromthe data it ships back which reduces the total amount of data returned to the database compute node,

thus speeding up the IO operation. It is also capable of processing simple filters based on comparisonoperators contained in the WHERE clause and also many built-in single-row SQL Functions.Exadata Smart also uses the Flash Cache feature of the Exadata Storage Server Software intelligentlycaches database objects in flash memory, replacing slow, mechanical I/O operations to disk with veryrapid flash memory operations. The Exadata Smart Flash Cache holds frequently accessed data in veryfast flash storage.The Oracle optimiser has a new set of access paths that reflect the access to the storage cells. Theseaccess paths include some of the following: Table Access ( Storage Full)Index (Storage Fast Full Scan)Table Access (Storage Full First Rows)Index (Storage Fast Full Scan First Rows)All of these access paths use direct scans at the storage cell layer and uses parallel slaves to executethese scans.In addition to this, Oracle has also introduced a number of new Oracle wait events such as Cell Smart Table Scan – Table Scan using smart scan on the storage cellCell Smart Index Scan – Index Scan using smart scan on the storage cellCell Multi Block Physical Read – Full Table Scan or Index Fast Full Scan not offloadedCell Single Block Physical Read – Conventional Index AccessUsing PrecisePrecise provides the ability to see these Exadata access paths and wait events and identify what isconsuming the most time.Precise is a leading Application Performance Monitoring tool that provides a clear accurate breakdownof where is time spent in transactions. It identifies which tier is consuming the most time and allowsinvestigation into that tier.A key feature of Precise is its ability to identify where all the time is spent within the Oracle databaseand identify those SQL statements; database objects and Oracle wait events that are consuming largeamounts of time.This allows you to quickly identify where that time is spent and to focus on those SQL statements thatare experiencing the worst performance.Precise is able to show those key Exadata events that are consuming the most time and allows you tosee how your database is performing on the Exadata machine.

Precise identifies these top events that are consuming the time as “Findings”. It tells you how much ofthe time is spent in this finding.You can then investigate the finding further and identify the cause of the performance issue.Case StudyIn the following case study you can see that Precise has identified a number of findings. The finding ofinterest to us shows that 7% of the total amount of time is spent in RAC/OPS wait. This is indicating thatwe may have issues with the Exadata database.By selecting this finding you can see that the time is spent in the wait event “cell single block physicalread”. This is a new wait event specific to Exadata. You can see from the screenshot that this is thebusiest wait event for this RAC instance and database.

The cell single block physical read wait event is a single block I/O.We can also see which database objects are experiencing the high waits. In the example we can see thatthe I OBJ2 index is experiencing the high waits.Precise allows you to see which SQL statements are experiencing the high wait events as shown in thescreenshot below. From this we can see that the statement 41340 is experiencing the high waits. Wecan also see that the number of executions is 1065 during this time frame and that the average responsetime is 1 minute and 8.5 seconds.You can see the details for this SQL statement by selecting the TUNE icon. Precise then shows you theexecution plan for the SQL statement. It shows very clearly the access path consuming the most time isspent. We can clearly see that the “Index (Storage Fast Full Scan)” on I OBJ2 is contributing to 97% ofthe workload in the execution plan.

We can also see that there are “Findings” identified for this SQL statement. The Findings show the heavytime spent in the “Index (Storage Fast Full Scan)” as well as RAC wait and also “No Parallel ProcessesAvailable”.Even though the execution plan is intending to use the Smart Scan access path, it appears that there areno parallel slaves available to carry out this scan of the storage cells. This could mean that the optimiserhas to change the access path in order to read the blocks more efficiently.This could therefore be the cause for high “Cell Single Block Physical Read”. This allows you toinvestigate the statement further and also check the parallel processes available.You can also confirm whether the number of executions for this access path is too many and that theoptimiser is being aggressive in choosing this access path.Precise can also provide recommendations too for improving SQL queries as you can see in thescreenshot below.You can see there are a number of Exadata access paths in this execution plan with “Table Access(Storage Full)” and “Index (Storage Fast Full Scan)” being chosen by the optimiser.You can see from the execution plan that the “Index (Range Scan)” on PS PROJ RESOURCE iscontributing 96% of the time to it. This is the biggest contributor.Precise is able to identify recommendations to improve this SQL statement as shown in the screenshotbelow. Precise has identified an index to improve this statement.

SummaryExadata provides significant improvements in accessing the data in tables quickly for certain types ofworkload and is beneficial in many BI applications.Precise helps you to identify those Exadata access paths and wait events. It helps you to decide whetherthese access paths improve the performance of the statements.Precise also helps you to identify recommendations for improving these statements and provides youwith clear information as to where that time is spent.

caches database objects in flash memory, replacing slow, mechanical I/O operations to disk with very rapid flash memory operations. The Exadata Smart Flash Cache holds frequently accessed data in very fast flash storage. The Oracle optimiser has a new set of access paths that reflec