The reason for oracle creating a new child cursor is Bind Mismatch, So

What is Bind Mismatch?

What are the reason which could lead to Bind Mismatch ?

When there is a change in Bind Variable definition associated with the SQL it leads to a Bind Mismatch, example like change in the data type of the bind variable(like the above test case) or change in the size of the bind variable(oracle has a range for this bind size) leads to creation of new child cursor for the same SQL Text.

Lets confirm the Bind Mismatch as the cause of new child cursor by validating the actual data,

The above info clearly indicates that during the second run the bind variable’s data type is changed from Number to Varchar2 thus leading to Bind Mismatch and creation of new Child Cursor for the identical SQL Text.