The tutorial you linked uses the multitenant architecture with pluggable databases. I quickly read through the tutorial, and it does not cover this topic, but pluggable databases do not open automatically with the root container, unless specified otherwise. My guess is that you tried to connect the pdborcl
pluggable database. Run the below:
select name, open_mode from v$pdbs;
If you see pdborcl
, with open_mode
= MOUNTED
, issue the below:
alter pluggable database pdborcl open;
Now try connecting again. If your database version is 12.1.0.2, you can use the below after opening the pluggable database to make it start automatically the next time the whole database is restarted:
alter pluggable database pdborcl save state;
If your database version is 12.1.0.1, the above feature is not available, and you need to open the pluggable database each time manually, or create an after startup trigger in the database, for example:
CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/
The following quote is from your link, emphasis mine:
PARALLEL_MIN_TIME_THRESHOLD is the second initialization parameter that controls automatic DOP. It specifies the minimum execution time a statement should have before the statement is considered for automatic DOP. By default, this is 10 seconds. The optimizer first calculates a serial execution plan for the SQL statement; if the estimated execution elapsed time is greater than PARALLEL_MIN_TIME_THRESHOLD (10 seconds), the statement becomes a candidate for automatic DOP.
Another good resource is Using Default Parameter Settings, emphasis mine:
PARALLEL_MIN_TIME_THRESHOLD: The execution time, as estimated by the optimizer, above which a statement is considered for automatic parallel query and automatic derivation of DOP.
To answer your questions directly:
Suppose the parallel_degree_policy is MANUAL and the SQL statement contains a parallel hint or a defined parallel DDL value. Will the Oracle optimizer still check PARALLEL_MIN_TIME_THRESHOLD?
No, PARALLEL_MIN_TIME_THRESHOLD
will not be used. If you specify the degree of parallelism why would the query optimizer calculate a cost for the serial plan? What would it do with that information? You've already specified the DOP. This only makes sense in the context of automatic DOP.
Also, is the estimated execution or the actual execute time used in the calculations around the threshold limit?
This has to be the estimated time. Does it make sense for Oracle to possibly execute most of the query in serial and then switch to a parallel plan? As far as I understand it that will not happen, DOP is chosen at the beginning of execution for a data flow operation.
Best Answer
I solved it as follows:
sga_target=0
and save it.After :
Now start the database :