DBA Blogs

RAC cluster_interconnect config

Tom Kyte - 16 hours 7 min ago
Hi Tom, Is it ok to use Fiber and Ethernet for cluster_interconnect type mixed? Ex. ib0 192.168.3.0 global cluster_interconnect,asm ==> InfiniBand Fiber Interface eno4 192.168.5.0 global cluster_interconnect,asm ==> Fast Ethernet Interface Any potential risk on that kind of configuration. Thank you. Davis
Categories: DBA Blogs

CLOB

Tom Kyte - 16 hours 7 min ago
Hi Tom, I have a table in which one column is CLOB datatype. Data in this column are 100 lines. ( single record has 100 lines) Each line has been separated by chr(13) ( enter key's ascii value). My requirement is If i search for a specific word, this word matches with First record's - 3rd line fifth record's - 50th line 100 th record's - 1 st line . Now, i need to show only the lines. not the entire record. means that my output will be 3 rows.But I should not show all three rows. instead 3 rows with the 3 lines only. That is lines which have the keyword. How to achive this? Thank you
Categories: DBA Blogs

A Function Based Index : 2

Hemant K Chitale - Sun, 2020-07-12 10:16
Here's another Function Based Index example.   This is in 19.3  (not to demonstrate any difference between  12.2 and 19.3 here)

SQL> create table employees
2 (employee_id number primary key,
3 first_name varchar2(30),
4 last_name varchar2(30))
5 pctfree 0
6 /

Table created.

SQL>SQL> insert into employees
...
...

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL>
SQL> select sample_size, blocks, empty_blocks, avg_row_len
2 from user_tab_statistics
3 where table_name = 'EMPLOYEES'
4 /

SAMPLE_SIZE BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
----------- ---------- ------------ -----------
10000 65 0 45

SQL>
SQL> select 45*10000/8192 from dual;

45*10000/8192
-------------
54.9316406

SQL>
SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22

SQL>
SQL> create index employees_full_name_ndx
2 on employees(first_name||last_name)
3 /

Index created.

SQL>
SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22
SYS_NC00004$

SQL>
SQL> -- no statistics on the new virtual column yet
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22
SYS_NC00004$ 40

SQL>
SQL> select num_rows, sample_size, blocks, empty_blocks, avg_row_len
2 from user_tab_statistics
3 where table_name = 'EMPLOYEES'
4 /

NUM_ROWS SAMPLE_SIZE BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ----------- ---------- ------------ -----------
10000 10000 65 0 85

SQL>
SQL> select 85*10000/8192 from dual;

85*10000/8192
-------------
103.759766

SQL>
SQL> select leaf_blocks
2 from user_ind_statistics
3 where index_name = 'EMPLOYEES_FULL_NAME_NDX'
4 /

LEAF_BLOCKS
-----------
72

SQL>



After creation of the Function Based Index, the new virtual column in the table has an average length of 40 bytes.  Surely, the table should now have taken up more blocks with the average row length increasing from 45 to 85 ?  The number of table blocks should have increased by about 50 or more blocks ? And, yet, the table still consumes only 65 blocks.  It is the Index that reports 72 leaf blocks.

So, adding a Function Based Index increases the reported "AVG_ROW_LEN" for the Table without actually increasing the space consumed by the table.  The computed values are not stored in the Table blocks but are actually only in the Index leaf blocks.

Conversely, this can also mean that when you come across a table with a low BLOCKS versus AVG_ROW_LEN*NUM_ROWS, you might want to look for a Function Based Index :


SQL> select index_name, index_type, uniqueness
2 from user_indexes
3 where table_name = 'EMPLOYEES'
4 /

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
SYS_C007674 NORMAL UNIQUE
EMPLOYEES_FULL_NAME_NDX FUNCTION-BASED NORMAL NONUNIQUE

SQL> select column_position, column_expression
2 from user_ind_expressions
3 where table_name = 'EMPLOYEES'
4 and index_name = 'EMPLOYEES_FULL_NAME_NDX'
5 /

COLUMN_POSITION COLUMN_EXPRESSION
--------------- ------------------------------
1 "FIRST_NAME"||"LAST_NAME"

SQL>


(SYS_C007674 is the Primary Key Index on EMPLOYEE_ID)


Categories: DBA Blogs

sql trace and tkprof

Tom Kyte - Sat, 2020-07-11 11:46
hi tom, i have seen lot of answers from you regarding sql trace and tkprof for sql performance tuning.but i am still not clear with few things. i hope you will explain me clearly those things 1.how does this sql trace and tkprof differ from set autotrace on. 2.if we use set autotrace on and explain plan, how do we analyze a particular query and tell whether the query is tuned or not 3.for sql trace and tkprof i want to know if if i do 'ALTER SESSION SET SQL_TRACE = TRUE' where does the trace file get created(my oracle server is running on unix). by formatting the trace file using tkprof how do i analyze it to tell the query is tuned or not? 4.is the order of tables in a query matters in performance? for this question i got an answer that it matters for RBO but not for CBO.but who decides to go for RBO,CBO.can we tell oracle to go for CBO OR RBO.If we can decide, when to go for RBO,when to go for CBO. sorry for the flow of questions, but i am confused on the above issues. please answer the above. thanks a lot. nk
Categories: DBA Blogs

Oracle APEX 20.2 IE 11 Depreciated

Tom Kyte - Fri, 2020-07-10 17:26
Hi, I saw the following depreciated features in Oracle APEX 20.1 release notes: <b>6.1.4 Support for Internet Explorer 11 Deprecated Support for Internet Explorer (IE) 11 is deprecated. Starting with release 20.2, only the current and prior major release of Microsoft Edge along with Google Chrome, Mozilla Firefox, Apple Safari will be supported.</b> Does it mean that Oracle APEX 20.2 will not work correctly in Microsoft IE 11? Does this statement apply only to the APEX builder?...or does it also apply to all applications in workspace? We have a lot of PC's that are still running IE 11. Thanks!
Categories: DBA Blogs

Oracle JET Paging Control in Oracle APEX

Tom Kyte - Fri, 2020-07-10 17:26
Hi, In APEX SOD, it says there will be additional Oracle JET-based components. May I know if there will include the following paging control in Oracle JET? https://www.oracle.com/webfolder/technetwork/jet/jetCookbook.html?component=pagingControl&demo=basicPagingTable I am particularly interested in replacing the pagination in classic reports by Oracle JET paging control. I haven't seen any information about how to implement these Oracle JET components into Oracle APEX. Thank you!
Categories: DBA Blogs

How can I audit the individual functions/procedures inside package?

Tom Kyte - Fri, 2020-07-10 17:26
Oracle Audit sentence: audit execute on [package] by access; How Can I Do something like this? audit execute on [package.function] by access; I need to audit execution of functions inside packages with Oracle Audit options. Is it possible to audit arguments sent to this functions too? And Is it possible to Select this info of SYS.AUD$ or other Views Best regards, Jery
Categories: DBA Blogs

ORA-06531: Reference to uninitialized collection

Tom Kyte - Fri, 2020-07-10 17:26
Hi Tom, I am getting "ORA-06531: Reference to uninitialized collection" even after initializing collection to assign a value. Could you please help. Please find the code below. create or replace TYPE TYP_GRP_ID is object (grp_id number); create or replace TYPE TAB_GROUP is table of TYP_GRP_ID; create or replace PACKAGE TEST AS PROCEDURE TEST; END; create or replace PACKAGE BODY TEST AS PROCEDURE TEST IS acc_arr TAB_GROUP; BEGIN acc_arr.EXTEND; acc_arr := new TAB_GROUP(null); acc_arr(acc_arr.LAST).grp_id := 1000; dbms_output.put_line(acc_arr(acc_arr.LAST).grp_id); END; END; Thanks in advance Mathew
Categories: DBA Blogs

An instance named dvil?

Tom Kyte - Fri, 2020-07-10 17:26
<u></u> Hello Masters, I hope you are OK, it was impossible to ask TOM during many days... Two weeks ago I was testing, with RMAN, the "RECOVER TABLE" command. It helped me to restore a dropped table, with RMAN backups : a very powerful and useful feature, thanks to Oracle for that tool. I droped the table HR.ZZRMAN01 of the PDB ORCL and, to restore it, my command was : <code>RMAN> RECOVER TABLE HR.ZZRMAN01 OF PLUGGABLE DATABASE ORCL UNTIL SCN 2331673 AUXILIARY DESTINATION '/u01/app/oracle/RMAN_TEMP' REMAP TABLE 'HR'.'ZZRMAN01':'ZZRMAN01_PREV';</code> And, surprise, on the screen I saw that Oracle creates an instance named dvil... <code> Starting recover at 20-JUN-20 using channel ORA_DISK_1 RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time List of tablespaces expected to have UNDO segments Tablespace SYSTEM Tablespace ORCL:SYSTEM Tablespace ORCL:UNDOTBS1 Tablespace UNDOTBS2</code> Here! <code> <u><b>Creating automatic instance, with SID='dvil'</b></u> initialization parameters used for automatic instance: db_name=ORCL12C db_unique_name=dvil_pitr_ORCL_ORCL12C compatible=12.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE sga_target=800M processes=200 db_create_file_dest=/u01/app/oracle/RMAN_TEMP log_archive_dest_1='location=/u01/app/oracle/RMAN_TEMP' enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used starting up automatic instance ORCL12C Oracle instance started Total System Global Area 838860800 bytes Fixed Size 8798312 bytes Variable Size 239079320 bytes Database Buffers 583008256 bytes Redo Buffers 7974912 bytes Automatic instance created ...</code> Well, my little question is just : does Dvil mean devil? You agree with me or I am wrong? I was so amused that I wanted to talk about it with Oracle experts :-) Have a nice day, David D.
Categories: DBA Blogs

Session wise rank ,change in value should lead to new rank

Tom Kyte - Fri, 2020-07-10 17:26
Hi Tom, I need help to get sequence no or rank whenever there is change in continuous sequence below is the example- Input seq,id,value 1,1,200 2,1,200 3,1,300 4,1,200 5,1,200 6,1,500 7,1,500 8,1,700 expected out put same combination of id,value as per sequence order should be assigned one value. for id 1 and values 200 --rnk should be 1 then there is a change in 3rd row --then rnk=2 then 4th row should be assigned with new rnk 3 not 1 ans so on. seq,id,value, rnk 1,1,200,1 2,1,200,1 3,1,300,2 4,1,200,3, 5,1,200,3, 6,1,500,4 7,1,500,4 8,1,700,5 9,1,800,6 I have tried lead, lag, first_value,last_value ,rank,dense_rank and row_number analytical function ,but i could not achieve the requirement.
Categories: DBA Blogs

View raise ORA-28094 based on "dbms_redact" base-table

Tom Kyte - Fri, 2020-07-10 17:26
Dear Ask-Tom-Team, I've to apologize up front because I think I've something missed in the Documentation regarding dbms_redact setup, or restrictions therewith. If so I'm sorry for wasting your time just to point me in right direction. I've a simple test-case on livesql (but you cannot run it there, because you have no execute-right on dbms_redact), please have a look at: https://livesql.oracle.com/apex/livesql/s/kcq634fgexodc6m6a8n4esb0l My questions is regarding the ORA-28094 raised by the "tom_redact_view_28094"-View (at statement 13). The restriction I couldn't find, and to be honest don't understand, is: The above mentioned view just selects all columns from the base-table (which has one redacted column - ssn), and just add two columns together (nr1 + nr2). And this addition seems to be enough to raise the ORA-28094. If you do not select the "sum" column from this view, it works just fine. I've tested this on AIX and Oracle 19.5, and on Oracle 12.2 and 18.3 on Oracle-Linux. All of the above shows the same behaviour, which is another reason why I think I've just missed a restriction in the documentation. I know I could redact the column in the view, but I think this would defeat the purpose. Because I would like to redact the 'base-table' and rather not 10 views based on this table. In the end my tests would go a step further, because my initial setup started with dbms_tsdp_* using dbms_redact. But I think that dbms_redact is the restricting part, so I've simplified this test-case. I hope the test-case and my explanation is sufficient for you to reproduce the behaviour. And as stated above if I've missed something in the documentation, and you can point me in the right direction, I'm grateful and sorry! Thank you in advance for your time and help! best regards, Tom
Categories: DBA Blogs

Inserting data into destination table from source if data is not present in destination table, taking more time due to large volume of data

Tom Kyte - Fri, 2020-07-10 17:26
sample example: /* This procedure is taking approx 20-25 mins to complete because of selecting large volume of data, Is there any way to reduce execution time ? */ <code> procedure sample ( a_in IN varchar2) IS v_row number; v1_row number; v2_row number; cursor c1 IS select a_value, b_value.., from source_table<where condition>; /* cursor c1 selecting 46 millions record, but inserted few records to the below two destinations tables based on conditions, source_table is a force view*/ Begin for i in c1 loop v_row := 0; select count(1) into v_row from table_item where item = i.a_value||'_'||a_in; if v_row > 0 then select count(1) into v1_row from destination_table1 where item1 = (i.b_value||'_'||a_in); if v1_row = 0 then insert into destination_table1 (a_value, b_value) values(i.a_value, i.b_value); commit; end if; if i.b_value is not null then v2_row := 0; select count(1) into v2_row from destination_table2 where item2 = (i.a_value ||'_'||a_in) and item3 = (i.b_value||'_'||a_in); if v2_row = 0 then insert into destination_table2 (item2, item3) values (i.a_value ||'_'||a_in, i.b_value||'_'||a_in); commit; end if; end if; end if; end loop; End sample; /* this procedure is taking approx. 20 mins to complete */ </code>
Categories: DBA Blogs

Adding a Function-Based Index

Hemant K Chitale - Thu, 2020-07-09 10:51
What happens when you create a Function Based Index ?

{This demo is in 12.2}

I start with my "large" table.

SQL> create table my_data_table 
2 as select * from dba_objects
3 union all select * from dba_objects;

Table created.

SQL> select count(*) from my_data_table;

COUNT(*)
----------
157408

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> select num_rows, avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

NUM_ROWS AVG_ROW_LEN
---------- -----------
157408 132

SQL>


I then add a Function Based Index on it.

SQL> create index my_data_fbi   
2 on my_data_table(lower(OBJECT_NAME))
3 /

Index created.

SQL>


I now look at Table and Column statistics

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> desc my_data_table
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER

SQL>
SQL> l
1 select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
2 from user_tab_columns
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME NUM_NULLS TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER 0 09-JUL 22:43
OBJECT_NAME 0 09-JUL 22:43
SUBOBJECT_NAME 155954 09-JUL 22:43
OBJECT_ID 2 09-JUL 22:43
DATA_OBJECT_ID 139120 09-JUL 22:43
OBJECT_TYPE 0 09-JUL 22:43
CREATED 0 09-JUL 22:43
LAST_DDL_TIME 2 09-JUL 22:43
TIMESTAMP 2 09-JUL 22:43
STATUS 0 09-JUL 22:43
TEMPORARY 0 09-JUL 22:43
GENERATED 0 09-JUL 22:43
SECONDARY 0 09-JUL 22:43
NAMESPACE 2 09-JUL 22:43
EDITION_NAME 157408 09-JUL 22:43
SHARING 0 09-JUL 22:43
EDITIONABLE 101970 09-JUL 22:43
ORACLE_MAINTAINED 0 09-JUL 22:43
APPLICATION 0 09-JUL 22:43
DEFAULT_COLLATION 125494 09-JUL 22:43
DUPLICATED 0 09-JUL 22:43
SHARDED 0 09-JUL 22:43
CREATED_APPID 157408 09-JUL 22:43
CREATED_VSNID 157408 09-JUL 22:43
MODIFIED_APPID 157408 09-JUL 22:43
MODIFIED_VSNID 157408 09-JUL 22:43

26 rows selected.

SQL>
SQL> l
1 select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME NUM_NULLS TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER 0 09-JUL 22:43
OBJECT_NAME 0 09-JUL 22:43
SUBOBJECT_NAME 155954 09-JUL 22:43
OBJECT_ID 2 09-JUL 22:43
DATA_OBJECT_ID 139120 09-JUL 22:43
OBJECT_TYPE 0 09-JUL 22:43
CREATED 0 09-JUL 22:43
LAST_DDL_TIME 2 09-JUL 22:43
TIMESTAMP 2 09-JUL 22:43
STATUS 0 09-JUL 22:43
TEMPORARY 0 09-JUL 22:43
GENERATED 0 09-JUL 22:43
SECONDARY 0 09-JUL 22:43
NAMESPACE 2 09-JUL 22:43
EDITION_NAME 157408 09-JUL 22:43
SHARING 0 09-JUL 22:43
EDITIONABLE 101970 09-JUL 22:43
ORACLE_MAINTAINED 0 09-JUL 22:43
APPLICATION 0 09-JUL 22:43
DEFAULT_COLLATION 125494 09-JUL 22:43
DUPLICATED 0 09-JUL 22:43
SHARDED 0 09-JUL 22:43
CREATED_APPID 157408 09-JUL 22:43
CREATED_VSNID 157408 09-JUL 22:43
MODIFIED_APPID 157408 09-JUL 22:43
MODIFIED_VSNID 157408 09-JUL 22:43
SYS_NC00027$ 0 09-JUL 22:43

27 rows selected.

SQL>

SQL> l
1 select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
OWNER 6
OBJECT_NAME 34
SUBOBJECT_NAME 2
OBJECT_ID 5
DATA_OBJECT_ID 2
OBJECT_TYPE 10
CREATED 8
LAST_DDL_TIME 8
TIMESTAMP 20
STATUS 7
TEMPORARY 2
GENERATED 2
SECONDARY 2
NAMESPACE 3
EDITION_NAME 0
SHARING 13
EDITIONABLE 2
ORACLE_MAINTAINED 2
APPLICATION 2
DEFAULT_COLLATION 4
DUPLICATED 2
SHARDED 2
CREATED_APPID 0
CREATED_VSNID 0
MODIFIED_APPID 0
MODIFIED_VSNID 0
SYS_NC00027$ 34

27 rows selected.

SQL>
SQL> select num_rows, avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

NUM_ROWS AVG_ROW_LEN
---------- -----------
157408 166

SQL>


A new column "SYS_NC00027$" appears in USER_TAB_COLS but not in USER_TAB_COLUMNS.  The new column is not also not visible when I run a "DESCRIBE" command.

Also, the AVG_ROW_LEN has increased by 34  (seeing as I do not have any objects with long names) to reflect the addition of the new virtual column.
But did all the blocks actually get re-written ?  Are the "lower(OBJECT_NAME)" values written into each table block as well ?

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select obj#, savtime, rowcnt, blkcnt, avgrln,samplesize,to_char(analyzetime,'DD-MON-RR HH24:MI:SS')
2 from sys.WRI$_OPTSTAT_TAB_HISTORY
3 where obj# =
4 (select object_id
5 from dba_objects
6 where owner = 'HEMANT'
7 and object_type = 'TABLE'
8 and object_name = 'MY_DATA_TABLE')
9* order by 1,2
SQL> /

OBJ# SAVTIME ROWCNT BLKCNT AVGRLN SAMPLESIZE TO_CHAR(ANALYZETIME,'DD-MON
---------- ---------------------------- ---------- ---------- ---------- ---------- ---------------------------
79843 09-JUL-20 10.39.03.789392 PM 157408 3106 132 157408 09-JUL-20 22:37:53
79843 09-JUL-20 10.43.59.424420 PM 157408 3106 132 157408 09-JUL-20 22:39:03
79843 09-JUL-20 11.02.35.088733 PM 157408 3106 166 157408 09-JUL-20 22:43:59

SQL>


Apparently, the actual number of blocks did not increase.

SQL> l
1 select segment_name, segment_type, bytes/1024
2 from user_segments
3 where segment_name like 'MY_DATA%'
4* order by 1,2
SQL> /

SEGMENT_NAME SEGMENT_TYPE BYTES/1024
------------------------------ ------------------ ----------
MY_DATA_FBI INDEX 8192
MY_DATA_TABLE TABLE 25600

SQL>
SQL>
SQL> l
1 select index_name, column_name
2 from user_ind_columns
3* where index_name = 'MY_DATA_FBI'
SQL> /

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
MY_DATA_FBI SYS_NC00027$

SQL>


So, the Index does take up space as an Index segment but the Virtual Column ("lower(OBJECT_NAME)")  created on the Table does not grow the table.  What seems to happen is that the "computed" value ("lower(OBJECT_NAME)") is stored in the Index segment (leaf blocks) but not in the Table segment (blocks)


What happens if I drop the Function Based Index ?

SQL> drop index my_data_fbi;

Index dropped.

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*)
2 from user_tab_columns
3 where table_name = 'MY_DATA_TABLE'
4 /

COUNT(*)
----------
26

SQL> select count(*)
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4 /

COUNT(*)
----------
26

SQL> select avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

AVG_ROW_LEN
-----------
132

SQL>


The additional column and the 34 bytes it represents has disappeared from the Table and Column statistics.



Categories: DBA Blogs

PL\SQL to GetDDL for all user account except system account using cursor and loop

Tom Kyte - Tue, 2020-07-07 16:06
I am not sure what I am making wrong with this statement, <code>Declare cursor cur_dba_users IS select username from dba_users where username not in ('ANONYMOUS','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','BI','CTXSYS','DBSNMP','DIP','DVF','DVSYS','EXFSYS','FLOWS_FILES','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','HR','IX','LBACSYS','MDDATA','MDSYS','OE','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSBACKUP','SYSDG','SYSKM','SYSTEM','WMSYS','XDB','SYSMAN','RMAN','RMAN_BACKUP','OWBSYS','OWBSYS_AUDIT','APEX_030200','MGMT_VIEW','OJVMSYS'); Begin For cur_username in cur_dba_users Loop SELECT dbms_metadata.get_ddl('USER',cur_username) FROM dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',cur_username) from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',cur_username) from dual; SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',cur_username) from dual; END LOOP; CLOSE cur_dba_users; End; Error report - ORA-06550: line 6, column 39: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 6, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 7, column 55: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 8, column 53: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 8, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 9, column 55: PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:</code>
Categories: DBA Blogs

Redo Log query

Tom Kyte - Tue, 2020-07-07 16:06
I understand instead of log generation rate , we should consider amount of redo generated. My question is - How can we identify any database bottleneck using amount of redo? Is there any way to compare the amount of redo generation between two days or months probably and see if there is need of tuning or re-sizing?
Categories: DBA Blogs

Need information on tuning a join

Tom Kyte - Tue, 2020-07-07 16:06
Hi Tom, I need to know how the where clause is effecting my query. I have a table ABC where it has batch_id. It has 1L rows. Its daily truncated table. so it will have only one batch_id. table1 = ABC table2 = XYZ select * from ABC t1, XYZ t2 where batch_id = 111 and t1.col1 = t2.col2 the above query is taking 20+ minutes to retrieve data. the below query is taking hardly 6s. select * from ABC t1, XYZ t2 where t1.col1 = t2.col2; Just want it understand why its taking time with just batch_id in where clause.
Categories: DBA Blogs

COLLECTION ITERATOR PICKLER FETCH

Tom Kyte - Tue, 2020-07-07 16:06
Hi Tom, I am using DBMS_XPLAN.DISPLAY to display the execution plan of a query. After using EXPLAIN PLAN SET STATEMENT ID .... I am giving the following query: <code>SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','SAM','ALL')); Execution Plan ---------------------------------------------------------- Plan hash value: 3013799171 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 32744 | 65488 | 49 (3)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | | | ---------------------------------------------------------------------------------------------</code> can you please help me to know how to avoid this "COLLECTION ITERATOR PICKLER FETCH" and get the proper explain plan? Thank you very much Sarayu K.S.
Categories: DBA Blogs

SQL TO_CHAR returns differently between 2 Servers

Tom Kyte - Tue, 2020-07-07 16:06
Hello, I have two server with version 12.2.0.1.0. When I run the same SQL on each I don't have the same result on dates when I used then with TO_CHAR... SQLPLUS: <code>SELECT CONTRACTDATE, TO_CHAR(CONTRACTDATE,'YYYY/MM/DD') FROM MYTABLES WHERE CONTRACTNO=101010;</code> COLUMN CONTRACTDATE is a DATE. SERVER1 CONTRACTD TO_CHAR(CO --------- ---------- 05-DEC-19 2019/12/05 SERVER2 CONTRACTD TO_CHAR(CO --------- ---------- 05-DEC-19 <b>00</b>19/12/05 As you can see on the second server it use <b>0019</b> for the year instead of the expected <b>2019</b> Any idea why I have such behaviour? Thank you PS: I have checked the NLS and they are identical. <code>SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION" FROM NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S WHERE DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+) ORDER BY 1;</code>
Categories: DBA Blogs

Hidden paramater: No_Invalidate

Tom Kyte - Tue, 2020-07-07 16:06
Hi Tom, from couple of months some queries in production environment are intermittently producing 2 hash plan values. Obviously one is better and other is bad. We run stats gather Job daily in the morning. Have check many other platforms and understood that there are many reasons for this behaviour. Some of them reasons for above behaviour, that I have check are below 1. Parsing a SQL statement with bind variable defined as wrong type. 2. Not running stats gather Job daily. above two reasons are not valid for my case. Other than this there are some more reasons that I wanted to ask you. a. Invalidation of cursors. I understand that, this is handled by parameter 'NO_INVALIDATE'. This is set to AUTO_INVALIDATE for me. So even though my stats gather Job has produced a bad hash plan value, this will not be effective immediately. b. Adaptive cursor sharing. How to make sure that because of this feature my SQL is producing multiple Hash plan value? Could you please help me with above understanding and suggest me for point a and b. Thanks a lot.
Categories: DBA Blogs

Special character constant package after deployment

Tom Kyte - Tue, 2020-07-07 16:06
Hi TOM i have a constant package where i define a variable constant whose value is '<b>#</b>' (pound), Now, running the package script in dev environment, package created with no errors and did not show any special character in constant variable, but running same package script in UT environment package created with no errors but constant value has '<b>A#</b>'. how can i resolve this issues ? came to know its different character set issue thanks in advance. Code: create or replace package pk_constants is lv_pound constant char(1) :='#'; -- for germany and europe countries ...... ..... end pk_constants; /
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs