Skip to Main Content
Forums

Oracle Database Discussions

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!

High version count for INSERTS due to HASH_MATCH_FAILED with cursor_sharing = force

Vsevolod AfanassievMay 14 2016 — edited May 15 2016

12.1.0.2 on Linux, standalone instance

We are testing new -3-rd party application, it isn't using bind variables. During the first test cursor_sharing was set to default value EXACT, the rate of hard parses reaches 800 per second, and we saw events indicating issues with shared pool (library cache: mutex X, latch: shared pool, cursor: pin S wait on X). However the percentage of these waits was small, less than 3% of total waits.

During the second test we set cursor_sharing to FORCE. Waits on shared pool-related events were high:

37% of total waits for 'cursor: pin S wait on X'

21% for 'library cache lock'

V$SQL shows several statements with hundreds of child cursors, mostly single-row INSERTS:

INSERT INTO schema.tableName (column1,columns2,..)

VALUES (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",timestamp:"SYS_B_7",SYSTIMESTAMP,:"SYS_B_8")

These cursors aren't being shared, every execution created new child cursor.


V$SQL_SHARED_CURSOR has 'Y' for HASH_MATCH_FAILED.This is the definition: No existing child cursors have the unsafe literal bind hash values required by the current cursor.

What does it mean?

There is nothing special about these tables, they have VARCHAR2, NUMBER, and DATE columns, no triggers.
This post has been answered by Jonathan Lewis on May 14 2016
Jump to Answer

Comments

EdStevens

How was it your installation/creation of the database did not prompt you for initial password for SYS and SYSTEM?
At an OS prompt, do the following (example using c:> prompt for windows)

C:\> set ORACLE_SID=<the name of your database>
C:\> sqlplus / as sysdba

# above command starts sqlplus cli and uses os authentication to connect you to the database as SYS, with SYSDBA authority. You can now ALTER USER .... to change any passwords you want. But you really shouldn't be connecting as SYS unless you absolutely need that level of unrestricted access. And even then, you should be connecting locally as shown above. For most DBA work, you should create another user (I use 'xxxxdba', where xxxx is the acronym for my organization) and 'GRANT DBA TO xxxxdba'.
Why were you installing 12c? It's already aged and on its way out the door. You should be looking at 19c, at the oldest.

User_X6ZQK

Thanks for the prompt reply EdStevens
No, it did not ask any SYS password. But it ask the Administrative password.
I need 12c because of other software dependency.
I have run the below command :

 sqlplus / as sysdba

Now I want to create the two new admin users with admin rights.
NOTE : I have not created any database yet.
is this right way : ?

create user c##user1  identified 1234;

How can I grant the user1 admin right ? and how can i check the is this admin user or not ?
After creation of admin user, i want to login in SQL Developer.

EdStevens

Now you say
NOTE : I have not created any database yet

But earlier you said
During the installation I have unlock just one user and gives the password.
How were you able to unlock a user and give a password if you have not created a database? Of course you cannot 'unlock a user" if you "have not created a database". So which is it?
NOte that while installting oracle and creating a database are two separate things, by default the installer will also create a database, if you make that selection.
No, it did not ask any SYS password. But it ask the Administrative password.
That is the password for SYS. The default "administrative" user is SYS.
One other thing, is this a non-multitenant database, or is it a Container database (CDB) with a Pluggable Database (PDB)? That will make a difference in how users are handled.

User_X6ZQK

Thanks for the reply EdStevens
Yes got your point here. during installation I have choose "Create and Configure Database" option.
I have change the "SYS" user password by using below command :

alter user SYS identified by 1234;

I have tried to connect with "SYS" user in SQL Developer . but message appeared invalid password.
It is Container database (CDB) with a Pluggable Database (PDB) database because I have select the below option :
pdb.JPGNow I want to create a user with admin right and want to login with this user in SQL developer.

I am able to connect in SQL developer with "System" user.

EdStevens

So what are the connection properties you defined in SQL Dev? Did you define it as BASIC or TNS? What are the values you supplied for the other fields? Are you wanting to connect to the CDB or the PDB?

User_X6ZQK

Thanks for the reply @edstevens1
I pick the Basic option from connection type.

EdStevens

Thanks for the reply @edstevens1
I pick the Basic option from connection type.
This is like pulling teeth. I also asked "What are the values you supplied for the other fields? Are you wanting to connect to the CDB or the PDB?"

EdStevens

Well, you just revived a thread that is dormant for over three months. Let me ask you this .... you said to "Create a new user with an _administrator_ password" (emphasis mine). So, what exactly is an "administrator" password? At the time you create a user and assign it a password, how would you distinguish an "administrator" password from any other password?

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2016
Added on May 14 2016
#general-database-discussions
9 comments
2,443 views