Просторы интернета
Since Christmas I have been asked to investigate two different “failures to use Smart Scan”. It turns out they both fell into the same little known restriction on the use of Direct Read. Smart Scan critically depends on Direct Read in order to read the synthetic output blocks into private [...]
If you turn on NSMTIO tracing you will see references to VLOT:
qertbFetch:[MTT < OBJECT_SIZE < VLOT]: Checking cost to read from caches (local/remote) and checking storage reduction factors (OLTP/EHCC Comp)I had said you could ignore VLOT and Frits Hoogland pointed out that tracing showed it had some impact, so let [...]
I heard someone assert that one reason you should only use 8k block sizes is that, and I quote, “Oracle only tests on 8k and doesn't really test 16k”. I tried googling that rumour and tracked it back to [...]
The question came up this week about whether the predicates for row level security are offloaded to Smart Scan. The simple answer is yes as long as the policy_function contains off-loadable predicates.
Let's see this in action. Using the public TPC-H schema we can set up the customer table so that [...]
Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):
SQL> select /*+ OPT_PARAM('cell_offload_plan_display' 'always') */ col1 From table1;and underscore parameters:
SQL> select [...]Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different [...]
When a background activity is happening on the cell you typically can't use RDBMS v$ views to monitor it in the same way. One such question is how to tell if a segment is fully loaded in the Exadata column cache since this does not appear in the equivalent In-Memory [...]
I normally blog about table scans on Oracle native data but Oracle also supports a wide variety of features for scanning external tables and I need to cover these too. One of the ways I learn new things is being sent a bug report and saying to myself “Oh! I [...]
12.1.0.2 introduced the new Columnar Flash Cache where 1MB of blocks that are all in HCC format are rewritten so as to make each column from each CU contiguous. This works by first writing all the block headers to an array, then writing all the CU headers to an [...]
I was looking through a test script and saw something I didn't know you could do in Oracle. I mentioned it to an Oracle ACE and he didn't know it either. I then said to the External Table engineers “Oh I see you've added this cool new feature” and he [...]
A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not [...]
One of the joys of regexp is that you can write a pattern that is painfully expensive to match and offloading these to the cell can cause significant impact on other users and overall throughput (including heartbeat issues). If you have a user who is prone to writing bad regexp [...]
There are three main tools available for cleaning up a segment (Alter Table Shrink, Alter Table Move, and export/import), but one of them isn't as helpful as you might have thought.
Consider the following sequence of events where we update the 256th column to cause widespread fragmentation:
SQL> update t [...]One of the easiest ways is to use diagnostic events:
alter session set events 'sql_trace {callstack: fname xctend} errorstack(1)'; [...]One of the easiest ways is to use diagnostic events:
alter session set events 'sql_trace {callstack: fname xctend} errorstack(1)'; [...]You may know that some applications generate queries with bind variables' names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:
SQLPlus:
SQL> var 1 number; SP2-0553: Illegal variable name "1".SQLCL:
SQL> var 1 number; ILLEGAL Variable Name "1"So we can't run such queries as-is, but, obviously, we can wrap them [...]
You may know that some applications generate queries with bind variables' names like :1 or :”1″, and neither SQL*Plus nor SQLCl support such variables:
SQLPlus:
SQL> var 1 number; SP2-0553: Illegal variable name "1".SQLCL:
SQL> var 1 number; ILLEGAL Variable Name "1"So we can't run such queries as-is, but, obviously, we can wrap them [...]
I know 2 “special” exceptions that can't be processed in exception handler:
“ORA-01013: user requested cancel of current operation” “ORA-03113: end-of-file on communication channel”Tanel Poder described the first one (ORA-01013) in details here: https://tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/ where Tanel shows that this error is based on SIGURG signal (kill -URG):
-- [...]I know 2 “special” exceptions that can't be processed in exception handler:
“ORA-01013: user requested cancel of current operation” “ORA-03113: end-of-file on communication channel” and + “ORA-00028: your session has been killed” from Matthias RogelTanel Poder described the first one (ORA-01013) in details here: [...]
Got an interesting question today in RuOUG:
Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:
create or replace PROCEDURE XXXX ( P_ORG_NUM IN number, p_result OUT varchar2, p_seq OUT number ) [...]Got an interesting question today in RuOUG:
Some very simple PL/SQL procedures usually are completed within ~50ms, but sometimes sporadically longer than a second. For example, the easiest one from these procedures:
create or replace PROCEDURE XXXX ( P_ORG_NUM IN number, p_result OUT varchar2, p_seq OUT number ) [...]@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:
@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask
@tracefile_read_last_by_mask filemask [regexp] [ignore_regexp]
– finds last trace by filemask and filters rows by regexp and filters out rows by ignore_regexp:
@ tracefile_by_mask.sql [mask] – finds and shows last 10 trace files by mask
Oracle diagnostic events is a great feature, but unfortunately poorly documented and nonintuitive, so it's difficult to remember all events/actions/parameters and even read its internal documentation using oradebug. So I decided to compile its internal doc as a more convenient html-version (http://orasql.org/files/events/) and make a cheat [...]
Oracle diagnostic events is a great feature, but unfortunately poorly documented and nonintuitive, so it's difficult to remember all events/actions/parameters and even read its internal documentation using oradebug. So I decided to compile its internal doc as a more convenient html-version (https://orasql.org/files/events/) and make a cheat [...]