Moneycontrol Brokerage Recos

Sunday, September 16, 2018

How to check Oracle Exadata Model




Sometimes you would be curious to know while working on Exadata database that what Exadata model your database is using??

Here is simple thing you can check being logged into any of the compute node of Exadata cluster(of course you can login to compute node since you support the databases running on it or except DBaaS instances for Oracle private cloud, depends on the project level work and roles you are involved in and it's access level)

Login to the exadata compute node 1 and navigate to the following directory.

$cd /opt/oracle.SupportTools/onecommand

Then

$cat databasemachine.xml | grep MACHINE

and you will get the below output detailing about the exadata model you are just playing with.



/opt/oracle.SupportTools/onecommand
dbm01rac01[TESTDB] cat databasemachine.xml | grep MACHINE

<MACHINETYPE>36</MACHINETYPE>
<MACHINETYPE>X3-2 Half Rack HC 3TB</MACHINETYPE>
<MACHINEUSIZE>42</MACHINEUSIZE>

In above output you would see that our current Exadata Database Machine Model is X3-2 Half Rack High Capacity with 3TB size of each HDD.

Tuesday, September 11, 2018

Check if SQL Statement used Exadata Smart Scans



I was just reading out an amazing Oracle Exadata book to enhance my knowledge about Oracle Exadata, it is a must read book for every Oracle DBAs. I am posting here one little snip from this book, how authors have explained the Exadata concepts and practical way to find the Exadata Offload eligible bytes for a given SQL statement i.e. how to check if your SQL statement was offloaded to Exadata storage cell or Exadata Smart Scan was used.


There are lots of things that is explained wonderfully in this book. If you want to get a copy of this and bring your career to a new level - get a copy of this book Click Here



https://www.amazon.in/Expert-Oracle-Exadata-Martin-Bach/dp/1430262419/ref=sr_1_fkmr1_3?ie=UTF8&qid=1536641983&sr=8-3-fkmr1&keywords=expert+oracle+exadata+second+edition




Offload Eligible Bytes
There is another clue to whether a statement used a Smart Scan or not. As you saw in previous sections, the V$SQL family of views contain a column called IO_CELL_OFFLOAD_ELIGIBLE_BYTES, which shows the number of bytes that are eligible for offloading. This column can be used as an indicator of whether a statement used a Smart Scan. It appears that this column is set to a value greater than 0 only when a Smart Scan is used. You can make use of this observation to write a little script (fsx.sql) that returns a value of YES or NO, depending on whether that column in V$SQL has a value greater than 0. The output of the script is a little too wide to fit in a book format, which is why there are a couple of cut-down versions in the examples. And, of course, all of the versions will be available in the online code repository. You have already seen the script in action in several of the previous sections. The script is shown here for your convenience, along with an example of its use:
> !cat fsx.sql
----------------------------------------------------------------------------------------
--
-- File name:   fsx.sql
--
-- Purpose:     Find SQL and report whether it was Offloaded and % of I/O saved.
--
-- Usage:       This scripts prompts for two values.
--
--              sql_text: a piece of a SQL statement like %select col1, col2 from skew%
--
--              sql_id: the sql_id of the statement if you know it (leave blank to ignore)
--
-- Description:
--
--              This script can be used to locate statements in the shared pool and
--              determine whether they have been executed via Smart Scans.
--
--              It is based on the observation that the IO_CELL_OFFLOAD_ELIGIBLE_BYTES
--              column in V$SQL is only greater than 0 when a statement is executed
--              using a Smart Scan. The IO_SAVED_% column attempts to show the ratio of
--              of data received from the storage cells to the actual amount of data
--              that would have had to be retrieved on non-Exadata storage. Note that
--              as of 11.2.0.2, there are issues calculating this value with some queries.
--
--              Note that the AVG_ETIME will not be acurate for parallel queries. The
--              ELAPSED_TIME column contains the sum of all parallel slaves. So the
--              script divides the value by the number of PX slaves used which gives an
--              approximation.
--
--              Note also that if parallel slaves are spread across multiple nodes on
--              a RAC database the PX_SERVERS_EXECUTIONS column will not be set.
--
---------------------------------------------------------------------------------------
set pagesize 999
set lines 190
col sql_text format a70 trunc
col child format 99999
col execs format 9,999
col avg_etime format 99,999.99
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7

select sql_id, child_number child, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/
decode(nvl(executions,0),0,1,executions)) avg_etime,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,
100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES))
"IO_SAVED_%", sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
In the fsx script, you can see that the OFFLOAD column is just a DECODE that checks to see if the IO_CELL_OFFLOAD_ELIGIBLE_BYTES column is equal to 0 or not. The IO_SAVED_% column is calculated using the IO_INTERCONNECT_BYTES field, and it attempts to show how much data was returned to the database servers.
The script can be used for many useful purposes. The author primarily uses it to find the SQL_ID and child cursor number of SQL statements in the shared pool. In this example, it is used to determine if a statement has been offloaded or not:
SQL> select /*+ gather_plan_statistics fsx-example-002 */
  2  avg(id) from bigtab where id between 1000 and 50000;

   AVG(ID)
----------
     25500

Elapsed: 00:00:00.64
SQL> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-002 */
  2  avg(id) from bigtab where id between 1000 and 50000;

   AVG(ID)
----------
     25500

Elapsed: 00:00:53.88
SQL> @fsx4
Enter value for sql_text: %fsx-example-002%
Enter value for sql_id:

SQL_ID         CHILD OFFLOAD IO_SAVED_%  AVG_ETIME SQL_TEXT
------------- ------ ------- ---------- ---------- ----------------------------------------
cj0p52wha5wb8      0 Yes          99.97        .63 select /*+ gather_plan_statistics fsx-ex
cj0p52wha5wb8      1 No             .00      53.88 select /*+ gather_plan_statistics fsx-ex

2 rows selected.
The elapsed times are a bit of a giveaway as to whether the statement was offloaded or not, but if you are called in after the fact, the output of the fsx script clearly shows that the child_number 1 has not been offloaded. The fact that a new child cursor has been created is very important in this example. When setting CELL_OFFLOAD_PROCESSING to FALSE, the optimizer created a new child cursor due to a mismatch. Reasons why child cursors are created can be found in v$sql_shared_cursor. This view contains a long list of flags that allow you to identify differences between child cursors but is very hard to read in SQL*Plus. Oracle added a CLOB containing XML data in 11.2.0.2 that makes it easier to spot the difference. Using the SQL ID from the previous example, this is put to use. Note that I cast the CLOB to XML for better readability:
SQL> select xmltype(reason) from v$sql_shared_cursor
  2   where sql_id = 'cj0p52wha5wb8' and child_number = 0;

XMLTYPE(REASON)
---------------------------------------------------------------------------
<ChildNode>
  <ChildNumber>0</ChildNumber>
  <ID>3</ID>
  <reason>Optimizer mismatch(12)</reason>
  <size>2x356</size>
  <cell_offload_processing> true     false  </cell_offload_processing>
</ChildNode>
Translating the XML output into plain English, you can see that there was an optimizer mismatch: The parameter cell_offload_processing has changed from TRUE to FALSE.
It is not always the case for child cursors to be created after changing parameters. Certain underscore parameters such as _SERIAL_DIRECT_READ will not cause a new child cursor to be created. Some executions of the same cursor might be offloaded, others not. This can be quite confusing, although this should be a very rare occurrence! Here is an example to demonstrate the effect:
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
  2  from bigtab where id between 1000 and 50002;

   AVG(ID)
----------
     25501

Elapsed: 00:00:00.68
SQL> alter session set "_serial_direct_read" = never;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
  2  from bigtab where id between 1000 and 50002;

   AVG(ID)
----------
     25501

Elapsed: 00:04:50.32
SQL> SQL> alter session set "_serial_direct_read" = auto;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+ gather_plan_statistics fsx-example-004 */ avg(id)
  2  from bigtab where id between 1000 and 50002;

   AVG(ID)
----------
     25501

Elapsed: 00:00:00.63
SQL> @fsx4
Enter value for sql_text: %fsx-example-004%
Enter value for sql_id:

SQL_ID         CHILD OFFLOAD IO_SAVED_%  AVG_ETIME SQL_TEXT
------------- ------ ------- ---------- ---------- ----------------------------------------
6xh6qwv302p13      0 Yes          55.17      97.21 select /*+ gather_plan_statistics fsx-ex
As you can see, there are three executions using the same child cursor (no new child cursor has been created). The statistics about I/O saved and execution time now have little value: Two executions completed in less than a second, and one took almost five minutes. This is the well-known problem with averages: They obfuscate detail.

Sunday, September 9, 2018

Cancelling a SQL Statement in a Session


Cancelling a SQL Statement in a Session
---------------------------------------------

An interesting new feature introduced in Oracle Database 12c Release 2 - i.e.  you can cancel a SQL statement running in a session using the ALTER SYSTEM CANCEL SQL statement.



The following clauses are required in an ALTER SYSTEM CANCEL SQL statement:

• SID – Session ID
• SERIAL – Session serial number



The following clauses are optional in an ALTER SYSTEM CANCEL SQL statement:

• INST_ID – Instance ID
• SQL_ID – SQL ID of the SQL statement



The following is the syntax for cancelling a SQL statement:

 ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';  



The following example cancels a SQL statement having the session identifier of 20, session serial number of 51142, and SQL ID of 8vu7s907prbgr:


 ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';