autotrace | output along with other telemetry on the process | Runtime Evironment library

My purpose is to write an insert statement that adds a random value from a given string, more specifically if the person is married, single or a widower. I needed to first populate my table with hundreds of random entries because I need to run Autotrace on it and check its optimization. In any event, I first created the table, then populated all the columns, except for the one concerning marital status. Then I wanted to write a different INSERT statement just for that because I thought it might be easier then putting it into the plSQL block I made to populate the table. Here is my code:

I am working to further optimize the following query. I have already had a lot of success optimizing the query up to this point, but now I am looking for help from others for ideas. I appreciate your help.

The following query takes over 200 seconds to complete yet the two individual WITH clause queries (receiving_1 and receiving_2) that make up the "UNION ALL" take less than 4 seconds each.

• receiving_1 -- 58 recs in 3.4 secs; cost 295 (no UNION)
• receiving_2 -- 0 recs in 3.8 secs; cost 295 (no UNION)
• UNION ALL of 1+2 -- 58 recs in 203.1 secs; cost 300 (UNION ALL)

I was hoping "rownum" would help optimize the "UNION ALL", but it did not. This attempt was based on a note from AskTom --> Files --> SQLTechniques.zip (between 61 and 72)

When you have two queries that run light speed separately But not so together Generally a mixed “CBO/RBO” problem Use of RBO with a feature that kicks in the CBO Rownum can be a temporary fix till all things are CBO

NOTE: One optimization that avoids this issue is embedded replacement parameters or cursor parameters that greatly reduce the rows at key points in the query. However, because I need to run this query through a database link against a read-only database, the following options are not available for this query.

• dbms_sql over database link: query is too long; dbs_sql does not accept a lob over database link; also DBAs don't allow the running of any PL/SQL in the read-only database.
• pipelined function: pipelined function has known bug over database link between 19c and 11g so fails
• sys_refcursor via PL/SQL: cursors are not supported over database links
• set parameter values in PL/SQL package and use them in view: not allowed to execute any code in read-only database (I am still bugging the DBAs to allow this. This might be my best hope if I am not able to optimize the below query to run as a view.)

NOTE: In an attempt at providing a good test case with as little code as possible, the below SQL (4,584 chars, 88 line breaks) is a greatly simplified version of the actual SQL (93,683 chars, 2,014 line breaks) I am developing. The original SQL has already gone through several rounds of optimizations to bring the runtime down from over an hour to under 10 seconds. This latest challenge is that the SQL has to be rewritten as a view as opposed to a SQL file with embedded substitution parameters. The substitution parameters were key to the optimizations up to now. I am now refactoring the SQL to work as a view in Oracle 11gR2 to be called via a database link from Oracle 19c. A view has no parameters, obviously. The driving parameters (ledger, OU, period) will be WHERE clause conditions when querying the view.

Big picture: The actual SQL under development is an amalgamation of 6 report queries that share a lot of duplicated code. My work is to convert all six source report queries into an efficient extract for importing into a new database schema for reconciliation purposes.

SQL Developer v 18.4

Problem: Autotrace not displaying executions statistics in SQL Developer

I think DBA gave me all the required privilege, I can select from views and autotrace (f6) works. But somehow still not able to see execution statistics at right bottom of SQL developer windows pane.

SELECT on below view works

I'm analysing the performance of a query. The method I'm currently using is the below:

I hope anyone can help me. I am designing a model with some RTMV (Real Time Materialized Views) in Oracle 12.2. I create all the MV logs and define the refresh group as well as the jobs to do it. So far all good..

However the query that I need to run over them does not work because apparently using either DUAL , REGEXP_SUBSTR or CONNECT BY LEVEL is not compatible with the hint FRESH_MV. I need to use the hint because the query is done over the RTMVs, and that is the only way to get the real time data ( as far as I know ).

If the hint cannot be used, I would like to know how to rewrite the condition shown below in order to avoid using DUAL, CONNECT BY LEVEL and REGEXP_SUBSTR.

I'm new to Oracle and would like to know the way how to get more statistics in the case i'll describe below. I'm using SQL Developer.

First, I execute this:

i'm working with oracle pl/sql and i have a stored procedure with this query, and it is a bit convoluted, but it gets the job done, the thing is it takes like 35 minutes, and the sql developer Autotrace says that is doing a full scan even though the tables have their indexes.

So is there any way to improve this query?

I have to get the execution plan of a query, i did this:

I'm having issues with spooling out results from queries, more or less it shows always no real cost, shows rows as one and no cost or anything added, and not the statistics of the actual query.

I'm using this setup: