Tom Kyte

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

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>

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

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

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.

<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.

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!

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!

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

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; /

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.

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.

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>

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?

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.

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>

Dear sir Thank you for your support We are using 18c XE database and it is not able to connect from Delphi application. During it connection it says SQLORA8.DLL failure. These drivers are being used by Delphi for Oracle DB connection and post...

Hi Team, I would like to understand the scenario, where we have: - TDE enabled at tablespace level. - RMAN encryption turned off Will the backup be still encrypted? As per oracle document - Doc ID 819167.1 First paragraph in Solution se...

Hi AskTom team, Originally I had a very nice question for you, but I forgot, but this one is equally important to me. My question is about why does my recompile "SQL> @?/rdbms/admin/utlrp.sql" take forever to return? there is no real users on my d...

I used alter "session set current_schema = xx" to change schema in a block of code. It is not working and I suspect that the schema has not been changed for some reason. How can I query the system to return the current schema I am in? Is there a p...