Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Newbie questions regarding ORDS

TheLonelyDBAMar 11 2020 — edited Mar 12 2020

Hi,

I have installed APEX 19.2 (works) and now ORDS 19.4, I followed this document (Oracle 19c as db, running in a weblogic cluster, three nodes):

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-… https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-18C6F9AC-F6DF-45D9-9AE4-6668DF563466

Installation went fine and I have installed the application in WLS, but when trying to access statis images I get:

404 error

The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured

How to take this further?

1. How do I enable tracing and restart the APEX listener?

2. Do I need to do more things than what was included in the document?

3. The document refres to the images as "java -jar ords.war static <apex directory>/images"

I used /shared/apex_images/images

There are images both in /shared/apex_images/ and /shared/apex_images/images.

How to tell which one to use?

Regards

TheLonelyDBA

Comments

AndrewSayer

Max_parallel_servers can be different on each of your instances. The DOP=6 looks to real Be Reporting That you’re using 6 instances.

The majority of the time taken in this execution doesn’t seem to have anything to do with the amount of parallelism, it looks to be mostly due to the “TABLE ACCESS STORAGE FULL FIRST ROWS“ which seems to be executed 12 million times on line 18. This looks to be a result of your statement written using scalar subqueries with rownum filters in them - forcing a row-by-row (slow by slow!) type of plan that doesn’t take advantage of bulky techniques that would fly through this work in parallel.

If you share your SQL, I’m sure we can suggest a much better way to write it.

Jonathan Lewis
Answer

Which version of Oracle ?

What does the query look like.

Key point to note about your plan, it shows 3 "DFO trees", and each DFO tree could run at a different DOP. (this blog note of mine is reading)

On top of that there are cases where a scalar subquery will report the PX slaves in the SQL Monitor summary once for EVERY execution of the aubquery,

Working backwards from the plan I think your query might be something like:

update table t1 set ...

where exists (parallel subquery 1)

and/or exists (parallel subquery)   -- I suspect OR, otherwise I think you'd see two unnested subquery and a pair of hash join.

You'll notice that operation 15 reports 142K starts - which is the right ballpark for reporting 290K summary lines if that subquery runs parallel 2.

This behaviour is a bug - and I may have written about it before - that appeared then disappeared in a relatively recent version of Oracle. I'll see if I can find the note.

Regards

Jonathan Lewis

Marked as Answer by Bhavani Dhulipalla · Sep 27 2020
Bhavani Dhulipalla

Hi Andrew -Thank you for the help -

Version is 12.1 and below is the SQL TEXT -

UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

SET

    scrpt_tbl.rx_nbr = NULL,

    scrpt_tbl.days_supply_qty = 0

WHERE

scrpt_tbl.rowid IN (

    SELECT

        ROWID row_id

    FROM

        pgn_scrpt_tmp_tbl

    WHERE

            pgn_clas IS NULL

        AND (

                EXISTS (

                    SELECT

                        1

                    FROM

                        pci_vw.rule_config_file_parm config

                    WHERE

                            config.config_file_id = 101

                        AND

                            config.config_parm1_id = 1001

                        AND

                            config.config_parm1_val = rxc_pat_id

                        AND

                            config.curr_ind = 'Y'

                )

            OR

                EXISTS (

                    SELECT

                        1

                    FROM

                        pci_vw.rule_config_file_parm config

                    WHERE

                            config.config_file_id = 104

                        AND

                            config.config_parm1_id = 1003

                        AND

                            config.config_parm1_val = store_nbr

                        AND

                            config.curr_ind = 'Y'

                )

            OR

                dea BETWEEN 2 AND 5

            OR

                trunc(months_between(

                    TO_DATE('06012019','MMDDYYYY'),

                    trunc(birth_dt)

                ) / 12) <= 18

            OR

                trunc(months_between(

                    TO_DATE('06012019','MMDDYYYY'),

                    trunc(birth_dt)

                ) / 12) >= 120

        )

)

Thanks

Bhavani

Bhavani Dhulipalla

hi Jonathon --Thank you for helping me with this request -

The Version is 12.1 and I just updated the SQL_TEXT as well -

we want to find the Actual slaves allocated for all the application query's  and I was basically using the SQL Monitor report Allocated Slaves section to find it  -

The Reason why we want to find Actual slaves:

we are Planning to use the DBRM to limit the DOP for the application and also want to Limit the Application only to 3 nodes where as the same application is using 8 nodes currently -That means the parallel query's are also limited to only 3 nodes despite the setting of parallel_force_local to TRUE Value  

The parallel_max_server value is 204 currently and application currently can use 8*204 parallel slaves if it wants -but after the DBRM since we are limiting to only 3 nodes ,it can only use 3*204 slaves --

So we want to see current utilization of parallel slaves and see if we can increase the value of parallel_max_servers  if required -

Question:

Is SQL Monitor report is reliable way of Finding the Slaves allocated?

is there any other best method to do this?

Thanks

Bhavani

Jonathan Lewis

Question:

Is SQL Monitor report is reliable way of Finding the Slaves allocated?

Clearly it is not reliable since you've said your maximum possible number of PX processes is 8 * 204 and the report says that is has allocated 290,084 which is rather larger than the limit.  On the other hand I have explained that the report can count the number of processes once for every execution of the subquery, so that would explain the extreme excess, and it would be something that you want to investigate and modify the code to avoid since allocation and de-allocation of the same small set of slaves MIGHT REALLY be happening and increasing the workload dramatically.

is there any other best method to do this [find number of slaves allocated] ?

I suspect there is no method that will guarantee to give the right answer in all cases - every query with multiple DFO trees probably needs to be examined individually and watched while it is running if you want to find the maximum number of parallel servers sets (and slaves) it ever has allocated. There is the report_sql_monitor output which has a few holes, there is the v$pq_tqstat view (after you run a query) which has a few holes - both improve with version; and every time you upgrade you find that Oracle has enhanced parallel query processing to reduce resouse wastage.  (You don't say whether your version of Oracle is 12.1.0.1 or 12.1.0.2 - but the plan suggests it might be 12.1.0.1 - and in 12.2.0.1 you might find that Oracle either does some clever tricks with sub query unnesting or uses "single server  parallel access" or "expression evaluation" to eliminate the 2nd and 3rd DFO tress entirely).

Possibly counting the number of p0xx lines in the Parallel Execution Details section would be accurate enough - though I think in 11.2.0.4 I managed to get a report with thousands of lines (matched the value of Servers Allocated.

Regards

Jonathan Lewis

AndrewSayer

Bhavani Dhulipalla wrote:

Hi Andrew -Thank you for the help -

Version is 12.1 and below is the SQL TEXT -

  1. UPDATEpgn_scrpt_tmp_tblscrpt_tbl
  2. SET
  3. scrpt_tbl.rx_nbr=NULL,
  4. scrpt_tbl.days_supply_qty=0
  5. WHERE
  6. scrpt_tbl.rowidIN(
  7. SELECT
  8. ROWIDrow_id
  9. FROM
  10. pgn_scrpt_tmp_tbl
  11. WHERE
  12. pgn_clasISNULL
  13. AND(
  14. EXISTS(
  15. SELECT
  16. 1
  17. FROM
  18. pci_vw.rule_config_file_parmconfig
  19. WHERE
  20. config.config_file_id=101
  21. AND
  22. config.config_parm1_id=1001
  23. AND
  24. config.config_parm1_val=rxc_pat_id
  25. AND
  26. config.curr_ind='Y'
  27. )
  28. OR
  29. EXISTS(
  30. SELECT
  31. 1
  32. FROM
  33. pci_vw.rule_config_file_parmconfig
  34. WHERE
  35. config.config_file_id=104
  36. AND
  37. config.config_parm1_id=1003
  38. AND
  39. config.config_parm1_val=store_nbr
  40. AND
  41. config.curr_ind='Y'
  42. )
  43. OR
  44. deaBETWEEN2AND5
  45. OR
  46. trunc(months_between(
  47. TO_DATE('06012019','MMDDYYYY'),
  48. trunc(birth_dt)
  49. )/12)<=18
  50. OR
  51. trunc(months_between(
  52. TO_DATE('06012019','MMDDYYYY'),
  53. trunc(birth_dt)
  54. )/12)>=120
  55. )
  56. )

Thanks

Bhavani

You would probably get away with running this with much fewer DOP if you rewrote those ORs. Either you could do something like 3 separate deletes:

UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

SET

    scrpt_tbl.rx_nbr = NULL,

    scrpt_tbl.days_supply_qty = 0

WHERE  pgn_clas IS NULL

AND    EXISTS (  SELECT

                     1

                 FROM

                     pci_vw.rule_config_file_parm config

                 WHERE

                         config.config_file_id = 101

                     AND

                         config.config_parm1_id = 1001

                     AND

                         config.config_parm1_val = rxc_pat_id

                     AND

                         config.curr_ind = 'Y'

             )

UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

SET

    scrpt_tbl.rx_nbr = NULL,

    scrpt_tbl.days_supply_qty = 0

WHERE  pgn_clas IS NULL

AND    EXISTS (

               SELECT

                   1

               FROM

                   pci_vw.rule_config_file_parm config

               WHERE

                       config.config_file_id = 104

                   AND

                       config.config_parm1_id = 1003

                   AND

                       config.config_parm1_val = store_nbr

                   AND

                       config.curr_ind = 'Y'

           )

 

UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

SET

    scrpt_tbl.rx_nbr = NULL,

    scrpt_tbl.days_supply_qty = 0

WHERE  pgn_clas IS NULL

AND    (

           dea BETWEEN 2 AND 5

       OR

           trunc(months_between(

               TO_DATE('06012019','MMDDYYYY'),

               trunc(birth_dt)

           ) / 12) <= 18

       OR

           trunc(months_between(

               TO_DATE('06012019','MMDDYYYY'),

               trunc(birth_dt)

           ) / 12) >= 120

)

Or you could try to use union all in the exists filters. Those changes should allow a hash join to be used for the existance check rather than having to execute them once per row (which just won't scale).

1 - 6

Post Details

Added on Mar 11 2020
3 comments
290 views